Marsh...you were right!! HELP please!

[Angi]

In a few posts back of mine about dependent cbo boxes, Marsh wrote:

Since a continuous (sub)form displays multiple records,
there is no way to keep the RowSource query in a dependent
combo box synchronized with the value in the next "higher
level" for **all** of the displayed records. The best you
can do is make the Current record work properly (same as
your previous problem). But the other rows will still have
the display problem you had before we added the Requery to
the Current event. In single view, you can't see those
records, but in continuous view, the problem is right in
front of you.

The work around is kind of tricky, involving placing a bound
text box directly on top of the text portion of the combo
box. You'll also need to munge around with the form's
RecordSource query to include the display value for the
combo box as well as add a small amount of code to manage
the focus if the user should click into the text box used to
display the correct combo text portion.


OK! I added the requery to the OnCurrent event of the form like you
said, and it works...as long as I'm on that record. I tried the work
around you mentioned, but quite honestly, I'm confused. If I put a
text box on top (I assumed bound to the value in the table) how can the
user change the cbo if they need to?

TIA!
Ang

 

[Bruce M. Thompson]

... as well as add a small amount of code to manage

the focus if the user should click into the text box used to
display the correct combo text portion.


OK! I added the requery to the OnCurrent event of the form like you
said, and it works...as long as I'm on that record. I tried the work
around you mentioned, but quite honestly, I'm confused. If I put a
text box on top (I assumed bound to the value in the table) how can the
user change the cbo if they need to?

Going back to Marsh's suggestions, note that he stated (and I will clarify what
he meant here) that you would need to add a bit of code to ensure that the
focus, if acquired by the textbox, would then be assigned to the combo box (by
adding a .SetFocus line to the textbox's "On Enter" or "Got Focus" event
procedure). As soon as the focus is moved to the combo box, the combo box will
be brought to front, as if it were the only control in that position and
allowing full control over the use of the same - moving the focus from the combo
box will again allow the textbox to regain its position over the combo box.

'SAMPLE CODE
Private Sub MyTextBox_Enter ()
'Set focus to combo box
Me.MyCombo.Setfocus
End Sub

 

[Marshall Barton]

In a few posts back of mine about dependent cbo boxes, Marsh wrote:

Since a continuous (sub)form displays multiple records,
there is no way to keep the RowSource query in a dependent
combo box synchronized with the value in the next "higher
level" for **all** of the displayed records. The best you
can do is make the Current record work properly (same as
your previous problem). But the other rows will still have
the display problem you had before we added the Requery to
the Current event. In single view, you can't see those
records, but in continuous view, the problem is right in
front of you.

The work around is kind of tricky, involving placing a bound
text box directly on top of the text portion of the combo
box. You'll also need to munge around with the form's
RecordSource query to include the display value for the
combo box as well as add a small amount of code to manage
the focus if the user should click into the text box used to
display the correct combo text portion.


OK! I added the requery to the OnCurrent event of the form like you
said, and it works...as long as I'm on that record. I tried the work
around you mentioned, but quite honestly, I'm confused. If I put a
text box on top (I assumed bound to the value in the table) how can the
user change the cbo if they need to?

Bruce explained how to shift the focus to the combo box.

What the text box is bound to depends on if the combo box's
bound column is visible (its ColumnWidth is >0). If it is
visible then, yes, the text box would be bound to the same
field as the combo box.

If the combo box's bound column is not visible (the combo
box displays something different than the stored value),
then you have to modify the form's RecordSource query to
Join to the combo box's table and include the combo box's
first visible column field in the query's field list. The
text box should be bound to this field.

I told you it was a tricky situation ;-)

 

[Angi]

Thanks guys!! OK...good news and bad
Good got the text box to work. It changes focus when I enter
it.

Bad the bound column (subclassID, 0 width) is not the value I
need. I'm getting a number instead of the description. ie: getting
subclassID (15) instead of subclassname (Rret). Can you please tell me
what I need to change? Here's the control source for the subform:

SELECT OrdersDetails.OrderDetailID, OrdersDetails.ClassID,
OrdersDetails.SubclassID, OrdersDetails.SizeID, OrdersDetails.Quantity,
OrdersDetails.ExtPrice, OrdersDetails.Discount,
ordersdetails.UnitPrice, ordersdetails.quoteid
FROM Products INNER JOIN OrdersDetails ON
(Products.SizeID=OrdersDetails.SizeID) AND
((Products.ClassID=OrdersDetails.ClassID) And
(Products.SubClassID=OrdersDetails.SubclassID));

The subclassname is in the Subclass table and it's connected to the
Orderdetails table through the Products table. (Does that make
sense??) Also, when i try to open this up to edit it, I get the error:

Database can't represent the expression
(Products.classid=ordersdetails.classid) AND
(products.subclassid=ordersdetails.subclassid) in design view.

Can I get rid of the INNER JOIN line?? I always mess those up and
don't know when I should use them or when I shouldn't. I don't know
why it's in there since I used the wizard to create the form.

This is finally coming along!! Thanks for your help!!
Ang

 

[Marshall Barton]

Thanks guys!! OK...good news and bad
Good got the text box to work. It changes focus when I enter
it.

Bad the bound column (subclassID, 0 width) is not the value I
need. I'm getting a number instead of the description. ie: getting
subclassID (15) instead of subclassname (Rret). Can you please tell me
what I need to change? Here's the control source for the subform:

SELECT OrdersDetails.OrderDetailID, OrdersDetails.ClassID,
OrdersDetails.SubclassID, OrdersDetails.SizeID, OrdersDetails.Quantity,
OrdersDetails.ExtPrice, OrdersDetails.Discount,
ordersdetails.UnitPrice, ordersdetails.quoteid
FROM Products INNER JOIN OrdersDetails ON
(Products.SizeID=OrdersDetails.SizeID) AND
((Products.ClassID=OrdersDetails.ClassID) And
(Products.SubClassID=OrdersDetails.SubclassID));

The subclassname is in the Subclass table and it's connected to the
Orderdetails table through the Products table. (Does that make
sense??) Also, when i try to open this up to edit it, I get the error:

Database can't represent the expression
(Products.classid=ordersdetails.classid) AND
(products.subclassid=ordersdetails.subclassid) in design view.

Can I get rid of the INNER JOIN line?? I always mess those up and
don't know when I should use them or when I shouldn't. I don't know
why it's in there since I used the wizard to create the form.

Well, Angi, it looks like you didn't need the Products table
before thhis combo box issue came along, BUT now, you do
need it. In fact, you need another one. Add the Subclass
table to the query and add the join line from Products to
Subclass between the appropriate fields. Then drag the
subclassname down to the query's field list.

The ON clause you have above isn't correct because the
Subclassid field is not in the Products table, it's in the
orderdetails table, right? Getting the join lines correct
in the top part of the query design window should take care
of this issue.

Once the query is sorted out properly, the text box on the
form would then be bound to the new subclassname field.

Are we having fun yet?

 

[Angi]

Marsh..
I know this is a little late, but I wasn't having any problems, so I
thought I would leave you alone! The box works great...as long as
I don't have to change anything. This is probably considered a new
thread, but rather than explain everything again, I'd thought I'd
continue it...hope that's ok.

My problem is, when I change the first cbo (Class) it requeries the
cboSubClass and cboSize cbos, which of course, become blank (this lets
the users know they have to select a new subclass and size) but the
text box we created is staying the old value so it looks like there's
something already there. It fixes itself after a new subclass and size
is selected, but how can I make them blank until something is
selected?? I've tried requery, making it = "" (which deletes my
subclass name), tried visible=false...everything. Is this possible??

Thanks for all your help!
Ang

 

[Marshall Barton]

Don't worry about being a little late, I've been out of town
for a week.

This situation is too tricky for me to work out using
newsgroup messages. I think I need to see your forms,
queries and tables. If you can make a copy of your mdb
file(s?), strip out all irrelevant stuff, compact and zip it
to me as an emali attachment, I'll see what I can figure
out.

 

[Angi]

Marsh
Ok...call me stupid, but I tried emailing you and it got returned "User
unknown"

What address should i be using? (e-mail address removed) or
(e-mail address removed)?

i tried the latter.

 

[Marshall Barton]

Marsh
Ok...call me stupid, but I tried emailing you and it got returned "User
unknown"

What address should i be using? (e-mail address removed) or
(e-mail address removed)?

It should be in the From field of all my posts, but here it
is again:

(e-mail address removed)

 

[Angi]

ok....ya can't say i didn't try!

----- The following addresses had permanent fatal errors -----
<[email protected]>

----- Transcript of session follows -----
.... while talking to wowway.com.s7a1.psmtp.com.:<<< 550 5.0.0 <[email protected]>... User unknown
550 <[email protected]>... User unknown


AND

----- The following addresses had permanent fatal errors -----
<[email protected]>

----- Transcript of session follows -----
.... while talking to wowway.com.s7a1.psmtp.com.:<<< 550 5.0.0 <[email protected]>... User unknown
550 <[email protected]>... User unknown

 

[Marshall Barton]

Where are you getting the ... part of the address???

Remove the "..." and replace it with "ton"

 

[Angi]

the ... is in all of your addresses....it reads (e-mail address removed).
Even when you typed it a few posts ago and the posts I just pasted.
Must be something with google. Does anyone else see the ...??? Will
send again.

Thanks!

(I knew I wasn't stupid or crazy!!!)

 

[Angi]

OK...better late than never!! First, thank you so much for doing all
that!! Secondly, I'm sorry I forgot the size form in there. Didn't
make it easy on ya, did I?? I'm getting ready to add the Edge cbo so
I'm sure I'll be back!

Here we go:

1 - I noticed you changed the table relationship from QuoteDetails to
Products from 1 to 3....why?

2 - You added Option Explicit to the code...why? Is that something I
should do with all my mods?

3 - About the inner join --> left join...why? I was getting the right
records with inner join, so I'm just curious. Can you tell I don't pay
much attention to joins???

4 - I understood all of the code, except...in the following, why did
you add the isnull's? Those fields cannot be left blank and still add a
record. Am I reading it right? If that's the case, then I need to add
some code in case they try to lose focus while blank.

Private Sub cboSizeID_AfterUpdate() (event)

If Not (IsNull(Me.cboClassID) Or IsNull(Me.cboSubID) Or
IsNull(Me.cboSizeID)) Then

5 - You mentioned something about my table relationships in the email.
Are they right?? I just tried adding the Edges table/field I told you
about and I'm not sure what to connect it to. Shouldn't my tables
relationships be like the cbo's?? Products -> Class -> SubClass ->
Size -> Edge??? Right now, everything's going through Subclass to get
to Products. Not right, right???

6 - Was it that bad?

 

[Marshall Barton]

Responses inline below.

OK...better late than never!! First, thank you so much for doing all
that!! Secondly, I'm sorry I forgot the size form in there. Didn't
make it easy on ya, did I?? I'm getting ready to add the Edge cbo so
I'm sure I'll be back!

Here we go:

1 - I noticed you changed the table relationship from QuoteDetails to
Products from 1 to 3....why?

I'm still not sure this was necessary, but it was an attempt
to do what the Left Join in the form's query takes care of.

2 - You added Option Explicit to the code...why? Is that something I
should do with all my mods?

Option Explicit should be in every module. Its purpose is
to tell the compiler to generate an message whenever it
finds an undefined variable in your code. You can set an
Option to have it inserted automatically in every ** new**
module, but you'll have to manually add it to existing
modules.

3 - About the inner join --> left join...why? I was getting the right
records with inner join, so I'm just curious. Can you tell I don't pay
much attention to joins???

The type of Join can be critical, pay close attention to
them. In this case, your inner join was wreaking havoc on
the synchronized combo boxes.

Every time you selected a subclass, we wanted to set the
size combo value to Null to invalidate any previous size
value. BUT, this means that the combination of
class/subclass/size would not be found in the size table,
and because of the inner join the record that was being
edited should be excluded from the form's recordset. This
created a catch22 where the edited record shouldn't be
available for editing. Changing it to a left join meant
that we want to see/edit the record whether there was a
matching record in the size table or not.

4 - I understood all of the code, except...in the following, why did
you add the isnull's? Those fields cannot be left blank and still add a
record. Am I reading it right? If that's the case, then I need to add
some code in case they try to lose focus while blank.

Private Sub cboSizeID_AfterUpdate() (event)

If Not (IsNull(Me.cboClassID) Or IsNull(Me.cboSubID) Or
IsNull(Me.cboSizeID)) Then

That is to prevent adding (invalid) records to the size
table when any of those fields is Null.

5 - You mentioned something about my table relationships in the email.
Are they right?? I just tried adding the Edges table/field I told you
about and I'm not sure what to connect it to. Shouldn't my tables
relationships be like the cbo's?? Products -> Class -> SubClass ->
Size -> Edge??? Right now, everything's going through Subclass to get
to Products. Not right, right???

Considering that I don't know what an "edge" is, I will
guess that that's right. Actually, size could (should?) go
through subclass to get to class to get to products, but
this depends on the details of what entities you're trying
to model.

6 - Was it that bad?

No. it wasn't too bad at all. The thing that took so long
to track down was that inner join conundrum.

 

[Angi]

Marsh,
Ok, regarding #4...I didn't notice everything was in ( ). I was
reading it as: If class IS NOT null or if subclass IS null or if size
IS null, then look it up. If there's not a match, then create a new
product. I now realize, it says if ANY of them are not null. That was
my fault.

The bad thing is I can tab right out of subclass and go to size without
an error until I try to create a new size. Or it creates an incomplete
line on the quote (class only). That's why I was thinking I needed to
add some code there (LostFocus) that wouldn't allow a blank field when
losing focus. I can handle that. Does the left join have anything to
do with that? I'm going to look into joins at greater depth, because I
really don't understand the differences. I understand the the option
explanations, I just don't know when the right times to use them are.

As far as Edges...it's just a new table that's similiar to Size.
Another wonderful dependent cbo. After looking at the relationships, I
was wondering how frazzled I was that day to do that!! I've now
changed them to logical order. Doesn't work any differently, but looks
better and now I know how to connect the Edge table. I think this form
is almost done! I know I've said it a lot, but I can't say it
enough...Thank you, thank you, thank you!!!

Gratefully,
Angi

 

[Marshall Barton]

Ok, regarding #4...I didn't notice everything was in ( ). I was
reading it as: If class IS NOT null or if subclass IS null or if size
IS null, then look it up. If there's not a match, then create a new
product. I now realize, it says if ANY of them are not null. That was
my fault.

The bad thing is I can tab right out of subclass and go to size without
an error until I try to create a new size. Or it creates an incomplete
line on the quote (class only). That's why I was thinking I needed to
add some code there (LostFocus) that wouldn't allow a blank field when
losing focus. I can handle that. Does the left join have anything to
do with that? I'm going to look into joins at greater depth, because I
really don't understand the differences. I understand the the option
explanations, I just don't know when the right times to use them are.

As far as Edges...it's just a new table that's similiar to Size.
Another wonderful dependent cbo. After looking at the relationships, I
was wondering how frazzled I was that day to do that!! I've now
changed them to logical order. Doesn't work any differently, but looks
better and now I know how to connect the Edge table. I think this form
is almost done! I know I've said it a lot, but I can't say it
enough...Thank you, thank you, thank you!!!

Angi, using the LostFocus event to force data entry, is
seriously flawed because the user may never use the control.
It is almost always better to use the form's BeforeUpdate
event or behind a button for something that requires all the
data. OTOH, if a user just doesn't know what data to enter
and you block them from procedding with what they do know,
then a smart user will enter garbage data to get past your
checks. And, no, this has nothing to do with the join type.

Getting close to done is a good thing, but make sure you
really are close before you relax too much ;-)