Populate multiple rows of data in a subform

[Boreal2009]

Hi Everyone,

I'm hoping someone out there can help me with this. I have found a great example on the web that provides code on how to autopopulate data in a subform based on info entered in the main form. This example is written for Access 2000 and when I try to use it in Access 2003 I keep getting errors. I have tried playing around with the code but I don't have much experience here and as such I have not been able to solve the errors. I was hoping someone out there could look at the example for me and make the appropriate changes to the code so it will work in 2003.

Here is the link to the example: http://www.techonthenet.com/access/forms/subform_data.php

And here is the code in question:

Private Sub Form_AfterInsert()
Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 1

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 24

LSQL = "insert into inventory_details (serial_number, item_number)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & serial_number & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmInventory_details.Requery

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

DoCmd.GoToRecord , , acNewRec
Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

 

[missinglinq]

It really helps us to help posters that are receiving error messages if we know what the messages are!

Your code appears to be to the letter identical with the example you downloaded. ID you subform named, in fact, frmInventory_details?

Is the table that is the record source for the subform inventory_details?

If so, in that table do you have fields named serial_number and item_number?

If so, is the serial_number field defined as a Text Datatype?

 

[Boreal2009]

My apolgies for not giving the error messages but I thought with all that knowledge out there that someone would just be able to look at the code and determine what needed to be changed for it to work in Access 2003.

So here's what's happening. I have made changes to the field names to match the field names in my tables. But that does not appear to be the issue. The error I am getting is: Compile error: User-defined type not defined. When I hit OK it goes to visual basic debug mode. Please see the attached word document for screen shots. It is because the error is right at the beginning of the code (first line of code Dim db As Database is selected) that I deduced that it was an Access version issue.

Perhaps I am wrong - any help you can provide will be appreciated.

 

[missinglinq]

Because of the error message and the line of code triggering it, I'm going to guess that your problem is a missing reference, possibly the Microsoft DAO 3.6 Object Library.

Here are Doug Steele's excellent, detailed instructions on how to troubleshoot the problem:

http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html