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

alan2013

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

I’m here to help
Staff member
Local time
Today, 06:51
Joined
Oct 29, 2018
Messages
21,357
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:
Code:
=[ComboboxName].[Column](1)
 

alan2013

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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:
Code:
=[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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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.
 

Attachments

  • My database (WIP).accdb
    3.9 MB · Views: 213
  • ParentActivities.accdb
    896 KB · Views: 157

CJ_London

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
OK - so where is the control that specifies the contract?
 

alan2013

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
I mean anywhere. I opened your form frmProductionCompany. I don't see any combo anywhere
 

alan2013

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 06:51
Joined
Mar 24, 2013
Messages
69
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.
 

Users who are viewing this thread

Top Bottom