Appending a record from one table into a new table "n" number of t

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains part numbers for manufactured finished goods
that will require serial numbers. After they are produced I want to append
the part number and associated fields into a different table with a dialog
asking how many were produced. This would add x number of records so that
serial numbers could be updated accordingly through another process.

any help..much appreciated

Robert
 
Your table of part numbers requiring serial numbers needs to look like:
TblPart
PartID
PartNum
<Other fields describing the part>

You then need a table of manufactured parts that looks like:
TblManufacturedPart
ManufacturedPartID
PartID
DateManufactured
SerialNum

You then need an unbound form named PFrmManufPartDialog with:
1. Combobox named PartNum based on TblPart. Bound Column = 1, Column Count
= 2, Column Width = 0;1
2. Textbox named PartManufDate to enter DateManufactured
3. TextBox named NumOfPartManuf to enter # manufactured
4. Command button with the following code in the Click event:

Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("TblManufacturedPart")
Do 1 to Me!NumOfPartManuf
Rst.Add
Rst!PartID = Me!PartNum
Rst!DateManufactured = Me!PartManufDate
Rst.Update
Next
Rst.Close
Set Rst = Nothing
Set Db = Nothing
 
Much thanks for the response,

When entering the code I get a compile error at the

Do 1 to Me!NumOfPartManuf

The error is, "Expected: While or Until or end of statement"

Thanks again,
Robert
 
Hi,
That syntax is incorrect.

Dim i as Integer
For i = 1 to Me!NumOfPartManuf
'code here
Next i
 
Hi Dan,
Thanks for the reply and I apologize for being a newbie on VB. I made the
changes you mentioned so my code now looks like the following.

Private Sub Command6_Click()
Dim Db As DAO.Database
Dim rst As DAO.Recordset
Set Db = CurrentDb
Set rst = Db.OpenRecordset("TblManufacturedPart")
Dim i As Integer
For i = 1 To Me!NumOfPartManuf
rst.Add
rst!PartId = Me!PartManufDate
rst.Update
Next i
rst.Close
Set rst = Nothing
Set Db = Nothing

End Sub

I now get a compile error on Dim Db as DAO.Database..says, "User Defined
Type not defined"

Thanks again,
Robert
 
You need to add the Microsoft Data Access Object Library 3.6 into the
References Collection of your database.

In the VBE (i.e. code window), use Tools / References ... Find the above
Library and check the CheckBox in front. OK to get out of the References
dialog and Compile your code.

HTH
Van T. Dinh
MVP (Access)
 
Ok, Im getting closer :)

I added the Object Library and now makes it to the

Rst.Add

At which point it fails and says,"Method or Data Member not found."

Thanks,

Robert
 
Sorry, I should have read the whole post previously and should have seen
that error.

It should be AddNew, not Add.
 
Back
Top