Update Combo Box

[Owen]

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

 

[Klatuu]

Requery the Form, not the combo.

 

[Owen]

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]

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

 

[Owen]

Thank you, I see several things in your code I can adapt to my use.

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

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