Autonumber

[Anarch]

I have a form that contains 2 subforms. The main form has a number field set for autonumber so as to uniquely identify that record. Aside from having gaps in the numbers from deleting record this works fine.

On my second subform which is a set up as a continuous form with a number field I have set as an autonumber which I would like to basically count up to 20 automatically in my form as there can be 20 entries in this subform. My question is how to allow my subform to have an autnumber type function that is reset whenever I refresh the subform to allow it to count 1 to 20 again. There are not always 20 entries put in but 20 is the max. Can this by done using autonumber or am I going to have to use VB to create a counter to fill in the number for me here (any coding examples would help me).

Thank you in advance for your help.

 

[Dave Eyley]

What about forgetting the Autonumber and having an additional field (Counter) set up as a number field. Start by just numbering them 1 to 20. Not to be used as a primary field! This is in addition to any key fields.

When you need to revise the list , after a deletion etc, use the code below to go thru the records and renumber the records.


Dim MyCount As Integer
MyCount=0
Dim MyDb As Database, MySet As Recordset
Set MyDb=DBEngine.WorkSpaces(0).Databases(0)
Set MySet=OpenRecordset ("MyTable")

MySEt.MoveFirst
Do Until MySet.EOF
MyCount=MyCount+1
MySet.Edit
MySet![Counter]=MyCount
MySet.Update
MySet.MoveNext
Loop

MySet.Close
MyDb.Close


This code could be placed in a button OnClick property to do an immediate refresh, or the form's OnClose property so it's there when the form is re-opened. The choice, as they say, is yours.

This will only number the records in the order they appear in the recordset/table and you will have to requery the form/subform afterwards to see the new numbering, but it is one way of doing it.

IT'S THE WEEKEND!!!!!!!!------Have a Good one-------
(weekend, that is)

Yahoo! (and I don't mean the Search Engine!)

Dave E

 

[GregP]

Dave,

What you've said looks to be the solution to a similar problem I have. However, please excuse my ignorance, I've never written these sorts of code before so am not familiar with the syntax etc. I tried to set your code in the OnOpen property of the form. When I opened the code builder, it automatically put in:

Private Sub form_open(Cancel As Integer)

End Sub

I pasted your code between these two lines, and added the necessary recordset name (the table the form is based on, correct?). As follows:

Private Sub Form_Open(Cancel As Integer)

Dim MyCount As Integer
MyCount = 0
Dim MyDB As Database, Myset As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Myset = OpenRecordset("tblGuests")

Myset.MoveFirst
Do Until Myset.EOF
MyCount = MyCount + 1
Myset.Edit
Myset![ListPosition] = MyCount
Myset.Update
Myset.MoveNext
Loop

Myset.Close
MyDB.Close

End Sub


When I try to view the form, I get an error:

Compile Error: Sub or Function not defined

I click OK, and the OpenRecordset command is highlighted blue, as though I had highlighted it myself, but the first line is highlighted yellow, with an arrow to its left (presumably indicating a fault with that line). What is the fault? The line it's highlighted is the one it put there itself, so I don't know what's wrong with it. I can follow your code OK, but not what or why it's added what it has. Help!

 

[GregP]

[No message]

 

[GregP]

Dave,

What you've said looks to be the solution to a similar problem I have. However, please excuse my ignorance, I've never written these sorts of code before so am not familiar with the syntax etc. I tried to set your code in the OnOpen property of the form. When I opened the code builder, it automatically put in:

Private Sub form_open(Cancel As Integer)

End Sub

I pasted your code between these two lines, and added the necessary recordset name (the table the form is based on, correct?). As follows:

Private Sub Form_Open(Cancel As Integer)

Dim MyCount As Integer
MyCount = 0
Dim MyDB As Database, Myset As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Myset = OpenRecordset("tblGuests")

Myset.MoveFirst
Do Until Myset.EOF
MyCount = MyCount + 1
Myset.Edit
Myset![ListPosition] = MyCount
Myset.Update
Myset.MoveNext
Loop

Myset.Close
MyDB.Close

End Sub


When I try to view the form, I get an error:

Compile Error: Sub or Function not defined

I click OK, and the OpenRecordset command is highlighted blue, as though I had highlighted it myself, but the first line is highlighted yellow, with an arrow to its left (presumably indicating a fault with that line). What is the fault? The line it's highlighted is the one it put there itself, so I don't know what's wrong with it. I can follow your code OK, but not what or why it's added what it has. Help!

PS. I tried replacing MyCount with Cancel in the first line (as per Dim MyCount as Integer), but then got:

Compile Error: Duplicate declaration in current scope

so that doesn't seem to help.

 

[Dave Eyley]

MyCount is a user-defined variable and is declared at the outset of the routine.
If you change this to 'Cancel' then you will have more problems because Cancel is a reserved word for Access and it won't like it.

With the first error - Sub of Function not defined, the line causing the problem should be highlighted in yellow. Did this happen and what was the line? This should give you a clue to the problem.

Dave E

 

[Dave Eyley]

Dear Anarch,

My sincere apologies,

In the line -

Set MySet=OpenRecordSet ("tblGuests")

change this to -

Set MySet=MyDb.OpenRecordset ("tblGuests")

I ran the code after this correction and it works OK. You will have to remember to requery the form/subform to see the changes on the form.

Now that I've messed you around, I do hope that it's what you want.



Dave E

 

[Anarch]

Trying it out today Dave as soon as I finish the morning running around ok. Thank you very much for the help will keep you posted.

 

[GregP]

Dave,

Yes, the first line was highlighted yellow

Private Sub form_open(Cancel As Integer)

so I presumed the problem was something to do with that line (naturally!). But not knowing enough about VBCode, I'm not sure what the problem is. As I said, I tried changing 'Cancel' to 'MyCount', but that gave a duplicate declaration problem. I've done a bit of C so am familiar with general program structures, thus can follow what your code does, but am not familiar enough with the syntax or declaration rules for VB to know where the problem lies.

 

[Dave Eyley]

Greg P,

Is this problem now resolved?

Has the above correction made a difference?

Dave E

 

[GregP]

Dave,

The problem still exists. Sorry, which was the 'above correction' you referred to? Could you please go over the last long thread I wrote and show me where the problem is? I'm still not too clear about the MyCount Cancel Integer business.