Search Button Not Finding Record and populating the text boxes on form. (1 Viewer)

Lah_01

New member
Local time
Today, 03:33
Joined
May 19, 2024
Messages
7
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

I’m here to help
Staff member
Local time
Yesterday, 19:33
Joined
Oct 29, 2018
Messages
21,641
If FName is a Text field, you'll need to enclose the value in quotes. For example:

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

Gasman

Enthusiastic Amateur
Local time
Today, 03:33
Joined
Sep 21, 2011
Messages
14,665
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.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Jan 20, 2009
Messages
12,866
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

Enthusiastic Amateur
Local time
Today, 03:33
Joined
Sep 21, 2011
Messages
14,665
Why are you even using a recordset, could be asked as well? Are the forms not bound?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:33
Joined
Feb 19, 2002
Messages
43,854
@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

Enthusiastic Amateur
Local time
Today, 03:33
Joined
Sep 21, 2011
Messages
14,665
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.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 03:33
Joined
Sep 21, 2011
Messages
14,665
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. :)
 

Users who are viewing this thread

Top Bottom