Solved Referencing a Field Name from a Query in VBA

[diannebcgray]

Hi,

I am struggling to reference a field name in a query in VBA. I want to use this field name to set my current form text box's value to that value when a client name is picked from a combobox. The query will look through my table and pick the client's information from the most recent record naming that client.

I know there are several other help pages on this:
First Example
Second Example
Third Example

But I still keep getting the error message

My two attempts doing this are:

Dim SavedQry As DAO.QueryDef
Dim rs As DAO.Recordset

Private Sub cboClientName_AfterUpdate()
Set SavedQry = CurrentDb.QueryDefs("Get Client Info - PIF New Project")
Set rs = SavedQry.OpenRecordset()
txtContactName = rs.Fields("Client Contact Name")
End Sub

and

Dim rs As New ADODB.Recordset

Private Sub cboClientName_AfterUpdate()
Set rs = CurrentDb.OpenRecordset("Get Client Info - PIF New Project")
txtContactName = rs.Fields("Client Contact Name")
End Sub

But both methods still give me the error.

The actual query content is as follows (created in Design View, and fully functional):

SELECT TOP 1 [Projects].[Client Contact Name], [Projects].[Client Address Unit], [Projects].[Client Street Name], [Projects].[Client City], [Projects].[Client Province], [Projects].[Client Postal Code], [Projects].[Client Phone], [Projects].[Client Extension], [Projects].[Client Alternative Phone], [Projects].[Client Fax], [Projects].[Client E-mail], [Projects].[Client Notes], [Projects].[Purchase Order Number], [Projects].Opened, [Projects].[Client Name]

FROM [Projects]

WHERE ((([Projects].[Client Name])=[Forms]![PIF - New Project]![cboClientName]))

ORDER BY [Projects].Opened DESC;

What do I need to do in my VBA to reference the query successfully?

 

[theDBguy]

Hi. Check out this generic function for avoiding that error.

Code Snippets

 

[diannebcgray]

Hi. Check out this generic function for avoiding that error.

Code Snippets

Wow, ok, that is an overwhelming amount of code. How do I apply it to my situation? I'm looking for as much simplicity as possible as programming is not my strongest suit.

 

[theDBguy]

Wow, ok, that is an overwhelming amount of code. How do I apply it to my situation? I'm looking for as much simplicity as possible as programming is not my strongest suit.

You would basically just copy and paste the code from the website to a Standard Module and save it with a name like Module1.
You would then replace all your code that goes like this:

Set rs = CurrentDb.OpenRecordset("Get Client Info - PIF New Project")

with something like this instead

Set rs = fDAOGenericRst("Get Client Info - PIF New Project")

Tip: Avoid using spaces in the names of your table or query or any other object

 

[diannebcgray]

You would basically just copy and paste the code from the website to a Standard Module and save it with a name like Module1.
You would then replace all your code that goes like this:

Set rs = CurrentDb.OpenRecordset("Get Client Info - PIF New Project")

with something like this instead

Set rs = fDAOGenericRst("Get Client Info - PIF New Project")

Tip: Avoid using spaces in the names of your table or query or any other object

Wow, it worked like magic! I got the behaviour I was looking for.

Thank you!

 

[theDBguy]

Wow, it worked like magic! I got the behaviour I was looking for.

Thank you!

You're welcome. Glad to hear you got it to work. Good luck with your project.