SOS Access Help plz

bjsteyn

Registered User.
Local time
Today, 16:31
Joined
May 15, 2008
Messages
113
Hey there, wondering if anybody can help me. I am designing a database similar to the example Northwind database. I used Northwind as a guideline as it is my first database. My Orders form and Orders Subform is basically the same as
northwind.

In the Orders Subform is use the following code:

Private Sub ProductID_AfterUpdate()
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", "Price list", strFilter)
Me!CasePrice = DLookup("CasePrice", "Price list", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

When I run the orders form and click on the Product ID it gives me the following error:
"Data type mismatch in criteria expression"

The ProductID is a combo box with
the control source: ProductID
and the row souce: SELECT [ProductID], [ProductName] FROM Products ORDER BY [ProductName];

The difference between my ProductID and Nortwind's is that mine is a text value and theirs is a number.

Anybody with idea???PLZ

Yours Sincerely
BJ Steyn
 
Well, that'll cause the error you're reporting, you can't replace a number with a text without refreshing the control. You didn't mention what line it was erroring on.

Why did you change it to a text? It sounds like you are denormalizing the original, which is exactly the opposite of what you should do to improve it.

What would be bad about changing it back to the way it was, as supplied by MS? That might help us to understand the problem a little better and what your goal is for your system.
 
change the line:
strFilter = "ProductID = " & Me!ProductID

to
strFilter = "ProductID = '" & Me!ProductID & "'"

Note the use of single quotation marks.

Chris
 
Actually, if you based it on the Northwind database, I'm pretty sure your problem is that you have lookup fields at table level, which can cause behavior like this. Remove the lookups from table level and it should fix the problem. Your original Product ID code is correct, but with the lookups it returns the text for the product. So, even Chris' (Stopher) code is not likely to help until you get rid of the field level lookup.

To get rid of it go into the table in design view, select the product ID field and go down to the bottom of the properties page and click on the tab marked LOOKUP. Change the DISPLAY CONTROL from Combo Box to Text Box, then save your table.
 
Thanks

Thanks guys 4 the input. I tried changing the lookup at field level but it made no difference.I changed the strFilter to the one with the single quotes and it worked. What does the single quotes do?Why did it make a difference?

The database i'm disigning is 4 a for a neighbour who is starting up a bussiness to sell nestle products and deliver them from a warehouse to bussinesses.

The reason i used a textvalue for my productID is that nestle has already
predefined product codes. If one of them starts with a zero then the zero
will disipere in a number field.

There are 5 different price lists for different customer types:
eg. base, business and industry etc.

My database has the following tables:

Customers - Categories
Product - Categories
Products
Customers
Order Details
Orders
Price list

Relationships is same as northwind except my customers - categories table
is connected to Customers table by CatagoryID and my Price list table is connected to Poducts table, by ProductID, and to
customers - categories , by CategorieID.

Any body knows where i can find nice tutorials on queries.

bj steyn


moz-screenshot.jpg
 
Thanks guys 4 the input. I tried changing the lookup at field level but it made no difference.
You still don't want to get into lookup fields at table level, so it is good to get rid of them regardless.
http://www.mvps.org/access/lookupfields.htm

I changed the strFilter to the one with the single quotes and it worked. What does the single quotes do?Why did it make a difference?
When looking for a string you need to encapsulate it in quotes and dates in # sign and numbers get nothing.


Any body knows where i can find nice tutorials on queries.
Go here http://www.functionx.com/access2003/index.htm
and they have a lot more good tutorials as well -
http://www.functionx.com
 
Thanx, I would like to import values from a price list in excel and update the prices. How would i go about that?i also wan ba ckup the database by code. Which method would i use? And lastly remeber my function field VAT = ccur([TaxableAmount]/100*14) in my orders detail extended. how do i get it to just calcute a value when another field VATExcempt = No. When the VATExcempt = Yes then VAT must be 0.thanx bj
 

Users who are viewing this thread

Back
Top Bottom