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 news:B87D7942-CD02-4838-BADC-(E-Mail Removed),
lmv typed:
> 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
>
>
>> 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
>> news:AFE51F78-A710-4D8B-A2E5-(E-Mail Removed),
>> lmv typed:
>>> 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!!
Similar Threads
