Populate the record(s) in a continuous subform using a combo box in that subform ?

[alan2013]

Apologies for the probably newbie question, but :

I'm trying to work out how to populate the lines in a continous subform based on my selection from a combo box within the subform.

I have a number of scenarios in which I'd like to use this method, but to pick one out :
I've a continuous main form 'frmOrders', listing all of my orders received. I click on a 'Go to order' button for an order of my choosing, and that takes me to an 'frmOrder' for that specific order. frmOrder has a TabCtl, and one of the tabs is entitled 'Dimensions'. Embedded on the 'Dimensions' tab is a continuous subform for capturing what the customer has requested. When someone orders a product from me, he/she may indicate that they want the product as 8"x10" and/or 10" x 14" and /or 16" x 18" (etc, etc). Each option where dimensions are concerned carry with it some other details (material type; margin widths; colour; etc). I have a data-table, tblSpecs, holding all of those dimensions, material, margin, colour, etc details. Each set of details has a primary key, intSpecsID.
Is it possible to populate the line or line(s) of the subform using a combo box within the subform (ie select from the combo box, and have the fields for dimensions, material, margin, colour, etc populated based on the choice) ?

 

[CJ_London]

don't see why not. perhaps some vba code along this code in your combo afterupdate event

me.field1=me.combobame.column(1)
me.field2=me.combobame.column(2)
etc

 

[alan2013]

don't see why not. perhaps some vba code along this code in your combo afterupdate event

me.field1=me.combobame.column(1)
me.field2=me.combobame.column(2)
etc

Thanks. Yes, I'm trying something along those lines. However, besides the material / colour / etc details, how do / should I assign the intSpecsID too in the subform, so that the intSpecsID is linked to the Order ? In trying to do so using me.field1=me.comboname.column(0), a whole new intSpecsID is created in the subform, and that's not what I want. Sorry for my lack of knowledge..

 

[CJ_London]

Depends on your column order - column 0 is the first column so check the rowsource of the combo

I assume the idea is these values are editable once copied across. If not then you should just be referencing the values

 

[alan2013]

Depends on your column order - column 0 is the first column so check the rowsource of the combo

I assume the idea is these values are editable once copied across. If not then you should just be referencing the values

Yes, intSpecsID is in the first column in the combo.
I was intending to set 'Enabled' and 'Locked' to 'No' and 'Yes' respectively, for the copied-across values, so that they cannot be edited.

 

[theDBguy]

Yes, intSpecsID is in the first column in the combo.
I was intending to set 'Enabled' and 'Locked' to 'No' and 'Yes' respectively, for the copied-across values, so that they cannot be edited.

I would second @CJ_London in suggesting that you simply reference the intSpecsID rather than store the related values in multiple tables. If you only use a foreign key, then you won't even need any code to show the related values from the lookup table. In the Control Source of the value you want to display, you could simply use something like:

=[ComboboxName].[Column](1)

 

[alan2013]

I would second @CJ_London in suggesting that you simply reference the intSpecsID rather than store the related values in multiple tables. If you only use a foreign key, then you won't even need any code to show the related values from the lookup table. In the Control Source of the value you want to display, you could simply use something like:

=[ComboboxName].[Column](1)

Thanks for you guidance. However, I'm struggling to understand exactly what needs to be done to be able to enter a record, or multiple records, in the subform. Could you provide an example database, d'you think? (Sorry)

 

[CJ_London]

since you are not very clear about the actual requirement, better you supply what you have - compact/repair then zip, plus an example of what you want the final outcome to be

 

[alan2013]

since you are not very clear about the actual requirement, better you supply what you have - compact/repair then zip, plus an example of what you want the final outcome to be

Please see attached :
1) my database (work-in-progress); <----{I've moved on to a different database to the one I initially described}
2) the only example of a similar scenario I've been able to find so far (addition of parent names to the subform embedded in frmActivities).

On the subform on the 'Production company / companies' tab on frmContract, I want to be able to choose (a) production company / companies that the contract applies to. (And, once I have that working, apply the same method on the 'Publishers' tab). I can't quite see why I can't get this to work, and would really appreciate your input. Thanks a lot.

 

[CJ_London]

OK - so where is the control that specifies the contract?

 

[alan2013]

OK - so where is the control that specifies the contract?

In the subform , you mean? Well, I see now that I included intContractID in the combo....though not sure that I needed to do that.. Are you able to get this working, or am I attempting something that isn't achievable?

 

[CJ_London]

I mean anywhere. I opened your form frmProductionCompany. I don't see any combo anywhere

 

[alan2013]

I mean anywhere. I opened your form frmProductionCompany. I don't see any combo anywhere

I've been - and am - trying to link contracts to production companies via the 'Contract' form (frmContract, accessible via frmContracts). If there's a better way to do that, I'm eager to know.

 

[CJ_London]

You know your app, I don't, So better go through the process of what is supposed to happen -

so you don't open frmProductionCompany, you open frmContracts - then what?

 

[alan2013]

You know your app, I don't, So better go through the process of what is supposed to happen -

so you don't open frmProductionCompany, you open frmContracts - then what?

Open frmContract. Go to 'Production company / companies' tab. In the subform, the field that's furthest left has a header 'Choose' (and Control Source intProductionCompanyID and Name cboProductionCompanyID). Is there some way of linking companies to contracts using that combo, d'you think (and having all of the other fields in the subform populated via their Control Sources, =[cboProductionCompanyID].[Column](1), etc) ?

 

[CJ_London]

looks to me like your table design/relationships is wrong - or are you saying you only use a production company or publisher for one contract only and never again.

think you need a joining table between contracts and production companies - and that is the table your subform should be based on

 

[alan2013]

looks to me like your table design/relationships is wrong - or are you saying you only use a production company or publisher for one contract only and never again.

think you need a joining table between contracts and production companies - and that is the table your subform should be based on

Apologies for the delay in replying.
Thanks for your advice. I seem to be making progress now, using a joining Table.