Find next Number in a autonumber table

  • Thread starter Thread starter Deano
  • Start date Start date
D

Deano

I may be in the worng newsgroup, apologise if I am. Does anone know how to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using SQL) and
then detials in a seperate details table. If I can find out what the next
number in the header table will be I can then add the details in the detail
table with the appropiate related header record number. If there is anyone
other way of doing this, I would be greatful

Thanks in advance
 
Hi,

If you use a Recordset object then as soon as you use the AddNew Method you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
 
Thanks for quick response and it works

I've never used this way of programming so bear with me a little. What you
have suggested works a treat. But if I want to add a number of detail
records, would I format a loop for the rs1 part and "do until x<> y" (as an
example) ??

Mark Phillipson said:
Hi,

If you use a Recordset object then as soon as you use the AddNew Method you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
Deano said:
I may be in the worng newsgroup, apologise if I am. Does anone know how to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using SQL) and
then detials in a seperate details table. If I can find out what the next
number in the header table will be I can then add the details in the
detail
table with the appropiate related header record number. If there is
anyone
other way of doing this, I would be greatful

Thanks in advance
Click to expand...
Click to expand...
 
Yes just put the rs1 part in any kind of loop that suits e.g.

For k = 1 To 10
rs1.AddNew
rs1![FK] = lngIDNumber
rs1![FieldName] = k
rs1.Update
Next

Mark.




Deano said:
Thanks for quick response and it works

I've never used this way of programming so bear with me a little. What
you
have suggested works a treat. But if I want to add a number of detail
records, would I format a loop for the rs1 part and "do until x<> y" (as
an
example) ??

Mark Phillipson said:
Hi,

If you use a Recordset object then as soon as you use the AddNew Method you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
Deano said:
I may be in the worng newsgroup, apologise if I am. Does anone know how to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using SQL) and
then detials in a seperate details table. If I can find out what the next
number in the header table will be I can then add the details in the
detail
table with the appropiate related header record number. If there is
anyone
other way of doing this, I would be greatful

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
I jumped a little here. What I need to do is a little bit more complicated
than 1 to 10.

What I have is a list of parts which are associated with suppliers( 2 list
boxes). click on a supplier(1listbox) and the suppliers parts are
listed(the other listbox). Once the supplier is click I press a button
which then creates an Order for that supplier and associated parts.
Creating the header will be OK but the associated parts - how do I loop it
to say all these parts(listed in the 2nd listbox / SQL Statement). Is this
possible?

Mark Phillipson said:
Yes just put the rs1 part in any kind of loop that suits e.g.

For k = 1 To 10
rs1.AddNew
rs1![FK] = lngIDNumber
rs1![FieldName] = k
rs1.Update
Next

Mark.




Deano said:
Thanks for quick response and it works

I've never used this way of programming so bear with me a little. What
you
have suggested works a treat. But if I want to add a number of detail
records, would I format a loop for the rs1 part and "do until x<> y" (as
an
example) ??

Mark Phillipson said:
Hi,

If you use a Recordset object then as soon as you use the AddNew Method you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
I may be in the worng newsgroup, apologise if I am. Does anone know
Click to expand...
how
to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using
Click to expand...
SQL)
and
then detials in a seperate details table. If I can find out what the next
number in the header table will be I can then add the details in the
detail
table with the appropiate related header record number. If there is
anyone
other way of doing this, I would be greatful

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
To loop through all items in a ListBox:

For k = 0 To Me.ListBoxName.ListCount
Debug.Print Me.ListBoxName.ItemData(k)
Next

Deano said:
I jumped a little here. What I need to do is a little bit more complicated
than 1 to 10.

What I have is a list of parts which are associated with suppliers( 2 list
boxes). click on a supplier(1listbox) and the suppliers parts are
listed(the other listbox). Once the supplier is click I press a button
which then creates an Order for that supplier and associated parts.
Creating the header will be OK but the associated parts - how do I loop it
to say all these parts(listed in the 2nd listbox / SQL Statement). Is
this
possible?

Mark Phillipson said:
Yes just put the rs1 part in any kind of loop that suits e.g.

For k = 1 To 10
rs1.AddNew
rs1![FK] = lngIDNumber
rs1![FieldName] = k
rs1.Update
Next

Mark.




Deano said:
Thanks for quick response and it works

I've never used this way of programming so bear with me a little. What
you
have suggested works a treat. But if I want to add a number of detail
records, would I format a loop for the rs1 part and "do until x<> y" (as
an
example) ??

Hi,

If you use a Recordset object then as soon as you use the AddNew
Method
you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
I may be in the worng newsgroup, apologise if I am. Does anone know how
to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using SQL)
and
then detials in a seperate details table. If I can find out what
the
next
number in the header table will be I can then add the details in the
detail
table with the appropiate related header record number. If there is
anyone
other way of doing this, I would be greatful

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Sorry to trouble you more but I don't seem to be able to get it to work.
Heres my code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("OrderEnquiry")
Set rs1 = db.OpenRecordset("OrderEnquiryDetails")
rs.AddNew
rs![SupplierID] = lstSuppliers.Column(0)
rs![Description] = "Testing Order Enquiry Entries"
rs![Status] = "Created"
rs![Notes] = "This is great"
lngIDNumber = rs![OrderEnquiryID] 'Store number in a variable for use
later on
rs.update

For k = 0 To Me.lstPartsReq.ListCount
Debug.Print Me.lstPartsReq.ItemData(k)
rs1.AddNew
rs1![OEID] = lngIDNumber 'Use the number here FK
rs1![PartID] = lstPartsReq.Column(0)
rs1![Description] = lstPartsReq.Column(1)
rs1![Quantity] = lstPartsReq.Column(2)
rs1.update
Next
rs.Close
rs1.Close

lstSuppliers is listbox 1 and when selected gives part in lstPartsReq which
is the second listbox and theres only 1 record displayed relating to the
lstSuppliers.(Testing Data Only)

Everytime I run this is adds 3 records to the footer with no data in any of
the fields. I'm at a loss as to why 'For k = 0 To Me.lstPartsReq.ListCount'
is saying theres 3 records when there's only 1 record in the listbox showing
in the suppliers listbox.

Thanks in advance


Mark Phillipson said:
To loop through all items in a ListBox:

For k = 0 To Me.ListBoxName.ListCount
Debug.Print Me.ListBoxName.ItemData(k)
Next

Deano said:
I jumped a little here. What I need to do is a little bit more complicated
than 1 to 10.

What I have is a list of parts which are associated with suppliers( 2 list
boxes). click on a supplier(1listbox) and the suppliers parts are
listed(the other listbox). Once the supplier is click I press a button
which then creates an Order for that supplier and associated parts.
Creating the header will be OK but the associated parts - how do I loop it
to say all these parts(listed in the 2nd listbox / SQL Statement). Is
this
possible?

Mark Phillipson said:
Yes just put the rs1 part in any kind of loop that suits e.g.

For k = 1 To 10
rs1.AddNew
rs1![FK] = lngIDNumber
rs1![FieldName] = k
rs1.Update
Next

Mark.




Thanks for quick response and it works

I've never used this way of programming so bear with me a little. What
you
have suggested works a treat. But if I want to add a number of detail
records, would I format a loop for the rs1 part and "do until x<> y" (as
an
example) ??

Hi,

If you use a Recordset object then as soon as you use the AddNew
Method
you
can read the Autonumber.

e.g.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngIDNumber As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MasterTable")
Set rs1 = db.OpenRecordset("DetailsTable")
rs.AddNew
rs![FieldName] = "some value"
lngIDNumber = rs![ID] 'Store number in a variable for use later on
rs.Update

rs1.AddNew
rs1![FK] = lngIDNumber 'Use the number here
rs1![FieldName] = "some value"
rs1.Update
rs.Close
rs1.Close


--
HTH

Mark Phillipson

Free Add-Ins at; http://mphillipson.users.btopenworld.com/
I may be in the worng newsgroup, apologise if I am. Does anone know how
to
find out what the next record number will be in a autonumber table.

I'm trying to create a record in a header table(from a form using SQL)
and
then detials in a seperate details table. If I can find out what
the
next
number in the header table will be I can then add the details in the
detail
table with the appropiate related header record number. If there is
anyone
other way of doing this, I would be greatful

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top