Sequential numbers only if field status is set to invoice.

[Adam1970]

Hi
I have attached my relationship tables

[Micron]

Looks better than I thought, but maybe not quite right. Looks like there might be some repeating fields, but no idea what a Dim is (which I wonder why that is in at least 3 tables). Interesting that one field name seems to have escaped not having space(s) in its name.

Whether or not it's all properly related depends on knowing the process that the db supports, which we don't. In that case, what's a Despatch? It seems many things are interdependent - as in it seems you can't have an Invoice without a Despatch, without an order, which you can't have without a quote. Can't have a quote without a product id either. You'd have to work through the process on paper or test your schema based on the process.

[Adam1970]

Hi
Dim is a manually entered field by the user & will later be used as a calculation for weight, but i can sort that later.
Despatch is despatch note that will accompany the items once shipped.
quotation is the 1st form & report required. once an order is taken the quotation is converted into an Order, hopefully a button will perform this (99% of the time all items would be ordered) the order would need to be amendable ie change qty, delete item add item etc. once order is ready the despatch note will be created again by a button, this will be identical to the order form but without prices. once despatch is complete an invoice is raised. there should be no changes allowed once the order has got to the despatch form.

[Micron]

I had to look up despatch. Apparently it is the UK spelling of dispatch.

this will be identical to the order form but without prices

Such situations are often handled by having multiple forms/reports for basically the same information or purpose. Better to have one and show/hide/alter things on the fly. I'd consider using the same object and just hiding controls.
I'm getting lost as to whether or not your original question has been answered.

[Adam1970]

Hi
No unfortunately it hasn't.
I would like 1 table that has 3 fields order no, despatch no & invoice no.
With corresponding yes no fields.
On the form if no check box is selected it is a quotation.
If any of the boxes are checked then it represents that field.
However the question was how can I get a different sequential number based upon the selected criteria.
For Eg quote no. = QU00001
Order number maybe SA00040, its 40 as the 1st quote maybe not the 1st ordered.
This would need to be shown on the forms & reports etc.
If I can get the solution I could use switch to populate the ref no on the form

Thanks
Adam

[Adam1970]

Hi
If I have an orderno field in a table & form.
Can I use an iif & dmax statement together on the form In code builder? For example
0rderno = IIF(0rder=true,DMAX("orderno","quote table")+1, null)
I would need this to work as soon as the yes/no is checked, would that be on event? It would also not allow the check to be removed once checked.
Thanks again for any help
Adam

[CJ_London]

try the checkbox click event. If your form is continuous you can't disable the checkbox, you would need to modify your code to something like this (untested)

if isnull(orderno) and order then 0rderno =DMAX("orderno","quote table")+1
order=not isnull(orderno)

you would also need need the to ensure the orderno control is disabled so users cannot edit it

[Adam1970]

Hi
I have attached an image of my table, i have worked created a button on my quoteform to open the specific record in orderform which works, however i also want the button to generate the new sequential order no. my vba code is

onclick

DoCmd.OpenForm "OrderForm", , , "quoteID=" & QuoteID


OrderNo = DMax("OrderNo", "QuoteTable")+1

am i missing something between the 2 commands as this is not working. I also tdeleted the second line of code & added a button on the orderform with the dmax code, but that didnt work either.

i am an old guy trying to learn VBA as i go, so please be gentle if this is an easy fix.

[Micron]

So in that case you'd want your order number to be "1" ? What does "doesn't work" mean?

[Adam1970]

it's not filling the orderno field.
Yes I would like the orderno. To be 1.

[Micron]

After a quick review of all I don't see where any vba (that you mentioned you're using) or query sql, or anything that suggests that you're committing any record additions or edits to a table. I think that at this point you should really consider posting a compacted and zipped copy of your db with instructions on what to do/open to work with your form(s). That should speed things up. If you have to remove tables to get the zip size down (methinks 500kb is max file size that you can upload) just make sure whatever's left will allow for a solution.

[CJ_London]

Is your order no field a number or text? If the latter, you can’t add. First you need to extract the number part, add 1 then put it all back together again. Also, if there are no existing values in the order no field, Dmax returns null, so you need to use the nz function around the dmax function.

[Adam1970]

Is your order no field a number or text? If the latter, you can’t add. First you need to extract the number part, add 1 then put it all back together again. Also, if there are no existing values in the order no field, Dmax returns null, so you need to use the nz function around the dmax function.

Thanks CJ_London Nz function worked perfectly. I only have one more requirement for this part of the project & i can then move on to making the forms look prettier.

the OrderNo is now populating the field however i have enter the following to the afterupdate event for the date, but its not populating the date, is this also something i can use NZ for?

Private Sub OrderNo_AfterUpdate()


If OrderNo = Null Then
OrderDate = Null
Else
OrderDate = Date
End If

End Sub

[CJ_London]

null doesn't equal anything - either it is null or it isn't

change

If OrderNo = Null Then

to

If isnull(OrderNo) Then

[Adam1970]

null doesn't equal anything - either it is null or it isn't

change

If OrderNo = Null Then

to

If isnull(OrderNo) Then

Great thanks worked perfectly, many thanks for your help
Adam