In a question somewhat related to m

[RCSNC]

In a question somewhat related to my previous post...

In a table named schedule I have two combo boxes named divivionCombo and classtypeCombo
Here's my record source for divisionCombo:

SELECT [divisionID], [divisionName] FROM Division ORDER BY [divisionName]

and for classTypeCombo:

SELECT [classTypeID], [classTypeName] FROM classType WHERE [classType].[divisionID]=[Forms]![schedules]![divisionCombo];

Note: divisionID and classTypeID are hidden

Here's my problem:

In another table named attendance I need to create a combo box that combines info from each of these boxes as well as addition fields. I have created a combo box named scheduleCombo with the following code:

SELECT [scheduleID], [divisionCombo] & ", " & [classTypeCombo] & " " & [startDate] FROM schedule ORDER BY [startDate];

When this field is displayed it only shows the hidden fields of divisionID and classTypeID NOT the divisionName and classTypeName that I am expecting (startDate does appear correctly)

Finally, I can write a query to pull the info I want and when I run the query it produces the correct info, i.e. names not ids. However, if I change row source of scheduleCombo to the query I get the same results as above, i.e. ids not names.

What am I doing wrong?

Any help is appreciated! Thanks!

 

[LittleSmudge]

[divisionCombo] as you havew it in your last RowSource refers to the BOUND COLUMN in the combo NOT to the VISIBLE column in the combo. So the code is doing what you have told it to do.

Next challenge then is to 'tell it' to do something different .. ..

Columns other than the bound column in a combo can be refered to using a zero based index so you can replace [divisionCombo] with

[divisionCombo].Column(0) and get the same result

to refer to the second column in the combo's drop down list use

[divisionCombo].Column(1)

Modify [classTypeCombo] in a similar way.

I've often used this in VBCode. However, I've never had a need to use it in a SQL string itself. I'd be interested to know if it works in this contect.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[RCSNC]

I'm sorry for the delay in responding...


The column property is not available in Design view. However, at least I understand now why it's showing the ids.


Any other ideas?

 

[LittleSmudge]

I've just re-read your original post - you say
In a table named schedule I have two combo boxes

Well combo boxes have NO PLACE being in tables at all.
combo boxes are controls and appear on Forms.
( I'm assuming that you really do mean FORMS and your reference to tables is a typo. )








G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[RCSNC]

Sorry, I'm new to Access. I did mean table - but regardless the column property only works in macros and vb. I probably broke several other design rules but I got it to do basically want I want by changing the bound column to the second column.

 

[LittleSmudge]

So if you've got combo boxes in TABLES then - that means - arrrrrg You've got Lookup Fields in your tables !!!!

Oh BAD News.

Design Rule 1. ) Users NEVER get to see table - only allow users access to data via FORMS.

Therefore, Lookup Fields have no +ve value and simply serve to confuse during maintenance and update time.


Believe me RCSNC - there IS a better way.








G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.