Creating a Order # on form (1 Viewer)

miked1978

New member
Local time
Today, 08:09
Joined
May 22, 2020
Messages
25
I need to generate an Order # on a form. I currently have a continuous form and the bottom is in Datasheet view. Each record has a check box and the user can check whichever record(s) they want and a SAVE button is provided that saves their selection to a SQL table when the button is pressed. That all works great.

I currently have an Access query that is generating a custom Order # and it runs when that SAVE button is pressed. I'm creating the Order # based off a field in the table and the date/time stamp on when the SAVE button was pressed. I know this is bad practice and it gives me too long of a Order # that my users will need to use in the future to reference.

Its important to note that a Order can consist of multiple records so I don't think adding a autonumber field is going to help. In theory a user can select 100 records before saving and that would make up 1 order.

Any suggestions on how I could do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:09
Joined
Oct 29, 2018
Messages
21,357
If you want a sequential Order #, have you tried using DMax()? Lots of examples on it are available. You should be able to find one if you do a search.
 

plog

Banishment Pending
Local time
Today, 08:09
Joined
May 11, 2011
Messages
11,611
Lots of sentences are red flags in your post, but let's work from this one:

Its important to note that a Order can consist of multiple records so I don't think adding a autonumber field is going to help

Actually, no, an order should be 1 record in your Order table. Then if you have items to add to that order those can be multiple records in another table. I fear you've built a spreadsheet in Access and aren't using it to its full potential.

Can you complete the Relationship Tool in Access, expand every table so I can see the fields, take a screenshot and then post it back here? Or post your database itself with any sensitive data omitted.
 

miked1978

New member
Local time
Today, 08:09
Joined
May 22, 2020
Messages
25
If you want a sequential Order #, have you tried using DMax()? Lots of examples on it are available. You should be able to find one if you do a search.

I'll search for Dmax. I was searching for auto generating a # and didn't get anything that I thought would work
 

miked1978

New member
Local time
Today, 08:09
Joined
May 22, 2020
Messages
25
Lots of sentences are red flags in your post, but let's work from this one:



Actually, no, an order should be 1 record in your Order table. Then if you have items to add to that order those can be multiple records in another table. I fear you've built a spreadsheet in Access and aren't using it to its full potential.

Can you complete the Relationship Tool in Access, expand every table so I can see the fields, take a screenshot and then post it back here? Or post your database itself with any sensitive data omitted.
You're right. I might be thinking about this all wrong by trying to force everything into one table when maybe I need to be using multiple tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:09
Joined
May 7, 2009
Messages
19,169
does a Manual invoice system rings to you when you have
a blank pre-printed form which are pre-numbered?
you only need to fill this form with the invoice item and the
date of the invoice and customer info.

with similar method, you can add a table with pre-numbered
records and a boolean field signifying if that number has
already been used.

you then Lookup the table for the least unused number.
no need to save the Number on the table, only its PK field.
 

vhung

Member
Local time
Today, 06:09
Joined
Jul 8, 2020
Messages
235
I need to generate an Order # on a form. I currently have a continuous form and the bottom is in Datasheet view. Each record has a check box and the user can check whichever record(s) they want and a SAVE button is provided that saves their selection to a SQL table when the button is pressed. That all works great.

I currently have an Access query that is generating a custom Order # and it runs when that SAVE button is pressed. I'm creating the Order # based off a field in the table and the date/time stamp on when the SAVE button was pressed. I know this is bad practice and it gives me too long of a Order # that my users will need to use in the future to reference.

Its important to note that a Order can consist of multiple records so I don't think adding a autonumber field is going to help. In theory a user can select 100 records before saving and that would make up 1 order.

Any suggestions on how I could do this?
try to use other way;
>custom Order # + 1
ex. SAVE button is pressed "custom Order # =0+ 1"
custom Order #1
next set is
sort order method; biggest 1
then 1=1
custom Order #2
 

Users who are viewing this thread

Top Bottom