Solved Populate subform based on selection of combo

[zac123]

Hello everybody,

Kind of fallen at the first hurdle here. In a contacts db i'd like to populate the fields of a subform based on when the user selects from a combo box.

1. Create form called CustomerDetails
2. Add a combo box and run through the wizard adding the 'CustomerName' field from the 'Customers' tbl
3. Confirm the combo box works in form view
4. Now add a subform to CustomerDetails called CustomerDetailsSubForm
5. Run through the wizard, select all the fields i want.
6. Preview in form view.

so...

questions...

1. How do i bind the subform results to only show my selection from the master form?
2. why when i view it in form view does the subform switch to datagrid?

Many thanks in advance.

zac

P.S. i love bullet point lists

 

[Ranman256]

check the form property to make sure you didn't set the prop DEFAULT VIEW to: datasheet.
you want single form. (for subform use)

the subform is normally tied to the Master Key ID, via subform property:
link master field: ID
link child field: ID

but you can filter further by using the combo boxes (or other)

sub btnFind_click()
sWhere = "1=1"
if not isnull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"

'then filer
if sWhere = "1=1" then
me.filterOn = false
else
me.filter = sWhere
me.filterOn = true
endif
end sub

Note this is for the SUBFORM filter, not master form.
so 'me.filter' may need to be: me.subfrm.form.filter =

 

[zac123]

check the form property to make sure you didn't set the prop DEFAULT VIEW to: datasheet.
you want single form. (for subform use)

the subform is normally tied to the Master Key ID, via subform property:
link master field: ID
link child field: ID

thanks ranman,
you were correct about the default view.

ive actually just come across this video which also helped.

the main thing seems to be to create the subform separately then drag it into the main form. rather then using the wizard to create the subform.

 

[mike60smart]

Hi Zac

From your description you seem to be creating a Main Form based on the Customer and then a Subform based on the same Customer table?

Is this Correct?

Normally you would create the Main Form based on the Customer table and then a Subform based on a related Child Table.

 

[zac123]

Hi mike, yes that's correct. My main form contains the combo box. The user selects the customer name that they wish to edit the details of. The subform then displays the customers info ready for the user to make some changes .

Is this bad practice? Its seems to be working

 

[mike60smart]

Hi Zac

It may be that it is a description problem.

What you are describing is a Combobox in the Header of the Form and then a Subform to display the results.

This process does not need a Subform.

You just need a Form based on the Customer table. Then in the Header a ComboBox.

 

[zac123]

Hi Zac

It may be that it is a description problem.

What you are describing is a Combobox in the Header of the Form and then a Subform to display the results.

This process does not need a Subform.

You just need a Form based on the Customer table. Then in the Header a ComboBox.

Thank you Mike. Ive just tested it with the combo in the FormHeader and tested it with the combo in the Detail section. Both ways seem to work. Are you saying its best practice to have it separated (header/details)? will i run into trouble later down the line if i don't separate them off?

i'm at the very beginning of this mini project so i may as well set off in good order.
thanks
zac

 

[mike60smart]

Thank you Mike. Ive just tested it with the combo in the FormHeader and tested it with the combo in the Detail section. Both ways seem to work. Are you saying its best practice to have it separated (header/details)? will i run into trouble later down the line if i don't separate them off?

i'm at the very beginning of this mini project so i may as well set off in good order.
thanks
zac

Hi Zac

I always place a Specific Search Combobox in the Header section of the Main Form and reserve the detail area for the actual Controls.

You can then utilise the Header section to Add New Customers if required and also have a Close Button

Then if you want to display related data from another table then you would create a Subform to display this data