VBA Select Query into String for use with automatic population of web page form

I am trying to:

Run a select query on an Access DB using VBA (this part is simple), populate a string with the results of the query (one field, but n results) with each result separated by a carriage return.

it's going to be a list of order numbers e.g:

1234567
2345678
3456789
9876543
etc

I then open a web site, wait for it to load, then 'paste' in the string results. Then press a search button (I actually have this part working).

What I can't figure out is how to get the results of the SQL query into a string. Right now I have them in a Variant and I can't use that with my web site paste and it won't convert into a string using Cstr.

Dim IE As Object
Dim OrderNos As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim OrderArray As Variant
Dim OrderString As String

'set up the SQL query in easy to swallow lumps.
strSQL = "SELECT [Short Term Storage].[Order no]"
strSQL = strSQL & "FROM [Short Term Storage]"
strSQL = strSQL & "WHERE (([Short Term Storage].[Sales Rep Name] Is Null And [Short Term Storage].[Status]<>'SHIPPED'));"

'set the variable to the results from SQL query
Set dbs = CurrentDb
Set OrderNos = dbs.OpenRecordset(strSQL)

OrderArray = OrderNos.GetRows()

I think I either need to use a For Each loop or a Do Until EOF loop.

How can I get each row of the array into a string, then add a vbcrlf, then the next row of the array until EOF?

Answers


I think you need something like:

Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset(strSQL)

Do Until rs.EOF = True
        OrderString = OrderString & rs!FieldName & vbCrLf
        rs.MoveNext
Loop

Need Your Help

server side RemoteService implementation?

java google-app-engine gwt

I'm really impressed with the functionality and capability found in the GWT async RPC mechanism which defines