Create New record from From - Object Required Error

[stephaniem]

Hey all. I am trying to use a form to search/add records. Using a combobox notinlist event, if a user enters a division that does not currently have a record in the database, the code should ask if they want to add it, and then take the data from the form and create a new record. I get an "Object Required" error after confirming the request to add a record. I've been looking at this and can't find the error. Maybe some new eyes can help?
Table(DivisionsandLocations) and Form(frmCompanyAddUpdate) have fields called "Company" and "Division").


Private Sub Division_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_Division_NotInList
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not an existing Division." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new divison, open a recordset
' using the DivisionsandLocations table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("DivisionsandLocations", dbOpenDynaset)
' Create a new record.
Rs.AddNew
Rs("Company").Value = frmCompanyAddUpdate.Company.Value
Rs("Division").Value = frmCompanyAddUpdate.Division.Value
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_Division_NotInList:
Exit Sub
Err_Division_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

 

[jdraw]

You may need
Forms!frmCompanyAddUpdate.Company.Value in your references to Form controls
You should use code tags around your vba in posts to keep any formatting of your code. That's the # on the Advanced form. Or you can add , without the spaces,
[c o d e] your code/vba here [/c o d e] to get the same affect.

 

[stephaniem]

I added the "Forms!", but it comes up with the "NotInList" standard error after I say I want to add the Division. Testing the form shows that it adds a new record with the Company field populated correctly, but the Division is still blank.

Any ideas why it would accept the

Rs("Company").Value = Forms!frmCompanyAddUpdate.Company.Value

but not the Division code below that?

Thanks for the tip about the code tags!

 

[jdraw]

How about posting the entire proc again using code tags?

I find it more readable to use
If ...
End if

for each If. I know it isn't required syntactically if everything is on one line but just easier to read.

 

[stephaniem]

Private Sub Division_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_Division_NotInList
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not an existing Division." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new divison, open a recordset
        ' using the DivisionsandLocations table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("DivisionsandLocations", dbOpenDynaset)
             ' Create a new record.
        Rs.AddNew
        Rs("Company").Value = Forms!frmCompanyAddUpdate.Company.Value
        Rs("Division").Value = Forms!frmCompanyAddUpdate.Division.Value
        ' Save the record.
        Rs.Update
       
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
    End If
Exit_Division_NotInList:
       Exit Sub
Err_Division_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue

End Sub

 

[jdraw]

Have you tried putting a breakpoint in the code to see what values are in the various fields?
Can you post a copy of the database?

 

[stephaniem]

I attached the zipped file. It is scrubbed of most of the data, but the menu gives a good idea of some of the things it will be doing...

I will look into some breakpoints, while you look at this.

One of the Comboboxes is still called Combo2, I changed the code to Company, but hadn't changed the name of the combo before I zipped it.

 

[spikepl]

To debug with an active ON ERORR defeats the point of the excercise

 

[jdraw]

Part of your issue arises from the fact that CompanyName and Division are in different tables. You allow a company to be added to the Combobox ( I was able to add 2 records to the Companies table). How do you intend to gather the other info about that company?

Perhaps you could describe your underlying database so we can try to understand your tables and relationships.

I would also recommend you do not use embedded spaces or special characters (&^%$#@...) in field names. And your tables should have appropriate keys.

 

[stephaniem]

Ugh. I didn't use the NewData to populate the Division...

New problem though, is that it is creating 2 new records. One has the Company and Division filled in correctly, and one has the Company blank and the Division filled in.

Stepping through the code doesn't show it going through twice...

If NewData = "" Then Exit Sub
    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not an existing Division." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new divison open a recordset
        ' using the DivisionsandLocations table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("DivisionsandLocations", dbOpenDynaset)
             ' Create a new record.
        Rs.AddNew
        Rs.Fields("Company") = Forms!frmCompanyAddUpdate.Company
        Rs.Fields("Division") = NewData
        ' Save the record.
        Rs.Update
        MsgBox "Division Added. Please add location and customer number, if known."
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
        Rs.Close
        DoCmd.SelectObject acForm, "frmCompanyAddUpdate"