How to get combo box in subform works in Navigation form?

[Kev_1988]

Hi,

I got a problem don't know how to solve it

I've made a form with two combo boxes to find something easier. The problem is, if I attach the form (name is: TestComboBox1) to the navigation form the combo boxes doesn't work anymore. I get an error like: enter parameter value "Forms!TestComboBox1!cbosuppliertest

How can I solve this?
I will attach my file so you can see it for yourself at Navigation form > product overview > overview and then select anything from supplier combo box

Note: I'm a beginner with Access and VBA please be patient with me

Please help me out..
Thanks a lot

 

[CJ_London]

it is because your form is now within another form - so you have to use a different 'path' in much the same way you would if you moved a file to a different directory.

see this link http://access.mvps.org/access/forms/frm0031.htm

 

[Kev_1988]

Can you explain to me step by step where I must change?
I don't know where i have to change with the link you gave to me
Sorry i'm still a beginner

it is because your form is now within another form - so you have to use a different 'path' in much the same way you would if you moved a file to a different directory.

see this link http://access.mvps.org/access/forms/frm0031.htm

 

[CJ_London]

you need to change it wherever you reference it - typically in a query in which case you can use the expression builder to navigate to the new location - in a subform on your navigation form

 

[Kev_1988]

But if you look at the attachment file on my first post. Maybe if you look at the file you can explain me better?
I don't have query forms.
I have only tables and forms.
So in my navigation form under tab "Product overview" in property sheet tab Data; Navigation Target Name I've select "SubForm"

Subform is a form. In the Subform i have under tab "overview" Navigation Target Name: TestComboBox1 (this is a form too)

So where do i need to open expression builder?

you need to change it wherever you reference it - typically in a query in which case you can use the expression builder to navigate to the new location - in a subform on your navigation form

 

[CJ_London]

you say you are not using queries, but you are - the rowsources to the combos, the recordsources to forms, the application of filters to forms are all queries (or the criteria part in the case of filters)

You are using macro's which I don't, so can't help with that as such.

The macro is applying a filter, so as mentioned above, so effectively a query.

I suspect that the macro has some underlying code which has related it to the testcombo1 form as its own form. But now that form isn't open in its own right - the form open is the navigation form and your testcombo1 form is a subform on the navigation form.

As I said, I don't use macros so cannot provide a solution. All I can suggest is you delete the macro and recreate in the navigate form. Alternative might be to not use screen.active control at all and use the actual name of the control instead.

If you are doing any sort of serious development, I strongly recommend you start to use VBA rather than macros. Macros have a relatively limited range of commands, no error checking facility and very little online help. And you will struggle to find someone to take over the maintenance of the application when it is time for you to move up the ladder.

Because these are macros you cannot search (so far as I know) for all instances where you have a problem. You can only do it by opening each macro and checking manually

 

[Kev_1988]

Oke so technically rowsources and recordscources are all queries? didn't know that.

Oke now you saw the form "TestComboBox1" for the second combo box (cboProductTest) I use macro indeed. Don't now how you change to vba code?

I want use those 2 combo box to select a product very easy and quick as you saw.
How do I change the marco code of the second combo box (cboProductTest) to a vba code? So that everything works the same as before?

Yes, the main form is navigation form and testcombobox1 form is indeed a subform on the navigation form

you say you are not using queries, but you are - the rowsources to the combos, the recordsources to forms, the application of filters to forms are all queries (or the criteria part in the case of filters)

You are using macro's which I don't, so can't help with that as such.

The macro is applying a filter, so as mentioned above, so effectively a query.

I suspect that the macro has some underlying code which has related it to the testcombo1 form as its own form. But now that form isn't open in its own right - the form open is the navigation form and your testcombo1 form is a subform on the navigation form.

As I said, I don't use macros so cannot provide a solution. All I can suggest is you delete the macro and recreate in the navigate form. Alternative might be to not use screen.active control at all and use the actual name of the control instead.

If you are doing any sort of serious development, I strongly recommend you start to use VBA rather than macros. Macros have a relatively limited range of commands, no error checking facility and very little online help. And you will struggle to find someone to take over the maintenance of the application when it is time for you to move up the ladder.

Because these are macros you cannot search (so far as I know) for all instances where you have a problem. You can only do it by opening each macro and checking manually

 

[CJ_London]

depends on the version of access. For 2010 see this link http://www.iaccessworld.com/how-to-convert-macro-to-vb-code/ and for 2007 this one https://btabdevelopment.com/how-to-convert-embedded-macros-to-vba/. Otherwise google 'access convert embedded macro to vba' for more sites/versions etc

 

[Kev_1988]

I've converted the macro to vba with" Convert form's macros to visual basic"
Now there is vba code.
But if i changed the query code of the combo box "cboProductTest" from [forms]![TestComboBox1]![cbosuppliertest] to Like "*" & [forms]![navigation form]![navigationsubform].[form]![cbosuppliertest] & "*"
the the combo box works in navigation form but the filter application doesn't work any more. It shows everything in the combo box (Product).
For example I select combo box of supplier: HEL
Then you supposed to get three options to select like: Tab1 or Tab2 or Tab3 in combo box Product
Instead I get to see all products so the filter application don't work as before

The only thing I did was change the macro to vba and change [forms]![TestComboBox1]![cbosuppliertest] to Like "*" & [forms]![navigation form]![navigationsubform].[form]![cbosuppliertest] & "*"

How can I solve this?

depends on the version of access. For 2010 see this link http://www.iaccessworld.com/how-to-convert-macro-to-vb-code/ and for 2007 this one https://btabdevelopment.com/how-to-convert-embedded-macros-to-vba/. Otherwise google 'access convert embedded macro to vba' for more sites/versions etc

 

[CJ_London]

if you change a value in cbosuppliertest you need to requery the cboProductTest control in the after update event of cbosuppliertest to refresh the rowsource to reflect the current supplier

 

[Kev_1988]

I change the macro in the cboProductTest.
If you see the new vba where do I write the requery?
Go to form "TestComboBox1 > property sheet cboProductTest > after update
I'll add the file so you can see it

if you change a value in cbosuppliertest you need to requery the cboProductTest control in the after update event of cbosuppliertest to refresh the rowsource to reflect the current supplier

 

[CJ_London]

I think the problem is you are using a navigation form template. They have limited functionality which means the rowsource to your product combo cannot see the full structure of the navigation form.

I don't use navigation forms because of this. But change the rowsource of your cboProduct to

SELECT ID, ProductName FROM tblProduct WHERE Supplier=[cbosuppliertest]

should get you going again

 

[Kev_1988]

Thanks a lot!!
It works

I think the problem is you are using a navigation form template. They have limited functionality which means the rowsource to your product combo cannot see the full structure of the navigation form.

I don't use navigation forms because of this. But change the rowsource of your cboProduct to

SELECT ID, ProductName FROM tblProduct WHERE Supplier=[cbosuppliertest]

should get you going again

 

[CJ_London]

your form should be more portable now (at least in respect of the product combo) since the reference is now kept internal to the form (rather than using forms!myform.. etc). It will still work even if you change the name of the form, or use as a subform to another form

 

[Kev_1988]

Thanks for helping me so far!!

I got a new question/problem...
Hope you can help me out here

I've created a "frmResult" form, with the same principal, two combi boxes.
Now I've named the first combo box: Product Name
Second combo box: Test Sample

What I want is in the first combo box: Product Name, selecting only products that's already selecting.
But the second one is tricky, for second combo box: Test Sample, I only want to select between: Test Sample A or Test Sample B or Test Sample C.
-Test Sample A contains 2 columns
-Test Sample B contains 2 columns
-Test Sample C contains 2 columns

So in other words if I select for Product Name: DevTab2
And for Test Sample: Test Sample A
It's only shows result: 2 hits (if I'm correct)
First one:
-Test Sample A, Result 1 = blue
-Test Sample A, Result 2 = red

Second one:
-Test Sample A, Result 1 = blue
-Test Sample A, Result 2 = yellow

Hope you understand me what I'm trying to tell
What vba code can I use in order to get what I want?
I'll add my file so you can see it for yourself

Hope you can help me with this

 

[CJ_London]

This is really a different question - suggest post to a new thread. I'm about to close down for Easter and don't have time to respond now

 

[Kev_1988]

Oke, thanks for your help.
Enjoy your holiday

This is really a different question - suggest post to a new thread. I'm about to close down for Easter and don't have time to respond now