How is the ordes details retrieving the Unit Prices?

[EZ KEY]

I have setup my orders system identical to the Northwind example, with a
subform based upon a query I created from the Order Details table. When I
enter the product in the subform, it all comes up nicely and correctly in
relation to product and price. However, the details of my order located back
in the Orders Details table (in my case: Product ID, Product Name, Product
Price fields) record everything EXCEPT the Prices for the orders.

Why would that occur? I've crossed checked everything to Northwind and I
can't seem to find the detail I'm missing. Any suggestions or help?

Thanks

 

[Ken Snell \(MVP\)]

Do you have a control on your subform that is bound to the Prices field?

 

[EZ KEY]

Ken:

Sorry to get back to you late, I work very early, and very late. I will try
this and get back to you. It sounds simple enough, as long as I have the
name right. Mine aren't exactly the same field name, but very close.

I will get back to you either way. Thanks for the dummy explanation, I
needed it.

 

[EZ KEY]

Ken:

Well I did go to the subform and selected my Product ID combo box, and
pasted and edited the code accordingly. However, as I feared, it would go
easily for me and I may have missed a couple of things. So, I thought I
would give some information that may help you help me.

You are correct, my subform is made up from a query of my Order Details
table with the following fields: Orders ID; Product Name; Product Price.
This is how I put the code in:

On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Product_ID = " & Me!Product_ID

' Look up product's unit price and assign it to UnitPrice control.
Me!Product_Price = DLookup("Product_Price", "Products_Name", strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

Was this correct?

 

[Ken Sheridan]

As you don't seem to have a Product_ID column in the query it looks like you
are using the text column Product_Name as the foreign key in the order
Details table rather than a numeric Product_ID column as in the Northwind
database, right? From this I assume that the Products_Name table also has a
Product_Name column as its primary key. This is fine so long as all product
names are unique. It does however make a difference to the code as text
values need to be delimited with quotes characters, so the line of code which
assigns a value to the strFilter variable would be:

strFilter = "[Product Name] = """ & Me![Product Name] & """"

A pair of adjacent quotes characters within a string itself delimited by
quotes evaluates as a literal quotes character. If, despite its name,
Product Name is a number data type column then omit the extra quotes.

Ken Sheridan
Stafford, England

 

[EZ KEY]

Ken:

First, thanks for the book info. I printed it out and will certainly look
some stuff up.

Well, I did what you suggested exactly as posted and I got an error similar
to this:
"You canceled the previous operation",
everytime I select something in my subforms drop down selection under
Product Name.

I think I did it correctly, I think it is just interfering with something
else already happening.

Now, this may be a stupid question/comment. However, in studying the VBA
commands I put in. I don't see how those commands would know to copy my
Product Price from my Subform "Order Details", and paste that value in the
Order Details table under the "Product Price" field there?

This is the last detail I need to get to function and my database is done.
So, I'm sure you may understand why I am so eager to understand and get this
done.

Thanks again. I look forward to your reply. Thanks for the book info too.

I hope I didn't give the impression of seeing any sarcasm; that certainly
wasn't the case. As regards books a few with which I'm acquainted are:

1. As a general primer I found John L Viescas's 'Running Microsoft Access'
to be good. The latest edition listed by Amazon is for Access 2000 (27.89
USD). There are more recent books listed by the same author, however.

2. For VBA programming in Access Evan Callahan's 'Microsoft Access Visual
basic Step by Step' provides an easily digested introduction. The latest
edition listed by Amazon is for Access 2002 (28.50 USD)

3. A very useful little book on the underlying principles behind relational
databases is 'Inside Relational Databases with Examples in Access' by Mark
Whitehorn and Bill Marklyn. I occasionally contribute stuff for Mark's
column in Personal Computer World magazine (on which the book is largely
based), so I confess a small bias here, but an understanding of the
underlying theory of the database relational model is extremely important,
and this book is a good introduction for non-experts. Amazon currently list
it at 32.97 USD.

4. For experienced Access developers the must-have is the 'Access 2002
Developer's Handbook' by Paul Litwin, Ken Getz and Mike Gunderloy. It comes
in two volumes, Desktop and Enterprise. The set is listed by Amazon at 99.98
USD. It is not one for beginners however.

5. Another one for experienced database developers is Joe Celko's 'SQL for
Smarties: Advanced SQL Programming'. This is not Access, but is a goldmine
of useful information on writing queries, which are at the heart of any
serious database application. Again not for beginners though. Current price
on Amazon is 32.97 USD.

6. Finally, the Bible of relational database theory, Chris Date's
'Introduction to Database Systems'. Quite abstract and not an easy read, but
essential reading for anyone who wants to know what relational theory is all
about under the skin. Definitely not for beginners. Currently 97.42 USD on
Amazon.

Ken Sheridan
Stafford, England

Ken:

I hope I didn't come off sarcastic, that wasn't my intent. This has all
been very helpful, just want to make sure I'm providing you good information
so you can help me out. I did change names of some fields so my boss would
better understand the necessary fields... so I had changed the captioning in
some cases.

You have assumed correctly in regards to changing UnitPrice to Product Price.

This helps. I still don't completely understand the VBA code terms, but I
do understand its general concept. I will give this a try and let you know.

Thanks for your time and patience. Could you recommend any books on Access
that are thourough? I've read the Comprehensive version written by Shelly
Cashman publishers. But it just scratches the surface.

Lets clear up a few general points first:

1. The caption property of a column in a query is irrelevant when
referencing that column. The actual name of the column is what matters.
This is why the Northwind code references the ProductID rather than the
caption 'Product' which it gives to the column.

2. A table can only have one primary key. The two columns are a
*composite* primary key, which means their values in combination must be
unique within the table. ProductID and OrderID, which make up the primary
key are individually foreign keys referencing the primary keys of Orders and
Products.

In the code for the AfterUpdate event procedure of the combo box you should
therefore be referencing [Product ID] (the brackets are necessary as it
includes a space; you don't need to put underscores) where the Northwind
code references ProductID, and [Product Services] where the Northwind code
references Products. As the Product ID is a number data type you don't need
to delimit it with quotes, so the syntax will be much the same as the
Northwind's.

From what you've said earlier I take it that you've used Product Price (as
the column name, not just its caption) where Northwind uses UnitPrice. I
assume that this is the name of the columns in the Order Details table and
Product Services tables.

So putting this all together the code should be:

On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "[Product ID] = " & Me![Product ID]

' Look up product's unit price and assign it to Product Price control.
Me![Product Price] = DLookup("[Product Price]", "[ Product Services]",
strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

Ken Sheridan
Stafford, England

:

Ken:
PK = Primary Key

Actually, I do have a Product ID (PK) in the query and it says that in the
Query Design View, however, it displays the Product Name because I captioned
it to read "Product Name" in the Order Details table. This is also what was
done in the Northwind example in regards to the Order Details table setup.
It changed the captioning as well.

In creating my Order Details table, the Product ID (PK) in that table
queries the Product Services Table (e.g. Like Products table in Northwind) in
the "LookUp" so I can select the Product Name from Product Services Table.
So, I changed the captioning to "Product Name" for the Product ID (PK) in the
Order Details table because that is what it displays.

This is how the Order Details table is in the fields list:

Order ID (PK); Product ID (PK) (caption reads Product Name); Product Price

So, as a result in creating my query, it displays:

Order ID; Product Name; Product Price

My Order Details table contains two Primary Keys as in Northwind, and they
are the same primary keys. Just the Product ID displays the Product Name
when I open the dataview, because I changed the captioning.

How would this affect my VBA editing so I can correct it? Hope this all
makes sense. It is setup just like Northwind, some names are different.
Also, another question would, in my VBA, do I have to put underscores (i.e.
__) for spaces in my field names so it reads correctly. I have done so, but
wasn't sure.

Thanks for the help!!



:

As you don't seem to have a Product_ID column in the query it looks like you
are using the text column Product_Name as the foreign key in the order
Details table rather than a numeric Product_ID column as in the Northwind
database, right? From this I assume that the Products_Name table also has a
Product_Name column as its primary key. This is fine so long as all product
names are unique. It does however make a difference to the code as text
values need to be delimited with quotes characters, so the line of code which
assigns a value to the strFilter variable would be:

strFilter = "[Product Name] = """ & Me![Product Name] & """"

A pair of adjacent quotes characters within a string itself delimited by
quotes evaluates as a literal quotes character. If, despite its name,
Product Name is a number data type column then omit the extra quotes.

Ken Sheridan
Stafford, England

:

Ken:

Well I did go to the subform and selected my Product ID combo box, and
pasted and edited the code accordingly. However, as I feared, it would go
easily for me and I may have missed a couple of things. So, I thought I
would give some information that may help you help me.

You are correct, my subform is made up from a query of my Order Details
table with the following fields: Orders ID; Product Name; Product Price.
This is how I put the code in:

On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Product_ID = " & Me!Product_ID

' Look up product's unit price and assign it to UnitPrice control.
Me!Product_Price = DLookup("Product_Price", "Products_Name", strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

Was this correct?

:

The important thing is that the UnitPrice column in your query is the column
(field) from the OrderDetails table, *not* from the Products table. Its this
column you want to assign a value to. In fact you

The code you need to insert is VBA, not SQL. SQL is the language used for
defining queries, while VBA (Visual basic for Applications) is the language
used for writing procedures and functions in modules. Each form has a
module, known as its class module, which holds the code for procedures or
functions associated with the form. Forms and controls on forms have 'event
procedures' in the form's class module, which are triggered when a certain
event occurs, such as the updating of a value in a control. You create an
event procedure by first opening the subform in design view.

In this case you want to create an event procedure which fires when the
value of the ProductID combo box control is updated, so to do this select the
combo box bound to the ProductID column, open its properties sheet and select
the After Update event property. Click on the 'build' button; that's the one
on the right with 3 dots. In the next dialogue select 'Code Builder' and
exit the dialogue. The VBA window should now open at the control's
AfterUpdate event property. The first and last lines will already be in
place, so you enter the code as new lines between them. If you've used the
same table, field names etc as the Northwind database you should be able to
paste the code I sent you in without any changes, but if you've changed any
of the object names you'll need to amend the code accordingly.

Don't hesitate to post back if you need further help.

Ken Sheridan
Stafford, England

:

I think I understand. Remember, I'm naive and new. Now, my Orders Details
subform is in "Dataview" and it is a subform based upon a query I created
from the Orders Details and Product tables.

"Orders ID" & "Product ID" from the Orders Details table
"Product Price" from the Products table

As a result, the order ID is directly inserted (Order ID field), and I
select the product name (Product ID field) from a drop down list, and its
price (Product Price field) is directly recalled all in the same subform.

Now from there, you are saying I need to change the code through the SQL
viewer to that which you suggested? Do I do this through the "forms view"
and click on the Product Name combo box, or is this the SQL for the entire
form? I'm not sure exactly how to get to the right place. Do I need a
"DLookUp" function in there somewhere then since that is in the SQL you
suggested?

Just bear with me. Just trying to understand.

Thanks




:

You need to assign the value from the Products table to a control bound to a
UnitPrice column in the OrderDetails table in the AfterUpdate event procedure
of the ProductID combo box. There are various ways to do this but the
Northwind database uses the following code:

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

What you must not do is simply look up the price from the products table in
an unbound control. The price in the Products table will change over time,
but you want to keep the price at the time of each order with the order, so a
column for the UnitPrice is needed in the OrderDetails table, and a control
on the subform bound to this column.

Ken Sheridan
Stafford, England

:

I have setup my orders system identical to the Northwind example, with a
subform based upon a query I created from the Order Details table. When I
enter the product in the subform, it all comes up nicely and correctly in
relation to product and price. However, the details of my order located back
in the Orders Details table (in my case: Product ID, Product Name, Product

 

[Ken Sheridan]

I'm not sure why you are getting that error. Ideally you need to debug the
code using the built in debugging tools, but lets first review how the
Product ID and product price controls are set up on the subform:

1. The Product ID field's control is your combo box from which you select a
Product Name. The way this works is that you see the names from the table of
products, but the value of the control is the Product ID for that name. This
achieved by hiding the first column of the list by setting properties of the
combo box as follows, so review these to see that its set up correctly:

ControlSource: [Product ID] (the underlying field from the Product
Details table)

RowSource: Either an SQL statement along the following lines or the
name of a saved query whose SQL is similar:

SELECT [Product ID], [Product Name]
FROM [Product Services]
ORDER By [Product Name];

You'll see that this query returns two columns the Product ID and Product
Name, and is ordered by the latter:

BoundColumn: 1 (this makes the first column, Product ID, the value of
the control)

ColumnCount: 2 (the number of columns returned by the RowSource query)

ColumnWidths: 0cm;8cm (or rough equivalent in inches. The important
thing is that the first dimension is zero; this hides the Product ID column
so you see just the names.


2. The Product Price control is a text box with [Product Price], the field
in the underlying Product Details table, as its ControlSource property.

The code in the Products combo box's AfterUpdate event procedure doesn't
'paste' the product price value from the Product Details table into the field
in the Order Details table, it 'assigns' the value to it with this line:

Me![Product Price] = DLookup("[Product Price]", "[ Product Services]",
strFilter)

You should see the value appear in the Product Price text box when a
selection is made from the combo box's list. At this stage the subform is
'Dirty', i.e. the record isn't yet saved to the Product Details table. That
is done when you leave the subform, move to another record in it or expressly
save the record using the menu, toolbar or keyboard short cut.

Check that the two controls are set up as above. If they are there should
be no problem, so some debugging will be needed to pin down the reason for
the error. If necessary I'll try and walk you through how to do that but its
really something you'd have to tackle yourself as there is a limit to how
much I can do remotely without having access to your file.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

By 'Product Details' I actually meant Order Details, which is the table
underlying the subform. If the RecordSource of the subform is a query then
it need only include this table, not the Product Details table
(notwithstanding the fact that Northwind does).

The RowSource of the combo box is fine, though the inclusion of the Product
price column is actually unnecessary (again notwithstanding the fact that
Northwind includes it). It serves no purpose, but on the other hand does no
harm.

Me is a VBA keyword. Strictly speaking what it does is return a reference
to the current instance of a class, but think of it as referring to the form
(in this case the subform) in whose module the code is executing.

The process of assigning the price from the relevant row of the Product
Details table is quite simple really:

1. Where you select an item in the combo box its AfterUpdate event is
triggered.

2. The code in the AfterUpdate event procedure uses the DLookup function to
return the price from the row of the Product Details table which matches the
criteria in the strFilter variable.

3. The code then assigns this to the Product Price control (the text box)
in the subform's current, but as yet unsaved, record. The = sign in this
code is the assignment operator, not the equality operator; it acts as either
depending on the context.

4. The text box's ControlSource must be the Product Price column (field) in
the Order Details table underlying the subform. I suspect the error may be
that the subform is based (like Northwind) on a query which includes the
Product Details table and the ControlSource of the text box is the column
from *that* table, not from Order Details. If you look at the query which is
the subform's RecordSource in Northwind you'll see that the UnitPrice column
is from Order Details, not Products.

All the above being the case it should work without any problems.

BTW in case you are wondering why I refer to rows and columns rather than
records and fields most of the time, this is because the former are the
correct terms in relation to tables in a relational databse. Records and
fields really apply to old style file systems in which data is stored
sequentially in fields in records and can be addressed by position, unlike in
a table where it can be addressed only by name, the method of physical
storage being completely irrelevant to the relational model . The terms
fields and records are commonly used in relation to tables in Access, but its
sloppy use of the terminology. No SQL programmer worth their salt would do
so.

Ken Sheridan
Stafford, England

Ken:

I can be totally wrong, but I have a hunch that the query might in itself be
causing the error, or that command line is assigning the same information on
top of the exisiting and it is causing the error through redundancy. After I
select a Product Name in the Order Details subform, that is when I receive
the error, and it tells me I may be cancelling out my previous actions. But
I'm not sure. And I will have to find the debugger and learn to use it to do
so.

I don't expect you to do the work for me, but I "GREATLY" appreciate you
helping me solve this problem so I understand how it works, it helps me
disect it. This information makes a good impact on helping me understand the
function of the Control Source and Row Source functions and some
communications. Thanks for your patience and not giving up on me.

(the subform is created from a query titled: "Orders Product Pricing Form"
create from the "Order Details" table.)

Here is how my "Order Details" subform [Product ID] combo box reads:

The Product ID combo box properties reads as follows:

Control Source: Product ID
Row Source: SELECT[Product Services Table].[Product ID], [Product Services
Table].[Product Name], [Product Services Table].[Product Price]FROM[Product
Services Table]ORDER BY[Product Name];

It is bound to 1 and Counted as 3

Product Price text box reads as follows:

Control Source: Product Price
Row Source: (not listed)

Now, I think what you mean when refering to my "Product Details" table you
actually mean my "[Product Services Table]" (Table is part of the name),
which is like the [Products] table in Northwind. This is when you talk
about the "assigning" the value to the "Order Details" table. Now I'm not
sure what "ME!" stands for, which might help. I did have it entered as you
mentioned:

ME![Product Price] = DLookup("[Product Price]", "[Product Services Table]".
strFilter)

Now I think this is all the essential information that may help. I'm still
not sure I understand how the command knows to assign the Product Price to
the Order Details table field and not the Product Services Table's Product
Price field?

Thanks again. I'm still trying to solve it on my own. I look forward to
your reply!

I'm not sure why you are getting that error. Ideally you need to debug the
code using the built in debugging tools, but lets first review how the
Product ID and product price controls are set up on the subform:

1. The Product ID field's control is your combo box from which you select a
Product Name. The way this works is that you see the names from the table of
products, but the value of the control is the Product ID for that name. This
achieved by hiding the first column of the list by setting properties of the
combo box as follows, so review these to see that its set up correctly:

ControlSource: [Product ID] (the underlying field from the Product
Details table)

RowSource: Either an SQL statement along the following lines or the
name of a saved query whose SQL is similar:

SELECT [Product ID], [Product Name]
FROM [Product Services]
ORDER By [Product Name];

You'll see that this query returns two columns the Product ID and Product
Name, and is ordered by the latter:

BoundColumn: 1 (this makes the first column, Product ID, the value of
the control)

ColumnCount: 2 (the number of columns returned by the RowSource query)

ColumnWidths: 0cm;8cm (or rough equivalent in inches. The important
thing is that the first dimension is zero; this hides the Product ID column
so you see just the names.


2. The Product Price control is a text box with [Product Price], the field
in the underlying Product Details table, as its ControlSource property.

The code in the Products combo box's AfterUpdate event procedure doesn't
'paste' the product price value from the Product Details table into the field
in the Order Details table, it 'assigns' the value to it with this line:

Me![Product Price] = DLookup("[Product Price]", "[ Product Services]",
strFilter)

You should see the value appear in the Product Price text box when a
selection is made from the combo box's list. At this stage the subform is
'Dirty', i.e. the record isn't yet saved to the Product Details table. That
is done when you leave the subform, move to another record in it or expressly
save the record using the menu, toolbar or keyboard short cut.

Check that the two controls are set up as above. If they are there should
be no problem, so some debugging will be needed to pin down the reason for
the error. If necessary I'll try and walk you through how to do that but its
really something you'd have to tackle yourself as there is a limit to how
much I can do remotely without having access to your file.

Ken Sheridan
Stafford, England

Ken:

First, thanks for the book info. I printed it out and will certainly look
some stuff up.

Well, I did what you suggested exactly as posted and I got an error similar
to this:
"You canceled the previous operation",
everytime I select something in my subforms drop down selection under
Product Name.

I think I did it correctly, I think it is just interfering with something
else already happening.

Now, this may be a stupid question/comment. However, in studying the VBA
commands I put in. I don't see how those commands would know to copy my
Product Price from my Subform "Order Details", and paste that value in the
Order Details table under the "Product Price" field there?

This is the last detail I need to get to function and my database is done.
So, I'm sure you may understand why I am so eager to understand and get this
done.

Thanks again. I look forward to your reply. Thanks for the book info too.

 

[Ken Sheridan]

I think you are missing the fundamental point. If you simply pull the
Product Price from the Product Details table your accounts are pretty soon
going to be in a complete mess as the moment any product's price changes all
earlier orders/invoices for that product are going to be incorrect because
they will reflect the new price, not the price at the time of the order. Its
absolutely imperative that the price is pushed into a column position in a
row in the Order Details table where it will retain the value it was at the
time the order/invoice was created.

Follow the paradigm of the Northwind's subform and your accounts will be
correct for ever more.

Ken Sheridan
Stafford, England

 

[EZ KEY]

Hi Ken:

That makes sense, and I actually knew that, but it wasn't penetrating for
some reason. Sorry!

Okay, I changed my query to use the "Product Price" of the "Order Details"
table and not the "Product Price" of my "Product Services Table". I made
those changes in the form as well.

The good news is that I no longer receive the error from entering the VBA
code in the "Product ID" after update event. However, now I am back to
where I was before when the price is not showing up in the Product Price text
box. Isn't that what the VBA code is supposed to do?

Thanks!

 

[EZ KEY]

Ken:
OKay, I was wrong. I'm having more problems this way.

Okay, I got frustrated, I hate to admit, and I realized there wasn't any
difference between my query for the Order Details subform and my Order
Details table. So I created a new subform based on just the Order details
table itself... same fields, same information, the whole works.

In the new subform "Order Details2" I still cannot get the price to show up
in the bound "Product Price" text box, and I am getting the error still "You
canceled the previous operation" everytime I select a product name in the
Product ID combo box. I haven't inserted it yet into the form, this is just
trying out first.

Is there any way I can send this to you? You may see something I'm not.
You aren't obligated to. Just thought I'd ask. You don't have to give me
your email... go to here and download it:

www.ezkeyllc.com/accessfile.htm

There are two "Order Details" forms. Order Details is still my subform in
the Client Orders form, the Order Details2 isn't. I was test running it
before inserting it.

Thanks!

 

[Ken Sheridan]

The problem was simply that the data type of Product ID is text not a number.
I'm not sure whether I mentioned this before, but text data types need their
values delimiting with quotes characters. I'd assumed that it was a number,
probably an autonumber in the case of the Product details table's primary
key. Put the following as the Product ID combo box's AfterUpdate event
procedure and it will be fine:

On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "[Product ID] = """ & Me![Product ID] & """"

' Look up product's unit price and assign it to UnitPrice control.
Me![Product Price] = DLookup("[Product Price]", "[Product Services
Table]", strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

You can ditch the Order Details 2 form. I've made one or two other small
changes which I think make it a little easier to use, so if you want to mail
me at:

ken<at>k<dot>sheridan<dot>orangehome<dot>co<dot>uk

I'll send the amended file to you.

Ken Sheridan
Stafford, England

 

[EZ KEY]

Ken:

Your edit worked. Thank you! I did send you the file so you could review
it and suggest any tweeks.

Thanks!

 

[EZ KEY]

Ken:

Wow! I'm so glad you were able to get me through that mess. I'm sorry I
was so thick-headed. However, I learned a lot of new things, and will have
to continue creating to retain those details.

I did have one more question unrelated to the issues before, but is there a
reason why when I create an order it always starts on "1" and only shows "1"
every time I open the Client Orders form instead of the total orders I've
entered?

 

[Ken Sheridan]

The reason its showing a record count of 1 is due to the form's DataEntry
property being set to True (Yes). This filters the form to just a new
record; its for use when you want a form only for adding new records, not
viewing or editing existing ones The number of records shown on the
navigation bar is the number of records in the filtered recordset, which is
only the new record. If you set the DataEntry property to False (No) the
form will open at the first record and show the total number of records. To
add an order you can then use your button on the form or the [>*] button on
the navigation bar to go to a blank new record.

Ken Sheridan
Stafford, England

Ken:

Wow! I'm so glad you were able to get me through that mess. I'm sorry I
was so thick-headed. However, I learned a lot of new things, and will have
to continue creating to retain those details.

I did have one more question unrelated to the issues before, but is there a
reason why when I create an order it always starts on "1" and only shows "1"
every time I open the Client Orders form instead of the total orders I've
entered?

The problem was simply that the data type of Product ID is text not a number.
I'm not sure whether I mentioned this before, but text data types need their
values delimiting with quotes characters. I'd assumed that it was a number,
probably an autonumber in the case of the Product details table's primary
key. Put the following as the Product ID combo box's AfterUpdate event
procedure and it will be fine:

On Error GoTo Err_Product_ID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "[Product ID] = """ & Me![Product ID] & """"

' Look up product's unit price and assign it to UnitPrice control.
Me![Product Price] = DLookup("[Product Price]", "[Product Services
Table]", strFilter)

Exit_Product_ID_AfterUpdate:
Exit Sub

Err_Product_ID_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_ID_AfterUpdate

You can ditch the Order Details 2 form. I've made one or two other small
changes which I think make it a little easier to use, so if you want to mail
me at:

ken<at>k<dot>sheridan<dot>orangehome<dot>co<dot>uk

I'll send the amended file to you.

Ken Sheridan
Stafford, England