Learning open recordset

[lawdy]

I have a form with a button 'btnOpenRecordset' with the folling code:

Private Sub btnOpenRecordset_Click()
Dim rs As Recordset


Dim strLastName As String


Set rs = CurrentDb.OpenRecordset("Select * tblPeople where fTxtFirstName=Loy")
While Not rs.EOF
strLastName = "Last Name: " & rs!fTxtLastName
rs.MoveNext
Wend

rs.Close
Set rs = Nothing
End Sub

This produces the error '3075' - Syntax error (missing operator) in query expression'*tlPeople where fTxtFirstName=Loy'

Can you give me some help?

[Welshgasman]

Where are you getting that data FROM
Also best to qualify the recordset these days with DAO or ADO?

[June7]

Need FROM keyword and apostrophe delimiters: "SELECT * FROM tblPeople WHERE fTxtFirstName='Loy'"

Could use DLookup instead of opening a recordset.

strLastName = Nz(DLookup("fTxtLastName", "tblPeople", "fTxtFirstName='Loy'"), "")

But why isn't lastname already available on form along with firstname?

[lawdy]

You solved my problem regarding the query and gave me a better way of doing it. I like the dLookup method. For your question regarding the last name, this is just and example, but on my real db there could be more than one first name. Thank yoy for your held.

[June7]

Then how would code know which first name should be matched to return correct last name?

And if you want first name input to be dynamic, concatenate reference to control.

strLastName = Nz(DLookup("fTxtLastName", "tblPeople", "fTxtFirstName='" & Me.tbFirstName & "'"), "")

Really, this VBA should not even be necessary with good design.

[orange]

lawdy, you have been given advice and have been asked questions to help with your project. As always, it is better to provide an overview of the entire process underlying your specific question and/or provide an example of what you have and what you need. There are often many options to achieve something in Access -- but readers need to see the issue in context in order to provide more focused information.