Solved Combo box drops first record when form inside Navigation form (1 Viewer)

[Goochie]

I have two forms working perfectly when I open them directly, but when I use them inside a navigation form I have the following problem -
A combo box allows me to choose a customer (or employee in the second form), all works fine, BUT after selecting a new customer I cannot use the combo box to choose the first record again. All other records are present and I can move between them but record 1 does not show.

 

[theDBguy]

Are you modifying the Row Source of the combo in any way?

 

[Goochie]

The row source criteria is <>Nz([Form]![CustomerID],0). This remains the same whether the form is viewed inside the Navigation Form or by itself.

 

[theDBguy]

The row source criteria is <>Nz([Form]![CustomerID],0). This remains the same whether the form is viewed inside the Navigation Form or by itself.

What happens if you change that to <> Nz([CustomerID],0)?

 

[Goochie]

Just tried that. The combo box has no records at all.

 

[CJ_London]

Perhaps customer Id is text and not a number in which case it would be <>””

 

[CJ_London]

Also is your rowsource a query? Or sql?

 

[CJ_London]

And next comment is to try using code to requery the combo. No info on your form so can only suggest use the form current event

 

[Goochie]

Perhaps customer Id is text and not a number in which case it would be <>””

No it is definitely a number.

 

[Goochie]

Also is your rowsource a query? Or sql?

It's a query.

 

[Goochie]

Thought it might help if you saw a picture of what I am doing. You see the combo box has 8 records showing, including the current record. Record no. 1 is not in the list but it should be. If I open the same form independently from the Navigation form I still only get 8 records BUT unlike when in the Navigation form the current record (Sarah Smith) is not listed and record 1 is available to choose. This is how it should be. Note this is the same form, it is not two different forms. The only difference is it doesn't behave properly when it is inside the Navigation form.

 

[theDBguy]

It's a query.

Can you please post the SQL statement of that query? Thank you.

 

[Goochie]

Can you please post the SQL statement of that query? Thank you.

SELECT CustomersExtended.CustomerID, CustomersExtended.CustomerName FROM CustomersExtended WHERE (((CustomersExtended.CustomerID)<>Nz([Form]![CustomerID],0))) ORDER BY CustomersExtended.CustomerName;

 

[theDBguy]

<>Nz([Form]![CustomerID],0)

Hi. Thank you! I just wanted to verify the above portion. I don't remember using the syntax before. Normally, I wouldn't have the [Form] part. That's why I suggested to remove it earlier. Now, it makes more sense why you didn't get any results when you do that. This is how I would normally do it.

<> Nz(Forms!FormName.CustomerID,0)

Could you please try that and verify that it returns the same items in the dropdown? Please remember to replace "FormName" with the actual name of your form.

If it works, then to use your form in a Navigation Form, you will have to change the above to something like this:

<> Nz(Forms!NavigationForm.NavigationSubform.Form!CustomerID,0)

Again, you will have to use the actual names of your Navigation Form and the Subform container on the Navigation Form.

Hope that helps...

 

[Goochie]

Hi. Thank you! I just wanted to verify the above portion. I don't remember using the syntax before. Normally, I wouldn't have the [Form] part. That's why I suggested to remove it earlier. Now, it makes more sense why you didn't get any results when you do that. This is how I would normally do it.

<> Nz(Forms!FormName.CustomerID,0)

Could you please try that and verify that it returns the same items in the dropdown? Please remember to replace "FormName" with the actual name of your form.

If it works, then to use your form in a Navigation Form, you will have to change the above to something like this:

<> Nz(Forms!NavigationForm.NavigationSubform.Form!CustomerID,0)

Again, you will have to use the actual names of your Navigation Form and the Subform container on the Navigation Form.

Hope that helps...

Worked fine by itself using your first example but when I modified it and ran it inside the Navigation form I got the following error.

 

[theDBguy]

Worked fine by itself using your first example but when I modified it and ran it inside the Navigation form I got the following error.
View attachment 91932

Yeah, that part is a little tricky. You need to make sure you use the name of the subform container, rather than the name of the form it contains. If you used the Wizard to create the Navigation Form, that name is usually assigned as Navigation Subform. If so, it sounds like CustmerDetails is the name of the form it contains.

 

[Goochie]

Yeah, that part is a little tricky. You need to make sure you use the name of the subform container, rather than the name of the form it contains. If you used the Wizard to create the Navigation Form, that name is usually assigned as Navigation Subform. If so, it sounds like CustmerDetails is the name of the form it contains.

That makes sense. I changed the form name to NavigationSubform and it runs, but no differently to the original issue. I still can't get that first record. It seems to me the SQL query is fine either way so it's got to be somewhere else interfering with the result.

 

[theDBguy]

That makes sense. I changed the form name to NavigationSubform and it runs, but no differently to the original issue. I still can't get that first record. It seems to me the SQL query is fine either way so it's got to be somewhere else interfering with the result.

That may be, and we can't really help if we can't see it in action. Are you able to post a demo version?

 

[Goochie]

Here you go. ResponsePortal is the Navigation form. Customers and Employees contain the combo boxes with the problem.

 

[theDBguy]

Here you go. ResponsePortal is the Navigation form. Customers and Employees contain the combo boxes with the problem.

Hi. Thanks! Please take a look now...