The typical way to handle this is with an Unbound combo. You can't really
use a bound combo for searching because it will change the value of the
current record when you look something up.
Using a two column combo is perfect. That way the user sees the the name
but not the actual primary key. You control that with the Column Widths
property of the combo. So your row source query would be something like:
SELECT ProviderID, ProviderName FROM tblProvider ORDER BY ProviderName;
Set These properties:
Column Count = 2
Bound Column = 1
Column Widths = 0";2.5"
The 0" make the first column hidden. The 2.5" can be whatever is
necessary to display the provider name.
Limit To List = Yes
Now, in the Combo's After Upate event, if the provider is found, make it the
current record:
With Me.RecordsetClone
.FindFirst "[ProviderID] = " & Me.cboProvider
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
If the Provider does not already exist, the NotInList event will fire.
In that event, you notify the user and ask them if they want to add the new
provider.
Here is how I do that:
Private Sub cboProvider_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboProvider.Undo
strSQL = "INSERT INTO tblProvider ( ProviderName ) SELECT """ &
NewData & """ AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[ProviderName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboProvider.Undo
Response = acDataErrContinue
End If
End Sub
Put a combo box on the form. Let the wizard do that for you and select
provider name as the source. Now right click on the combo, find events, go
to
the afterupdate event and click the little button out to the right. Start
the
code builder and type:
Me.filter = "[providername] = """ & me.combo# & """"
Me.filteron = true
The combo#, like combo 1 or combo 10, will be listed.
Hello,
I'm creating a form for users to do the data entry. It's a form with
provider info's - provider name, provider specialty, Address, City,
Stat and buch of date fields. What I would love to do is, when the
user enter the name of the provider, I would love to let the user know
if that name already been enterd before, if it did than I would love
to have that record show up. If not, than the user would just go to
each field and fill out the info's. Please, let me kmow if I need to
be more clear. thanks!- Hide quoted text -
- Show quoted text -
I appreciate your help on this. How would your code be changed if I
add the provider Id along with the provider name to let the user know
if it's a duplicate record based on those two fields instead of just
the name? thanks!
