filtering data other than combo boxes (1 Viewer)

[Ramnik]

Hi all,
I am using master/child form for data entry for packings and the details for this packings (i.e. the products inside a packing is entered in subform and packing master is entered in main form ).

In the detail subform i use a productId field which has look up from 1000 products , so i want to filter that particular combo box based on different types from product master ( from which it choses the products).

How many ways are there to filter that combo box based on different types ( which i have in product master as type,design,material etc).
One way of which is combo boxes on main form.

For reference please visit this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=261823

. One of experts advised me to start a new thread to find the answer. If you need , please visit my previous thread for my root question.

Thanks.

 

[Uncle Gizmo]

If I understand your problem correctly you have a sub-form which contains a combo box. When entering data you have a problem when you get to the combo box because it has a 1000 entries to choose from. To solve the problem of a large number of entries you added criteria selection to the main form to reduce the number of entries selectable.

Unfortunately this solution resulted in a situation where sometimes the combo displayed a blank entry. However I think you are heading in the direction to a solution that may work with a re-think. Instead of providing the combo box with a row source that fits all, provide it with a dynamically changing row source.

The "Blank Entry" indicates the challenge you will have to solve. Once you start changing the combo box row source you will have to make sure it has a suitable row source for ALL situations. It will need a suitable row source for when you are browsing records, so that it returns the correct results, and it will need suitable row source for when you are entering new records.

What I would propose is that you use your existing arrangement of combo boxes on the main form for controlling the criteria in the combo box on the sub form. So now when you make a selection of the criteria on the main form, use the combo box after update event to update the Row Source of the combo box on the sub form.

I find the best approach when making a major change to your database like this one is to follow a series of simple steps; step which you can practically guarantee the outcome of. In other words, if you don't get the outcome you expect, then it is quite easy to track the fault.

For this method I am proposing that we need to gain access to the row source of the combo box on the sub form, (we actually want to update the row source) However as a simple check; a simple test we can make to insure that we are on the right track, let's see if we can extract the row source from the combo box.

To do this we need to run some code, the best way to run some code is to create a command button on the main form. Now we need to access the combo box on the sub form, lets call the combo "ComboX", and lets call the sub form "MySubForm". Accessing a sub form is not intuitive as a sub-form actually resides in a subform control (which I refer to as a subform window), lets call this subform control "subformWindow".

So the code you need to put in your command button to get the row source of the combo box should look something similar to this: MsgBox " >>> " & Me.subformWindow.form.ComboX.rowsource ---

NOTE: "form" refers to the form "MySubForm", but you don't use the form name "MySubForm", you just use "form" as shown.

PS: I'm sure there are other ways to tackle it, like have a popup form from your sub-form, (I think this would be a better solution) and I'm sure others will have suggestions which is why I suggested a new thread.

 

[Ramnik]

Thanks , I will try to do it this way.
But did you see my last post in that thread and can you provide me the reason why it happened ?

 

[Uncle Gizmo]

did you see my last post in that thread and can you provide me the reason why it happened ?

Please post a sample database that exhibits the behaviour and I will have a look. (Access 2007)

I suspect that's the correct behaviour however I would like to investigate further before committing myself.

 

[Ramnik]

Here is a test.
Check it,

 

[Ramnik]

Please post a sample database that exhibits the behaviour and I will have a look. (Access 2007)

I suspect that's the correct behaviour however I would like to investigate further before committing myself.

Does my test database describes the problem well?????

 

[Uncle Gizmo]

Does my test database describes the problem well?????

I didn't find your example particularly helpful because it did not appear to duplicate the issue you were having in the other database. I could see all the records, and there did not appear to be any missing "Blank" entries like in your other DB.

In other words the first task you need to perform to track down a problem is "Duplicate" the problem, and be able to consistently duplicate the problem!

I did successfully duplicate blank records and I believe the problem of displaying blanks is related to the record-set that's available when the form first loads. However I'm not a hundred percent sure as I will need to do some more tests, which I do not currently have the time to conduct.

However it's a very interesting issue and I hope to get back to it at a later date.

I did manage to get the combo boxes on the main form to successfully return the correct record-sets in the sub form combo box.

 

[Uncle Gizmo]

Here's a YouTube of it Working OK... http://youtu.be/W0nw9qCS1fQ

 

[Ramnik]

I didn't find your example particularly helpful because it did not appear to duplicate the issue you were having in the other database. I could see all the records, and there did not appear to be any missing "Blank" entries like in your other DB.

Its clearly duplicating the issue .
please see it again
I m sending u screenshots...

 

[Uncle Gizmo]

I don't know how far you've got with this? If you can see the video you will see that I have it got it working. If you want some help to get it working in your database then the first thing to do is extract the row source from the combo-box as I mentioned in a previous post.

 

[Ramnik]

Yes i can see in the video that you got it working ok ... But how ??????
This is what I need . Please send me back the same test database which you have modified and posted the video . Thanksss

 

[Uncle Gizmo]

The first thing to do is extract the row source from the combo-box as I mentioned in a previous post.

Extract from previous post:-

I find the best approach when making a major change to your database like this one is to follow a series of simple steps; step which you can practically guarantee the outcome of. In other words, if you don't get the outcome you expect, then it is quite easy to track the fault.

For this method I am proposing that we need to gain access to the row source of the combo box on the sub form, (we actually want to update the row source) However as a simple check; a simple test we can make to insure that we are on the right track, let's see if we can extract the row source from the combo box.

To do this we need to run some code, the best way to run some code is to create a command button on the main form. Now we need to access the combo box on the sub form, lets call the combo "ComboX", and lets call the sub form "MySubForm". Accessing a sub form is not intuitive as a sub-form actually resides in a subform control (which I refer to as a subform window), lets call this subform control "subformWindow".

So the code you need to put in your command button to get the row source of the combo box should look something similar to this: MsgBox " >>> " & Me.subformWindow.form.ComboX.rowsource ---

NOTE: "form" refers to the form "MySubForm", but you don't use the form name "MySubForm", you just use "form" as shown.

 

[Ramnik]

Here is the rowsource as you suggested...
see attached pics.

 

[Uncle Gizmo]

Now you can do the reverse: send different SQL statements into the row source of the combo box.

For example:

The code you need to put in your command button to SET the row source of the combo box should look something similar to this: Me.subformWindow.form.ComboX.rowsource = "Select ProductID, ProductDesc FROM tblProduct"


You could start with the SQL in this image:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=53351&d=1396605250

 

[Ramnik]

Okk what u suggested is i should give the rowsource in VBA instead of form properties . Am I right????
And should i give it in on load event of form and after update event of controls ????

Thanks .

 

[Uncle Gizmo]

Yes that sounds about right. The awkward part is you need to construct a correctly formatted SQL statement which passes value from the three combo boxes. This has to be a string and it's not straightforward unless you know how.

 

[Uncle Gizmo]

I had a re-think...

Try This:

Private Sub t1_AfterUpdate()
Me.subfrm.Form.cboProductId.Requery
End Sub

Where:- "cboProductId" is your combo box