ok, i have the form all set up, but i've run into another snafu. i want to incorporate a sort of lookup function so that when the user inputs a social security #, or selects a name from the list, all of the data boxes are filled in according to the bound table.
info update
- Thread starter bpaquette
- Start date
Mr. Wizard put this code behind a form I use to do the same thing.
Private Sub cboSocialID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SocialID] = " & Str(Me![cboSocialID])
Me.Bookmark = rs.Bookmark
End Sub
Private Sub cboSocialID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SocialID] = " & Str(Me![cboSocialID])
Me.Bookmark = rs.Bookmark
End Sub
i wish i would have paid attention in highschool VB.
Private Sub SSAN_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SSAN] = " & Str(Me![cboSSAN])
Me.Bookmark = rs.Bookmark
End Sub
the object cbSSAN...what is that exactly? it brings up an error msg upon running.
Private Sub SSAN_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SSAN] = " & Str(Me![cboSSAN])
Me.Bookmark = rs.Bookmark
End Sub
the object cbSSAN...what is that exactly? it brings up an error msg upon running.
2 things:
1) you mean me.RecordsetClone
and not Me.Recordset.Clone
2) taking into account a socialsecurity # might be a string...
For a string use:rs.FindFirst "[SocialID] = '" & Me![cboSocialID] & "'"
Regards
Edit: that was in regards to Jon's response
---- To your question
the cboSSAN should be the name of the combo (cbo) box where you fill in your Social security number. If its a textbox: txtSSAN would be the logical choice.
1) you mean me.RecordsetClone
and not Me.Recordset.Clone
2) taking into account a socialsecurity # might be a string...
For a string use:rs.FindFirst "[SocialID] = '" & Me![cboSocialID] & "'"
Regards
Edit: that was in regards to Jon's response
---- To your question
the cboSSAN should be the name of the combo (cbo) box where you fill in your Social security number. If its a textbox: txtSSAN would be the logical choice.
Last edited:
there comes a time in every mans life where he has to admit he has no idea what he is doing. now is one of those times. the suggestions and code posted; while i'm sure were entirely adequate to accomplish my goal; didn't work. Undoubtedly due to my lack of experience in either MS acess or VBA.
If anyone could put their patience to the test and try their hand at assisting me again, I'd be immeasureably appreciative.
thanks!
If anyone could put their patience to the test and try their hand at assisting me again, I'd be immeasureably appreciative.
thanks!
Maybe try from scratch. Put an unbound combo box on your form. In the Row Source Type, select table/query and in Row Source, type: SELECT [tblYourTable].[SSAN] FROM tblYourTable;
Then, in the AfterUpdate event, I hace this code:
Private Sub cboSSAN_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SSAN] = " & Str(Me![cboSSAN])
Me.Bookmark = rs.Bookmark
End Sub
This works great for me as I have it here. I copied directly from my code and only changed the field names.
I'm stumped other than this.
Then, in the AfterUpdate event, I hace this code:
Private Sub cboSSAN_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SSAN] = " & Str(Me![cboSSAN])
Me.Bookmark = rs.Bookmark
End Sub
This works great for me as I have it here. I copied directly from my code and only changed the field names.
I'm stumped other than this.
thank you namlian!
one quick question, though. i tried implementing this code into my form, and i got a "user type not defined" error upon compiling. it was on the second line, what looks like a declaration/value assignment, Dim rs As DAO.Recordset. i then just changed the table sources on the form you sent me, and i get the same error. the code works fine, as long as i dont touch it
is there something different i need to do in order for it to wokr using my sources?
one quick question, though. i tried implementing this code into my form, and i got a "user type not defined" error upon compiling. it was on the second line, what looks like a declaration/value assignment, Dim rs As DAO.Recordset. i then just changed the table sources on the form you sent me, and i get the same error. the code works fine, as long as i dont touch it
is there something different i need to do in order for it to wokr using my sources?
B.
You have a references problem.
Get your code into design view.
Choose Tools --> References from the menu bar.
Scroll Down the list until you find: Data Access Objects
Check it and promote its priority as high as it will
go with the arrows on the screen.
Now you should be OK.
Wayne
You have a references problem.
Get your code into design view.
Choose Tools --> References from the menu bar.
Scroll Down the list until you find: Data Access Objects
Check it and promote its priority as high as it will
go with the arrows on the screen.
Now you should be OK.
Wayne
Woo promoting DAO worked just fine. but now i get sthng else :\
datatype mismatch on
rs.FindFirst "SSAN = " & cboSSAN
is that a syntax error on my part? -i- can't see why, but that's not too hard to believe
anyone know this off the top of their heads?
datatype mismatch on
rs.FindFirst "SSAN = " & cboSSAN
is that a syntax error on my part? -i- can't see why, but that's not too hard to believe
anyone know this off the top of their heads?
beautiful. perfect. now to figure out how to make that work with a list box
where did you all learn access? i checked out some web tutorials but i was wondering if there was a be-all-end-all of access tutorials/online classes
where did you all learn access? i checked out some web tutorials but i was wondering if there was a be-all-end-all of access tutorials/online classes
I learned access all over the place....
Listbox works exactly the same. Just 3 different things:
number
Text (string)
date (time)
That in the notations:
Number: "SomeField = " & field
Text: "SomeField = '" & field & "'"
or "SomeField like '*" & field & "*'"
or "SomeField = "& Chr(34) & Field & chr(34)
or "SomeField = """ & field & """"
(last two are the same chr(34) = ")
Date: "SomeField = #" & format(field;"mm-dd-yy") &"#"
(maybe replace ; by , )
Thats really all that is to it....
Regards
Listbox works exactly the same. Just 3 different things:
number
Text (string)
date (time)
That in the notations:
Number: "SomeField = " & field
Text: "SomeField = '" & field & "'"
or "SomeField like '*" & field & "*'"
or "SomeField = "& Chr(34) & Field & chr(34)
or "SomeField = """ & field & """"
(last two are the same chr(34) = ")
Date: "SomeField = #" & format(field;"mm-dd-yy") &"#"
(maybe replace ; by , )
Thats really all that is to it....
Regards
my list box is bound to 3 columns. when writing the after_update code for it, do i have to list all the field names in findfirst?
also the syntax i have now is
rs.FindFirst "LASTNAME = '" & lstLastname & "'"
i'm assuing last name is the name of the field in the src table and lstLastname is the name of the list box itself. is this accurate?
also the syntax i have now is
rs.FindFirst "LASTNAME = '" & lstLastname & "'"
i'm assuing last name is the name of the field in the src table and lstLastname is the name of the list box itself. is this accurate?
No go, a list box can only have 1 bound column (all others are just for visual effect...
General good pratice for a lookup thingy is to have a box (list or combo) with a bound column (which is hidden (width 0)) on the PRIMARY KEY and showing your important data, FirstName, LastName
Then do a find on the primary key...
The name of the (list)box is whatever you make it in the properties.
Regards
