Form Control Values Display As different from source values

[svrich]

Ok. New to the site and haven't found the answer I need.



I need to build a drop down list to display 2 values (Yes, No). Simple right? Not so much. The problem I keep having is that the drop down box values only return data that exactly matches values in that field.

I need the "No" to return one set of data (Basically all my NULL values) and the "Yes" to return all other sets (Populated). I know this can be done, somehow someway somewhere. Just not sure how.

Anyone have any suggestions or dealt with similar issues? Thanks.

[John_G]

Hi -

Because your dropdown is to show data choices (Yes / No) that are not part of the underlying table, the dropdown control cannot be bound to a field in the table. What you need to do is take some action after making a selection in the dropdown.

What is is that you want to do after making a Yes/No choice, i.e. where is it that you want the "returned" data to go?

Can you provide more detail, please?

John

[svrich]

John -

Thats just it. Im not sure what steps I need to build to get to my end-state but I know its possible.

I have Column "D". It contains several different values (Say 1-10) as well as <Null>values (Empty fields). I want my "No" to return only <Null> values and my "Yes" to return all values, including <Null>.

How do I achieve this goal? Is it something simple? Or am I approaching a point where I would need a second query? Or do I need to code all this in the background?

[John_G]

Hi -

Once you have selected Yes or No, what is it you need to do? Do you want to filter which records you see on the form based on the contents of Column D, or is it something else?

In any case, the solution is probably quite simple, and should require only a bit of VB code related to the Yes/No dropdown:

If Yes is selected, then
..
.. Do something
..
Else
..
.. Do something else
..
Endif

Can you clarify what you mean by "returning the values", and what "do something" above should mean?

John


John

[svrich]

Sorry. I'm horrible at describing details. The form executes a built-in macro that opens a query that filters based on contents of my form's various fields. I have control for columns A-C that are all working fine. Its that column D is build differnt. in D the "Yes" result would return all records where criteria in other columns was met while a "No" result would exclude any records with a value (1-10 in this instance).

The VBA code is probably my only option (and I don't use VBA bc I never learned it) I imagine it would look something like this though.

If No is selected, then
..
.. Criteria: Not Like "*"
..
Else
..
.. Return all..
Endif

[John_G]

This is untested (!), and I don't use macros, but try this:

Change the Yes / No dropdown on your form to a checkbox, which if checked will return all records let's call it ReturnAll.

Then in the query that the macro opens include this in the criteria for column D:

forms!myform!ReturnAll OR Column_D is Null replacing myform with your actual form name.

(The criteria for your other columns should look something like that.)

Do you see how that works? If the form field ReturnAll is checked, then forms!myform!ReturnAll is always true, so the criteria for Column D is always true.

If the box on the form is not checked, then forms!myform!ReturnAll is always false, so in order for the criteria for ColumnD to be true, ColumnD must be Null.

Clear as mud?

As I say, I have not seen your query, so all of this might be wrong. Let us know how you make out.

John

[svrich]

I see how it works. But I'm having trouble figuring out how to tell the check box that it is bound Coulmn_D. Do I need to code the box in VBA?

[svrich]

I think the problem stems from Access not allowing a checkbox to function as a criteria for a Text field.

[John_G]

Hi -

The checkbox is not bound to Column D - it is not bound to anything, since it is not getting data from the table.

Actually, if I read your description correctly, the form is being used only to set criteria for the query called by the macro, and not to update any table data. If that is the case, then NONE of the form controls should be bound to a table field, because if they are, as soon as you make a seletion in your form controls (dropdowns?), you have changed a table record.

Can you explain further, please?

John

[svrich]

You're completely right John. None of my fields are bound. They are all unbound bc they only apply criteria to my query, they don't have a control source, just row source. (Yes, I'm doing a QBF, probably where I went wrong in the first place...) I would like my check box to return a true/false criteria to my query. Maybe thats something I have to do in VBA on th query itself?

[John_G]

Hi -

No, you don't need much else. A checkbox value is True if checked, False otherwise. Your query can refer to the checkbox control just as it does any other control. The expression forms!formname![Checkboxname] will be true if the box is checked on the form. There is no harm done if you want to use forms!formname![Checkboxname] = True for clarity.

John

[svrich]

This doesn't seem to have any effect on the query. I tried changing the query criteria to "Not like "*" WHERE [Forms]![Formname]![Checkbox] = False" however Access says that it contains invalid syntax and highlihts "WHERE" as "an operand without an operator"

Ideas?

[John_G]

Queries don't like WHERE in the criteria most of the time. Try something like this:

forms!myform!ReturnAll = True OR Column_D is Null

Replace myform with the actual name of your QBF form, and ReturnAll with the name of the checkbox control.

John

[svrich]

John,

I tried the criteria you provided but the query seems to ignore it. Not sure what to do. This shouldn't be this difficult. I know Microsoft is stupid at times, but even they aren't this bad (99% of the time anyways).

"If checkbox = True then return all records

else
return all records not like "*" "

Shouldn't be more complicated than that, right?

[John_G]

Hi -

What are the properties of Column_D? Specifically, what is the data type, the default value (if any), and what are the values for Required and Allow Zero Length?

If you are using Like "*", then " " (a blank) and "" (zero-length string) are both like "*". It can also depend on how you populated Column_D - if it was imported from Excel, you don't get Nulls.

HTH

John