Change in form design based on check box criteria in query

[RexesOperator]

I have four tables: Company->Client->Transaction->Site. I also have a query called qryAddNewTransaction based on the four tables.

When I use the form wizard to create the forms/subforms, based on the query, everything works as expected. However Transactions are based on two types (a lot of information is common to both, so one table). I only want certain information for one or the other type. I have a check box in the Transaction table to distinguish between the two.

If I run the form wizard with the checkbox criteria empty, the forms/subforms are fine. However, if I use 0 or -1 in the criteria, no subforms are created. The data is displayed as one block of information.

One option I have is to use the visible true/false property, so I do have a work around (that I will probably use).

But does anyone know why having a criteria entered in the checkbox field changes the way the forms wizard works?

I'm using A2K

 

[RexesOperator]

I'm still wondering about this. Does anyone know why having a value in the criteria of a checkbox field of a query based on multiple tables change the way the forms wizard presents the data?

 

[unclejoe]

Hi,

Not sure what you want to do. My guess is that you want to change the form's Recordsource. That's the easiest solution.

Here's what i think.

Create or copy "qryaddNewTransaction". With the new query, add the WHERE condition.

In the checkbox on click event,change the RecordSource to the new query.

If Me.CheckBox = -1 Then
Me.RecordSource = YourNewQueryNameHere
Else
Me.RecordSource = qryaddNewTransaction
End If

snip

I have four tables: Company->Client->Transaction->Site. I also have a query called qryAddNewTransaction based on the four tables.
One option I have is to use the visible true/false property, so I do have a work around (that I will probably use).
But does anyone know why having a criteria entered in the checkbox field changes the way the forms wizard works?
I'm using A2K

 

[RexesOperator]

I guess I should have put this in the general section.

My question was why does the form design change (from form and subforms to form only) when I have a value entered in the checkbox field?

Thanks for your answer - I will try it out in the morning.

 

[unclejoe]

Hi,

It’s very unlikely that the design change due to a checkbox, unless you have a function that changes the look and the form’s recordsource. The changes you see is most likely the recordsource SQL syntax that will determine the data retrieved from the tables you have selected.

I believe you have attempted to use the checkbox to filter records but was not 100% successful.

The checkbox must not be bound to any Field/column of a table. There are many combobox search form here that you can try out. Combobox can select many criteria but not checkbox. Checkbox have only two conditions, which is True or False (-1 and 0). The easy method I gave you should do.

If the named query you used include a criteria
“[Forms]![YourformName]![Checkbox]”
The query will attempt to filter the record base on the form’s control and if the checkbox is bound to a field/column of a table, it depend on the current record, in your case “Transaction”. If it’s 0(False), the recordsource will filter all records base on the current record’s field called “Transaction”. Same as for –1(True). When you click on the bound checkbox, not only will “Transaction” change, it will depend on the “current records” checkbox condition (Transaction) on that current filtered records. This I believe might have confused you.

As you have not explained how your design to works or show the query syntax, this is my guess on the checkbox filtering.

I guess I should have put this in the general section.
My question was why does the form design change (from form and subforms to form only) when I have a value entered in the checkbox field?
Thanks for your answer - I will try it out in the morning.

 

[RexesOperator]

Hi,

It’s very unlikely that the design change due to a checkbox, unless you have a function that changes the look and the form’s recordsource. The changes you see is most likely the recordsource SQL syntax that will determine the data retrieved from the tables you have selected.

I believe you have attempted to use the checkbox to filter records but was not 100% successful.

When I look at the query in spreadsheet view the value of the checkbox is correct and it shows only those records I want to see. The change occurs only in form view. You are correct in that I am attempting to use the checkbox as a filter in this case. I have no functions that change either the form's appearance or the recordsource.

The checkbox must not be bound to any Field/column of a table. There are many combobox search form here that you can try out. Combobox can select many criteria but not checkbox. Checkbox have only two conditions, which is True or False (-1 and 0). The easy method I gave you should do.

I haven't had time to try your method out yet - it's been a little chaotic here the last couple of days. The reason I am using the checkbox rather than a combobox is that I only want one of two conditions - either/or.

If the named query you used include a criteria
“[Forms]![YourformName]![Checkbox]”
The query will attempt to filter the record base on the form’s control and if the checkbox is bound to a field/column of a table, it depend on the current record, in your case “Transaction”. If it’s 0(False), the recordsource will filter all records base on the current record’s field called “Transaction”. Same as for –1(True). When you click on the bound checkbox, not only will “Transaction” change, it will depend on the “current records” checkbox condition (Transaction) on that current filtered records. This I believe might have confused you.

As you have not explained how your design to works or show the query syntax, this is my guess on the checkbox filtering.

The checkbox is a field in the Transactions table, not a filter on a form or a call from the query to the form. I think your method will be what I am looking for. Thanks for looking at this.

I'm still taking baby steps in Access, but its intrecacies blow me away. I don't know how you guys remember all these details. Although my ignorance frustrates me at times, this project that I am working on is the most fun I've had in years.