Changing table defaults & form checkboxes 1

[Luongo1]

Hey everyone, sorry for the repeated questions here. I'm working on a fairly big project without any real experience in visual basic (or access) so it's been tough, but I've managed to make some good progress. I just have two more questions that I couldn't seem to find answers to anywhere else.

The first is how to change the default value for a field depending on a user's selection in a form. Right now I have a form where the user selects a shift number and supervisor before proceeding to the datasheet form. I'd like for this info to be automatically entered into each record submitted, in the fields 'Shift' and 'Supervisor'. However, these fields will not be displayed in the form, as I want to use them solely for sorting and printing reports. I figured the best way to do this would be to have the field's default values change once the form is submitted, but I'm not sure on how to do this. Any thoughts would be welocme.

My second question, which hopefully is a bit more simple, is how to construct a query based on checkbox selections from the user. I have a form setup where the user chooses a begin and end date, and then selects one or more ehifts from a series of checkboxes. I'm having no problem with the dates, but am unsure of how to proceed with the checkboxes since I figure the query would change based on which ones are selected.

Sorry for the long post here - any help on either of these issues would be great...

 

[TomCarnahan]

For your second question, are your checkbox values "mutually exclusive"?

Example:

Over age 18:

Yes o No o

where the answer can be either "yes" or "no" but not both.



--- Tom

 

[Luongo1]

Hi Tom,

No, they're not mutually exclusive. There are four checkboxes and the user can select any number of them (Shift 1, 2, 3, 4). Thanks...

 

[JerryKlmns]

Luongo1,

For the first q:
Create a table for all combinations and the default values you want to pair. Retrieve these values into variables (an array maybe) and use them to UPDATE the new records after they have been submited. Since they don't see them they wouldn't notice anything.

I wouldn't change the default value of a table on a multiuser enviroment for one user while onother one is using the same table!

For the second q:
Are these checkboxes filtering the same field?
An example would be very enlightening

 

[Luongo1]

Thanks for the reply Jerry. For the second question, the checkboxes are all filtering the same field in the same tbale. I have four boxes - Shift 1, Shift 2, Shift 3, and Shift 4. I would the user to be able to select one of these, all of them, or any combination, and have those records appear. Problem is I'm not sure how to construct the query, since those that aren't selected obviously wouldn't have any value. Would I need to use some sort of conditional statements, or could I have one query that would somehow deal with it all?

 

[JerryKlmns]

I think

SELECT yourTable.*
FROM yourTable
WHERE IIF(chkBox1=True,yourField='TheValue1',yourField=NULL)

UNION 

SELECT yourTable.*
FROM yourTable
WHERE IIF(chkBox2=True,yourField='TheValue2',yourField=NULL)

UNION 

SELECT yourTable.*
FROM yourTable
WHERE IIF(chkBox3=True,yourField='TheValue3',yourField=NULL)

UNION 

SELECT yourTable.*
FROM yourTable
WHERE IIF(chkBox4=True,yourField='TheValue4',yourField=NULL)

hopping that yourField is of Text data type and that does not accept null values. If it does accept nulls or it is numeric then use a non possible value instead of NULL in the WHERE clause.

 

[Luongo1]

Thanks Jerry, works great!

 

[Luongo1]

Could you perhaps specify a bit your answer to the first q? Right now I'm passing the variables through the OpenArgs function of the first form. How could I get these values in each new record created using this UPDATE function? Thanks, sorry for bugging ya again...

 

[Luongo1]

No worries, I've figured it out....

 

[JerryKlmns]

Luongo1

No bugging me at all. I just have low availability this period. But I 'm glad you managed to figure it out.

Thanx for the star.