PO# DMax

[Guest]

My PO number is

LLLL000LL

first part is generated in the new record by the ProjID and last PurchaserID
I would like the numbers to increase by 1 is there a way to do this.
I have this as a cmd button on the PO field now but would like it to do it
automatic... after choosing the Purchaser...is that possible?
Thanks!!

Private Sub cmdFillPO_Enter()
On Error GoTo Err_cmdFillPO_Click

Me.PurchaseOrderNumber = Me.ProjectID & "000" & Me.PurchaserID

Exit_cmdFillPO_Click:
Exit Sub

Err_cmdFillPO_Click:
MsgBox Err.Description
Resume Exit_cmdFillPO_Click
End Sub

 

[Rick Brandt]

My PO number is
first part is generated in the new record by the ProjID and last PurchaserID
I would like the numbers to increase by 1 is there a way to do this.
I have this as a cmd button on the PO field now but would like it to do it
automatic... after choosing the Purchaser...is that possible?
Thanks!!

Private Sub cmdFillPO_Enter()
On Error GoTo Err_cmdFillPO_Click

Me.PurchaseOrderNumber = Me.ProjectID & "000" & Me.PurchaserID

Exit_cmdFillPO_Click:
Exit Sub

Err_cmdFillPO_Click:
MsgBox Err.Description
Resume Exit_cmdFillPO_Click
End Sub

The best way would be to have a separate number field and NO PurchaseOrderNumber
field at all. You can then combine all three fields together for *display* and
call it a PONumber. It does not need to have a separate field in the table.

If the numeric portion is in its own field then it's fairly easy to DMax() + 1
to get the next number.

 

[Guest]

Thanks for the response...
I tried the following but it didn't work...

Me.PurchaseOrderNumber = Me.projectID & _
CStr(DMax(lngCountPO,Orders)+1) & Me.PurchaserID

The form is based on a qry and the fields ProjectID and PurchaserID are not
saved to the table when I fill the field that will generate the number. Does
that have anything to do with why it doesn't work?

Thanks

 

[Rick Brandt]

Thanks for the response...
I tried the following but it didn't work...

Me.PurchaseOrderNumber = Me.projectID & _
CStr(DMax(lngCountPO,Orders)+1) & Me.PurchaserID

The form is based on a qry and the fields ProjectID and PurchaserID are not
saved to the table when I fill the field that will generate the number. Does
that have anything to do with why it doesn't work?

The arguments to DMax all belong in quotes unless they are variable names. What
field is lngCountPO? Are you doing something to increment that as well?

 

[Guest]

I don't know what I am doing ... I am just trying other things I have read
and suggestions I have gotten.

Someone suggested making a field that was a number field that is what
lngCountPO
is...

LLLL000LL

Is the format...

So, could you actually show me where to put the quotes?
Thanks...

 

[Rick Brandt]

I don't know what I am doing ... I am just trying other things I have read
and suggestions I have gotten.

Someone suggested making a field that was a number field that is what
lngCountPO
is...


Is the format...

So, could you actually show me where to put the quotes?
Thanks...

If I were doing this I would have three fields all stored in my order table...

ProjectID
PONum
PurchaserID

....and I would use an expression to combine them only for display. There would
be no stored field in the format LLLL000LL, but that is what the user would see
on all forms and reports. In that context I could use the BeforeUpdate event to
assign the PONum with...

If Me.NewRecord Then _
Me.PONum = Nz(DMax("PONum", "Orders"), 0) + 1

The Nz() is only required for the very first record entered.

There is an alternative method that involves grabbing the next numeric value
from a separate table, but that does not use the DMax() method at all. If you
were seeing posts referring to that method there should have been links to full
examples.