Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with If statement

    Hi folks,

    Can someone help with this if statement?

    Code:
    If MsgBox("Should these labels be addressed personally?", vbYesNo, "Schools Database") = vbYes Then
        If [Forms]![frmAddressee]![chkAll] = True Then
            DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAllPersonal", , , "Personal"
        Else
            DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAll", , , "Addressee"
        End If
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchoolPersonal", , , "Personal"
    Else
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchool", , , "Addressee"
    End If
    Basically the user ticks a checkbox to create labels for all records in the database, and leaves unchecked to create individual ones.
    I also added a message box to ask the user if they want to personalise the label with the persons name or not.

    The problem I am getting is that if I leave the checkbox unchecked (therefore creating an individual label), and select Yes when the message box is displayed it opens the wrong report.

    I can get it to work adding more than one message box, but I only want one.

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Would something like this do it

    Code:
    Dim Personal As integer
    
    Personal = MsgBox("Should these labels be addressed personally?", vbYesNo, "Schools Database")
    
    If Personal = vbYes And [Forms]![frmAddressee]![chkAll] Then
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAllPersonal", , , "Personal"
    ElseIf Personal = vbNo And [Forms]![frmAddressee]![chkAll] Then
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAll", , , "Addressee"
    ElseIf peronal = vbYes Then
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchoolPersonal", , , "Personal"
    Else
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchool", , , "Addressee"
    End If
    ??

    MTB
    Last edited by MikeTheBike; 12-11-06 at 09:05.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    That's a great help thanks.

    I was wondereing how I could assign the messagebox to a variable of some sort and just specify the vbYes/vbNo properties.

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    OK, slightly off topic, but it's associated with the original post.

    I'm now having a problem with running the reports, specifically the two reports that display the persons name.
    Every time these two reports open it asks for a parameter for the ContactPosition field.
    I have added a query parameter to the query, and tried to assign QDef before the report is opened, but still it asks for a parameter.

    Query:
    Code:
    PARAMETERS [Forms]![frmAddressee]![txtAddressee] Text ( 255 );
    SELECT dbo_tblSchoolDetails.ID, dbo_tblSchoolContacts.ContactName, dbo_tblSchoolDetails.SchoolName, dbo_tblSchoolDetails.Addr1, dbo_tblSchoolDetails.Addr2, dbo_tblSchoolDetails.Town, dbo_tblSchoolDetails.County, dbo_tblSchoolDetails.PostCode, dbo_tblSchoolDetails.PostCode2
    FROM dbo_tblSchoolDetails INNER JOIN dbo_tblSchoolContacts ON dbo_tblSchoolDetails.ID = dbo_tblSchoolContacts.SchoolID
    WHERE (((dbo_tblSchoolDetails.ID)=[Forms]![frmSchoolDetails]![ID]) AND ((dbo_tblSchoolContacts.ContactPosition)=[Forms]![frmAddressee]![txtAddressee]));
    Code:
    Code:
    Public Function GenerateLabels()
    Dim QDef As QueryDef
    Dim rs As DAO.Recordset
    Dim Personal As Integer
    Personal = MsgBox("Should these labels be addressed personally?", vbYesNo, "Schools Database")
    If Personal = vbYes And [Forms]![frmAddressee]![chkAll] = True Then
        Set QDef = CurrentDb.QueryDefs("qryLabelsAllPersonal")
        QDef.Parameters("[Forms]![frmAddressee]![txtAddressee]").Value = [Forms]![frmAddressee]![txtAddressee]
     
        Set rs = QDef.OpenRecordset
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAllPersonal", , , "Personal"
    ElseIf Personal = vbNo And [Forms]![frmAddressee]![chkAll] = True Then
            DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsAll", , , "Addressee"
    ElseIf Personal = vbYes Then
        Set QDef = CurrentDb.QueryDefs("qryLabelsAllPersonal")
        QDef.Parameters("[Forms]![frmAddressee]![txtAddressee]").Value = [Forms]![frmAddressee]![txtAddressee]
     
        Set rs = QDef.OpenRecordset
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchoolPersonal", , , "Personal"
    Else
        DoCmd.OpenReport "rptLabels", acViewPreview, "qryLabelsBySchool", , , "Addressee"
    End If
    End Function
    What am I doing wrong?

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It looks to me that you're running this code in a module, as opposed to running it from a frmAddressee event. Try putting the code in a frmAddressee event instead, such as a command box's Click event. When you do that, the Qdf.Parameters(...) statement should become unnecessary, as the form is the current object, and Access should stop asking for a parameter.

    Hope this helps,

    Sam

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Yes I had put the code in a module, but sorted it now.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •