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.

 

[Pat Hartman]

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

It is not just spaces that should be avoided so let me tell you which characters to use since that is a much shorter list.
Letters A-Z and a-z
Numbers 0-9
Punctuation Character _ (the underscore)

That makes 63 of the 255 options valid for use in object and variable names.

The two common styles of naming:
CamelCase where the first letter of each word is capitalized.
the_underscore where words are separated with the underscore.

My personal preference is CamelCase for two reasons.
1. I can type everything in lower case. When I move to the next line, the internal text editor capitalizes the letters that were capitalized in the definition. This is an easy sanity check to identify typos. If Access doesn't properly case your name, there is a typo.
2. All those underscores are like fingernails on a chalkboard to me. I find them jarring. I only use underscores if I want to emphasize some part of a name the way Access does when it makes names for event procedures.