Search Button Not Finding Record and populating the text boxes on form.

[Lah_01]

Hi all,

I need a form to populate once an option is chosen from a listbox.

What I having working:
User Types into a textbox
The list box filters based on value in the text box
The user chooses item from the list
The text box now matches this

Where I am experiencing problems:
The user clicks on the search button and the form populates with the correct record based on the value in the textbox.

Code I have:

Private Sub cmd_UpdateSearch_Click()
Dim rst As DAO.Recordset
Dim strsql As String
strsql = "Select * from Mastertbl where FName = " & Search.Value & ""
Set rst = CurrentDb.OpenRecordset(strsql)
If rst.EOF Then
MsgBox " Not in database"
FirstName.Value = Nothing
LastName.Value = Nothing
Number.Value = Nothing
Email.Value = Nothing
Address.Value = Nothing
Else
FirstName.Value = rs.field("First Name")
LastName.Value = rs.field("Last Name")
Number.Value = rs.field("Phone Number")
Email.Value =rs.field("Email Address")
Address.Value = rs.field("User Address")
End If
rst.Close
Set rst = Nothing
End Sub

I keep getting a run time error Runtime Error 3075: Syntax Error (missing operator) in query expression '[FName] = Sarah'

When I click debug is shows me this line:
Set rst = CurrentDb.OpenRecordset(strsql)

What is wrong here - please explain in lay man terms if possible.

Thank you in advance.

 

[theDBguy]

If FName is a Text field, you'll need to enclose the value in quotes. For example:

strsql = "select * from mastertbl where fname='" & me.search & "'"

 

[Gasman]

You put a quote after your control name but not before.
Always debug.print your sql string until you get it correct, then either comment out that line or remove it.

Please put your code within code tags, make it easier to read and those single quotes stand out better.

 

[Galaxiom]

So how is your code going to work when you have more than one person with the same FName?
Looks like you will only ever see the first one.

 

[Gasman]

Why are you even using a recordset, could be asked as well? Are the forms not bound?

 

[Pat Hartman]

@Lah_01 you are a new poster so welcome. If you are new to Access, you might want to watch a few videos posted by Crystal or Richard Rost. I am not allowed to post links to promote their work so you need to rely on google. Both have beginner videos that will show you how to create a bound form.

Watching some of these videos is also important if you are an experienced developer because Access is a RAD (Rapid Application Development) tool and if you don't understand the purpose of bound forms you miss out on the best feature of Access.

 

[Gasman]

I am not allowed to post links to promote their work

Really?, I do it all the time, not paticularly from them, just a quick search on YouTube.

 

[Pat Hartman]

Really?, I do it all the time

Jon objects. Not sure why but he joined a conversation a few days ago and said to not do it.

 

[Gasman]

Jon objects. Not sure why but he joined a conversation a few days ago and said to not do it.

Oh, thanks for the heads up. I have not been told off yet by anyone.

 

[CJ_London]

 

[Lah_01]

So how is your code going to work when you have more than one person with the same FName?
Looks like you will only ever see the first one.

I don't have more than one person with the same name - the FName was just an example its not the actual data that is in that field. There can be no duplicates in the field i am using.

 

[Lah_01]

Why are you even using a recordset, could be asked as well? Are the forms not bound?

The form is bound.

I think I just got frustrated because I couldn't figure out the code for the search button (on click) after the text box matches the users choice from the list box

If any one could help me with this I would very grateful.

 

[Gasman]

I could not do better than Allen Browne

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com

Please tell us the actual data, not make up examples, poor examples at that.

Social Security Number would have been a better example?

 

[Pat Hartman]

An easy way to have an unbound combo as a search box on a bound form is to bind the form to a query with criteria:

Where SomeField = Forms!yourform!YourCombo

Then in the AfterUpdate event of the combo, you requery the form.

Me.Requry.

So --- one line of code and a query with a where clause is all it takes.