Hi. What does "did not work" mean? What were you expecting to get?
PS. Here's some info on what a recordset object is and what you can do with it. Perhaps if you could tell us what you're planning to do with the result of your SQL, we can recommend a more appropriate object or approach.
Then, my guess is Access cannot resolve Me.Name_Choose. Either that or there's some incorrect spelling in there somewhere. Double-check the spelling for [Category_Name] and [Company Name]. Otherwise, consider posting a small copy of your db, so we can take a look.
the code needs to be located in a function or sub in the class module for the form where the control Me.Name_Choose exists - perhaps the click event of a button
I'm trying to retrieve the value in the 'Category' field of the 'Sales Tax Table' table with a VBA SQL statement. The SQL statement matches the 'Name_Choose' (textbox) with the value in the field 'Company Name' in the 'Sales Tax Table' table. I need to place this value in 'Category_Name' (textbox). Below is what I have.
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT [Category] FROM [Sales Tax Table] WHERE [Company Name] = '" & Me.Name_Choose & "'"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
That is, presuming that [Company Name] is a field of [Sales Tax Table] and so is [Category]. You need the brackets for the domain and for the criterion because the field names have spaces in them. So the brackets are mandatory. Might get away with leaving off the brackets for [Category] since it shows no spaces in the name. Tera is right that you don't need SQL because a DLookup will do what you want from VBA.
Do we know from the limited code posted that the sql will only return one record? DLookup won't return more than one so maybe it's not useful here.
The error can be caused by several things but the root cause is that the vba side cannot resolve a reference that points to the container side (objects such as forms). There is more than one solution for your situation; one being to assign the criteria to a variable(s) and use the variable in the sql construct. Aside from the obvious, such as correcting mis-spelled references, you could call a query with defined parameters (parameters property sheet). Or you could define the parameters in code. If those don't appeal to you and a DLookup won't work, then I'm out of ideas for now.
EDIT: OK, so maybe one or two more
- Eval function on a form control reference sometimes works.
- as a test, you could try Openquery method since it's a select query. If it runs, that indicates to me that the .Execute method doesn't pre-process the sql ( as I've read), thus the references aren't evaluated first.
That is, presuming that [Company Name] is a field of [Sales Tax Table] and so is [Category]. You need the brackets for the domain and for the criterion because the field names have spaces in them. So the brackets are mandatory. Might get away with leaving off the brackets for [Category] since it shows no spaces in the name. Tera is right that you don't need SQL because a DLookup will do what you want from VBA.
I never use space in my fields naming and didn’t know we have to use breakets in Dlookup in this case. I thought its just for sql statements.
Thanks for the correction and reminder.
- as a test, you could try Openquery method since it's a select query. If it runs, that indicates to me that the .Execute method doesn't pre-process the sql ( as I've read), thus the references aren't evaluated first.
You can't 'run' or execute a SELECT SQL statement in VBA.
Do one of the following.
1. Create a query def or saved query using that sql and then open that for viewing
2. Create a form using that SQL as its record source. View the form
I can't see a syntax error in your DLookup. Does it just need wrapping in the Nz function to handle nulls?
