Sequential Order ID on Form (2 Viewers)

[charya]

Hi everyone,
Its my first thread here!

Let me describe the database Im doing...

Consist of 4 tables - customer, order, supplier and product
Customer can have many orders, one order can only have one product, and a supplier can supply many products.

What I want to do:
Create an Order Form in which the Order ID field automatically has a sequential Order ID.
Lets say the Order Table's last value was ORD125.
The form would then put a sequential value in Order ID of ORD126.

The rest of the fields in the form would be filled by the customer.

What Ive done:
Ive created a form and set the Data Entry to "Yes" so that the form is blank.

Ive created one query to take the Higest Order ID from the order table (lets say ORD123), and then ive created a second query which uses the first query to generate a sequential ID.

i.e.:
First query: ORD123
Second Query: ORD123 +1 = ORD124 (I did this with "ORD" & RIGHT etc.)

I simply linked the OrderID field from the Form to the second query so that the sequential id appears, but I keep getting "Name?"

Thats the problem really, any help would be really appreciated!

Thanks so much!

 

[John Big Booty]

Welcome to the forum.

You could use the following code in your form's On Current Event;

If Me.OrderNum = 0 Or IsNull(Me.OrderNum) Then[COLOR="Green"] 'check if this record already holds and order number[/COLOR]
        Me.OrderNum = [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL]([URL="http://www.techonthenet.com/access/functions/domain/dmax.php"]DMax[/URL]("OrderNum", "TBL_Orders") + 1, 1)
End If

You can then use the following in and Unbound text box to display your order number;

= "ORD" & Me.OrdNum

The attached sample has a practical example.

 

[charya]

Welcome to the forum.

Hi,

Thanks, but im not too familiar with using Codes, thus I try sort the problem out by doing queries etc.

Do you think you could re-read what Ive said and try figure out a solution via queries and stuff? I think what ive done up till now is good, but im missing something! - thats why I cant get the query field to show up properly on the form.

 

[John Big Booty]

In this situation I'd suggest you bite the bullet and go with the code, it is very simple and only consists of 3 lines, it should be a simple matter of changing the various (highlighted) references to reflect the controls and objects in your DB.

If Me.[COLOR="Red"]OrderNum[/COLOR] = 0 Or IsNull(Me.[COLOR="Red"]OrderNum[/COLOR]) Then [COLOR="Green"]'check if this record already holds and order number[/COLOR]
        Me.[COLOR="Red"]OrderNum[/COLOR] = Nz(DMax("[COLOR="Red"]OrderNum[/COLOR]", "[COLOR="Red"]TBL_Orders[/COLOR]") + 1, 1)
End If

In fact there are only two things you need to change. Change OrderNum to reflect the name of the name of the control that holds your Order Number and TBL_Orders to the name of the table that stores the Order Numbers.

Simply copy and paste the code into Your form's On Current event and make the required changes.

 

[charya]

In this situation I'd suggest you bite the bullet and go with the code, it is very simple and only consists of 3 lines, it should be a simple matter of changing the various (highlighted) references to reflect the controls and objects in your DB.

If Me.[COLOR="Red"]OrderNum[/COLOR] = 0 Or IsNull(Me.[COLOR="Red"]OrderNum[/COLOR]) Then [COLOR="Green"]'check if this record already holds and order number[/COLOR]
        Me.[COLOR="Red"]OrderNum[/COLOR] = Nz(DMax("[COLOR="Red"]OrderNum[/COLOR]", "[COLOR="Red"]TBL_Orders[/COLOR]") + 1, 1)
End If

In fact there are only two things you need to change. Change OrderNum to reflect the name of the name of the control that holds your Order Number and TBL_Orders to the name of the table that stores the Order Numbers.

Simply copy and paste the code into Your form's On Current event and make the required changes.

Problem is..im not too familiar with editing code and stuff..
have you got MSN/Skype so we can sort this quicker?

Thanks

 

[charya]

UPDATE: I used the following code after editing:

"If Me.Order_number = 0 Or IsNull(Me.Order_number) Then 'check if this record already holds and order number
Me.Order_number = Nz(DMax("Order_number", "tblOrder") + 1, 1)
End If"

Access already had some extra stuff so the final code was:

"Private Sub Form_Current()
If Me.Order_Number = 0 Or IsNull(Me.Order_Number) Then 'check if this record already holds and order number
Me.Order_Number = Nz(DMax("Order_number", "tblOrder") + 1, 1)
End If
End Sub
"

Now, When i open the form, I get some "Type mismatch"

 

[John Big Booty]

Open You form in design view. Click in the top left hand corner of the form you should see a small black square appear in the box between the vertical and horizontal rulers. Now in the form's On Current event select Event Procedure from the drop down list. Now click on the button with the three full stops on it (ellipsis), the code wind will open, past the code below Private Sub Form_Current() and above End Sub. Make the changes mentioned to the code in my previous post.

 

[charya]

Open You form in design view. Click in the top left hand corner of the form you should see a small black square appear in the box between the vertical and horizontal rulers. Now in the form's On Current event select Event Procedure from the drop down list. Now click on the button with the three full stops on it (ellipsis), the code wind will open, past the code below Private Sub Form_Current() and above End Sub. Make the changes mentioned to the code in my previous post.

Read my last reply

 

[John Big Booty]

Read my last reply

Sorry you must have posted whilst I was typing reading now

 

[John Big Booty]

OK check how your field Order_number is formatted, if it is not change it's format to Number if it is not already.

 

[charya]

Sorry you must have posted whilst I was typing reading now

Ok no prob
I fixed that somehow, but I got another error

I changed the field name to "orderid" instead.

I substituted it on the form, and now when I open the form i get
Compile error: Method or data member not found.

 

[charya]

OK check how your field Order_number is formatted, if it is not change it's format to Number if it is not already.

Cant put it as Number..its ORD111, so it has to be Text.
I have to store the id as ORDXXX, and I cant get into using Strings and stuff..has to be something simpler.

 

[John Big Booty]

You only need the ORD prefix for display purposes, so don't store it, simply append it to the order number whenever it needs to be displayed, via the method I mentioned in my first post.

Remember, that Tables are not for human consumption, so it is irrelevant how things appear when the raw data is viewed. Your users should only interact with that data via Forms or view it in Reports, at which point you can manipulate the data so the user sees things in a format they are expecting.

 

[charya]

You only need the ORD prefix for display purposes, so don't store it, simply append it to the order number whenever it needs to be displayed, via the method I mentioned in my first post.

Remember, that Tables are not for human consumption, so it is irrelevant how things appear when the raw data is viewed. Your users should only interact with that data via Forms or view it in Reports, at which point you can manipulate the data so the user sees things in a format they are expecting.

But I have to store it in the table as ORDXXX :S, thats a must in this case.
Can I speak to you on MSN or something?

 

[Rabbie]

But I have to store it in the table as ORDXXX :S, thats a must in this case.
Can I speak to you on MSN or something?

Read JBB's post. The world won't stop if you listen to his excellent advice and you might get your DB to work

 

[charya]

Read JBB's post. The world won't stop if you listen to his excellent advice and you might get your DB to work

No, its not that - his advice is really good, but Im doing this in an exam and I have to store it as ORDXXX.

 

[Rabbie]

No, its not that - his advice is really good, but Im doing this in an exam and I have to store it as ORDXXX.

So its coursework! Good luck

 

[charya]

So its coursework! Good luck

Thanks! - Hope I get this sorted today. :S

 

[John Big Booty]

Does the course work specify that the order number must be stored in the format ORDxxx or simply displayed in that format

 

[charya]

Does the course work specify that the order number must be stored in the format ORDxxx or simply displayed in that format

Need to store the record with ORD.
Do you want a copy of my database to see what Ive done till now? (Including that code you gave me?)