Sequential numbers only if field status is set to invoice.

[Adam1970]

Hi
I have a table which contains a number of fields.
I have an invoice no. field & a yes/no field.


I need the table to generate a sequential invoice no. only if the value is yes in the field.
I would also need the yes/no field locked if yes is selected.

Is this possible?
Thanks
Adam

[CJ_London]

Investigate what is commonly called DMax+1, probably applied on the yesno field click event

see links at the bottom of this thread

[Micron]

I would also need the yes/no field locked if yes is selected.

You cannot lock fields in a table if that's what you mean. You'd have to use a form (and users should not be entering data directly in tables anyway).

[Adam1970]

Hi
I have the formula but when entering it into my field default value i get the message unknown function DMax, i am using access 2016. Do you have any ideas why?

Thanks
Adam

[Welshgasman]

Hi
I have the formula but when entering it into my field default value i get the message unknown function DMax, i am using access 2016. Do you have any ideas why?

Thanks
Adam

Show how you have used it?
This works for me as control source or Default Value

=DMax("ID","TestTransactions")

[Micron]

WGM, I'm beginning to think I'm typing white on white.
No doubt this was put into a table field. Cannot do that.

[Welshgasman]

WGM, I'm beginning to think I'm typing white on white.
No doubt this was put into a table field. Cannot do that.

Oops, sorry
I was testing on a form.

As new users mix the name, fields with controls, I just automatically took it to be in a form.

[Adam1970]

WGM, I'm beginning to think I'm typing white on white.
No doubt this was put into a table field. Cannot do that.

Hi
I was following the method from Chat Gpt-4, it stated that i need to enter the function in the table
"in the "Invoices" table, set the "InvoiceNumber" field to a default value of "DMax("[InvoiceNumber]", "[InvoiceNumbers]") + 1" to automatically generate the next available invoice number when a new record is added."

I have attempted to enter the function in the default value, but the only functions beggining with D are Date, DateS, DateAdd, DateDiff, DatePart, DateSerial, DateValue. Day & DDB.
Dmax is not available. I get unknown function DMax in validation expression or default value.
Thanks
Adam

[CJ_London]

I also didn't realise you wanted this actioned in a table. As you have found, there is limited functionality within a table itself.

Users should not be interacting directly with a table so the assumption is you would be using a form - where the dmax function will work as described

With regard chatGPT - it has a long way to go. You didn't quote what you asked but it is important to get the context exactly right - i.e. you wanted the code in the table - so as with us humans, it misunderstood the actual requirement.

[Micron]

AFAIK, DMax is a domain function and such functions can't be used in a table. Can be used in query, vba code, calculated control or macro. I think I'd find some other non-AI source to answer your Access questions, assuming that's what chatGPT is.

EDIT - it seems my first post lacked clarity in that it didn't include all the feedback I had composed in my old brain. I was hoping for clarification on whether or not the requests pertained to a table (seemed that way to me) and when I didn't get it, I guess I over-reacted a bit. My apologies.

[davegri]

Oddly, with my tests, you can use a built-in function such as Date() for a table level default value, but connot make a user defined function (or domain aggregate function) work without an error (cannot find function named blah...)

[Adam1970]

Hi
i would like to give a bit more detail in what i am attempting to achieve.
i have created all the tables and am now in the process of completing the forms for people data entry.
i have a form quotes which contains quote detail in continuous from.
I copied the above using Richard Rost methods from youtube
i am using the autonumber as my quote ref eg. Q00001. i have a yes/no field for orders, despatch & Invoice.
What i need to happen is if yes is selected for orders then a field for order No.s is populated eg SO000? this may not be 1 as an older or earlier quote may have taken that number, hence the sequential number requirement. once this yes/no field is set to yes then it cannot be changed back to no. i need this repeating for despatch & invoice. i would then need a report with the different headers ie Quotation, Sales Order, Despatch Note, Invoice.
I attempted to create different tables for quotes, orders, despatches, invoices but could not get the numbers to populate etc
I hope i have made myself a little clearer, im more of an excel geek & havent got the access jargon yet.

[Welshgasman]

Do not use the autonumber. That should just be used to link records.
Create your own and use the same method (in the form, NOT THE TABLE) to get the next number.

[CJ_London]

quotes/orders/invoices are all different entities, they just happen to have some similar fields such as a customerID. But the date of each is different, what is quoted isn't always what is ordered, what is invoiced may be part of an order or a combination of two or more orders. Quotes usually have a time limit for being honoured, prices may change, etc

What I think you are saying in post #8 is you have those separate tables.

Each of those tables should have a PK (Primary Key) and you link them with a FK (Foreign Key) in the other table - The FK is populated with the PK from the other table.

So if a quote is then ordered, typically you would store the PK of the quote in the quoteFK field in the order table

Similarly if an invoice only relates to one order, the orderPK would be stored in the orderFK field of the invoice.

If an invoice can related to more than one order or part of an order then you would normally have a joining table to store the PK of the order and the PK of the invoice in a single record.

So your invoice number only needs be generated at the time the invoice is created (using the DMaX+1 method as previously discussed) and is stored in the invoice table, not the order table.

You may well create the invoice by copying data from the order and you might have a button 'create an invoice from this order' button on your order form. But you don't need a tickbox in your order record to say it has been invoiced. That is easily determined by inspecting the invoice (or joining) table for the existence of the orderPK in the orderFK field.

Some things to consider:
What if the wrong order is invoiced and needs to be cancelled?
What if after invoicing the order is modified?
How will you handle credit notes?

You haven't said what your business is or how complex it is so perhaps much of the above doesn't matter. But Access is a very different application to Excel and there is very little of Excel processes/data structures that apply to Access (or any rdbms).

[Micron]

If you are using your Excel brain to design tables, and you need a field to define something as an order, I'd bet those tables are not designed correctly. If you created relationships for those tables, why not post a pic of them? See How to Attach files at the top of the page (you can't copy/paste images or files).