1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA help in Access 2007

Discussion in 'Business Applications' started by Creativemfs, Apr 7, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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
     
  2. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    I got the check box code to work, now i just need to get my openreport to work
     
  3. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     
  5. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    So do you want to filter the query using just one of the warehouse IDs?
     
  7. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    I want to filter it based on one, two, or all three of the criteria
     
  8. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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.
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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
     
  10. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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 :)
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  12. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    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!
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  14. Creativemfs

    Creativemfs Thread Starter

    Joined:
    Feb 18, 2010
    Messages:
    27
    Thats a good idea, i think i will do that
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/915476

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice