combo box help please....did search

[Angi]

I've done a search for saving unbound combo boxes and also how to refer
to values for dependent cbo boxes, but I couldn't find anything that I
could put together for what I'm trying to do. I'm so confused, I hope
I get the across the way I want it.

I've downloaded the templates on the microsoft website for customer
orders and a whole bunch of others. I've studied northwind, but the
part I'm stuck on doesn't apply.

I'm trying to create a form for quotes and I can't even get past the
main form! The ctl source for form is:

SELECT QuoteMain.QuoteID, QuoteMain.OrderDetailID, QuoteMain.CoID,
QuoteMain.InvoiceID, QuoteMain.ContactID, QuoteMain.OrderDate,
QuoteMain.PurchaseOrderNumber, QuoteMain.ShipDate,
QuoteMain.ShippingMethodID, QuoteMain.SalesTaxRate, CompMain.Address1,
CompMain.Address2, CompMain.City, CompMain.State, CompMain.Zip
FROM CompMain INNER JOIN QuoteMain ON CompMain.CoID = QuoteMain.CoID;

I have a bound cbo box to CoID which finds company names. Works great!

I have another cbo box Contact (currently bound to ContactID). This
lists the contacts within that company. It looks up the right names
but doesn't save the info. Well, it does seem to save them, but it's
not displaying them. Also, when I select the name, it stays there from
record to record, instead of going back to blank or showing the right
one. It's bound column is (0) which is the ContactID. It's
recordsource is

SELECT ContactMain.ContactID, ContactMain!FirstName & " " &
ContactMain!LastName AS Expr1, ContactMain.CoID FROM ContactMain WHERE
(((ContactMain.CoID)=Forms!zQuoteMain1![CoID])) ORDER BY
ContactMain!FirstName & " " & ContactMain!LastName;

I want to save this value for other things. What am I doing wrong??
Does this have to do with the fact that there isn't a relationship
between tblContacts and tblQuotes?? All my tables are connected by the
CoID which is PK for tblCompany

TIA! I know you guys get tired of this question but I honestly
couldn't find something similiar.

 

[Marshall Barton]

I've done a search for saving unbound combo boxes and also how to refer
to values for dependent cbo boxes, but I couldn't find anything that I
could put together for what I'm trying to do. I'm so confused, I hope
I get the across the way I want it.

I've downloaded the templates on the microsoft website for customer
orders and a whole bunch of others. I've studied northwind, but the
part I'm stuck on doesn't apply.

I'm trying to create a form for quotes and I can't even get past the
main form! The ctl source for form is:

SELECT QuoteMain.QuoteID, QuoteMain.OrderDetailID, QuoteMain.CoID,
QuoteMain.InvoiceID, QuoteMain.ContactID, QuoteMain.OrderDate,
QuoteMain.PurchaseOrderNumber, QuoteMain.ShipDate,
QuoteMain.ShippingMethodID, QuoteMain.SalesTaxRate, CompMain.Address1,
CompMain.Address2, CompMain.City, CompMain.State, CompMain.Zip
FROM CompMain INNER JOIN QuoteMain ON CompMain.CoID = QuoteMain.CoID;

I have a bound cbo box to CoID which finds company names. Works great!

I have another cbo box Contact (currently bound to ContactID). This
lists the contacts within that company. It looks up the right names
but doesn't save the info. Well, it does seem to save them, but it's
not displaying them. Also, when I select the name, it stays there from
record to record, instead of going back to blank or showing the right
one. It's bound column is (0) which is the ContactID. It's
recordsource is

SELECT ContactMain.ContactID, ContactMain!FirstName & " " &
ContactMain!LastName AS Expr1, ContactMain.CoID FROM ContactMain WHERE
(((ContactMain.CoID)=Forms!zQuoteMain1![CoID])) ORDER BY
ContactMain!FirstName & " " & ContactMain!LastName;

I want to save this value for other things. What am I doing wrong??
Does this have to do with the fact that there isn't a relationship
between tblContacts and tblQuotes?? All my tables are connected by the
CoID which is PK for tblCompany

It soundes like the contacts combo box is unbound. Try
setting its ControlSource property to the ContactID field.

 

[Angi]

I have another cbo box Contact (currently bound to ContactID).

 

[Marshall Barton]

I have another cbo box Contact (currently bound to ContactID).

I think maybe this may be at least part of the issue. You
need to have a VBA statement in the CoID combo box's
AfterUpdate event procedure to Requery the Contact combo
box:
Me.Contact.Requery

You also need the same statement in the form's Current
event.

 

[Angi]

Marsh,
I actually had that statement in the cboCoID, but I didn't have it in
the form's current event. It works now but what I don't understand is
why I needed that. The value is in the table for that record but yet
the form wasn't updating. Is that something specific for cbo boxes?
Just asking since I've never had this problem.

Thanks
Ang

 

[Marshall Barton]

I actually had that statement in the cboCoID, but I didn't have it in
the form's current event. It works now but what I don't understand is
why I needed that. The value is in the table for that record but yet
the form wasn't updating. Is that something specific for cbo boxes?
Just asking since I've never had this problem.

Actually, the form was updating, but since the contacts
combo box's RowSource did not include the value in the
field, its display was out of sync on records that had a
different company than the last time the company combo box
was updated.

Sheesh, but that sounds confusing. Think about it for
awhile and maybe you can figure out what I'm trying to say.
Man, am I glad this wasn't on a continuous form, talk about
confusing. Uh oh, you never did say what kind of form
you're using. Please Angi, tell me you're using a single
view form ;-)

 

[Angi]

Marsh,
Well, since you asked...Yes, this particular form is single view, but
I'm about to add a subform to it that has 4 dependent cbo boxes on it.
Each dependent on the one before it. It's gonna be in continuous form
view...what should I know??? Or should I wait until i finish the form
and ask my questions then?

Thank you very much for the explanation! I think what you mean is the
row source isn't the control source so it wasn't updating...right???
If it's row source was equal to the control source, then it would
display. Either way, I think I understand and now it makes sense.

Thanks,
Ang

 

[Marshall Barton]

Well, since you asked...Yes, this particular form is single view, but
I'm about to add a subform to it that has 4 dependent cbo boxes on it.
Each dependent on the one before it. It's gonna be in continuous form
view...what should I know??? Or should I wait until i finish the form
and ask my questions then?

Oh boy, how do I explain this? Try to take it one step at a
time?

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.

Go ahead and get started and you will soon see the problem.
See if my confusing explanation makes a little more sense
then. Try playing around with the ideas and post back when
you get tied in knots ;-)

Thank you very much for the explanation! I think what you mean is the
row source isn't the control source so it wasn't updating...right???
If it's row source was equal to the control source, then it would
display. Either way, I think I understand and now it makes sense.

Close. Keep in mind that the RowSource is a query that
retrieves a list of records. The value of the combo box's
bound field (specified in the ControlSource) needs to match
one of the RowSource records so the combo box can display
another field in the matching record.