Don't understand VBasic

  • Thread starter Thread starter Francisco
  • Start date Start date
F

Francisco

How can I do this?
I have a field in an form that is number, and I want it to
increment for every new page
Ex. page 1 10980
page 2 10981
How can I do this without autonumber?
I tried this but I have a problem with the null, is this
ok?

Private Sub Form_Current()
If Me.NewRecord Then
' we do not want modify EXISTING record, isn't it
Me.PO_Number = Nz((DMax(PO_Number, "PO Log Table",
[PO Number])), "0") + 1
End If
End Sub

I'm an Access 2000 user with little or no knowledge of
Vbasic.
 
The DMax function has the following format:

DMax(Expr, Domain, Criteria)

The criteria argument is optional. Your statement could be written as:

Me.PO_Number = Nz((DMax("PO_Number", "PO Log Table")),"0") + 1

This should return the highest value in the PO_Number field of the PO Log
Table plus 1 or 1 if there are no records in the table. I believe you could
just put:

= Nz((DMax("PO_Number", "PO Log Table")),"0") + 1

in the default value of the PO_Number control you're using to store the
number. That way you don't need to put any code in any module or worry about
whether or not you're in a new record. The default value would only be used
if the field was not already populated. You need to be careful about how you
name your controls to avoid confusion. In your example it's not clear what
the name of your field is. You refer to "Me.PO_Number", "PO_Number" and "PO
Number". It's often a good idea to give your control names a prefix such as
txtPO_Number if it's a text box or cboPO_Number to signify that it's a
combobox. That way you won't get the name of your control mixed up with the
name of the field in the table for which your control stores data.
 
How can I do this?
I have a field in an form that is number, and I want it to
increment for every new page
Click to expand...

Every new "page"? Tables don't have pages, they have records - is that
what you mean?
Ex. page 1 10980
page 2 10981
How can I do this without autonumber?
I tried this but I have a problem with the null, is this
ok?

Private Sub Form_Current()
If Me.NewRecord Then
' we do not want modify EXISTING record, isn't it
Me.PO_Number = Nz((DMax(PO_Number, "PO Log Table",
[PO Number])), "0") + 1
End If
End Sub
Click to expand...

Almost. The quote marks around the 0 are making it a Text String
instead of a number, and all three arguments of DMax should be Text
values rather than the names of fields. Since you want the maximum PO
number in the entire table, you can leave the optional third argument
off; and since NZ() defaults to returning a zero if you don't specify,
you can leave that off too. Try:

Me.PO_Number = Nz(DMax("PO_Number", "[PO Log Table]")) + 1

The Form's BeforeInsert event might be a better choice than Current -
no need to test for NewRecord.
 
Back
Top