How to pass a value from main form to a subform

scheeps

Registered User.
Local time
Tomorrow, 09:04
Joined
Mar 10, 2011
Messages
82
How can I achieve this? I've got a subform with a specific combo box in it. Depending on a selection in the parent/main form, I would like to update the combo box according to that value.

I populate the combo box's row source is as follow in the OnCurrent event:
Code:
Me.cboReturnLineType.RowSource = 
"SELECT ODS.Return_Line_Type.Return_Line_Type_ID, ODS.Return_Line_Type.Return_Line_Type_Name 
FROM ODS.Return_Line_Type 
WHERE Return_Type_ID = " & ReturnType & " 
ORDER BY Return_Line_Type_Name"
Not sure using a global variable is the way to go but I've declared a ReturnType variable as "Public ReturnType as Integer" on the parent form and I've assigned it a value.

How can I force my code to pick up the global variable's value? And is it at all possible to pass a value between forms and subforms?

I've also tried using [Forms]![frmReturn_Header]![cboReturnType] to point to the specific field:
Code:
Me.cboReturnLineType.RowSource = 
"SELECT ODS.Return_Line_Type.Return_Line_Type_ID, ODS.Return_Line_Type.Return_Line_Type_Name 
FROM ODS.Return_Line_Type 
WHERE Return_Type_ID = 
" & [Forms]![frmReturn_Header]![cboReturnType] & " 
ORDER BY Return_Line_Type_Name"
without any luck.
 
And is it at all possible to pass a value between forms and subforms?
Take a look here: Refer to Form and Subform properties and controls

How can I force my code to pick up the global variable's value?
Global variables need to be designated in a model as a public variable.

I've also tried using [Forms]![frmReturn_Header]![cboReturnType] to point to the specific field:
Take a look at my post here on using a pop-up form.
 
Last edited:
Thanks Steve, that is a very helpful site to have handy.

Awesome, I've solved my issue. Thanks again!
 
Code:
WHERE Return_Type_ID = " & ReturnType & "

Are you sure this shouldn't be Return_Line_Type_ID ?

In any case using such long and very similar names for all the fields is a sure way to make the code hard to read. I would strongly advise you to reconsider your naming strategies.

I am not a fan of the underscore in names either. Just another unnecessary character to type and it breaks up the name.
Visually the underscore is more of a separator than the dot so it ties the tablename into the field name and makes the dot position harder to recognise.

ReturnLineTypeID is much more integrated than Return_Line_Type_ID.

BTW you don't have to include the table name with each field in the SELECT section if the field name is unambiguous.
 
If it is just use within a module the variable only needs to be declared as Private (or Dim) in the Declarations section at the top of the module.

A Public declaration in a Module makes the variable available to the whole Project.

Variables (Private or Pubilc) are not available directly to the database. For this use a function to return the variable value or (in 2007+) use a TempVar.
 
Thanks Galaxiom.

I unfortunately have not done the table and schema design and the table/field names are of such a nature that underscores are probably the best option seeing that most of the tables are Return_Line_Item, Return_Header_Item, Return_Line_Type etc. And the last mentioned are the way of the business entities are named which I can't help for.
I do think it helps with the readability of the fields and tables, but I agree that there is not always a place in the database universe for an underscore.

Thanks for your 2cents in any case :)
 
I don't understand why the subform needs to have anything from the main form. That just isn't normal in a normalised database. If it is normalised, and it's just for presentation, then You can do it easily. In it's simplest form, it's just

parent!parentcontrol
 

Users who are viewing this thread

Back
Top Bottom