Link to home
Start Free TrialLog in
Avatar of Michael Paravicini
Michael ParaviciniFlag for Chile

asked on

Combobox in a datasheet - different options on how to load data

I use a combobox in a datasheet and would like to achieve the following. If the underlying data element is <= 2 then we should load the combobox using a select statement such as:

Me.cmbExcursions.RowSource = "SELECT Code,Txt1 FROM Codes WHERE ID=600

However, if the underlying data element is 1 or 2 I would like to set the combobox equal to the comment field of the same data row. Is there any way to achieve this?
Thank you so much for any help.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can do what you want in a datasheet.  The trick is to add an unbound combobox and put a formula as the controlsource.  However, I need some clarification first:

I don't see any difference between the [dataelement]<=2 and the [dataelement]=1 or [dataelement]=2 since [dataelement]=1 is also [dataelement]<=2.  It's got to be one or the other.

Also, what do you mean by "set the combobox equal to the comment field"?  do you mean set the rowsource (what would the rowsource be), or set the .Value so the combobox displays the comment (no rowsource).

The solution depends on your answer....
Avatar of Michael Paravicini

ASKER

Hi Mark thank you for your comments and help. Let me provide you a bit more information. I have a datasheet with a combobox column which should either load the data from a table or from a field (ie other column of the same table). I have the following data elements:

excursions - numeric
comments - text

If the excursions field has a value <= 2 then I would like the combobox to be equal to the comments column of the same record, otherwise it should set the rowsource of the column excursions combobox to

Me.cmbExcursions.RowSource = "SELECT Code,Txt1 FROM Codes WHERE ID=600

Hope this clarifies the issue. Thank you so much for your help. Regards Michael
Note that if you use an expression as your rowsource for the combo, the data won't be saved (unless you use VBA to save it). If you use the textbox/combobox method, you can bind the Textbox to your table's underlying field and let Access handle things. Not saying one method is better than the other, just making you aware of this before you go down that road.
Mike:

I'm assuming that if the [Excursions] field is <=2 and the rowsource is populated, then you want the value in the combobox to be the value of the [Excusions] field, or something else?  Do you want the user to be able to change the value of the combobox in then case?  Is the combobox suppose to be updatable, or just show the droplist or the comment field?

Thx
o.k. one more thing.  You say you want the rowsource set to an SQL statement if the [Excursions] value is <=2 (any value displayed?  should the combobox be editable?), otherwise, you want the value in the combobox to show what's in the Comments column for that record, but any rowsource here?  Does the combobox need to be editable?

Please explain fully for each situation:
-Value displayed in combobox.
-Rowsource for combobox.
-Need to be able to edit or not edit combobox value.

Easy to do unless the combobox needs to be editable (value changed), otherwise a little more difficult.

I've only got part of the picture....
Thx
Thank you both of you for your help. Rg Michael
I've got a working, updatable model for the datasheet that does what you described, but without the details you haven't covered yet, you'll have to handle them, unless you want to post them now and I'll handle them.

I've included a couple of screenshots.
Dynamic-Datasheet-Combobox-11.PNG
Dynamic-Datasheet-Combobox-22.png