Urgent Help Needed: How do I add a record to a linked table using code on a form?

[Nelson]

I have some code on a form that runs for the "On Not in List" Event. When
the code runs, I get the error msg: "Object variable or With block variable
not set". Essentially, what the code does is as follows:
If a user tries to enter a new product that is not currently part of our
inventory, the code adds the user's entry to our inventory.

We have 2 databases. The code works in the first database (we'll call it
db1), but not in the second database (db2). db1 is for our users in
Department 1 and db2 is for our users in Department 2. db1 holds all the
data, and db2 uses linked tables (that link to db1) for it's data.

I tried copying the form (which includes the code) and the query from db1 to
db2 and I still get the same error msg (Object variable or With block
variable not set). This leads me to believe the error is due to trying to
enter data into a linked table. Why else would it work in db1 and not in
db2?

Can somebody tell me how to get this to work? I don't even necessarily need
to fix the current code if someone knows how to solve this a different
way...

Thanks,
Chris

Here is the code that works in db1, but not in db2:
Private Sub PRODUCT_CODE_NotInList(NewData As String, Response As Integer)
Dim db As Database
Dim rs As Recordset
Dim Msg As String
Dim CR As String: CR = Chr$(13)

' Exit Sub if user cleared the selection.
If NewData = "" Then Exit Sub

' Ask the user if they wish to add the new product
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 7 Then
' If the user chooses No, Instruct the user to try again.
Response = DATA_ERRCONTINUE
MsgBox "Please try again."
Else
' If the user does not choose No, create a new record in the
' Inventory Table.
On Error Resume Next

' Open the Inventory Table.
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("INVENTORY", DB_OPEN_TABLE)

rs.AddNew
rs![PRODUCT CODE] = NewData
rs.Update

' If an error occurred while adding the record...
If Err Then
' ...instruct the user to try again.
Response = DATA_ERRCONTINUE
Beep: MsgBox Err.DESCRIPTION, 48
MsgBox "Please try again."
Else
' If no error occurred, add the element to the combo box
' list.
Response = DATA_ERRADDED
End If

End If
End Sub

 

[Brian]

How about simply passing off the data on your form to an append query and
have it do the append without having to all that VBA code?

Once the user has answered Yes to the append operation, simply run
DoCmd.OpenQuery "Query1". The query could append
[Forms]![YourCurrentForm]![Field1] to Field1 in the table, etc.

 

[Stephanie]

Nelson,
I'm not much of a coder, so the first thing I check when I have code working
in one database but not in another is the references. When you have the code
screen open, use the Tools toolbar to select References. I check to make
sure that I have all of the same references checked and that these references
are in the same order. Hey. Couldn't hurt.

 

[Nelson]

Thanks for the response. I tried this to no avail.

Chris

Nelson,
I'm not much of a coder, so the first thing I check when I have code working
in one database but not in another is the references. When you have the code
screen open, use the Tools toolbar to select References. I check to make
sure that I have all of the same references checked and that these references
are in the same order. Hey. Couldn't hurt.

I have some code on a form that runs for the "On Not in List" Event. When
the code runs, I get the error msg: "Object variable or With block variable
not set". Essentially, what the code does is as follows:
If a user tries to enter a new product that is not currently part of our
inventory, the code adds the user's entry to our inventory.

We have 2 databases. The code works in the first database (we'll call it
db1), but not in the second database (db2). db1 is for our users in
Department 1 and db2 is for our users in Department 2. db1 holds all the
data, and db2 uses linked tables (that link to db1) for it's data.

I tried copying the form (which includes the code) and the query from db1 to
db2 and I still get the same error msg (Object variable or With block
variable not set). This leads me to believe the error is due to trying to
enter data into a linked table. Why else would it work in db1 and not in
db2?

Can somebody tell me how to get this to work? I don't even necessarily need
to fix the current code if someone knows how to solve this a different
way...

Thanks,
Chris

Here is the code that works in db1, but not in db2:
Private Sub PRODUCT_CODE_NotInList(NewData As String, Response As Integer)
Dim db As Database
Dim rs As Recordset
Dim Msg As String
Dim CR As String: CR = Chr$(13)

' Exit Sub if user cleared the selection.
If NewData = "" Then Exit Sub

' Ask the user if they wish to add the new product
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, 32 + 4) = 7 Then
' If the user chooses No, Instruct the user to try again.
Response = DATA_ERRCONTINUE
MsgBox "Please try again."
Else
' If the user does not choose No, create a new record in the
' Inventory Table.
On Error Resume Next

' Open the Inventory Table.
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("INVENTORY", DB_OPEN_TABLE)

rs.AddNew
rs![PRODUCT CODE] = NewData
rs.Update

' If an error occurred while adding the record...
If Err Then
' ...instruct the user to try again.
Response = DATA_ERRCONTINUE
Beep: MsgBox Err.DESCRIPTION, 48
MsgBox "Please try again."
Else
' If no error occurred, add the element to the combo box
' list.
Response = DATA_ERRADDED
End If

End If
End Sub