Adding data to table with combo box with "On not in list"

G

Guest

I have tried many of the previous suggestions on this site and still can't
get this to work. I have a combo box that references one table 'A' to select
a facility to enter into the current table 'B', through control source field.
However, if I have a new facility for table 'A' it errors out.
I do have an event for OnNotInList that does prompt for verification on the
add but I can't seem to get pass this with all great examples out there. Do
I need a more specific field reference? Any help would be appreciated.
Here is my code to date:

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)
On Error Resume Next

rs.AddNew
rs![TestAgengies]![TestingAgency] = NewData
rs.Update

If Err Then
MsgBox "Error occurred, Please Retry"
Response = acDataErrContine

Else
Response = acDataErrAdded
End If

End If

rs.Close

Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
 
G

Guest

Why do you have the field name apear twice
rs![TestAgengies]![TestingAgency]

Should be
rs![The Name of the field in the table] = NewData
 
G

Guest

Actually, I have the name of the table, TestAgencies, and the name of the
field listed here. I originally had the only the field name and that wasn't
working either. While I am trying to get information from TestAgencies
table, and update with any additions, I also want to store my selection in
another table, TestReports. If I select a TestingAgency that does exist it
does store properly. I just want the option of adding additional agencies to
the original table without having to go to a maintenace form to add/delete
from TestAgencies.

Ofer said:
Why do you have the field name apear twice
rs![TestAgengies]![TestingAgency]

Should be
rs![The Name of the field in the table] = NewData

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Fritzee said:
I have tried many of the previous suggestions on this site and still can't
get this to work. I have a combo box that references one table 'A' to select
a facility to enter into the current table 'B', through control source field.
However, if I have a new facility for table 'A' it errors out.
I do have an event for OnNotInList that does prompt for verification on the
add but I can't seem to get pass this with all great examples out there. Do
I need a more specific field reference? Any help would be appreciated.
Here is my code to date:

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)
On Error Resume Next

rs.AddNew
rs![TestAgengies]![TestingAgency] = NewData
rs.Update

If Err Then
MsgBox "Error occurred, Please Retry"
Response = acDataErrContine

Else
Response = acDataErrAdded
End If

End If

rs.Close

Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
Click to expand...
Click to expand...
 
G

Guest

Try this code

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)

rs.AddNew
rs![TestingAgency] = NewData
rs.Update
rs.Close
End If


Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Ofer said:
Why do you have the field name apear twice
rs![TestAgengies]![TestingAgency]

Should be
rs![The Name of the field in the table] = NewData

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Fritzee said:
I have tried many of the previous suggestions on this site and still can't
get this to work. I have a combo box that references one table 'A' to select
a facility to enter into the current table 'B', through control source field.
However, if I have a new facility for table 'A' it errors out.
I do have an event for OnNotInList that does prompt for verification on the
add but I can't seem to get pass this with all great examples out there. Do
I need a more specific field reference? Any help would be appreciated.
Here is my code to date:

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)
On Error Resume Next

rs.AddNew
rs![TestAgengies]![TestingAgency] = NewData
rs.Update

If Err Then
MsgBox "Error occurred, Please Retry"
Response = acDataErrContine

Else
Response = acDataErrAdded
End If

End If

rs.Close

Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
Click to expand...
Click to expand...
 
G

Guest

Thanks so much for your quick response and support.
It does work now, but I had to put a response action in before the close to
allow the item to be added to the original table and the bound field, as
follows:

rs.AddNew
rs![TestingAgency] = NewData
rs.Update Response=acDataErrAdded
rs.Close
Click to expand...

Ofer said:
Try this code

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)

rs.AddNew
rs![TestingAgency] = NewData
rs.Update
rs.Close
End If


Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Ofer said:
Why do you have the field name apear twice
rs![TestAgengies]![TestingAgency]

Should be
rs![The Name of the field in the table] = NewData

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Fritzee said:
I have tried many of the previous suggestions on this site and still can't
get this to work. I have a combo box that references one table 'A' to select
a facility to enter into the current table 'B', through control source field.
However, if I have a new facility for table 'A' it errors out.
I do have an event for OnNotInList that does prompt for verification on the
add but I can't seem to get pass this with all great examples out there. Do
I need a more specific field reference? Any help would be appreciated.
Here is my code to date:

Private Sub TestAgencyID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_TestAgencyID_NotInList

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please retype or clear Lab Name."

Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("TestAgengies", dbOpenDynaset)
On Error Resume Next

rs.AddNew
rs![TestAgengies]![TestingAgency] = NewData
rs.Update

If Err Then
MsgBox "Error occurred, Please Retry"
Response = acDataErrContine

Else
Response = acDataErrAdded
End If

End If

rs.Close

Exit_TestAgencyID_NotInList:
Exit Sub
Err_TestAgencyID_NotInList:
MsgBox Err.Description
GoTo Exit_TestAgencyID_NotInList
rs.Update

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

item not found in this collection 1
NotInList Errors 3
Add Record with combo box 13
NOTINLIST 2
NotInList not firing ? 1
Run-Time Error 3146 ODBC Call Failed 3
Combo Box Adding to List--Error 91 2
Combo Box to add items to a table 4

Top