Binding values of one combo box to another 2

[Triacona]

Dear All,
Thanks for a great forum!

My questions stems from the need to have two combo boxes with the identical values from a table with 2 columns.

The table Constraints has the following fields:

ConstraintsCode
ConstraintType

I have 2 Combo Boxes:

ConsId
ConstraintType


So ConsId in the table is a code that corresponds directly with the ConstraintType, so:
ConstraintsCode ConstraintsType
0BYC 0-1333m access by bicycle to nearest centre

I want the user to choose a value with the ConsId Combo Box and then have the ConstraintType Combo Box (Combox from now on) fill with the corresponding value.

Is there a way to do this with access via the form or with Visual Basic?

I hope the above explanation makes sense...
Thank you for your forthcoming help

Kind regards
Triacona

Thank you,

Kind regards

Triacona

 

[dhookom]

Why does the second control need to be a combo box? Isn't a read-only text box ok? Do you need to be able to select from one or the other?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,
Thanks for your help!
Basically if the user can not identify the code, this is for the non technical users, it would be nice to be able to select from one or the other.

If this is not possible, I could change it to a read only text box...and then give the users a list of the codes and corresponding meanings...

But if there is a way to do it I'd be much abbliged...

Another though is to display both columns and then when the user chooses one it only stores the first column, so bound column 1??

That way the other combox could be changed to a read only textbox and then show the corresponding values.

But I have no idea on how to link the text/combox together.

Thanks for your help it is much appreciated

Thank you,

Kind regards

Triacona

 

[dhookom]

You can use two combo boxes both bound to the same field in the form's record source. One can display the code and the other the title. The bound column must remain the same.

You can also use a single combo box with a row source like:

SELECT ConstraintsCode, ConstraintsCode & " - " & ConstraintType
FROM Constraints
ORDER BY ConstraintsCode;

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Duane,
Thanks for your help
I tried the code you supplied...
This concantanates the 2 fields together...

SELECT ConstrConstrType.ConstraintsCode, [ConstraintsCode] & " - " & [ConstraintsType] AS Expr1
FROM ConstrConstrType
ORDER BY ConstrConstrType.ConstraintsCode;

I referenced the wrong table in my previous post.
The Table that holds the constraints list is called:
ConstrConstrType
It has 2 Fields called
ConstraintsCode
ConstraintsType

But what I want is more complex than just concatenating or variying views.

I have them accessing the same record source...the Table called ConstrConstrType

What I want is for the user to choose the Code Combox (ConsId) and then the ConstraintType Combox
will update with the relevant description.

So if I choose
0BYC Using the ConsId Combox which uses the ConstrConstrType Table as its data source, using the ConstraintsCode as its value.
I want the
ConstraintType combox to update with the same record but with the description, i.e. ConstraintsType, which is the description for this record.

Thank you for you help and forthcoming help



Thank you,

Kind regards

Triacona

 

[PHV]

If the 2 combo have the same RowSource and BoundColumn, then in the AfterUpdate event procedure of ConsId:
Me!ConstraintType = Me!ConsId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If they both have the same control source, they should be updated automatically. Change one and the other should change.

Triacona,
If you can't figure this out, come back these properties from the two combo boxes:

[tt]Name:
Control Source:
Row Source:
Number of Columns:
Bound Column:
Column Widths:[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane and PH,

Thank you so much for all your help!!

PH your code worked brilliantly thank you so much!

If the 2 combo have the same RowSource and BoundColumn, then in the AfterUpdate event procedure of ConsId:

Me!ConstraintType = Me!ConsId

Have a star both of you!
[star]



Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

After extensive testing I have run into a problem...

It seems the command in the After Update updates the ConstraintType value with the ConsId value...

Previously I had the description populating the Constraints table in the , but now with the

Me!ConstraintType = Me!ConsId command

It now updates the ConstraintType with the ConsId Code in the Constraints Table.

Previously I had the folling on the Data tab
V1.0 (This version populated the ConstraintType in the Constraints table with the correct value i.e. the constraints value)
ConstraintType Combox Box
Control Source : ConstraintType
Row Source : ConstrConstrType
Row Source Type : Table/Query
Bound Column : 2
Limit to List : No
Allow Value List Edits : Yes
List Items Edit Form :
Inherit Value List : Yes
Show only Row Source Value : No
Input Mask :
Default Value :
Validation Rule :
Validation Text :
Enabled : Yes
Locked : No
Auto Expand : Yes
Smart Tags :

Event Tab All Blank

V1.05 (this version is inputting the ConsId into the ConstraintType rather than entering the ConstraintType (description)

ConstraintType Combox Box
Control Source : ConstraintType
Row Source : ConstrConstrType
Row Source Type : Table/Query
Bound Column : 1
Limit to List : No
Allow Value List Edits : Yes
List Items Edit Form :
Inherit Value List : Yes
Show only Row Source Value : No
Input Mask :
Default Value :
Validation Rule :
Validation Text :
Enabled : Yes
Locked : No
Auto Expand : Yes
Smart Tags :

Event tab all blank

I have also tried to use Bound Column 2, but this does not work, I still get the code in the Constraints Table and the code in the actual ConstraintType Combox.
If I use Bound Column 1, the Value on the Form in the Combox ContraintType is correct i.e. it has the ConstraintType (the constraint description instead of the code) BUT the Table Constraints has the value of the ConsId Code.

If I use Bound Column 2 I get the code value of ConsId in Both the ConsId Combox and the ConstraintType Combox, this is also true for the Constraints Table with both values being the code and no description, so ConsId has the code value and ConstaintType has the ConsId value.

I need the ConstraintType Value displayed on the form and stored in the table as the long description and not the code.

Any further help will be greatly appreciated!
Thank you
P.s upon previewing this post I noticed all my carefully placed spaces, it won't do tabs, dissapear, so my nicely formatted list of attributes looks terrible, sorry, any ideas?



Thank you,

Kind regards

Triacona

 

[dhookom]

I would never store the description in the related table. The Control Source should be the code/primary key field. I would expect your table relationships to be joined on the code/primary key field.

If you must store the title (IMO not a good idea) make sure the title column is the bound column in both combo boxes. They should have the same Control Source so updating either combo box will cascade the update to the other with no code.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Duane,
Thanks for your prompt reply

What if I want to run a query off the Constraints table...for a particular site...The user would have to know the code off by heart...I don't think that is practical as the list is 200 + records.
What I want is the Constaints Table ConsId is the Primary key and ConstraintType is the discriptive field.

The ConstrConstrType is a table containing the values for the dropdown box.

So the ConsId ComBox and the ConstraintType Combox both link to the ConstrConstrType table, the control source or table getting the data inputted into it is the Constraints table.

So I would like the code to be chosen, from the ConstrConstrType table ConstraintCode field and populated into the Constraints Table ConsId field
and then the ConstraintType to be chosen, from the ConstrConstrType table ConstraintType field, and in the Constraints table the ConstraintType field be populated with the discription from the ConstrConstrType tables ConstraintType field.

Thanks


Thank you,

Kind regards

Triacona

 

[dhookom]

I use autonumber primary keys in almost every table I create. Users never know the numbers that are stored in my tables and don't have to. All user interfaces show only the title/description value. This is basic relational table structures.

You only need to join your main table to the related ConstrConstrType table in queries.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear all,
Thank you for your help

Below is the ConstrConstrType table that the Drop down combox is run from. (so the values the users choose)
ConstraintsCode[tab][tab][tab]ConstraintsType
0BYC[tab][tab][tab][tab][tab][tab][tab][tab]0-1333m access by bicycle to nearest centre
0FT[tab][tab][tab][tab][tab][tab][tab][tab][tab]0-400m access by foot to nearest centre
0GP[tab][tab][tab][tab][tab][tab][tab][tab][tab]0-5min distance to nearest GP
0PSCH[tab][tab][tab][tab][tab][tab][tab][tab]0-5min distance to nearest primary school
0RS[tab][tab][tab][tab][tab][tab][tab][tab][tab]0-10min distance to nearest railway station
0SSCH[tab][tab][tab][tab][tab][tab][tab][tab]0-5min distance to nearest secondary school
0WTC[tab][tab][tab][tab][tab][tab][tab][tab]0-10min distance to Woking town centre

So ConstraintsCode value is used for the ConsId (control source)
The ConstraintsType is used for the valus dropdown combox for Constraint Type, ConstraintType is the Control source.

The Constraints table is structured as follows.

ID[tab]SiteId[tab]ConsId[tab]ConstraintType[tab]DateEntered[tab]FurtherInfo
29[tab]123123[tab]0BYC[tab][tab]0BYC
30[tab]123123[tab]0FT[tab][tab][tab]0FT
31[tab]123123[tab]0RS[tab][tab][tab]0RS

As you can see from the data output that the ConstraintType is getting filled in with the code too, which I do not want, otherwise I would just have kept the ConsId and have no type.
The form displayes the correct information, so the code and the ConstraintType in words.

In the VB code it has linked the 2, is there a way of making it store the ConstraintType instead of the ConstraintCode?
Otherwise I will have to delink the textboxes and let the user choose the code another way...by displaying both columns and the user will then correlate the ConstraintCode to the ConstraintType
Thank you for your forthcoming help



Thank you,

Kind regards

Triacona

 

[dhookom]

You can set the bound column of the combo box to whatever you want. I think it is almost always the wrong decision to store the Constraints type value ("0-1333m access by bicycle to nearest centre" or "0-10min distance to nearest railway station") in any table other than in the ConstrConstrType table.

PHV has provided the basic code to set the value of one combo box after updating another.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,
Thanks for your response
I have tried changing the bound columns, and the result is the same as described above, when the Constraints table has in both ConsId and ConstraintType the ConstraintCode value from the Table ConstrConstrType table.

The reason I want the actual values of the code in the Constraints table is as before so the queries created off this DB can be searched on this field...it is not a description but a corresponding value.
These are actual values attributed to each site.
There are many sites and each site has many constraints.

You mentioned that PHV's code could be manipulated...as above I want the actual value not the code in the ConstraintsType field, so how would I modify either the bound column or code to reflect this?
I have tried changing the bound columns as above and had little success, if I leave it at 1 the forum displayes the ConstraintType correctly (not the code) but if you look directly in the table it has just the code stored in the ConstraintType field. If I change the bound column to 2 it displayes the code in the forum and in the Constraints table in the ConstraintType field.

Thanks for all your help it is much appreciated

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,
I have tried

Me!ConstraintType = Me!ConsId.Column(2)

This does not provide the same functionality as before when you click on the dropdown of ConsId Combox and select the code and the ConstraintType Combox fills in.
It now just is static and I have to click on the ConstraintType combox and click on the value, the good news is that the Constraints Table has the correct ConstraintType value in...but obviously this non linking of comboxes is a backward step...

Any help will be greatly appreciated thanks

Thank you,

Kind regards

Triacona

 

[dhookom]

You should never look directly at the table. You query can include the ConstrConstrType table so the description is available for display and searching.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Originally, you had two combo boxes with different control sources. Duane had you change them to the same and use two different row sources so that they would both update the same ConstraintType field. When one changes the other has to change. As an alternative to that, not an addition, PHV suggested you use the AFTER UPDATE Event to assign one value to the other.

So PHV's code is what is filling in the description and this has ABSOLUTELY NOTHING to do what is displayed in the screen. Remove PHV's After update code, and then change a value on screen. You will see that the ConstraintsType on screen reflects the change in stored value ConsID and the ConstraintsType in the table has not changed. You can delete the ConstraintsType from the table that is the rowsource for the form as it creates repeated data.

As a side note, you are confused by the fact that Access data field and control syntax is the same. Before I ever use a control name someplace, I always change the name. Combo boxes I prefix with cbo so cboConsID would likely be one of them. txt is used for textboxes. If you change the names, the code should be more intuitive. This is a common naming convention... I forget it's name but you should be able to search for a complete list of controls.