combo boxes based on fields from a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am constructing a form using fields from a query, this query is
taking fields from three different tables. Now, I already have one form that
displays one of the fields as a combo box and I am happy with it the way it
is, but I want the same combo boxes in the new form (based on the query) so
that when I make a selection in the combo box in one of the forms it displays
the same value in the combo boxes in the other form.

It's for an engineering application and the combo box displays different
units for a given variable e.g. for mass you could choose tons or tonnes or
pounds or kilograms or whatever, but when you choose one option in one form I
want it to automatically display that unit in the other form so that there is
consitency of units.

Any suggestions would be greatly appreciated,

Thanks in advance guys,
alastair
 
Hi Alastair,

I am assuming that they are 2 seperate forms - let me know if you intend to
use a subform. Initially I will also assume you are using Access 2000.

Try this:

Lets say you have the following:
Form1 and within it Combo1
Form2 and within it Combo2

1. Create the two comboboxes (in the different forms) with the same rowsource.

2. In the design mode of Form1, go to the properties of Combo1 and go to the
'Event' tab. On the 'On Change' bit, select [Eevent Procedure] from the drop
down and click the '...' box

3. In between the 'Private Sub' and 'End Sub' lines, enter the following:

If CurrentProject.AllForms("Form2").IsLoaded Then Forms!Form2!Combo2 = Combo1

Obviously, use the correct names rather than Form2/Combo2/Combo1 etc -
remember the above code is for Form1.

4. Do the same for the Form2/Combo2, but obviously reference the opposing
form/combo.

This will now change the combo on the other form if you change any of the
comboxes - if only 1 form is open then it will only change the current combo
and do no more.

You might want to consider subforms if you haven't already.
If you are using an older version of Access, then replace

If CurrentProject.AllForms("Form2").IsLoaded Then

with

If SysCmd(acSysCmdGetObjectState,acForm,"Form2") <> 0 Then

If I've confused you/you need any more help, reply!

Basil
 
Hi Basil,

To be honest I am a little confused, i've only been using Access for the
past two weeks (FYI it is Access 2000). Since I posted the message I've
managed to get the combo boxes (there are 7 different ones) onto the second
form with the desired options in the dropdown list, BUT, now in form view it
won't let me select any of the options and when I try to in the bottom
lefthand corner the message is displayed:

"Control cann't be edited; it's bound to unknown field 'field name'."

I don't understand why Access would allow me to have the combo box on the
form, but not allow me to edit it?

Is this something you've come across before?

Thanks for the help,
Alastair

p.s. I haven't thought about using subforms, but I may look into that

Basil said:
Hi Alastair,

I am assuming that they are 2 seperate forms - let me know if you intend to
use a subform. Initially I will also assume you are using Access 2000.

Try this:

Lets say you have the following:
Form1 and within it Combo1
Form2 and within it Combo2

1. Create the two comboboxes (in the different forms) with the same rowsource.

2. In the design mode of Form1, go to the properties of Combo1 and go to the
'Event' tab. On the 'On Change' bit, select [Eevent Procedure] from the drop
down and click the '...' box

3. In between the 'Private Sub' and 'End Sub' lines, enter the following:

If CurrentProject.AllForms("Form2").IsLoaded Then Forms!Form2!Combo2 = Combo1

Obviously, use the correct names rather than Form2/Combo2/Combo1 etc -
remember the above code is for Form1.

4. Do the same for the Form2/Combo2, but obviously reference the opposing
form/combo.

This will now change the combo on the other form if you change any of the
comboxes - if only 1 form is open then it will only change the current combo
and do no more.

You might want to consider subforms if you haven't already.
If you are using an older version of Access, then replace

If CurrentProject.AllForms("Form2").IsLoaded Then

with

If SysCmd(acSysCmdGetObjectState,acForm,"Form2") <> 0 Then

If I've confused you/you need any more help, reply!

Basil


Alastair said:
Hello,

I am constructing a form using fields from a query, this query is
taking fields from three different tables. Now, I already have one form that
displays one of the fields as a combo box and I am happy with it the way it
is, but I want the same combo boxes in the new form (based on the query) so
that when I make a selection in the combo box in one of the forms it displays
the same value in the combo boxes in the other form.

It's for an engineering application and the combo box displays different
units for a given variable e.g. for mass you could choose tons or tonnes or
pounds or kilograms or whatever, but when you choose one option in one form I
want it to automatically display that unit in the other form so that there is
consitency of units.

Any suggestions would be greatly appreciated,

Thanks in advance guys,
alastair
Click to expand...
Click to expand...
 
Hi Alastair,

Sorry for the delay in replying.
I'm sure you will have got further since your last post, but I wanted to try
and help anyway.

The reason for that problem (I think) is that you are using the control
source property which is linked to a particular field in a particular table.
Go through the help re: control sources and row sources. Then you will be
able to design a solution either by cycling through records via the forms, or
using only one of the forms to interrogate the data - and the others as
drivers (no control sources on these combos, but events on them that will
find the appropriate record in the other form).

If you have got nowhere and are still after help on this - please give me
more detail about each of the forms and the data tables that sit behind them
- and on how you want them to interact.

Basil

Alastair said:
Hi Basil,

To be honest I am a little confused, i've only been using Access for the
past two weeks (FYI it is Access 2000). Since I posted the message I've
managed to get the combo boxes (there are 7 different ones) onto the second
form with the desired options in the dropdown list, BUT, now in form view it
won't let me select any of the options and when I try to in the bottom
lefthand corner the message is displayed:

"Control cann't be edited; it's bound to unknown field 'field name'."

I don't understand why Access would allow me to have the combo box on the
form, but not allow me to edit it?

Is this something you've come across before?

Thanks for the help,
Alastair

p.s. I haven't thought about using subforms, but I may look into that

Basil said:
Hi Alastair,

I am assuming that they are 2 seperate forms - let me know if you intend to
use a subform. Initially I will also assume you are using Access 2000.

Try this:

Lets say you have the following:
Form1 and within it Combo1
Form2 and within it Combo2

1. Create the two comboboxes (in the different forms) with the same rowsource.

2. In the design mode of Form1, go to the properties of Combo1 and go to the
'Event' tab. On the 'On Change' bit, select [Eevent Procedure] from the drop
down and click the '...' box

3. In between the 'Private Sub' and 'End Sub' lines, enter the following:

If CurrentProject.AllForms("Form2").IsLoaded Then Forms!Form2!Combo2 = Combo1

Obviously, use the correct names rather than Form2/Combo2/Combo1 etc -
remember the above code is for Form1.

4. Do the same for the Form2/Combo2, but obviously reference the opposing
form/combo.

This will now change the combo on the other form if you change any of the
comboxes - if only 1 form is open then it will only change the current combo
and do no more.

You might want to consider subforms if you haven't already.
If you are using an older version of Access, then replace

If CurrentProject.AllForms("Form2").IsLoaded Then

with

If SysCmd(acSysCmdGetObjectState,acForm,"Form2") <> 0 Then

If I've confused you/you need any more help, reply!

Basil


Alastair said:
Hello,

I am constructing a form using fields from a query, this query is
taking fields from three different tables. Now, I already have one form that
displays one of the fields as a combo box and I am happy with it the way it
is, but I want the same combo boxes in the new form (based on the query) so
that when I make a selection in the combo box in one of the forms it displays
the same value in the combo boxes in the other form.

It's for an engineering application and the combo box displays different
units for a given variable e.g. for mass you could choose tons or tonnes or
pounds or kilograms or whatever, but when you choose one option in one form I
want it to automatically display that unit in the other form so that there is
consitency of units.

Any suggestions would be greatly appreciated,

Thanks in advance guys,
alastair
Click to expand...
Click to expand...
Click to expand...
 
Back
Top