Problems adding values to combobox

[Archie999]

Hi All,

After reading many threads in this most valuable forum, I tried to set up a combobox in my form to automatically add a value that a user puts. It's not quite working.

I have my LimitToList property set to Yes and have added the code below to the NotInList event.

However, when I try to run it, I get an error that says the CategoryID must not be empty (referential integrity that is required in this table). The thing is, I have already entered the CategoryID before getting to the combobox in question.

Do I need to somehow update the DB before starting the code below? Or what...

Thanks as always!



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

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

If vbNo = MsgBox("Would you like to add a new issue?", vbQuestion + vbYesNo, "Add Issue?") Then
Response = acDataErrContinue
Exit Sub

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

rs.AddNew
rs!IssueName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End If

End Sub

 

[scottfarcus]

Archie,

Are you storing the value that you entered for CategoryID in a variable?

What you are trying to do is add a new record. In the table, "tblIssue", a new record cannot be added because you are not providing a value for the required CategoryID field.

If you capture CategoryID in a variable, intCatID, and then add the line:

rs!CategoryID = intCatID

then it should work. Assuming there are no other fields in tblIssue that are required or cannot be zero length strings.

Hope this helps!

 

[Archie999]

Does not work either

Thanks for the reply Scott,

Actually that's exactly what I thought too. I did something similar to what you suggested already (I just referenced the form directly as opposed to setting up a variable) - see below. When I did this it worked but when I went to the tblIssue table it added TWO records.

I tried this:

rs.AddNew

rs!IssueName = NewData
rs!CategoryID = Me.CategoryID
rs!ProductID = Me.ProductID
rs!MyIssueID = Me.MyIssueID
rs.Update

This covers ALL the fields in my table. Now a look in my table showed that it added 2 records. Note that the first record's issue name is the MyIssueID (PK) of the 2nd one.

MyIssueID ProductID CategoryID IssueName
---------------------------------------------------------------------
132 QA QA Manager 133
133 QA QA Manager More and more tests


What do you think? Thanks in advance.

Archie