Autonumber in an unbound form using VB

  • Thread starter Thread starter RIP
  • Start date Start date
R

RIP

I am not sure how to do this...

I have an unbound form named frmWallDetails. I want to autonumber a field
named "wallNo" using a query as the date source using VB.

Any suggestions will be greatly appreciated.

Regards,
Walter
 
On Wed, 24 Dec 2008 19:06:00 -0800, RIP

I'm not sure how to answer, because I don't understand "using a query
as the date source". What date? What table?
Also, suppose we get past this point and you have a perfect wallNo,
what are you going to do with it next? Give us some context so we can
better suggest solutions.

-Tom.
Microsoft Access MVP
 
I hope this makes more sense...

This is the code I am currently running to get the wallNo field to increment
based on the last number found in the table.

Private Sub Form_Current()
Dim db As DAO.Database
Dim rstWallAdd As DAO.Recordset

Set db = CurrentDb()
Set rstWallAdd = db.OpenRecordset("tblWallDetail", dbOpenTable)

rstWallAdd.Index = "primarykey"
rstWallAdd.MoveLast
wallNo = rstWallAdd!wallNo + 1
rstWallAdd.Close
db.Close
End Sub
--------------------------------------------------
The "tblWallDetail" has the following fields/indexes

estnum - (indexed=yes duplicates ok)
wallNo - (indexed=yes duplicates ok)
and misc other fields

Problem 1 - I have to change the form "tblWallDetail" to allow duplicate
"wallNo" entry with the same "estnum" entry. Now there is no primary key,
but instead only indexed fields.

Example:
estnum wallNo
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
etc...

I want to get the last number from the "wallNo" field in the "tblWallDetail"
table that have the same "estnum" value.

I was thinking of running a query to filter out the data to include only
records that have the specified "estnum" value.

Thanks in advance for all your help.
 
Back
Top