Combo box selection to update text box with new order number

I figured that problem out on my own, but I'm not done with my project

I added [LatestCustOrderNumber]+1 AS NextCustOrderNumber to the CustomerAlpha
query and the combo box row source.

Now, I need to figure out how to update the LatestCustOrderNumber field on
the Customers table with the NextCustOrderNumber value.

--
Message posted via http://www.accessmonster.com

Instead of 'pulling' the new order number into the unbound text box control
you need to 'push' it into a bound control. However, your customers table
design is currently flawed by having a column (field) to store the last order
number per customer. This introduces redundancy as this number can be
derived from the orders table, being the highest order number in that table
for the customer in question. That column can be deleted from the customers
table and from the combo box's RowSource.

You are also introducing redundancy by including the CustomerID value as a
part of the order number. As well as storing the CustomerID in one column
you should store only the sequential number per customer in an OrderNumber
column in the orders table as you can easily return the full structured order
number at any time by concatenating the values of the two columns.

Its important to understand that redundancy is not merely wasteful, it is
dangerous as it leaves a table open to inconsistent data.

Having amended the customers table design, and the data in the order number
column in the orders table in this way you should first add a control to the
orders form bound to the order number column and set its Visible property to
False (No). You should then amend the ControlSource of the unbound
structured order number control to:

= [CustomerID] & Format([OrderNumber],"00000")

In the AfterUpdate event procedure of the customer's combo box you assign a
value to the hidden bound OrderNumber control with:

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "CustomerID = " & ctrl.Column(1)

Me.[OrderNumber] = _
Nz(DMax("OrderNumber", "Orders", strCriteria),0)+1

You'll probably need to change the table and column names in the above to
your real ones. When a customer is selected the value of the highest (MAX)
order number for that customer in the orders table is looked up and 1 added.
If there is no order yet for the customer the Nz function is used to return a
zero and 1 is added to this. That value is then assigned to the bound
OrderNumber control. This is the value stored in the Orders table. The
unbound text box then displays the computed structured order number by
concatenating the CustomerID value to the OrderNumber value, formatting the
latter as five digits with leading zeros where necessary.

One caveat: the following will work fine in a single user environment, but a
conflict could arise in a multi-user environment if two or more users are
adding orders for the same customer simultaneously, as they would each get
the same next order number. To prevent this its important that a unique
index is created on the CustomerID and OrderNumber columns in the orders
table. The easiest way to do this is to make both columns the composite
primary key of the table, which you do in table design view by Ctrl-clicking
on each field, making sure you click on the field selector (the little grey
rectangle to the left of the field name), then right-click and select
'Primary key' from the shortcut menu.

If you are already using another column as the primary key you can index the
fields uniquely by selecting indexes from the View menu. Enter a suitable
index name in one row of the left column, then enter the column names on two
rows of the Field Name column. With the first row (the one with the index
name) selected enter Yes as the 'Unique' property.

Ken Sheridan
Stafford, England

milwhcky wrote:
I figured that problem out on my own, but I'm not done with my project

I added [LatestCustOrderNumber]+1 AS NextCustOrderNumber to the CustomerAlpha
query and the combo box row source.

Now, I need to figure out how to update the LatestCustOrderNumber field on
the Customers table with the NextCustOrderNumber value.

--
Message posted via http://www.accessmonster.com

Ken, thank you for the detailed explanation. I've been out sick the last few
days, so I was unable to thank you sooner.

I think I did everything that you described. I removed the unnecessary
fields from my customers table and combo row source. I added an invisible
control bound to the order number field in the orders table. I added a new
text box for the order number with "= [CustomerID] & Format([OrderNumber],
"00000")" as the control source and added the code you provided as the
AfterUpdate on the combo box (adjusting only the Column number).

That gave me a six digit order number of "000001", regardless of the customer.
I found a fix for the prefix by using "[Form]![Orders]![Customer].[Column](22)
& Format([OrderNumber],"00000")" in the new order number text box control
source.

That gives me a nine digit order number with the proper four digit customer
prefix, but it always ends with 00001. It does not increase if I add more
orders for a given customer.

Again, thanks for the help. Your advice makes sense, but I'm not quite able
figure out what I am doing wrong.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

I think I misread the SQL statement behind you customers combo box. Looking
at it again it looks to me like its bound column is CustomerID, right? In
which case the code for its AfterUpdate event procedure should have been:

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "CustomerID = " & ctrl

Me.[OrderNumber] = _
Nz(DMax("OrderNumber", "Orders", strCriteria),0)+1

That should assign the next sequential value for the selected customer to the
OrderNumber field (and to the hidden control bound to it) in the form's
current record. However, I note that you are referencing the customer number
as Column(22) of the control when concatenating it to the order number in the
unbound text box. Does the value in this column differ form the CustomerID,
which appears to be the control's bound column? If it does then amending a
line of the above code to:

strCriteria = "CustomerID = " & ctrl.Column(22)

should work.

I must admit that I do wonder why a combo box has 21 (or more) columns??

Ken Sheridan
Stafford, England

milwhcky wrote:
Ken, thank you for the detailed explanation. I've been out sick the last few
days, so I was unable to thank you sooner.

I think I did everything that you described. I removed the unnecessary
fields from my customers table and combo row source. I added an invisible
control bound to the order number field in the orders table. I added a new
text box for the order number with "= [CustomerID] & Format([OrderNumber],
"00000")" as the control source and added the code you provided as the
AfterUpdate on the combo box (adjusting only the Column number).

That gave me a six digit order number of "000001", regardless of the customer.
I found a fix for the prefix by using "[Form]![Orders]![Customer].[Column](22)
& Format([OrderNumber],"00000")" in the new order number text box control
source.

That gives me a nine digit order number with the proper four digit customer
prefix, but it always ends with 00001. It does not increase if I add more
orders for a given customer.

Again, thanks for the help. Your advice makes sense, but I'm not quite able
figure out what I am doing wrong.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200910/1

Thanks for your help, Ken. I was getting type-mismatch errors with your
latest advice, but I came across an idea which has worked in testing so far.

I added a field to the query tied to the combo box (MaxOfOrderNumber from the
Orders table). I added another hidden column to the combo (23) for that
value. I then changed the combo box AfterUpdate code to:

Dim ctrl As Control
Set ctrl = Me.ActiveControl
Me.[OrderNumber]= Nz((ctrl.Column(23)),0)+1

Yes, the CustomerID is different from the customer order number prefix I am
using. The CustomerID is alpha-numeric. I wanted a strictly numeric order
number, so I added a field to the customer table for each non-obsolete
customer to have a four digit order number prefix ("NewCustomerID" as Column
22).

I decided against replacing the original CustomerID with the new four digit
number ID in fear that it might goof up some of the various historical sales
reports in the database. As I become more familar with this database (and
Access in general), I will work at reducing redundancy, which you warned
against.

The combo box has over 20 hidden columns which fill in other fields in the
form when a customer is selected... shipping address fields, billing address
fields, shipping rates, special instructions, triggers for certain reports,
etc. It's something I inherited, but it works.

Again, thanks for your help.

KenSheridan wrote:
I think I misread the SQL statement behind you customers combo box. Looking
at it again it looks to me like its bound column is CustomerID, right? In
which case the code for its AfterUpdate event procedure should have been:

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "CustomerID = " & ctrl

Me.[OrderNumber] = _
Nz(DMax("OrderNumber", "Orders", strCriteria),0)+1

That should assign the next sequential value for the selected customer to the
OrderNumber field (and to the hidden control bound to it) in the form's
current record. However, I note that you are referencing the customer number
as Column(22) of the control when concatenating it to the order number in the
unbound text box. Does the value in this column differ form the CustomerID,
which appears to be the control's bound column? If it does then amending a
line of the above code to:

strCriteria = "CustomerID = " & ctrl.Column(22)

should work.

I must admit that I do wonder why a combo box has 21 (or more) columns??

Ken Sheridan
Stafford, England

Ken, thank you for the detailed explanation. I've been out sick the last few
days, so I was unable to thank you sooner.
[quoted text clipped - 17 lines]
Again, thanks for the help. Your advice makes sense, but I'm not quite able
figure out what I am doing wrong.

--
Message posted via http://www.accessmonster.com