MS Access Printing Report from Button

[Sscumbag]

Hi all,

I'm new at this, never did a thing with MS Access...

This is my problem..

I have a form with 2 listboxes, the left listbox (Batchfiles) gets its data from a select statement. The onclick event "sends" data to the right listbox (lstDetails) with another query.

Private Sub BatchFiles_Click()
lstDetails.RowSource = "SELECT LastName FROM Table WHERE ID = """ & BatchFiles.value & """
lstDetails.Requery
End Sub

I like to print the value of lstDetails.

I tried a lot of things, but none of them worked.. So finally i made a query with the query SELECT LastName FROM Table WHERE ID = ID .

Then I made a report that uses the above query. When I open the report a popup will ask the value of ID and the output is what i want...

Next I made a button on the form with the onclick event:
DoCmd.OpenReport "Incasso", acViewPreview


But when the button opens the report it will ask the value of the ID, how can i send this value to the report?


I hope anyone can answer my question..

grtz..

 

[THWatson]

Try...
DoCmd.OpenReport "Incasso", acViewPreview,, "ID = " & YourFormName!ID""

Tom

 

[GingerR]

...and take the criteria/where clause out of your report's recordsource (maybe set the report's recordsource back to the table?)....

 

[Sscumbag]

Thanks so far, but I'm not there yet..

I changed the reportsource, it's not pointing to the query anymore. I pasted the query right behind it.
Then I set Filter On = Yes

Now when I press the button, a popup shows: Enter Parameter Value

When I leave the parameter blank, it will show all records. But when I give it a value, it shows a blank report.
If I change this opened report to design view, the Filter field is filled with the value from the form..

Is this filter field different than a Where statement in SQL?

What else am I doing wrong?

 

[GingerR]

Is it a list box that you are taking the data from on the form? Or is it a text box? What is the name of it?


What is the code behind your button's OnClick event?
What is the Recordsource of your Report?
What is the data type of the ID? Number? Text?

 

[Sscumbag]

1The data I use in the onclick event is from the listbox named "Batchfiles"

2
Private Sub Print_Click()
'On Error Resume Next
If MsgBox("Wilt u de selectie printen?", vbYesNo, "JCF - Print selectie") = vbYes Then
DoCmd.OpenReport "rptIncasso", acViewPreview, , "finMachtigingBetalingen.IncassoRunBestand='" & BatchFiles.value & "'"
End If
End Sub

3
SELECT rbsRelaties.Achternaam, finRelatieRekeningnr.Rekeningnummer, finMachtigingBetalingen.Bedrag FROM (rbsRelaties INNER JOIN (finRelatieRekeningnr INNER JOIN finMachtiging ON finRelatieRekeningnr.RekeningID=finMachtiging.RekeningFK) ON rbsRelaties.RelatieID=finMachtiging.RelatieFK) INNER JOIN finMachtigingBetalingen ON finMachtiging.MachtigingID=finMachtigingBetalingen.MachtigingFK;

4
Text


Thanks in advance...

 

[GingerR]

A list box--you can't just get the value from it like you can a text box or combo box. Does the ID value have to come from a list box? what's the purpose of that? How about making it a text box?

Since the value is text, you have to put parentheses around it.


...where LastName = '" & me.txtLastName & "'"

notice the single then double parentheses.
(me.txtLastName is what I'm calling a text box on your form).

Does any of this makes sense?

 

[Sscumbag]

The listbox get's it values from another query.

I got the parentheses...

='" & BatchFiles.value & "'"

Yep it does make sense, but somehow I can't get it to work.
But I've made a workaround, deleted the report and made with the excel object my own report.


Screen.MousePointer = 11
Dim strRekeningnummer, strAchternaam, strBedrag As String
Dim I, Y As Integer

Dim rs As Recordset
Dim db As Database
Dim objExcel As Object

Set objExcel = CreateObject("EXCEL.application")
objExcel.Visible = False
objExcel.workbooks.Add



'On Error GoTo Foutje

Set db = CurrentDb()
Set rs = db.OpenRecordset(" " & _
"SELECT rbsRelaties.Achternaam, finRelatieRekeningnr.Rekeningnummer, finMachtigingBetalingen.Bedrag " & _
"FROM (rbsRelaties " & _
"INNER JOIN (finRelatieRekeningnr " & _
"INNER JOIN finMachtiging " & _
"ON finRelatieRekeningnr.RekeningID=finMachtiging.RekeningFK) " & _
"ON rbsRelaties.RelatieID=finMachtiging.RelatieFK) " & _
"INNER JOIN finMachtigingBetalingen " & _
"ON finMachtiging.MachtigingID=finMachtigingBetalingen.MachtigingFK " & _
"WHERE (((finMachtigingBetalingen.IncassoRunBestand)=""" & BatchFiles.value & """)) ; ")



Do Until rs.EOF
If IsNull(rs("Rekeningnummer")) = False Then
strRekeningnummer = rs("Rekeningnummer")
Else
strRekeningnummer = ""
End If
If IsNull(rs("Achternaam")) = False Then
strAchternaam = rs("Achternaam")
Else
strAchternaam = ""
End If
If IsNull(rs("Bedrag")) = False Then
strBedrag = rs("Bedrag")
Else
strBedrag = ""
End If
Y = Y + 1
For I = 0 To rs.Fields.Count - 1
objExcel.Application.Cells(Y, (I + 1)).value = rs.Fields(I).value

Next

rs.MoveNext
Loop
rs.Close
objExcel.Cells.EntireColumn.AutoFit
objExcel.ActiveWindow.SelectedSheets.PrintOut
objExcel.ActiveWindow.Close SaveChanges:=False

objExcel.Application.Quit

Set objExcel = Nothing
Screen.MousePointer = 0
Exit Sub
Foutje:
MsgBox Err.Description, vbCritical, "Fout"
Err.Clear

End Sub



It works just fine.

Thanks a lot for your support!!