Okay, first order of business is that the combo box should not be a bound
control. If you use a bound control for searching, it will cause problems
for you. Here is an example:
Your current record is John Smith. His name is showing in the combo box.
You decide to more to Sally Jones, so you select her name from the combo.
You have just changed the value of the bound combo to Sally Jones. But,
you have not moved off the current record yet, so when your code in the
AfterUpdate event of the combo tries to go to Sally Jones record, Access will
attempt to save the current record, but now you get an error because it is
trying to create a duplicate key. (You changed John to Sally is already in
the table)
It is better to use a text box to bind the name and use the unbound combo
for searching.
Now to how to make this all work. First, the combo needs to be a two column
combo based on the Contacts table:
SELECT [full name], [initials] from [Contacts Table]
Set the combo's Column Count to 2
Set the Bound Column to 1
Set the Column Widths tp 0";2" - The 0 means the initials will not show in
the combo. Change the 2 to whatever makes the name display like you want it.
Now, you use the after update event to do your navigation:
With Me.RecordsetClone
.FindFirst "[Submitted By] = '" & Me.MyCombo & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Me.Initials = Me.MyCombo.Column(1)
End If
End With
That takes care of existing records.
Now we want to add a new record. This is done using the Not In List event
of the combo. Be sure the Limit To List property is set to Yes for the combo.
If MsgBox(NewData & " Is Not In The Property Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO PropertyTable ([full name]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
If Not .NoMatch Then
.FindFirst "[full name] = '" & NewData & "'"
Me.Bookmark = .Bookmark
Me.Initials = Me.MyCombo.Column(1)
Response = acDataErrAdded
End If
End With
Else
Me.MyCombo.Undo
Response = acDataErrContinue
End If
--
Dave Hargis, Microsoft Access MVP
SuzyQ416 said:
It relates to the name
:
What does Submitted by relate to? the name or the intials?
--
Dave Hargis, Microsoft Access MVP
:
Klatuu,
Thank you. My initials and full name are in my Contacts Table. Submitted by
is in my Property Table which is where my form is pulling its information
from.
I have created a relationship between these two fields: Submitted by and
Full Name. My combo box is bound by Submitted By. Does this help?
Linda
:
Sorry, Suzy. Based on your previous post, I thought you had code that is not
working.
You combo box should be a 2 column combo that would include the person's
name and their initials. If those two pieces of data are not in the same
table, you will need to create a join to get them to match up. For example
purposes, I will assume you have a field for Name and a field for Initials in
your table.
Your row source should be a query based on the table that returns both fields.
Before I show how the code would work, I do need a couple of pieces of
information:
Are the Name and the Intitials in the same table?
Is the combo box a bound control? If it is, what field is it bound to?
--
Dave Hargis, Microsoft Access MVP
:
That's the problem. I don't know what code to write to make this work. I am a
novice at this level. I will be happy to share all my information. What do
you need?
:
Can't see your code all the way from here. Could you hold it a little
closer, please?
--
Dave Hargis, Microsoft Access MVP
:
I have a Combo Box called Submitted By that includes several names, i.e.,
John Smith. When I select a name from this list, I want the initials of this
person to autopopulate a Text Box called Priority Code. I do have a table
with all of this information and have created the appropriate relationships.
I cannot get this to work. I have tried entering many expressions into the
AFTERUPDATE on my combo box, but nothing seems to work. Can anyone tell me
what I am doing wrong?
