Automatically adding records (1 Viewer)

[fsict]

Hi All,

Hoping someone can help with out with this one.

I have a database that is going to store the details of books including their ISBN in a table, as below:

tbl_books
*ISBN
Book Name
QuantityInStock

In another table called tbl_booksinstock I have:

*ISBN+BookNumber
*ISBN

What I want to be able to do is to add a new book to tbl_books and for the database to automatically create X number of entries in the tbl_booksinstock table based on the number I enter in QuantityInStock.

I will obviously create a form based on tbl_books

What is the best way to do this? I've tried an append query but it only adds in the last number so example if the number in stock is 5 and the ISBN is 1234567890:

1234567890-5

Can I do this through an Append Query or should I do I need to do some VB code and link this to a button on my form?

Thanks to all who help

 

[arnelgp]

you will be needing VB.
create a form for your tbl_books.
on its AfterUpdate put this:


Private Sub Form_AfterUpdate()
Dim i As Integer
For i = 1 to Me.QuantityInStock
CurrentDb.Execute "Insert Into tblBookInStock(ISBNplusBookNumber, ISBN) SELECT " & Chr(4) & Me.ISBN & "-" & Trim(i) & Chr(34) & ", " & Chr(34) & Me.ISBN & Chr(34)
Next i
End Sub

 

[fsict]

Thank you.. that's two points of mine you have answered far

Many thanks