Help putting SQL into VBA module 6

[annie52]

Would someone please help me put the quotes and continuation marks (underscores) where they belong. I just can't seem to get the hang of it.


DoCmd.OpenReport stDocName, acViewPreview, , Forms![frmReqnWatch]![ReqnID] = Forms![frmReqnWatch]![frmDisreps].Form![XRefReqnIDtoDisrepNum.ReqnID]

 

[PHV]

Your request makes no sense !
The syntax of the 4th parameter of the OpenReport method is a WHERE clause (SQL code) without the WHERE keyword:
"[some field in the underlaying report's query]=SomeValue"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[annie52]

I know it's supposed to be a WHERE clause without the WHERE. That's what I'm trying to write. WHERE [ReqnID] on the main form = [ReqnID] on the subform. Please explain why that doesn't make sense. Thanks.

 

[annie52]

By the way, when I ran it as it was, the report prints for all records. I only want to print the report for the current record. I tried using the statement as a filter rather than as a WHERE clause and get this error:

The Microsoft Office Access database engine could not find the object '-1'. Make sure the object exists and that you spell its name and the path name correctly.

It's probably because I haven't used quotes anywhere which bring me back to my cry for help.

 

[vbajock]

Dim ReqId

ReqId=Forms![frmReqnWatch]![frmDisreps].Form![XRefReqnIDtoDisrepNum.ReqnID]

DoCmd.OpenReport stDocName, acViewPreview, , "[ReqnID] = '"&ReqId&"'

 

[NXMold]

I don't get it.

Do you want to open a Report from a Form, filtered to just the current record of the Form?

Private Sub OnClick()
Dim strReqnID as String
Dim strWhere as String
Dim stDocName as String

strReqnID = Forms![frmReqnWatch]![ReqnID]
strWhere = "[ReqnID]='" & strReqnID & "'" 'Assuming field ReqnID appears in the report and is string data
stDocName = "SomeReport"

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

End Sub

 

[joelflorendo]

That's what I'm trying to write. WHERE [ReqnID] on the main form = [ReqnID] on the subform. Please explain why that doesn't make sense. Thanks.

The reason why this doesn't make sense is that you're trying to open a report based on some criteria, but your criteria doesn't reference anything in the report. The left side of the equation should reference a field in the report like the others have suggested so that Access understands what to filter.

 

[TheAceMan1]

How are ya annie52 . . .

[blue]NXMold[/blue] is on target! Below is the same code with some trimming:

[blue]   Dim Cri As String
   
   Cri = "[ReqnID] = [red][b]'[/b][/red]" & Me!ReqnID & "[red][b]'[/b][/red]"
   DoCmd.OpenReport "[purple][B][I]YourReportName[/I][/B][/purple]", acViewPreview, , Cri[/blue]

If [blue]ReqnID[/blue] happens to be numeric ... remove the two single quotes in red.

[blue]your thoughts/ . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[annie52]

Thank you all for helping me with this.

I'm not sure why I didn't reference the field on the report. Maybe my brain went out for a walk.

Do any of you know of something I can read that will help me grasp how and when to place the different quotation marks? I seem to have a terrible mental block with this.

 

[dhookom]

Allen Browne has some pages of value Quotation marks within quotes and Copy SQL statement from query to VBA

Duane
Hook'D on Access
MS Access MVP

 

[joelflorendo]

If a field type is text, you have to surround the value you are setting it to in quotes.

You can make use of single quotes to differentiate from double quotes if quotes need to be included in the final string. For example, this line from AceMan:

Cri = "[ReqnID] = '" & Me!ReqnID & "'"

The double quotes are needed because the criteria argument is a string/text. But you also need to surround the value you are giving for the [ReqnID] field with quotes because it is a text field. By using single quotes, you are literally including the single quotes in the final criteria string as part of the text. So when the code is run, the string Cri will equal

[ReqnID] = 'ABC123'.

Or whatever the ReqnID field is set to on your form.
If you were to use double quotes to surround the value you are giving for the [ReqnID] field, Access would be confused and think that you are ending the quote after the equals sign.

 

[annie52]

Hi dhookom. Interesting links. I really like the converter discussed at the CopySQL statement from query to VBA link. I tried it and ended up with the following VBA:

strSQL = "Reports![rptDisrepCover]![ReqnID] = " & vbCrLf & _
"Forms![frmReqnWatch]![ReqnID] And " & vbCrLf & _
"Reports![rptDisrepCover]![XrefDisrepNum] = " & vbCrLf & _
"Forms![frmReqnWatch]![frmDisreps].Form![XrefDisrepNum]"

Apparently, I'm still doing something wrong because my report comes up empty. If I only pass the ReqnID value, the report prints okay but always prints the information from the last record in the filtered subform. In other words, if I have 3 records in the subform that are linked to the mainform, the report will always print the third record even if the first or second record has the focus. The [XRefDisrepNum] is different on each of the three records so I'm trying to pass that value also.

Anybody have any ideas?

 

[dhookom]

The links were probably not meant to create a where condition in code. Your where condition doesn't need all that "Reports!rptDisrepCover!" stuff.

Where is the code running (main form or subform)?
What are the data types of ReqnID adn XrefDisrepNum?
If the code is running on the main form, do you really want to filter by the unique XrefDisrepNum value from the subform? If so, which value?

If the code is running in the subform and the fields are numeric, then this might work:

Dim strWhere as String
strWhere = "[ReqnID] = " & Me.Parent![ReqnID] & _
   " And  [XrefDisrepNum] =  " & Me![XrefDisrepNum]
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

If both fields are text then try:

Dim strWhere as String
strWhere = "[ReqnID] = """ & Me.Parent![ReqnID] & _
   """ And  [XrefDisrepNum] =  """ & Me![XrefDisrepNum] & """ "
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

Hi Duane. I just now got back on this project and wanted to thank you. ReqnID is numeric and XrefDisrepNum is text so I combined your two examples and Voila! Thanks a million.

 

[annie52]

Good grief. I guess I spoke too soon. Now I realize that sometimes it works and sometimes it doesn't. I can't seem to see a pattern. Please take a look at the code I ended up with and see if you spot my problem.

Private Sub cmdCoverSheet_Click()
On Error GoTo Err_cmdCoverSheet_Click

Dim strWhere As String
Dim strDocName As String

strWhere = "[ReqnID] = " & Me.Parent![ReqnID] & _
" And [XrefDisrepNum] = """ & Me![XrefDISREPNum] & """ "
strDocName = "rptDisrepCover"

DoCmd.OpenReport strDocName, acViewPreview, , strWhere

Exit_cmdCoverSheet_Click:
Exit Sub

Err_cmdCoverSheet_Click:
MsgBox Err.Description
Resume Exit_cmdCoverSheet_Click

End Sub

 

[joelflorendo]

try this:

strWhere = "[ReqnID] = " & Me.Parent![ReqnID] & _
    " And [XrefDisrepNum] = [COLOR=red]'[/color]" & Me![XrefDISREPNum] & "[COLOR=red]'[/color]"

 

[annie52]

Please ignore my last post. There was a problem with the underlying query. All is well now. Thanks again to everyone.