OK I give up...need advice

[Angi]

I'll try not to make this too long! For this forms sake I'm dealing
with 5 tables (listed below). I've looked at the Nwind and a whole
bunch of others, but mine doesn't work the way those do, so I'm
confused.

I'm trying to create an order details subform for my main quote form.
I can handle that part. Products are created from class, subclass, and
size. Rather than select an item (ProductID & Item number), the users
want to CREATE them using cbo bxs for class, subclass, and size (which
is what creates the item number). I realize I'm going to have to do a
dlookup or something to see if the item is already there and if so,
then pull the other information. If not, add it as a new product. My
problem is getting around the productID since that is the relationship
connection (1 to many). My cbo bxs are supposed to requery the next to
find subclasses within that class and that's not working (it does on
another form based solely on the Products table, so I know it's right.)
What is the best way to do what I need to do?

Thanks to anyone who can understand this and offer some insight!

Order details:
OrderDetailID (pk)
ProductID
Qty
ExtPrice
Discount

Products table:
ProductID (pk)
ClassID
SubclassID
SizeID
ProductName (concatenation of classdesc and subdesc)
UnitPrice
ItemNo (concatenation of class, sub, and size)

Class table:
ClassID (pk)
ClassName
ClassDesc

Subclass table:
SubclassID (pk)
ClassID
SubclassName
SubDesc

Size table:
SizeID (pk)
SubclassID
txtWidth
txtHeight
txtDepth
txtSize (concatenation of all dimensions)

 

[Angi]

John,
I got your posts in my email...don't know why. First, I would like to
say thank you very much for taking the time to respond. It was VERY
helpful!!! I've changed what you suggested. I'm known for keeping
redundant data and I thought that might be the problem. I'm now onto
the form and I'm having trouble with the lookup. I keep getting a type
mismatch error on the isthere = dlookup line and i don't understand
why.

I've set the code to look up the item after the size is changed.

Private Sub cboSizeID_Change()
Dim isthere
Dim cid, sbid, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

isthere = DLookup("[unitprice]", "products", "[classid]= " & cid
And "[subclassid]= " & sbid And "[sizeid]= " & sid)
If IsNull(isthere) Then
Me.UnitPrice.SetFocus
Else: Me.Quantity.SetFocus
End If

End Sub

 

[Marshall Barton]

John,
I got your posts in my email...don't know why. First, I would like to
say thank you very much for taking the time to respond. It was VERY
helpful!!! I've changed what you suggested. I'm known for keeping
redundant data and I thought that might be the problem. I'm now onto
the form and I'm having trouble with the lookup. I keep getting a type
mismatch error on the isthere = dlookup line and i don't understand
why.

I've set the code to look up the item after the size is changed.

Private Sub cboSizeID_Change()
Dim isthere
Dim cid, sbid, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

isthere = DLookup("[unitprice]", "products", "[classid]= " & cid
And "[subclassid]= " & sbid And "[sizeid]= " & sid)
If IsNull(isthere) Then
Me.UnitPrice.SetFocus
Else: Me.Quantity.SetFocus
End If

End Sub

Not sure who you're talking to here. From where I sit,
you're the only person in this thread. But if someone named
John recommended using DLookup, I don't see any reason to
argue.

The problem with your DLookup is that you have the ANDs
outside the quotes. Another issue is that your Dim
statement is a little ambiguous. Try changing to this:

Dim isthere As Variant
Dim cid As Integer, sbid As Integer, sid As Integer
. . .
isthere = DLookup("[unitprice]", "products", _
"classid=" & cid & " And subclassid=" & sbid & _
" And sizeid=" & sid)

 

[John Nurick]

Not sure who you're talking to here. From where I sit,
you're the only person in this thread. But if someone named
John recommended using DLookup, I don't see any reason to
argue.

I'm the guilty party, Marsh. I accidentally replied to Angi by email
rather than to the group.

 

[Marshall Barton]

I'm the guilty party, Marsh. I accidentally replied to Angi by email
rather than to the group.

Ahh, that explains it.

Hope I didn't confuse anything by dropping into the
conversation.

 

[John Nurick]

John,
I got your posts in my email...don't know why.

A synapse misfired for a while and I clicked "Reply by Email" not "Reply
to Group"! For completeness I've pasted my reply at the bottom of this.

First, I would like to
say thank you very much for taking the time to respond. It was VERY
helpful!!! I've changed what you suggested. I'm known for keeping
redundant data and I thought that might be the problem. I'm now onto
the form and I'm having trouble with the lookup. I keep getting a type
mismatch error on the isthere = dlookup line and i don't understand
why.

I've set the code to look up the item after the size is changed.

Private Sub cboSizeID_Change()
Dim isthere
Dim cid, sbid, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

isthere = DLookup("[unitprice]", "products", "[classid]= " & cid
And "[subclassid]= " & sbid And "[sizeid]= " & sid)
If IsNull(isthere) Then
Me.UnitPrice.SetFocus
Else: Me.Quantity.SetFocus
End If

End Sub

--
Original reply:

Hi Angi,

I can see that you've had advice from several other people, so forgive
me if what I'm about to day has already been done to death. Comments
inline.

I'll try not to make this too long! For this forms sake I'm dealing
with 5 tables (listed below). I've looked at the Nwind and a whole
bunch of others, but mine doesn't work the way those do, so I'm
confused.

ISTM that the problem is with your data structure, in particular with
the redundant (and therefore non-normalised) fields.

I'm trying to create an order details subform for my main quote form.
I can handle that part. Products are created from class, subclass, and
size. Rather than select an item (ProductID & Item number), the users
want to CREATE them using cbo bxs for class, subclass, and size (which
is what creates the item number). I realize I'm going to have to do a
dlookup or something to see if the item is already there and if so,
then pull the other information. If not, add it as a new product. My
problem is getting around the productID since that is the relationship
connection (1 to many). My cbo bxs are supposed to requery the next to
find subclasses within that class and that's not working (it does on
another form based solely on the Products table, so I know it's right.)
What is the best way to do what I need to do?

Thanks to anyone who can understand this and offer some insight!

Order details:
OrderDetailID (pk)
ProductID
Qty
ExtPrice
Discount

Replace ProductID with ClassID, SubclassID and SizeID (see below).

Products table:
ProductID (pk)
ClassID
SubclassID
SizeID
ProductName (concatenation of classdesc and subdesc)
UnitPrice
ItemNo (concatenation of class, sub, and size)

ProductID is redundant because a Product is defined by ClassID, SubClass
and SizeID. Make those three fields the PK of this table and use them as
the FK in OrderDetails (see above).

ProductName is redundant because it can be generated whenever needed
(e.g. for the Order Details subform) by looking up the descriptions in
the Class and Subclass tables.

ItemNo is similarly redundant.

Class table:
ClassID (pk)
ClassName
ClassDesc

Subclass table:
SubclassID (pk)
ClassID
SubclassName
SubDesc

Size table:
SizeID (pk)
SubclassID
txtWidth
txtHeight
txtDepth
txtSize (concatenation of all dimensions)

txtSize is redundant.

With a setup like that, when users use the three comboboxes to enter a
combination of class, subclass and size, you're working only with the
existing key fields ClassID, SubclassID, SizeID. You can use these to
check whether there's already a corresponding Product record (in which
case all you have to do is look up the unit price), or not (in which
case you just have to ask the user to give you the appropriate unit
price, at which point you have all the data needed to create a new
Product record - whose primary key ClassID, SubclassID and SizeID you
already know).

 

[John Nurick]

Ahh, that explains it.

Hope I didn't confuse anything by dropping into the
conversation.

Far from it. Thanks for joining in.

 

[Angi]

Marsh and John,
Marsh, I realize now it looked like I was talking to myself! Sorry
about the confusion. Also, thank you for "dropping" in. I'll take all
the help I can get!

John, thanks for clearing up and reassuring everyone that I'm really
not crazy!

If I should be starting new posts and end this one, please let me know.
It was part of your email, so I just thought I would continue with
this thread. Now, I'm having a problem with the details form. I've
got the Dlookup for the item and price and it's working great. The
problem I'm having is, when I change the price on the quote form, it's
changing the price in the products table and I don't want it to. IE:
Price is 10 but we're giving it to the customer for 8. I want to
change it to 8 for this quote but leave it 10 for everyone else.
Here's the code I'm using (which should be: if the price is empty, then
update the table with the entry, if not don't update):

Private Sub UnitPrice_afterupdate()
Dim mysql As String
Dim cid As Integer, sbid As Integer, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

If IsNull(DLookup("[unitprice]", "products", "[classid]= " & cid &
"And [subclassid]= " & sbid & "And [sizeid]= " & sid)) Then
mysql = "UPDATE Products SET Products.UnitPrice = me.[UnitPrice] "
& _
"WHERE (((Products.ClassID)= cid ) AND ((Products.SubClassID)=
sbid ) AND ((Products.SizeID)= sid));"
CurrentDb.Execute mysql
End If

End Sub

Thanks for all your help guys! I've got a lot more to do on this form
and I know I'm gonna need more help!!

Ang

 

[John Nurick]

Hi Angi,

I'm not quite sure what's going on (not least because I can't look over
your shoulder to see what's on your forms). But looking back at the
message I accidentally emailed you and comparing it with your post
below, I don't think you'll to use an Update query on the Products table
from the Details form.

As I understand it, if there's already a record in the Products table
for the product (i.e. combination of class, subclass and size) in
question, you just want to get that price from the Products table.

If the record doesn't already exist, you need to create it, using the
class, subclass and size info you already have and getting the user to
provide the unit price. I.e. using an Append query, not an Update query.

THe logic would be something like this:

Dim varUnitPrice As Variant

varUnitPrice = DLookup("[unitprice]", "products", _
"[classid]= " & cid & "And [subclassid]= " & sbid _
& "And [sizeid]= " & sid)
If IsNull(varUnitPrice) Then
'Ask user to enter the price
...
'Build Append query
mySql = "INSERT INTO Products blah blah"
CurrentDB.Execute mySql
Else
Me.UnitPrice = varUnitPrice
End If

I can see there could be a problem with discounts. I thought you had
that covered with these fields in the OrderDetails table

Qty
ExtPrice
Discount

but on second thoughts these may not be quite the best. Qty is fine,
obviously, but maybe ExtPrice should be replaced by UnitPrice to give

Qty
UnitPrice
Discount

That way, if there is already a product record, you can just grab the
UnitPrice from there and put it into the UnitPrice field of the
OrderDetail record. If there isn't a product record, you can have the
user enter the price in the order detail form, and run an append query
to create the new Product record. Either way, the UnitPrice will be the
same in both places. (They'll be the same at first, that is. In time,
the company will want to revise its prices, and you'll edit the Product
records - but the actual historical prices will remain in each order
detail record.)

Meanwhile the *actual* unit price you are charging the customer is
[UnitPrice] * (1 - [Discount])
and the extension price is
[Qty] * [UnitPrice] * (1 - [Discount])


Hope this helps

Now, I'm having a problem with the details form. I've
got the Dlookup for the item and price and it's working great. The
problem I'm having is, when I change the price on the quote form, it's
changing the price in the products table and I don't want it to. IE:
Price is 10 but we're giving it to the customer for 8. I want to
change it to 8 for this quote but leave it 10 for everyone else.
Here's the code I'm using (which should be: if the price is empty, then
update the table with the entry, if not don't update):

Private Sub UnitPrice_afterupdate()
Dim mysql As String
Dim cid As Integer, sbid As Integer, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

If IsNull(DLookup("[unitprice]", "products", "[classid]= " & cid &
"And [subclassid]= " & sbid & "And [sizeid]= " & sid)) Then
mysql = "UPDATE Products SET Products.UnitPrice = me.[UnitPrice] "
& _
"WHERE (((Products.ClassID)= cid ) AND ((Products.SubClassID)=
sbid ) AND ((Products.SizeID)= sid));"
CurrentDb.Execute mysql
End If

End Sub

Thanks for all your help guys! I've got a lot more to do on this form
and I know I'm gonna need more help!!

Ang

 

[Marshall Barton]

[...] The
problem I'm having is, when I change the price on the quote form, it's
changing the price in the products table and I don't want it to. IE:
Price is 10 but we're giving it to the customer for 8. I want to
change it to 8 for this quote but leave it 10 for everyone else.
Here's the code I'm using (which should be: if the price is empty, then
update the table with the entry, if not don't update):

Private Sub UnitPrice_afterupdate()
Dim mysql As String
Dim cid As Integer, sbid As Integer, sid As Integer

cid = Me.cboClassID
sbid = Me.cboSubID
sid = Me.cboSizeID

If IsNull(DLookup("[unitprice]", "products", "[classid]= " & cid &
"And [subclassid]= " & sbid & "And [sizeid]= " & sid)) Then
mysql = "UPDATE Products SET Products.UnitPrice = me.[UnitPrice] "
& _
"WHERE (((Products.ClassID)= cid ) AND ((Products.SubClassID)=
sbid ) AND ((Products.SizeID)= sid));"
CurrentDb.Execute mysql
End If

End Sub

If you only want to set the price when it doesn't already
have one, you'll have to check for that condition:

mysql = "UPDATE Products " _
& "SET UnitPrice=" & me.UnitPrice _
& " WHERE (ClassID=" & cid _
& ") AND (SubClassID=" & sbid _
& ") AND (SizeID=" & sid _
& ") AND (UnitPrice Is Null)"

However, this will only deal with the situation where there
is already a record with the class, subclass and size and a
missing price. If you want to add a new record when ther is
no record at all, then you need to distinguish the two
different situations and either do the update or append a
new record.

Think about it and come back with your complete
specifications of what's needed for this operation.

 

[Angi]

John,
After this post, you'll want to strangle me like I want to do to the
user!!

I was using an update because I was appending the product in the
afterupdate event of the sizeid field. That's why I used the if isnull
statement for the dlookup in the unitprice_afterupdate event. I knew
the record was there, so I just needed to check to see if the price was
originally empty after he entered something.

Now, he's changed his mind....doesn't want a set unit price or
discount; he wants to enter a new price each time (don't ask!). So, I
put the unitprice field in the ordersdetails table. Now, this leaves
only the three ID's in the products table (classid, subclassid and
sizeid). Which makes sense to me to keep it there since I still don't
want more than one product, so I had to change my dlookup to look for
the classid instead of the unit price. Only, now I'm only looking it
up to see if it's there and add it if it's not. He's going to be the
death of me!!! Anyway, it looks like I finally got this to work (so
far) and I REALLY appreciate everyone's time, especially your initial
post! That really got me moving in the right direction. Gotta love
those users!!! I guess we'd be twiddling thumbs without them making us
change code!!

Best regards,
Ang