Variables and SQL

[James.uk]

I have no idea what I am doing here, as I have never used SQL with VBA before:

namelookup = "SELECT people.name FROM people WHERE people.name='personID';"
name = DoCmd.OpenQuery "namelookup"

What do I have to do in order to get the name from the query and assign it to the name variable?

 

[TessB]

Dim strSQL as String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [people]"
strSQL = strSQL & " WHERE [ID] like Me![ID]
strSQL = strSQL & " ORDER BY [name];"
Set rst = dbs.OpenRecordset(strSQL)

May be more appropriate if you have the ID on the form. Or am I totally missing what you are trying to do?

 

[James.uk]

Just treat this as seperate from the other post I made, I'm trying out something else.

All I want to do is assign the result of an SQL Select query to the variable 'name'. My actual query will work, won't it?

 

[Pat Hartman]

No. The syntax is not correct.

namelookup = "SELECT people.name FROM people WHERE people.personID = " & personID & ";"

I am assuming you want to select based on personID and personID is numeric.

You then need to open a recordset that you can process with VBA. The OPenQuery will open a query in datasheet view which is not the same as opening a query that you can process via code. So look for sample code for "OpenRecordset".

BTW, what you are doing is overkill. It would be far simplier, and more efficient, to just modify the recordsource of your form or report to join to the people table.

FYI, "Name" is inappropriate as the name of a column since there is a Name property for all VBA objects and using this field in code could cause strange happenings that don't even trigger errors. In general, you should avoid any name that is the name of a function, property, or method in addition to the avoidance of embedded spaces and special characters.

 

[James.uk]

Ok, I have had just about enough with VBA and SQL and I'm looking for an easier route to do this, probably simple, task.

What I have is a text box in which an ID is entered. A button is clicked and then I want to find the name that corresponds to the ID in a table and return it as a label caption. What is the easiest route to doing this?

 

[James.uk]

...I finally cracked it after a billion years by using a Dlookup. Now I have a new question:

How would I incorporate a dlookup into an if statement and return an error message if the id does not exist?

 

[Rich]

Just use a combo box, you don't need to use DLookUp. Use the NotInList event to verify new entries

 

[James.uk]

Just use a combo box, you don't need to use DLookUp. Use the NotInList event to verify new entries

I'm happy with using Dlookup. I'm not spending another 10 hours trying to get something else to work. I just need to display an error message if the id I am entering in as the criteria of the Dlookup doesn't exist.

 

[Rich]

The combo box Wizard will create the box for you in seconds, and a combo box will be much faster than DLookUp, which incidentally most would only use if there was no alternative .