Help combining these Queries

[Djblois]

I use 11 queries for one form for the different choices that a user selects. I want to however try to combine them to 1 query if possible. Here are the 11 queries:

I have posted the form for people to look at the queries.

The issue is that I have 3 separate sets of checkboxes that affect the queries.

Set 1 - Has 2 checkboxes, user chooses if he wants to view Receiving appts or Shipping appts or both

- This one is easy to manipulate because Shipping and Receiving are two separate Yes/No fields in the database.


((Scheduled_Appts.Incoming)=[Forms]![frmScheduled_Appts]![cbViewRec]) AND (Scheduled_Appts.Outgoing)=[Forms]![frmScheduled_Appts]![cbViewShip])Set 2 - Has 1 checkbox, user chooses if they want to see Completed appts or not. (non-completed appts are always shown in the queries)

- This is determined by if the Date Completed Field is null or not. If it is null then it is not completed.
- This has caused me to double the amount of queries I have. I cannot figure out how to filter by a null field if it is Not checked but then do not filter if it is checked.


((Scheduled_Appts.Dep_Time) Is Null)Set 3 - Has 3 checkboxes, user chooses if they want to view Whse 1, Whse 2, Whse 3, or a combination of them

- This is determined by one field that tells what Whse the appt is out of.
- This is easy when it is only one Whse:


((Scheduled_Appts.WhseID)=getWhse()))
Function getWhse() As String

getWhse = GetSetting("WST", "General", "Whse")

End Function
If Me.cb01 Then SaveSetting "WST", "General", "Whse", 1- It is not easy when it is two or more Whses, I change the Query instead:


Private Sub ChooseView()

On Error GoTo err_handler

If (Not Me.cbViewRec And Not Me.cbViewShip) Or (Not Me.cb01 And Not Me.cb02 And Not Me.cbDemed) Then
enableDisableFields False
Else
On Error Resume Next
If Me.cb01 And Me.cb02 And Me.cbDemed Then
Me.cmdPrintReports.Enabled = False
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryAppts1&2&3"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompleted1&2&3"
End If
If DLookup("[AtOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True And _
DLookup("[02OrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True And _
DLookup("[DeOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
ApptEditingAbility False
ElseIf Me.cb01 And Me.cb02 Then
Me.cmdPrintReports.Enabled = False
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryAppts1&2"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompleted1&2"
End If
If DLookup("[AtOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True And _
DLookup("[02OrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
ApptEditingAbility False
ElseIf Me.cb01 And Me.cbDemed Then
Me.cmdPrintReports.Enabled = False
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryAppts1&3"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompleted1&3"
End If
If DLookup("[AtOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True And _
DLookup("[DeOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
ApptEditingAbility False
ElseIf Me.cb01 Then
If DLookup("[AtReports]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
Me.cmdPrintReports.Enabled = True
Else
Me.cmdPrintReports.Enabled = False
End If
SaveSetting "WST", "General", "Whse", 1
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryApptsOneWhse"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompletedOneWhse"
End If
If DLookup("[AtOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
If DLookup("[AtApptsEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
ApptEditingAbility True
Else
ApptEditingAbility False
End If
ElseIf Me.cb02 And Me.cbDemed Then
Me.cmdPrintReports.Enabled = False
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryAppts2&3"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompleted2&3"
End If
If DLookup("[02OrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True And _
DLookup("[DeOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
ApptEditingAbility False
ElseIf Me.cb02 Then
If DLookup("[02Reports]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
Me.cmdPrintReports.Enabled = True
Else
Me.cmdPrintReports.Enabled = False
End If
SaveSetting "WST", "General", "Whse", 2
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryApptsOneWhse"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompletedOneWhse"
End If
If DLookup("[02OrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
If DLookup("[02ApptsEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
ApptEditingAbility True
Else
ApptEditingAbility False
End If
ElseIf Me.cbDemed Then
If DLookup("[DeReports]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
Me.cmdPrintReports.Enabled = True
Else
Me.cmdPrintReports.Enabled = False
End If
SaveSetting "WST", "General", "Whse", 3
If Me.cbCompleted Then
Form_SubfrmAppts.RecordSource = "qryApptsOneWhse"
Else
Form_SubfrmAppts.RecordSource = "qryNotCompletedOneWhse"
End If
If DLookup("[DeOrdersEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
OrderEditingAbility True
Else
OrderEditingAbility False
End If
If DLookup("[DeApptsEdit]", "tblRoles", "[Roles] = '" & strGroupPolicy & "'") = True Then
ApptEditingAbility True
Else
ApptEditingAbility False
End If
Else
enableDisableFields False
Me.cmdPrintReports.Enabled = False
Exit Sub
End If
enableDisableFields True
End If

If Form_SubfrmAppts.AllowEdits Then
Me.cbInputTime.Visible = True
Else
Me.cbInputTime.Visible = False
End If
Exit Sub
End Sub

This is adding a lot of unnecessary code and Queries. Please help me reduce this. I know this is a big job and anyone that can help me fix this - I would be willing to pay them through paypal for their time. My goal is to get it down to 1 query if possible if not as few as possible.

[hansup]

Before you upload a database for us to look at, you should open it yourself and verify it shows us what you want us to see.

There are no tables in that database. It is possible to analyze queries without the tables they rely on, but it's an unreasonable amount of effort for this case.

The form is challenging to diagnose without seeing the data it should include. It also complains about a missing subform.

If you decide to upload a replacement database, make sure it works first. Also run Debug->Compile and fix anything the compiler complains about.

Finally I think it would be in your interest to use MDB instead of ACCDB format for the sample database. ACCDB limits your pool of helpers to only those who have Access 2007 or 2010. A lot of folks haven't upgraded to that level yet. Heck I even see a fair number of folks still use Access 97.

[Djblois]

hansup, thank you for your reply but I did open it up - However, this is part of a big Access Application that I have written. It is tied to too many areas and the tables have too much data. I have tried to get rid of all the compile errors in order to get it to work with just the single form and related subforms but it is a prodigious task. This is probably the best I can do that is why I typed out a really long post showing all the code and queries with explanations. Also, I am willing to pay anyone a good amount of money for their time because I understand the insurmountable task.

I may be able to upload it with tables with less in them and maybe remove ALL the other code but I do not know if it will work. I will try it out and see.

[hansup]

Let me try coming at this from a different direction, Daniel.

Go to the VB editor, and select Tools->References. In the references dialog, verify none of the checked references are identified as Missing or Broken.

Assuming all references OK, choose Debug->Compile. Fix any errors the compiler complains about. (If Compile option is greyed out, you can make any trivial edit to a code module to enable the Compile menu option.)

Those are fundamental VBA good hygiene practices. Also in that category you must include Option Explicit in the Declarations section of each and every code module.

Perhaps you routinely do those things with your application. However, the copy you gave us included code which referenced an ADODB object, yet the project didn't include a reference for ActiveX Data Objects.

So here is what I propose in addition to those basic hygiene practices.

Make a copy of your database.

In that copy, remove every form which doesn't apply to the problem at hand. But make sure any subforms used by the problem form are kept.

Remove all queries except those used directly or indirectly by the problem form, its subforms and other controls.

Remove all tables which are not used by the queries, form, or subforms.

Ditto for reports, code modules, macros, etc.

Verify the form works as you expect. Do Debug->Compile again. Resolve errors, if any.

Then go back and remove all but a few representative rows from the tables. If the data is confidential, substitute dummy values.

Verify the form works as you expect. Resolve errors, if any.

Finally do a Compact&Repair and create a Zip of the database.

I would also encourage you again to consider converting the database from ACCDB to MDB format. However, it seems you may be trying to work with SharePoint, so perhaps you actually need ACCDB-specific features.

So are you saying that is impossible for you to do? Or that you don't want to put that much time into it? It should take less than an hour ... unless your application has too many other problems you haven't mentioned. And if that's the case, it would be lunacy not to fix those problems before further development.

"Willing to pay" is fine. But if you can't make this easier on your helper, you'll have to pay much more. Also you might consider disclosing your budget allowance for these tasks.