RE: Update Combo Box

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You have to first have to create the new record in the table. There are a
number of ways to do this and I can't be specific because I don't have enough
info about your situation. But, as an example, here is a Not In List event
from one of my applications.

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

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
'Here is a way using SQL to create the new record
Me.cboActivity = Me.cboActivity.OldValue
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
CurrentDb.Execute ("INSERT INTO tblMasterActivity (Mactivity) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
'This line adds the new record to the form's recordset
Me.Requery
'This line adds a record to the subform
Me.frmSubAttributeTable.Requery
'This make the new record the current record
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

--
Dave Hargis, Microsoft Access MVP


"Owen" wrote:

Mr. Hargis,

Thanks for the quick response but I can't seem to get this one to go. The
relevent code (and I use that term loosely) is below. I not canot get the
combo box updated but now I'm getting every possible eror message known to
man, well just two or three, but still.

If intresult = vbNo Then
'Cancel adding new entry into the lookup table
intResponse = acDataErrorContinue
cbo.Undo
Exit Sub

ElseIf intresult = vbYes Then
'Open CONTACTS form to allow new data to be entered
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "COMPANIESfrm"
DoCmd.OpenForm stDocName, , , , acFormAdd

End If

'Continue without displaying default error message
intResponse = acDataErrorAdded

'Requery form to update cboPROJECTEOR
Me.Requery

Exit_Command99_Click:
Exit Sub

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Descrption: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

Any suggestions or ideas would be greatly appreciated,

TIA

Owen

"Klatuu" wrote:

Requery the Form, not the combo.
--
Dave Hargis, Microsoft Access MVP


"Owen" wrote:

Hello,

I have a form called PROJECTSfrm with a combo box called cboContacts which
is populated from a table called tblCONTACTS. The limit to List is set to
true. When a new contact is entered, a message box appears and ask if the
the user wants to add the new contact, if the user hits yes, the CONTACTSfrm
opens up and the user can add the relevent data. When the CONTACTSfrm is
closed and the user is returned to the PROJECTSfrm, I get an error message
that the new contact is still not in the list. I have tried to requery the
combobox upon return but am told I have to save the record, which I can't do
because the new contact is still not in the list....you know the drill. Is
there a way to get the combo to recognize the new contact without closing and
reopening the PROJECTSfrm?

TIA

Owen
.

Relevant Pages

  • RE: Update Combo Box
    ... Response As Integer) ... Response = acDataErrContinue ... ElseIf intresult = vbYes Then ... 'Continue without displaying default error message ...
    (microsoft.public.access.formscoding)
  • Re: Duplicate Entries in Ke/Unique Indexs
    ... Dim strmsg As String ... Response = acDataErrContinue ... 'Custom Error Message if error is due to missing Stock Invoice, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Elegant way of handling combo box "Not in list" event?
    ... Is there any way to get rid of or replacing the error message that pops up ... the Not In List event, but there is still an Access error that is raised. ... Setting Response to acDataErrContinue or acDataErrAdded avoids the ... Response As ...
    (comp.databases.ms-access)
  • Re: Close form, skips calling form focus
    ... the not in list code to add a new value to the comb box is: ... Response = acDataErrContinue ... So, we going have to tell the form to requery, but we can't do it in the ...
    (microsoft.public.access.forms)
  • Re: Combo "Not in List Event", MS generic message still appears
    ... If Err Then ... Response = acDataErrContinue ... Please advise Correct Field Name. ...
    (microsoft.public.access.forms)