Selecting only (!) one of many datasets in a table

[FSPH]

Hi there,

I have a table ("tblProject_DataSet") with 3 columns. I am wondering how to
allow the user to only select one of the three possible datasets.

Column 1: "DataSet" - number - 1, 2, 3

Column 2: "AreaName" - text - Area A, Area B, Area C

Column 3: "Run" - boolean variable - yes/no.

IDEALLY, I WOULD ALLOW THE USER TO ONLY SELECT ONE OF THE THREE DATASETS. SO
THE USE OF BOOLEAN VARIABLE IN COLUMN 3 MAY NOT BE THE RIGHT APPROACH.

What's an alternative approach?

I know that I could use a variable called DataSet (a number) for which the
user
may select any one of the three numbers (1, 2, or 3). But then the danger is
that the user gets confused about the right numbers for the appropriate
areas.
That's why I would like to user to be able to select, for example, "DataSet
3", "Area C". How can I do this?

Thanks for your help.

 

[Jeanette Cunningham]

Hi,
is the user selecting from a form using a combobox?
If there is a combo box for DataSet where the user can choose one dataset
and a combo box for Area where the user can choose one area, wouldn't your
problem be solved?
Then you can use multiple criteria to either open a form, open a report or
filter the form.


Jeanette Cunningham

 

[Dale Fye]

Not sure that either Jeannette or I fully understand what you are doing, but
I'll give it a stab.

You have a table that contains three columns(DataSet, AreaName, and Run)?

You want to get some input from the user that ultimately needs to correspond
to the data in your DataSet column, but you want them to actually enter the
value that corresponds to the [AreaName] column. Is that accurate? If so,
you need a multi-column combo box.

1. Add a combo box to your form
2. Right click the combo box and display the properties
3. Name the form "cbo_DataSet" in the Other tab
4. Go to the Data tab and in the RowSource property, click the dropdown
button and select the table that has your DataSet and AreaName fields. Then
click on the command button with the "..." label next to the dropdown button.
This will open up a query window. Select the DataSet and AreaName fields,
and set the DataSet field to sort Ascending. Finally save the query
(something like qry_formName_cboDataSet).
5. If this field is bound to some field in the forms Record Source, then
set the Control Source property.
6. On the format tab, set the Column Count to 2, and the Column Widths to
0"; 1.5"

Now when you open your form, the user will be able to select from among the
AreaNames, but by using the combo boxes value property, you will be able to
determine which "DataSet" the user selected. To verify this, you might want
to click on the Event tab, and in the combo boxes "AfterUpdate" event select
"Event Procedure" and then click on the "..." button. This will popup the
Visual Basic for Applications code editor and should take you to a subroutine
that reads:

Private Sub cbo_DataSet_AfterUpdate()
End Sub

Insert a line between these two lines that reads:

Private Sub cbo_DataSet_AfterUpdate()
msgbox "combo box cbo_DataSet = " & me.cbo_DataSet
End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[FSPH]

Hello Jeanette and Dale,

thanks very much for your recommendation to use a combo box on a form.

Dale, I very much appreciate your detailed suggestion of how to put such a
combo box on a form and how to get the whole thing working.

However, I was looking for a much simpler solution. I don't use any forms
for the entry into my Access tables. I only use the original tables in
Access. So the user gets a database with lots of tables. Each table contains
quite a bit of information.

Anyway, I have in this table the following three rows and three columns:

DataSet AreaName Run
1 Vancouver Island No
2 Olympic Peninsula Yes
3 Cascade Mountains No

I want to give the user (and myself) only the chance to pick ONE DataSet by
saying Yes in the boolean variable Run only in one row. How can I achieve
this within Access? I guess there should be an error message if there are two
rows that contain a Yes in the boolean variable Run. Is there a better
alternative?

I hope these additional comments clarify my intentions.

Thank you for your continued help.

Not sure that either Jeannette or I fully understand what you are doing, but
I'll give it a stab.

You have a table that contains three columns(DataSet, AreaName, and Run)?

You want to get some input from the user that ultimately needs to correspond
to the data in your DataSet column, but you want them to actually enter the
value that corresponds to the [AreaName] column. Is that accurate? If so,
you need a multi-column combo box.

1. Add a combo box to your form
2. Right click the combo box and display the properties
3. Name the form "cbo_DataSet" in the Other tab
4. Go to the Data tab and in the RowSource property, click the dropdown
button and select the table that has your DataSet and AreaName fields. Then
click on the command button with the "..." label next to the dropdown button.
This will open up a query window. Select the DataSet and AreaName fields,
and set the DataSet field to sort Ascending. Finally save the query
(something like qry_formName_cboDataSet).
5. If this field is bound to some field in the forms Record Source, then
set the Control Source property.
6. On the format tab, set the Column Count to 2, and the Column Widths to
0"; 1.5"

Now when you open your form, the user will be able to select from among the
AreaNames, but by using the combo boxes value property, you will be able to
determine which "DataSet" the user selected. To verify this, you might want
to click on the Event tab, and in the combo boxes "AfterUpdate" event select
"Event Procedure" and then click on the "..." button. This will popup the
Visual Basic for Applications code editor and should take you to a subroutine
that reads:

Private Sub cbo_DataSet_AfterUpdate()
End Sub

Insert a line between these two lines that reads:

Private Sub cbo_DataSet_AfterUpdate()
msgbox "combo box cbo_DataSet = " & me.cbo_DataSet
End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi there,

I have a table ("tblProject_DataSet") with 3 columns. I am wondering how to
allow the user to only select one of the three possible datasets.

Column 1: "DataSet" - number - 1, 2, 3

Column 2: "AreaName" - text - Area A, Area B, Area C

Column 3: "Run" - boolean variable - yes/no.

IDEALLY, I WOULD ALLOW THE USER TO ONLY SELECT ONE OF THE THREE DATASETS. SO
THE USE OF BOOLEAN VARIABLE IN COLUMN 3 MAY NOT BE THE RIGHT APPROACH.

What's an alternative approach?

I know that I could use a variable called DataSet (a number) for which the
user
may select any one of the three numbers (1, 2, or 3). But then the danger is
that the user gets confused about the right numbers for the appropriate
areas.
That's why I would like to user to be able to select, for example, "DataSet
3", "Area C". How can I do this?

Thanks for your help.

 

[Jeanette Cunningham]

Hi,
if the user has the tables open, I don't see how you can stop them from
selecting any dataset they want.
Do you send the user the tables, then the user opens the tables and makes a
selection?
What do they do with the dataset that they select?
A bit more info on what you are trying to do could help us to help you
achieve your goal.

Jeanette Cunningham

 

[John W. Vinson]

However, I was looking for a much simpler solution. I don't use any forms
for the entry into my Access tables. I only use the original tables in
Access. So the user gets a database with lots of tables. Each table contains
quite a bit of information.

Than you're misusing Access... drastically.

Would you use an Excel spreadsheet which requires the user to do calculations
on a hand calculator and enter them into the cells in the spreadsheet? That's
about equivalent.

Anyway, I have in this table the following three rows and three columns:

DataSet AreaName Run
1 Vancouver Island No
2 Olympic Peninsula Yes
3 Cascade Mountains No

I want to give the user (and myself) only the chance to pick ONE DataSet by
saying Yes in the boolean variable Run only in one row. How can I achieve
this within Access? I guess there should be an error message if there are two
rows that contain a Yes in the boolean variable Run. Is there a better
alternative?

Yes. Use Access as it is designed, rather than making a Procrustean bed of
your preconceptions. Use Tables *TO STORE DATA*, and only to store data; use
Forms and Queries to subset data and interact with it.

This is trivially easy with a form.

It is all but impossible using table datasheets to interact with the data,
since forms have programmable actions and tables don't. It's just that simple!

John W. Vinson [MVP]

 

[FSPH]

Hello Jeanette,

yes, the user (and myself) can open the table and select any dataset they
want.

However, if the user wanted to Run DataSet 3, it's important that both
DataSet 1 and 2 are NOT selected (i.e., ticked off in boolean variable Run).
Otherwise my model would first run these DataSets (1 and/or 2) before finally
running DataSet 3 (as wished by the user).

So the user should get a clear indication that he/she can only tick off ONE
DataSet in this table....... Ideally the table should not be "savable" if
more than one DataSet is ticked off. Perhaps there is an elegant solution to
this......

I hope this additional information helps in clarifying my intentions.

Thank you for your continued interest in this question.

 

[Jeanette Cunningham]

I am intrigued to find out more about how a user runs your model.
Couldn't you just send them an extra table with a single row in it with the
name and details of the dataset to run?
How do you know which dataset the user wishes to run?

Have you tried building a simple form where a user could choose a dataset
from a dropdown box?
A simple form like this would be fairly easy to build, is there a very good
reason why you wouldn't use a form?
What does the model do with the dataset?


Jeanette Cunningham

 

[FSPH]

Hello John,

thanks for your input. Yes, I realize that I will have to use forms in the
long-run to let the user interact with my model that way.

 

[FSPH]

Hello Jeanette,

thanks for your input. Yes, I realize that I will have to use forms in the
long-run to let the user interact with my model that way. So far, only I have
used the model, but if other users are selecting model settings, I may use
forms.

Again, thanks for your interest and for helping me .........