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

R

Robert H

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
 
P

PC Datasheet

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
 
R

Robert H

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
 
D

Dan Artuso

Hi,
That syntax is incorrect.

Dim i as Integer
For i = 1 to Me!NumOfPartManuf
'code here
Next i
 
R

Robert H

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
 
V

Van T. Dinh

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)
 
R

Robert H

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
 
V

Van T. Dinh

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

It should be AddNew, not Add.
 
Top