Cascading combo on sub form (1 Viewer)

[james7705]

Good day
I have attached a sample of my database here.
the problem I am having is I have 2 combo boxes on my form which works perfectly. When I attach that form to another form as a subform, everything falls apart.
I have tried to create a subform in datasheet and it doesnt work.
I then tried to create a normal continuous form and attach it to my main form and it also doesnt work.
Everytime on my main form it does not populate the second combo box.

Please can someone help:banghead:

 

[isladogs]

Your form opens to a new record.
When I change it to another saved record, both subforms are populated

So I'm unclear what the issue is

I can only see 1 combo on your main form - Actual Work Completed - its row source is incorrect referencing a non existent table. Correct that & it will work

 

[james7705]

the issue is not the main form. both subforms have combo boxes.
the 1st one...department works fine
the 2nd one...description should get info from 1st one, but doesnt.
if you create a new record on main form and the scroll down to any of the 2 sub forms, it allows you to select department, but does not populate description.
it works if you open the subforms separately from the main form.

i hope this makes more sense?

 

[Gasman]

I selected Courier Fees in the first subform and it asks for Store.Department. That is also asked for when I tried the second subform on it's own.


Trying second subform I get Forms!Domestic Material Used Form!cboDepartment, so the department does not look like it is being supplied correctly to get the correct records?

 

[Gasman]

I believe as you made them subforms you now need to refer to the subform control.?
I changed the subform control names to sfm and sfm1 to distinguish between control and form.



I do not use macros much, so the top subform uses a macro, the bottom uses vba.


HTH

 

[james7705]

I believe as you made them subforms you now need to refer to the subform control.?
I changed the subform control names to sfm and sfm1 to distinguish between control and form.



I do not use macros much, so the top subform uses a macro, the bottom uses vba.


HTH

thanks for the help Gasman...looking at it...it makes more sense now...having to refer to both forms. thank you.

would it be possible for you to pop in a little dlookup on the PLU section of the subform?
i'm not 100% sure how that would work on a form or subform.
in other words...when i select department, then description it should lookup the PLU and save it in the same table so that when i print a report it shows the PLU number as well.
is that possible?

 

[Gasman]

TBH that took me a while to figure out and I had to Google as well. It was just that I remembered something about subform control name and not the subform name. Sometimes they can be both, especially if you drag teh form to the mainform. As yours were a bit of a mouthful, I shortened them to sfm and sfm1.

If all you want is a DLookup, then that is just the code for that function.
I do not want to complicate matters here, but if the PLU is based on the department, then I would consider bringing that into the source for the department and then use that extra column to set the PLU?

No one know how you intend to use those forms, as both subforms seem to be the same, so I assumed you were trying out various things as I would do.?
If you would need to use the form both as a standalone form and a subform, I would consider using a TempVar or global variable for the query criteria, and set that in each form. That way the query works for any form that needs it.?

HTH

 

[james7705]

TBH that took me a while to figure out and I had to Google as well. It was just that I remembered something about subform control name and not the subform name. Sometimes they can be both, especially if you drag teh form to the mainform. As yours were a bit of a mouthful, I shortened them to sfm and sfm1.

If all you want is a DLookup, then that is just the code for that function.
I do not want to complicate matters here, but if the PLU is based on the department, then I would consider bringing that into the source for the department and then use that extra column to set the PLU?

No one know how you intend to use those forms, as both subforms seem to be the same, so I assumed you were trying out various things as I would do.?
If you would need to use the form both as a standalone form and a subform, I would consider using a TempVar or global variable for the query criteria, and set that in each form. That way the query works for any form that needs it.?

HTH

yea i was trying different ways with the subforms to see which would work the best.
i will be using the form exactly as it is except that i will remove one of the subforms.
the idea is to input the work details and then the items used for that particular work.
the dlookup needs to refer back to the "store" table... get the [plu] number corresponding the [description] of the item and display it on the subform and enter it to the table "material used" in the [plu] column
DLOOKUP("[PLU]", "[STORE]" and this is pretty much where i fall apart.

 

[Gasman]

You are getting the Description from the Store table already, so just bring in the PLU as an extra column in the query source, and when the description is selected, populate the PLU. make it read only so that it cannot be changed? In fact it could be hidden?

Something along the lines of

Me.PLU = Me.cboDescription.Column(2)

as the column numbering starts at 0


That leads me to question why you have the description everywhere?, surely the PLU is sufficient?


HTH


Edit: DB attached to show what I mean