Custom Counter for Access 2002

[Leo]

Hi there, I am following up on a post I made last week.
I was told that I needed to use vba code to make a custom
counter. I did some searching and the only document I
could come up with was MS Knowledge Base Article 140908,
and it didn't work.

Could someone lend a helping hand or a direct link to an
article that could help me create a custom counter for my
form in access 2002? Thank you!

Leo

 

[Leo]

rick,

sorry i am kind of dumb. I dont know anything about VBA
code so i would need step by step instructions on where
to put that...

-----Original Message-----


Assuming a form based on Table "MyTable" with an

incrementing record number field

called "ID"

In the Form's BeforeUpdate event. . .

If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "MyTable"), 0) + 1
End If


.

 

[Rick Brandt]

rick,

sorry i am kind of dumb. I dont know anything about VBA
code so i would need step by step instructions on where
to put that...

Open your form in design view and look at the events tab of the property sheet.

Double-Click on the BeforeUpdate event for the form (not a control) and the entry
"[Event Procedure]" should appear. If it doesn't you can select it from the
drop-down list of entries.

Once "[Event Procedure]" is showing in the property, click on the build button to the
right [...]. You should now be in the code module window for the form and Access
will have already inserted the lines to define the BeforeUpdate sub-routine. Just
paste my code in between the lines that were created for you. When finished. it
should look similar to ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "MyTable"), 0) + 1
End If

End Sub

You will need to substitute the actual names of your number field and table in the
code.

 

[Leo]

Rick,

Still can't get it. In my table the field I want to be
the one changing is called WO

The form is called WO, the table is called WO, the number
we want it to start from is 3025.

In the table i tried making WO a number field, is that
right? I also tried it as an autonumber field, niether
worked. I also tried making it the primary key. Still
no luck. Let me know what i'm doing wrong.
This is a copy and paste of what my code looks like.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![WO]) = True Then
Me![WO] = Nz(DMax("[WO]", "WO"), 0) + 1
End If

End Sub

-----Original Message-----

rick,

sorry i am kind of dumb. I dont know anything about VBA
code so i would need step by step instructions on where
to put that...

Open your form in design view and look at the events tab of the property sheet.

Double-Click on the BeforeUpdate event for the form (not a control) and the entry
"[Event Procedure]" should appear. If it doesn't you can select it from the
drop-down list of entries.

Once "[Event Procedure]" is showing in the property,

click on the build button to the

right [...]. You should now be in the code module window for the form and Access
will have already inserted the lines to define the

BeforeUpdate sub-routine. Just

paste my code in between the lines that were created for you. When finished. it
should look similar to ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "MyTable"), 0) + 1
End If

End Sub

You will need to substitute the actual names of your number field and table in the
code.


.

 

[Rick Brandt]

Rick,

Still can't get it. In my table the field I want to be
the one changing is called WO

The form is called WO, the table is called WO, the number
we want it to start from is 3025.

In the table i tried making WO a number field, is that
right? I also tried it as an autonumber field, niether
worked. I also tried making it the primary key. Still
no luck. Let me know what i'm doing wrong.
This is a copy and paste of what my code looks like.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![WO]) = True Then
Me![WO] = Nz(DMax("[WO]", "WO"), 0) + 1
End If

End Sub

You want it to be a number. I would use Long Integer.

Your code looks perfect. In testing I would guess it's not
working because number fields will default to zero and this
causes the IsNull() test to always see a value in the field.
Remove the default value and see if that makes it work.

 

[Leo]

Rick,

I still can't get it to work.

I dont know what i'm doing wrong. It's very
frustrating. My table is called WO, the field is called
WO, what should the properties of the field be? IE
number, auto number, primary key, etc

-----Original Message-----

rick,

sorry i am kind of dumb. I dont know anything about VBA
code so i would need step by step instructions on where
to put that...

Open your form in design view and look at the events tab of the property sheet.

Double-Click on the BeforeUpdate event for the form (not a control) and the entry
"[Event Procedure]" should appear. If it doesn't you can select it from the
drop-down list of entries.

Once "[Event Procedure]" is showing in the property,

click on the build button to the

right [...]. You should now be in the code module window for the form and Access
will have already inserted the lines to define the

BeforeUpdate sub-routine. Just

paste my code in between the lines that were created for you. When finished. it
should look similar to ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "MyTable"), 0) + 1
End If

End Sub

You will need to substitute the actual names of your number field and table in the
code.


.