Add form parmeter

[Triel2006]

Hi -

Thanks to anyone who can help me figure this out.

I have an access database that was created long before my time. The users have a report that is selected between 2 dates. Now I need to add a parameter from the form (which I added on the screen) called acctltr. This is to select also select only the account numbers in the table InvoiceTable that end with whatever they put in the box. Really they are limited to "X" and "P X" at this time.

I am not very good with VBA so I am not sure how to add this into the code. The code is below. If there is a better way I am sure open to it because this code looks VERY confusing.

Thanks up front for any help!

Private Sub cmdprint_Click()
On Error GoTo exit_cmdprint
  'mysql = "SELECT * from InvoiceTable " & _
  '         " WHERE ((not InvoiceTable.InvoicePrintDate1 Is Null) AND (not InvoiceTable.InvoicePrintDate2 Is Null) AND (InvoiceTable.InvoicePrintDate3 Is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#))"
  
  'Me.RecordSource = mysql
  
  'If Me.Form.Recordset.RecordCount > 0 Then
  '   DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , "((Not InvoiceTable.InvoicePrintDate1 Is Null) AND (Not InvoiceTable.InvoicePrintDate2 Is Null) AND (InvoiceTable.InvoicePrintDate3 Is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#) and (not balancedue = 0))"
  'End If

  'mysql = "SELECT * from InvoiceTable " & _
  '         " WHERE ((not InvoiceTable.InvoicePrintDate1 Is Null) AND (InvoiceTable.InvoicePrintDate2 Is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#))"
  'Me.RecordSource = mysql
  
  'If Me.Form.Recordset.RecordCount > 0 Then
  '   DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , " ((not InvoiceTable.InvoicePrintDate1 is  Null) AND (InvoiceTable.InvoicePrintDate2 is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#) and (not balancedue = 0))"
  'End If

  mysql = "SELECT * from InvoiceTable " & _
           " WHERE (InvoiceTable.InvoicePrintDate1 Is Null) AND (datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#) and (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#)"
  Me.Form.RecordSource = mysql
 
  If Me.Form.Recordset.RecordCount > 0 Then
     DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , " ((InvoiceTable.InvoicePrintDate1 is  Null) AND (Datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#) And (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#))"
  End If

  'mysql = "SELECT * from InvoiceTable " & _
  '         " WHERE ((not InvoiceTable.InvoicePrintDate1 Is Null) AND (not InvoiceTable.InvoicePrintDate2 Is Null) AND (NOT InvoiceTable.InvoicePrintDate3 Is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#) and (not balancedue = 0))"
  'Me.RecordSource = mysql

  'If Me.Form.Recordset.RecordCount > 0 Then
   ' DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , "  ((Not InvoiceTable.InvoicePrintDate1 Is Null) AND (Not InvoiceTable.InvoicePrintDate2 Is Null) AND (Not InvoiceTable.InvoicePrintDate3 Is Null)) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#) and (not balancedue = 0)"
  'End If
  GoTo exit_normal
exit_cmdprint:
MsgBox (Err.Description)
exit_normal:
'Update_Invoice_PrintDate
 'MsgBox (MyRecordSource)


End Sub

 

[jsotola]

here is your code without all the commented-out lines

i added the account field to the query, you need to put in the actual name of the account field

Private Sub cmdprint_Click()


    On Error GoTo exit_cmdprint


    mysql = "SELECT * from InvoiceTable " & _
            " WHERE (InvoiceTable.InvoicePrintDate1 Is Null)" & _
            " AND (InvoiceTable.accountField = acctltr)" & _
            " AND (datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#)" & _
            " AND (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#)"
    
    Me.Form.RecordSource = mysql


    If Me.Form.Recordset.RecordCount > 0 Then
        DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , _
                         "(    (InvoiceTable.InvoicePrintDate1 is  Null)" & _
                         " AND (InvoiceTable.accountField = acctltr)" & _
                         " AND (Datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#)" & _
                         " AND (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#))"
    End If




    GoTo exit_normal
exit_cmdprint:
    MsgBox (Err.Description)
exit_normal:


End Sub

 

[Triel2006]

here is your code without all the commented-out lines

i added the account field to the query, you need to put in the actual name of the account field

Thank you so VERY much, now it makes a little more sense. I didn't realize those were all comments!!

I put the code in that reads so much more clearer, but I am getting a dialogue box asking to enter the Parameter Value for acctltr. I know that is the name of the text box that I put on my form along with the invoice dates that are above it. From what I am reading about why this happens it says the name is wrong or it is not available within that scope.

I double checked the name and even copied it from the form and pasted it in but I am still getting the box. It doesn't make sense that it is not available because the other 2 parms are coming from the same screen.

I enter the parm into the box and then I get nothing, no report.

Oh, I think I know why I don't get the report. It says:

AND (InvoiceTable.AccountNumber = acctltr)

I need it to be "LIKE" acctltr. Because the account number is like "10.1260.10180.151P X" Or like "10.1260.10180.002 X". So it ends in the "X" or the "P X" that they are entering into the screen. So the account number will not be equal to acctltr.

I had to have done something wrong.

 

[Triel2006]

I finally got it to work! I wanted to post the rest just in case someone else was looking for the same thing.

Private Sub cmdprint_Click()


    On Error GoTo exit_cmdprint


    mysql = "SELECT * from InvoiceTable " & _
            " WHERE (InvoiceTable.InvoicePrintDate1 Is Null)" & _
            " AND (InvoiceTable.AccountNumber Like '*" & acctltr & "*')" & _
            " AND (datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#)" & _
            " AND (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#)"
    
    Me.Form.RecordSource = mysql


    If Me.Form.Recordset.RecordCount > 0 Then
        DoCmd.OpenReport "rptInvoiceSumReport", acViewPreview, , _
                         "(    (InvoiceTable.InvoicePrintDate1 is  Null)" & _
                         " AND (InvoiceTable.AccountNumber Like '*" & acctltr & "*')" & _
                         " AND (Datevalue([InvoiceTable.InvoiceDate]) >= #" & dtefrom & "#)" & _
                         " AND (datevalue([InvoiceTable.InvoiceDate]) <= #" & dteto & "#))"
    End If



    GoTo exit_normal
exit_cmdprint:
    MsgBox (Err.Description)
exit_normal:


End Sub

This is for if you have a parameter from a form that you want to get data for from the database using a like statement.

Thanks again for your help!