Combo Box update (fmr)

[RA]

I think I've seen this done before but I do not have access to my hand books.

I want a combo box to pull the entries currently in the table, the names
appearing only once, so that if the new entry is a repeat you can just select
the name associated with it.

What I am doing is tracking contacts from our offices to the support staff.
I have a date field, a, who handled the contact field, a, the type of contact
field and then the field in question (who contacted us). This field could be
very large if everyone contacts the support staff, which isn’t expected

What would be nice is for the people who repeatedly contact our office
having their names popup in a dropdown box on the form when accessed. If it
is a new contact have that name go into the table and then appear in the drop
down box the next time.

Is it possible?

 

[Armando Blanco]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

Armando Blanco
Microsoft MVP

 

[Klatuu]

Not at all difficult. First, to get only one occurance of an entry, use the
DISTINCT predicate in the SQL for your combo's row source:
SELECT DISTINCT CONTACT_ID FROM tblContacts

Now, the traditional way to deal with adding a new item to a list in a combo
is to use the Not In List event. Be sure the Limit To List property for your
combo is set to Yes so the event will fire.

Private Sub cboContact_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Contact Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tlbContacts (CONTACT_NAME) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[CONTACT_NAME] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

 

[Jeff Boyce]

Disregard the "cant do this with Access" message. It is SPAM (or worse).
Follow the link at your own peril...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP