Combo Box Lookup 1

[gwendon]

I'm using a combox with a unique identifer, Social Security Number, (along with the associated Last Name and First Name). This works great for locating records, but if I input an SSN in my combo box that does not exist in my table I get some un-associated record.

My question is: Is there away to use the combo box for lookup and if no record exists with that SSN, to accept the new SSN automatically as a new record on my form?

 

[grtammi]

gwendon:

If you set the LimitToList property to Yes, you can then make use of the NotInList event, which will allow you to detect when a new value has been entered, similar to as follows:

(NOTE: Below only works if your RowSourceType is Table/Query)

Private Sub MyCBO_NotInList(NewData As String, Response As Integer)

' Add item to combo list
' First, declare variable to hold row source of this
' combo box - I do this because the Requery method is
' quirky
Dim cRowSource : cRowSource = MyCBO.RowSource
' now, update the recordset that the RowSource is based upon.
DoCmd.RunSQL "INSERT INTO YourTable (SSN) VALUES ('" & NewData & "');"
' new SSN inserted - now reset the row source - this effectively does a requery
MyCBO.RowSource = cRowSource
' set the response so the db knows we added something
Response = acDataErrContinue

End Sub
Hope this helps ... if you require assistance, feel free to leave another post.


Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[gwendon]

My RowSourceType is Table/Query.
I set the LimitToList property from No to Yes.
But in the Visual Basic I couldn’t quite follow your instructions.
If you were to send me a actual copy (a pattern) of the VB code, then perhaps I could change my Code accordingly:

Private Sub Combo254_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SocialSecurityNumber] = '" & Me![Combo254] & "'"
Me.Bookmark = rs.Bookmark
End Sub

gwendon

 

[grtammi]

gwendon:

Here's the complete snippet of code for your combo box event:

Private Sub Combo254_NotInList(NewData As String, Response As Integer)

  ' Add item to combo list
  ' First, declare variable to hold row source of this 
  ' combo box - I do this because the Requery method is 
  ' quirky
  Dim cRowSource : cRowSource = Combo254.RowSource
  ' now, update the recordset that the RowSource is based upon.
  DoCmd.RunSQL "INSERT INTO YourTable (SSN) VALUES ('" & NewData & "');"
  ' new SSN inserted - now reset the row source - this effectively does a requery
  Combo254.RowSource = cRowSource
  ' set the response so the db knows we added something
  Response = acDataErrContinue   
  ' set value of combo box to new inserted value
  ' note that this only works if the SSN column is the 
  ' bound column of the combo box - otherwise, you'd 
  ' have to perform a separate step here to retrieve 
  ' the lookup value for the combo box.
  Combo254.value = NewData

End Sub

HTH



Greg Tammi, IT Design & Consultation
Work: [URL unfurl="true"]http://www.atsbell.com[/URL]
Home: [URL unfurl="true"]http://members.shaw.ca/gregandliz[/URL]

 

[adamroof]

grtammi...

i gave you a star because of small detail that i missed...
my notinlist was not processing and i had no idea that limit to list set to yes allowed that! All other cboxes were processing that event fine, and i even recreated the cbox from scratch and still nothing!

Multiple thanks, and glad i can at least catch the details in these posts to solve my lack of details in designing!