not in list event procedure

  • Thread starter Thread starter Deron
  • Start date Start date
D

Deron

Please i need anot in list event procedure to help me update a table A with
values i need to add. i.e i have form F which updates data in table T no
probs. The control source is ok bu i reset the rowsource to a list in Table
A. no probs. however i need Table A to add any new data that i may have to
put on the list.
 
Deron

Here some that I used to an aiport code to a tbl_cities when the code that
entered was not in the drop down list.

I also used the GOT FOCUS event of the drop down to update the list with the
new entry see coding below.


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

Dim strmsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strmsg = "'" & UCase$(NewData) & "' is not in the list. "
strmsg = strmsg & "Would you like to add this Port ? "

If vbNo = MsgBox(strmsg, vbYesNo + vbQuestion, " New Port Code") Then
resposne = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_cities")
rst.AddNew
rst("city_code") = UCase$(NewData)
rst.Update
Response = acDataErrAdded
End If

End Sub

Private Sub select_port_GotFocus()
DoCmd.Requery "select_Port"
End Sub


Allan
 
Thanks

But its a bit complex for me.
The table i want to update is called CLIST and has just one field called CNAME
the Combo box name is BCNAME
Can you help rewrite the code

using this information. i have only used valuelists before now and that was
simple.
thanks
 
I have tried using your code and just replacing the combo box, table and
field names but it still give syntax error at the Dim rst As DAO.Recordset
line

please help
 
I had to modify it eventually thanks
Private Sub BCNAME_NotInList(NewData As String, Response As Integer)

Dim strmsg As String

strmsg = "'" & UCase$(NewData) & "' is not in the list. "
strmsg = strmsg & "Would you like to add this Port ? "

If vbNo = MsgBox(strmsg, vbYesNo + vbQuestion, " New Port Code") Then
resposne = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("CLIST")
rst.AddNew
rst("CNAME") = UCase$(NewData)
rst.Update
Response = acDataErrAdded
End If

End Sub

Private Sub BCNAME_GotFocus()
DoCmd.Requery "BCNAME"
End Sub
 
Back
Top