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?
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