Solved: VBA help in Access 2007

[Creativemfs]

Here is some code i am trying to write for checkboxes to add text to a string

Option Compare Database

Private Sub btnProceed_Click()
Dim strWhere As String

If Me.Check10 = "Yes" Then
strWhere = strWhere + "OR-1"
End If
If Me.Check12 = "Yes" Then
strWhere = strWhere + "WA-1"
End If
If Me.Check14 = "Yes" Then
strWhere = strWhere + "WY-1"
End If

MsgBox (strWhere)

End Sub


The message box is empty, what am i doing wrong that the text in parenthesis is not getting entered into the string.
I have a form that has 3 check boxes and a button

Thanks,
Mike

 

[Creativemfs]

I got the check box code to work, now i just need to get my openreport to work

 

[Creativemfs]

i needed to change "yes" to True, that was easy, my next problem is that
I can not get a DoCmd.OpenReport to work
I have a report based on a query. I need the wherecondition to be something like this
[WareID]= strOR strWA strWY
I know there needs to be some "AND"s in there somewhere.
I have the WareID in the query and as a field on the report.

Any help would be great, thank you.

p.s. I was wondering if I should not base the report on a query but build it from scratch, would that make things easier?

And my new code is

Private Sub btnProceed_Click()

Dim strOR As String
Dim strWA As String
Dim strWY As String

If Me!Check10 = True Then
strOR = "OR-1"
Else
strOR = ""
End If
If Me!Check12 = True Then
strWA = "WA-1"
Else
strWA = ""
End If
If Me!Check14 = True Then
strWY = "WY-1"
Else
strWY = ""
End If

MsgBox (strOR & strWA & strWY)


End Sub

I just have the msgbox there to show that the strings work

 

[OBP]

No it would not be easier to build the report from scratch. It might be easier to build the Query from scratch using the QueryDef function.
However I need to understand the "Options" first, do you want to list just one of the Options, any 2 of the options or even all 3 options?

 

[Creativemfs]

The check boxes represent warehouse IDs (the primary key for tblWarehouse) so OR-1 is the ID for oregon warehouse, WA-1 is the ID for Washington warehouse WY-1 is the ID for Wyoming. Under the check boxes is the button "Proceed" that the above code is written for.

The query is based off two tables, the employee table and the warehouse table. The fields are empFirst, EmpLast from the employee table, and WareID Address, city, state, zip from the Warehouse table.

Here is the query sql:

SELECT tblEmployee.EmpLast, tblEmployee.EmpFirst, tblWarehouse.WareID, tblWarehouse.Address, tblWarehouse.City, tblWarehouse.State, tblWarehouse.Zip
FROM tblWarehouse INNER JOIN tblEmployee ON tblWarehouse.WareID = tblEmployee.WareID
WHERE (((tblEmployee.PositionID)='2'))
ORDER BY tblEmployee.WareID;

PositionID is just there to select the managers.

 

[OBP]

So do you want to filter the query using just one of the warehouse IDs?

 

[Creativemfs]

I want to filter it based on one, two, or all three of the criteria

 

[Creativemfs]

the report is for mailing labels, so i want to be able to print any or all of the selected three. I know there are other ways to do this, but I am practicing using check boxes.

 

[OBP]

Then the best method is the QueryDef method.
The SQL string is built up depending on which Check boxes you have checked and looks like this, which is much more complicated than yours, but should get the idea from it.
Note the ".QueryDefs.Delete "NewQueryDef"" line of code it deletes the current query, so if you haven't got the query it will cause an error.

Dim sql As String, qdfNew As Object, sSELECT As String, sFROM As String, sWHERE As String
On Error GoTo finderror
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

sSELECT = "CLINICDATE.* "

sFROM = "CLINICDATE "
sWHERE = "not isnull(ClinicID)"
If Not IsNull(Me.CLINIC) Then sWHERE = sWHERE & " AND CLINIC like '*' & '" & Me.CLINIC & "' " & " & '*' "
If Not IsNull(Me.Box) Then sWHERE = sWHERE & " AND BOX like '*' & '" & Me.Box & "' " & " & '*' "
If Not IsNull(Me.[Patient Number]) Then sWHERE = sWHERE & " AND [Patient Number] like '*' & '" & Me.[Patient Number] & "' " & " & '*' "
If Not IsNull(Me.Surname) Then sWHERE = sWHERE & " AND Surname like '*' & '" & Me.Surname & "' " & " & '*' "
If Not IsNull(Me.Forename) Then sWHERE = sWHERE & " AND Forename like '*' & '" & Me.Forename & "' " & " & '*' "
If Not IsNull(Me.[Traced to]) Then sWHERE = sWHERE & " AND [Traced to] like '*' & '" & Me.[Traced to] & "' " & " & '*' "
If Not IsNull(Me.Comments) Then sWHERE = sWHERE & " AND Comments like '*' & '" & Me.Comments & "' " & " & '*' "
If Not IsNull(Me.Notes) Then sWHERE = sWHERE & " AND Notes like '*' & '" & Me.Notes & "' " & " & '*' "
If Not IsNull(Me.[Clinic Date]) Then sWHERE = sWHERE & " AND [CLINIC DATE] = #" & Me.[Clinic Date] & "# "
If Not IsNull(Me.[Date Added]) Then sWHERE = sWHERE & " AND Left([Date Added], 10) = #" & Me.[Date Added] & " # "
If Me.[Clinic Cancelled?] = -1 Then sWHERE = sWHERE & " AND [Clinic Cancelled?] = " & Me.[Clinic Cancelled?]
If Me.[Else-body's Clinic?] = -1 Then sWHERE = sWHERE & " And [Else-body's Clinic?] = " & Me.[Else-body's Clinic?]
If Me.[None missing] = -1 Then sWHERE = sWHERE & " And [None missing] = " & Me.[None missing]
If Me.[E-mail?] = -1 Then sWHERE = sWHERE & " And [E-mail?] = " & Me.[E-mail?]
If Me.Check34 = -1 Then sWHERE = sWHERE & " And [Requested?] = " & Me.Check34
If Me.[Recieved?] = -1 Then sWHERE = sWHERE & " And [Recieved?] = " & Me.[Recieved?]
If Me.[Missing Completely?] = -1 Then sWHERE = sWHERE & " And [Missing Completely?] = " & Me.[Missing Completely?]
If Me.[Noted?] = -1 Then sWHERE = sWHERE & " And [Noted?] = " & Me.[Noted?]
If Me.[Cancelled?] = -1 Then sWHERE = sWHERE & " And [Cancelled?] = " & Me.[Cancelled?]
If Me.[In Clinic Already?] = -1 Then sWHERE = sWHERE & " And [In Clinic Already?] = " & Me.[In Clinic Already?]
If Me.[Inpatient?] = -1 Then sWHERE = sWHERE & " And [Inpatient?] = " & Me.[Inpatient?]

sSQL = "SELECT " & sSELECT
sSQL = sSQL & "FROM " & sFROM
If sWHERE <> "" Then sSQL = sSQL & "WHERE " & sWHERE

Set qdfNew = .CreateQueryDef("NewQueryDef", sSQL)
DoCmd.OpenForm "Search Results"
End With
Exit Sub
finderror:
If Err.Number = 2501 Then
MsgBox "No Matching Records were found"
Exit Sub
End If
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

 

[Creativemfs]

Again, Thank you OBP
Heres what my finished code looks like, I have yet to learn querydefs, but I think I have a basic understanding of it now.

Option Compare Database


Private Sub btnProceed_Click()
On Error GoTo Error_Handler
Dim strSQL As String, qdfNew As Object
Dim strWhere As String
Dim strSelect As String
Dim strFrom As String

With CurrentDb
.QueryDefs.Delete "qryWarehouseLabels"

strSelect = "tblEmployee.EmpLast, tblEmployee.EmpFirst, tblEmployee.WareID, tblWarehouse.Address, tblWarehouse.City, tblWarehouse.State, tblWarehouse.Zip"
strFrom = "tblWarehouse INNER JOIN tblEmployee ON tblWarehouse.WareID=tblEmployee.WareID"
strWhere = "(tblEmployee.PositionID)='2' AND (tblEmployee.WareID) In ("

If Me!Check10 = True Then strWhere = strWhere & "'OR-1',"
If Me!Check12 = True Then strWhere = strWhere & "'WA-1',"
If Me!Check14 = True Then strWhere = strWhere & "'WY-1'"

strSQL = "SELECT " & strSelect
strSQL = strSQL & " FROM " & strFrom
If strWhere <> "" Then strSQL = strSQL & " WHERE " & strWhere & ")"

Set qdfNew = .CreateQueryDef("qryWarehouseLabels", strSQL)
DoCmd.OpenReport "rptWarehouseManagerLabels", acViewReport
DoCmd.RunMacro "mcrCloseStuff.mcrCloseLForm", 1

End With

Exit_Procedure:
Exit Sub

Error_Handler:
MsgBox "You forgot to check a box, Stupid!", vbCritical, "Error"
Resume Exit_Procedure
End Sub

Works Flawlessly

 

[OBP]

I am not sure that it will handle all the combinations, have you tested it for that?
I would have thought that you would need some "AND" in the strWhere statement.
And then you would also need the section that removes an And if the user doesn't check a checkbox.

 

[Creativemfs]

Nope, it works. At first I did use and but it was not working so I took my original query and found out that IN(blahblahblah) worked, so I looked at the sql for that and built it into the code, so all three buttons work in any combination!

Thanks a lot man!

 

[OBP]

Great, I will make a note of your code in case others want to try it.
That is soemthing I would suggest that you do as well if you are likely to continue working with Access, copy your code witha simple note in to a Word doc or excel sheet for future reference.

 

[Creativemfs]

Thats a good idea, i think i will do that