Automate: Composite Order Number

[lmv]

I'd like to automate my PO number in my orders table it is NOT the PK OrderID
number which is an autonumber. It can be duplicated ... the 001-199 part
could be changed but the projID/CatID/PurchID should be tied to the fields

It is taken from 3 fields in my order form which is based on a qry

LLLL000000LL

LLLL is the ProjID
000 is the catID
000 is 001-199
LL is PurchaserID
Is there a way if all three fields are filled in that with a cmd button or
?? that you could generate that info into the forms field?

Thanks!!

 

[Nick Coe \(UK\)]

I can think of a couple of possible approaches.

One/ Use concatenation (look up the & and + concatenation
operators in help) to bolt the fields together.

An easy way to create a command button is to use the wizard
to create a form close button and then use it for your own
purposes by deleting the Close command, but leave the error
trapping, and put your own code in.

PONum = projID & CatID & PurchID

Where do the 001 to 199 come from in the middle? Can't
figure that bit from your question.

Two/ Run an update query which does the concatenation.
This is more of a batch operation really. Number One is
probably your best bet.

HTH
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In lmv typed:

 

[lmv]

Thank you for the response. My programming skills are very limited and so if
you could give me more instruction on what to put in the click event it would
be helpful.
I built the button but I don't know how to tell the following to fill the
field with the current information on the form.

PurchaseOrderNumber = projectID & CategoryID & PurchaserID

Where do the 001 to 199 come from in the middle? Can't
figure that bit from your question.

This will just be a changeable number that needs to be entered...
As each new order is entered it should increase by 1 starting at 001
I am just trying to automate the process so the person entering a new order
doesn't have to fill this themself

Two/ Run an update query which does the concatenation.

I don't know how to do the above.
Thanks!!
lmv

 

[Nick Coe \(UK\)]

If the fields you're concatenating exist on the form then:

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
Me.PurchaserID

Will set the control(field) PurchaseOrderNumber value. Me
is a shorthand reference to the form the code is in.

You need to substitute your own control names for the ones
I've used/guessed at above.

Having created a close form button with the wizard thus:

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Which I've called cmdClose then delete the line DoCmd Close
and replace it with your version of the line :

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
Me.PurchaserID

That should make you a PO Num without the incrementing
number when it's clicked providing that values exist for
projectID and so on.

Now we have to figure out the 001 - 199 bit.

I suggest adding a new field to your table, make it a long
numeric and set a unique index on it. You may have to fill
out numbers for the existing records if there are any.
Let's call the new field lngCountPO.

Now we'll use DMax() to get the highest existing number in
lngCountPO, add one to it and put that into the
concatenation.

Change your concatenation line to:

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
CStr(DMax(lngCountPO,YourTableName)+1) & Me.PurchaserID

Good luck

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In lmv typed:

 

[lmv]

Hi Nick,
I tried ... nothing happens. No errors nothing...

If the fields you're concatenating exist on the form then:

All are on the form based on a qry and all are already filled in except the
PurchaseOrderNumber.
I tried with ! instead of . I tried without Me I tried with [] around fields
nothing...

Private Sub cmdFillPO_Click()
On Error GoTo Err_cmdFillPO_Click

Me.PurchaseOrderNumber = Me.ProjectID & Me.CategoryID & Me.PurchaserID

Exit_cmdFillPO_Click:
Exit Sub

Err_cmdFillPO_Click:
MsgBox Err.Description
Resume Exit_cmdFillPO_Click

End Sub

 

[Nick Coe \(UK\)]

You're going to have to debug this a bit better. Much as I
like to help I simply don't have the time to create an
application that mirrors what I assume yours is like and do
it myself.

One option is to use MsgBox() to stop and tell you what's
happening to variables. Alternatively use the debugging
tools within the vba editor to watch your variables.

Faultfinding anything must be a ruthlessly logical
procedure, if you jump about trying this and that to see if
it fixes the problem you are unlikely to achieve very much.

If that's a problem then you'll have to repost and see if
anyone else offers any suggestions.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In lmv typed:

Hi Nick,
I tried ... nothing happens. No errors nothing...

If the fields you're concatenating exist on the form
then:

All are on the form based on a qry and all are already
filled
in except the PurchaseOrderNumber.
I tried with ! instead of . I tried without Me I tried
with []
around fields nothing...

Private Sub cmdFillPO_Click()
On Error GoTo Err_cmdFillPO_Click

Me.PurchaseOrderNumber = Me.ProjectID & Me.CategoryID &
Me.PurchaserID

Exit_cmdFillPO_Click:
Exit Sub

Err_cmdFillPO_Click:
MsgBox Err.Description
Resume Exit_cmdFillPO_Click

End Sub
------------------------------
Any ideas?
Thanks
lmv

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
Me.PurchaserID

Will set the control(field) PurchaseOrderNumber value.
Me
is a shorthand reference to the form the code is in.

You need to substitute your own control names for the
ones
I've used/guessed at above.

Having created a close form button with the wizard thus:

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Which I've called cmdClose then delete the line DoCmd
Close
and replace it with your version of the line :

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
Me.PurchaserID

That should make you a PO Num without the incrementing
number when it's clicked providing that values exist for
projectID and so on.

Now we have to figure out the 001 - 199 bit.

I suggest adding a new field to your table, make it a
long
numeric and set a unique index on it. You may have to
fill
out numbers for the existing records if there are any.
Let's call the new field lngCountPO.

Now we'll use DMax() to get the highest existing number
in
lngCountPO, add one to it and put that into the
concatenation.

Change your concatenation line to:

Me.PurchaseOrderNumber = Me.projectID & Me.CategoryID &
CStr(DMax(lngCountPO,YourTableName)+1) & Me.PurchaserID

Good luck

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In
lmv typed: