autonumbers, updating tables, and user entries

[Aravind]

Hi folks.

I have 2 tables, AccList and Book.

Book has ISBN as its primary key, while AccList has AccNo as its
primary key (data type = Autonumber) and ISBN as its foreign key.

The entries in AccList goes something like this:

AccNo ISBN
----- ----
00010 1234
00011 1234
00012 1234
00013 9876
00014 9876
00015 9876

Book and AccList are linked in a 1-to-many relationship (Book = 1,
AccList = many).

I have a form, frmAddBook, that is based on Book. frmAddBook has the
text boxes "ISBN" and "Total", in which the user enters the ISBN of
the new book and the number of copies of that book, respectively. Is
it possible for the form to automatically generate the AccNo of
AccList (starting from 00016 as per the example above) based on
"Total" and fill in the ISBN field in Book (for example, the user
enter "Total" as 3 and "ISBN" as 159; AccList wil then have the
entries 00016, 00017, 00018, and 00019, with 159 assigned to each of
them)? If yes, how do I do it?

[Salad]

Aravind wrote:

Hi folks.

I have 2 tables, AccList and Book.

Book has ISBN as its primary key, while AccList has AccNo as its
primary key (data type = Autonumber) and ISBN as its foreign key.

The entries in AccList goes something like this:

AccNo ISBN
----- ----
00010 1234
00011 1234
00012 1234
00013 9876
00014 9876
00015 9876

Book and AccList are linked in a 1-to-many relationship (Book = 1,
AccList = many).

I have a form, frmAddBook, that is based on Book. frmAddBook has the
text boxes "ISBN" and "Total", in which the user enters the ISBN of
the new book and the number of copies of that book, respectively. Is
it possible for the form to automatically generate the AccNo of
AccList (starting from 00016 as per the example above) based on
"Total" and fill in the ISBN field in Book (for example, the user
enter "Total" as 3 and "ISBN" as 159; AccList wil then have the
entries 00016, 00017, 00018, and 00019, with 159 assigned to each of
them)? If yes, how do I do it?

You could create a query to get the max value of your acctNo field.
Next, open up the query as a recordset to get the max number.
Then do a for/next and add the records.
Example using air code

Sub AddRecs
Dim i As Integer
Dim lA As Long
Dim r As Recordset
Dim rAdd As Recordset
Dim s As STring

Set rAdd = Currentdb.openrecordset("Table1",dbopendynaset)
s = "Select Max(AcctNo) As MA from Table1"
set r = currentdb.openrecordset(s,dbopensnapshot)

'convert to integer the value of acctno
ia = CLng(r!MA)

For i = 1 to Me.TotalRecs
ia = IA + 1
rAdd.AddNew
rAdd!AcctNo = Format(ia,"00000")
rAdd!ISBN = Me.ISBN
rAdd.Update
Next i
rAdd.close
r.close
set rAdd = Nothing
set r = Nothing

msgbox "Done adding records!"
End Sub