Help With What Is (Probably) A Simple Problem

[June7]

Dependent comboboxes that have RowSource with a lookup alias will not work nicely with datasheet or continuous form. This is a known issue and there is no easy way to deal with.

The issue is caused by the fact there is only one control and when its RowSource is filtered, lookup values are not available for all records. When you return to the previous record the RowSource is still filtered for the second record, which means the lookup alias values associated with the first record are not available for display. The combobox RowSource must be requeried, which will then make the lookup alias values needed for the second unavailable.

I recommend you filter the dependent combobox only when:

1. on a new record

2. the primary combobox value is changed

This will require code that modifies the combobox RowSource sql statement.

[shoelesscraig]

This will require code that modifies the combobox RowSource sql statement.

Fair enough...I don't know much about VBA code, just enough to mess stuff up. Where can I find out how to do what you suggested. Seems like there shouldn't be too much involved to make that happen...?

[June7]

In the form Current event:

Me.cbxContacts.RowSource = "SELECT ContactID, ContactLastName & ', ' & ContactFirstName AS ContactName FROM tblCompanyContacts " & IIf(Me.NewRecord, "WHERE CompanyID = " & Me.cbxCompany, "") & ";"

[shoelesscraig]

In the form Current event:

Me.cbxContacts.RowSource = "SELECT ContactID, ContactLastName & ', ' & ContactFirstName AS ContactName FROM tblCompanyContacts " & IIf(Me.NewRecord, "WHERE CompanyID = " & Me.cbxCompany, "") & ";"

Ok, so in doing this:

Is the following correct?
1. This should be the ONLY VBA code I have, correct? I took away the part about "requerying" the 3 combo boxes from the "AfterUpdate" for the CoName box on the form.
2. I should leave the RowSource for the 3 contact selection combo boxes blank? They will be filled based on the code you provided?
3. When you say "form Current event", you mean in VBA select "Form" in the left hand box and "Current" in the right hand box?

If this is correct, we're closer, but something is still not right. Now when I click the contact name dropdown when I'm viewing an existing record, ALL contacts are available to pick from dropdown, not just the ones that are associated with that company. However, when I make selections for one record, move to another, then come back, my changes are still there...so that is fixed!

Also, when I'm trying to enter a new record, doesn't matter which company I select for it...NOTHING is available in the contact dropdown. I've attached another copy of db so you can see what I'm talking about.

Project & Proposal Summary 3rd Post.zip

[June7]

The real trick is figuring out what event(s) to put code into.

Will also need the code in the cboCoName AfterUpdate or the contact comboboxes GotFocus event, only without the NewRecord condition.

Your code is filtering on the CoName but value of cboCoName is CompanyInfoID

Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts WHERE ContactCo = " & Me.cboCoName & ";"

I recommend the GotFocus.

Also, in cboCoName AfterUpdate event, might want to set the contact comboboxes to Null.

[shoelesscraig]

The real trick is figuring out what event(s) to put code into.

Will also need the code in the cboCoName AfterUpdate or the contact comboboxes GotFocus event, only without the NewRecord condition.

Your code is filtering on the CoName but value of cboCoName is CompanyInfoID

Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts WHERE ContactCo = " & Me.cboCoName & ";"

I recommend the GotFocus.

Also, in cboCoName AfterUpdate event, might want to set the contact comboboxes to Null.

Ok, so I did this and it works! I put the code (without the "new record" part) in the GotFocus of the 3 contact combos. In the "Form Current" I put the code you suggested (including the "new record" part). And in the CoName combo, for the AfterUpdate event, I told it to Null out the 3 combos below.


I can use it like this...no problems...however, if we want to strive for perfection, it does have one issue. When I'm viewing the form and go all the way to the last record and then on to the "new record"...if I look at the blank new record and then hit the back arrow to go back to the last record, it pops up an error saying "Syntax error in FROM clause". Any ideas?

Thanks again for your help. I learned a lot...I didn't just paste your code in...I actually studied WHY you said to code it like that. Definitely learned a lot. Thanks again!

[June7]

I've used dependent comboboxes only once and did not encounter that. So not quite sure what cause is. Could try setting the RowSource with static reference to the primary combobox, instead of concatenating as a variable.

Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts " & IIf(Me.NewRecord, "WHERE ContactCo = [cboCoName]", "") & ";"

Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts WHERE ContactCo = [cboCoName];"

Then use Me.cboCoContact1.Requery.

Review link at bottom of my post for debugging guidelines. Debugging is critical skill for a developer.

[shoelesscraig]

Thanks...I'll work on figuring it out.

One more question...How would I go about making my 3 contact dropdowns order the items in their list by last name (which is CoContactLN). I tried it myself, get various errors no matter which way I go.

[June7]

Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts " & IIf(Me.NewRecord, "WHERE ContactCo = [cboCoName]", "") & " ORDER BY CoContactLN;"


Me.cboCoContact1.RowSource = "SELECT ID, CoContactLN & ',' & ' ' & CoContactFN AS CoContact1 FROM tblCoContacts WHERE ContactCo = [cboCoName] ORDER BY CoContactLN;"