'Quick Add' for a combo box

[mindstorm]

I have read through all of the past posts about auto-updating a combo
box by using the NotInList event, but for some reason I can't wrap my
head around it. I am putting together a simple little expense tracking
db for our department, and wanted to add a 'Quick Add' like
functionality to the Vendor list in the 'Transaction' form... here's a
real quick sketch of my structure:

tblVendor
Vendor ID (text)
Vendor Name (text)

tblGLCodes
GL Code (text)
Description (text)

tblLocations
Location ID (text)
Location (text)

tblTransactions
Transaction ID (AutoNumber)
GL Code (text)
Location (text)
Vendor (text)
Purchase Amount (Currency)
Description (text)
Reason (text)
Purchase Date (date/time)
Submit Date (date/time)

Now, on my Transactions Form, I created a button called Quick Add that
calls for a form that has the tblVendor fields there so they can add a
new vendor to the DB, but as expected, when they close the subform, the
new vendor does not show up in the combo box. Am I missing something in
my logic? Is there a way to use the NotInList procedures to accomplish
what I need, though we need 2 pieces of information for that table? Am
I just overthinking it?

Thanks in advance for any assistance.

Jim

 

[Guest]

You need to requery the combo after you close the subform. It is in the
table, but the combo doesn't know it yet.

 

[Jim Williams]

I knew that, but didn't know how to impliment it. But a search found
the answer. So thank you for pointing me in the right direction.

Just in case someone might need the answer as well, the code to requery
the combo box is:

From: jl5000
Date: Tues, May 24 2005 10:07 pm
Email: jl5000 <[email protected]>
Groups: microsoft.public.access.forms

Try this code in the "On Got focus" event in your combo box:

me.mycombobox.requery

 

[Guest]

Glad you got it working. I guess, perhaps I didn't get that from your
question.

Although putting the requery in the Got Focus event will work, it is adding
unnecessary overhead. That means you will be doing a database fetch every
time the combo gets the focus.

If you did use the Not In List event to do it all, the performance would be
better and your code a little tighter. For example:

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

If MsgBox(NewData & " Is Not In The Vendor Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
DoCmd.OpenForm "frmVendor", , , , acFormAdd, adDialog
Me.cboVendor.Requery
Set rst = Me.RecordsetClone
Response = acDataErrAdded
Else
Me.cboVendor.Undo
Response = acDataErrContinue
End If

End Sub