How can I pass the data from a form to a report? 1

[sanders720]

Command is a button on an open form, which reads current combo box selections. The query has been tested and runs fine. The data does not make it on the report under SubDesctiption for the control name. The control source is unbound.

Private Sub command4_click()
Dim rst As Recordset
Dim sql As String

sql = "SELECT Description from tblSubAssyListing WHERE JobNo = " & Me.Combo8.Value & " AND SubAssy = '" & Me.Combo22.Value & "' "
Set rst = CurrentDb.OpenRecordset(sql)

OpenReport "rptManufactured Parts List", True
Application.Reports![rptManufactured Parts List].SubDescription.Value = rst("Description&quot

rst.Close
Set rst = Nothing
End Sub

Thx,

 

[fullkeel]

Wouldn't this get you the same result?

Private Sub command4_click()
Dim Crit as String

Crit = "JobNo = " & Me.Combo8.Value
Crit = Crit & " AND SubAssy = '" & Me.Combo22.Value & "' "

OpenReport "rptManufactured Parts List", ,Crit

end sub

Bruce Gregory

 

[sanders720]

Everything seems to work until I try to open the form. This is when I get an error of "Compile Error, argument not optional" If I use...

OpenReport "....", TRUE, Crit I get the same result.

Is there an issue with spaces in the report name? I tried brackets and still had problems.

Or, should I use the docmd option here? I have a book that shows the syntax that way

Or, should I use parenthesis with the report command, as is shown in the VB editor when I type OpenReport?

Thanks in advance for the help!

 

[fullkeel]

I failed to notice your openreport statement and just duplicated it for my example, the following would be the correct syntax for the example.

Private Sub command4_click()
Dim Crit, Docname as String

Crit = "JobNo = " & Me.Combo8.Value
Crit = Crit & " AND SubAssy = '" & Me.Combo22.Value & "' "

Docname = "rptManufactured Parts List"
DoCmd.OpenReport Docname, A_PREVIEW, , Crit
'I called for preview so you can view the report on the screen

Best,
Bruce Gregory
end sub

 

[sanders720]

Actually, the Crit information uses Combo8 and Combo22 information from an open form to obtain the "description" from another table. It is this description that I would like to pass to the "SubDescription" textbox in the header of my report.

I need to define and run a query, and then implement the description information into my report.

Thanks again!

 

[KyleS]

Hi,

I've got a couple questions for you...?

1)Is this the only value that will be in the Subdescription field? - if so then you could just use DLookup() as the control source for the field on the report to get the value you're looking for..
DLookup('[Description]','[tblSubAssyListing]','[JobNo] = ' & Forms![YOURFORMNAMEHERE]!Combo8 & ' AND SubAssy = ' & Forms![YOURFORMNAMEHERE]!Combo22 & "' &quot

2)If that won't work for you try these changes...
sql = "SELECT Description from tblSubAssyListing WHERE JobNo = '" & Me.Combo8 & "' AND SubAssy = '" & Me.Combo22& "'; "
To make sure your sql is exactly right you should paste itinto a query and try it that way.
Reports![rptManufactured Parts List].SubDescription = rst!Description

3)(OK so now they're just suggestions...)If the sql is right and when you run the query it gives you the "Description" you're looking for, save it as a query and change your line to this..
Set rst = CurrentDb.OpenRecordset([YOURQUERYNAME])
And remove the sql variable

Just some thoughts...

Hope this helps..

 

[sanders720]

This is what I've typed into the Control Source for the textbox property on my report.

=DLookUp("[Description]","tblSubAssyListing","[JobNo] = " & [Forms]![frmReportsListing]![Combo8] & " AND SubAssy = ' & [Forms]![frmReportsListing]![Combo22] & ' &quot

The result produces nothing, including no error - however data does exist. The JobNo is an integer value and the SubAssy is a text value.

Any thoughts? And thanks again for the help!

 

[KyleS]

The only thing I can see missing are two quotation marks, other than that it should be working...

=DLookUp("[Description]","tblSubAssyListing","[JobNo] = " & [Forms]![frmReportsListing]![Combo8] & " AND SubAssy = '" & [Forms]![frmReportsListing]![Combo22] & " ' &quot

if this doesn't work, try pasting this in there just to see...

=DLookUp("[Description]","tblSubAssyListing","[JobNo] = " & [Forms]![frmReportsListing]![Combo8])

This will tell you that your first criteria is working, then try it with just the second criteria - I know the data won't be right, but if there is any data in there it will let you know the criteria is right. If they both work, I'm not sure what else could be the propblem...

OK, one last idea if all else fails (i wish access would give you an error when there is no data) you could put your sql code in the on open of the report...

Dim rst as recordset
dim sql as string

sql = "SELECT Description from tblSubAssyListing WHERE JobNo = " & Me.Combo8.Value & " AND SubAssy = '" & Me.Combo22.Value & "' "
Set rst = CurrentDb.OpenRecordset(sql)

Me!SubDescription= rst("Description&quot

Kyle

 

[fullkeel]

Another approach would be to use Chr$(34) The ascii equivilent to the quotation mark

DLookUp("[Description]","tblSubAssyListing","[JobNo] = " & [Forms]![frmReportsListing]![Combo8] & " AND SubAssy = " & Chr$(34)& [Forms]![frmReportsListing]![Combo22] & Chr$(34) &quot

Bruce