Difficulty Using Form with Checkboxes as Query Interface

[gyli84]

I am trying to create a form where controls on it can be used as the criteria for a query. I have a table called computers which also has certain software titles on it of yes/no data type to indicate whether that computer has that software installed on it. The computer table is linked to a staff table by a User ID and I have created a query that shows the staff details I want as well as having every software title listed in it with their criteria set to corresponding checkboxes on a form I have created called "Software Query Form". This unbound form I have created has every software title as a checkbox and it has a button that runs the Software Query I set up. Ideally the user should click on the software titles on the form, press the cmdRunSoftwareQuery button and the query would return the User ID and user details of the person who had that software. This does not work though and I don't know why. I have tried running the query entering the criteria rather than using the form as an interface and that works. What could I do to make it work?

Also, in this query, seeing as I have set criteria for every software title as the corresponding control on a form, if I say only select "Acrobat" on the form would it return records where the User JUST had acrobat (would not selecting anything in the other checkboxes make the query search for null values in the other fields) or any records that contained Acrobat. I need the 2nd option otherwise the form is useless.

Thanks

 

[v.khawaja]

Could you show us the commands behind to cmdRunSoftwareQuery button? (One method I’ve used in the pass is that when the above button is clicked, a single temporary table is created containing all info [with no filering] i.e. all users with all apps. The query or report is then run against this temp table [with appropriate filtering from the form]. The advantage of this is that referential integrity is not used and all info for the report/ query comes from one source).
Please show sql behind the form…….

 

[gyli84]

Private Sub cmdSoftwareQuery_Click()
On Error GoTo Err_cmdSoftwareQuery_Click

Dim stDocName As String

stDocName = "Software Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdSoftwareQuery_Click:
Exit Sub

Err_cmdSoftwareQuery_Click:
MsgBox Err.Description
Resume Exit_cmdSoftwareQuery_Click

End Sub

and the SQL behind cmdSoftwareQuery is:

SELECT Staff.[User ID], Staff.Name, Staff.Surname, Staff.Department, Staff.Title
FROM Staff INNER JOIN Computers ON Staff.[User ID] = Computers.[User ID]
WHERE (((Computers.OS)=[Forms]![Software Query Form]![OS1]) AND ((Computers.Acrobat)=[Forms]![Software Query Form]![Acrobat]) AND ((Computers.AI)=[Forms]![Software Query Form]![AI]) AND ((Computers.OAG)=[Forms]![Software Query Form]![OAG]) AND ((Computers.[Office 2K])=[Forms]![Software Query Form]![Office 2K]) AND ((Computers.Photoshop)=[Forms]![Software Query Form]![Photoshop]) AND ((Computers.PKZip)=[Forms]![Software Query Form]![PKZip]) AND ((Computers.RichWin)=[Forms]![Software Query Form]![RichWin]) AND ((Computers.WinFax)=[Forms]![Software Query Form]![WinFax]));

I have a OS listbox and checkboxes for all other software. The form is unbound.

 

[gyli84]

If someone can tell me how to enter the criteria so that it will search for records where a certain checkbox field is True Or False then i can make this form work. I Know that when you create a query you can enter 0 Or -1 to do this or True Or False, but if I create a query which reads a textbox containing the text 0 Or -1 as the criteria then the query always fails. Something about a type mismatch i think.

 

[KevinM]

Your software table has been set up wrong, and this is why you are struggling to do a simple query.

Your software titles should be data, in one column and NOT several fields.
What do you do when you want to add or delete titles....add new fields to the table???

Treat it like your Computer table.

Create a third table and link this to your computer table with a one to many relationship.

You did it correctly with your Staff and Computer table, but I can't understand why you didn't apply the same logic with Computers and Software.

Also, it would be a go idea to create a LookUp table of all available Software to your company. You then use a combo box in the ComputersSoftware table to select from this Lookup table.

HTH


[This message has been edited by KevinM (edited 08-24-2001).]