Passing query results to vba variables

[drjulian]

Let me start by saying that I have been new to Access and been away from VB for MANY years. I am just getting back into it for this project, so I am basically a neophyte, again.
The project is as follows: I need to query an SQL database, assign the result of the query to variables, and create a text document, which includes the data in the assigned variables and output to a file.
So far, I have been able to link the sql file and create the query, and create and output the text document; however, I my failure is in assigning the query results to variables in VBA.
I am able to assign the field information to variables; however, that does not give me the filtered results.

This is my query:

SELECT DISTINCT dbo_tblTicketsRow.fldTicketID, dbo_tblClients.fldClientID, dbo_tblTicketsSummary.fldTicketStatus, dbo_tblClients.fldFirstName, dbo_tblClients.fldLastName, dbo_tblClients.fldHomePhone, dbo_tblTicketsSummary.fldDateScheduled, dbo_tblTicketsRow.fldStartTime, dbo_tblClients.fldCellPhone
FROM (dbo_tblClients INNER JOIN dbo_tblTicketsSummary ON dbo_tblClients.fldClientID = dbo_tblTicketsSummary.fldClientID) INNER JOIN dbo_tblTicketsRow ON dbo_tblTicketsSummary.fldTicketID = dbo_tblTicketsRow.fldTicketID
WHERE (((dbo_tblTicketsSummary.fldTicketStatus)="open") AND ((dbo_tblTicketsSummary.fldDateScheduled)=[Enter Date]));

Would someone advise the best way to assign this filtered query data to variables?

Thank you

 

[theDBguy]

Hi. The result of a query would be a set or records (a recordset, in other words), so you would use a Recordset object to assign it to a variable. Is that what you're asking?

 

[drjulian]

Thank you for your reply.
I think that is what I am asking. Perhaps you would direct me to some examples of recordset in this context? I am new to access and re-learning VB. I think I am having difficulty understanding how they interact.

 

[theDBguy]

Thank you for your reply.
I think that is what I am asking. Perhaps you would direct me to some examples of recordset in this context? I am new to access and re-learning VB. I think I am having difficulty understanding how they interact.

Something like this?

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TableName", dbOpenSnapshot)

 

[Cronk]

Depending on your data, the recordset returned from your query could have none, one or many records. Because you have selected a number of fields in your query, each record that exists in the recordset would have the contents of the the fields selected in that recordset. For example, the first field is fldTicketID and the second is fldClientID.

Search for and read up on looping through recordsets. That might enable you to ask more incisive questions to get what you want.

 

[The_Doc_Man]

Let me also suggest a feature of the forum that might be helpful. You can use the SEARCH button (top menu bar, far right) to look for things. Like using the "Advanced search" for the phrase "Assign value from recordset" - which led me to this link:

Assign a value from select to a variable

so i have a simple select query which selects one value from a table. how do i then pass the one value to a variable ? the query in question is highlighted.

www.access-programmers.co.uk

In it, there is an example of opening a recordset and using VBA to grab record values and (in this example) place them on a form. You would of course do something different, but using our search sometimes helps very quickly.

When looking up a single value, a DLOOKUP might help. When looking up multiple fields, a recordset will be more efficient - and more conducive to a loop if you doing the same thing for a list of patients.

 

[Pat Hartman]

It is quite likely that you don't actually need to save the data from the table to variables as an intermediate step before using them to create a document. You didn't mention Word automation but this might give you some ideas.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk