calling an MS access sql query in a button click

[oly]

What/where do you see that sql fitting? I did find a reference, but I haven't tried it.
Here's the link
http://en.allexperts.com/q/Using-MS-...cordsource.htm

When you post code snippets, you should use [ c o d e ] and [ / c o d e ] tags around the code ** no spaces**

The link you posted, unfortunately doesnt work

[orange]

Must be your end, because it works for me.

I've copied the material here.

Using MS Access/setting report's recordsource 			  Advertisement
   
 	     Expert: Scottgem - 5/11/2009
     
Question
Due to the complexity of my one report's query  (utilizing subqueries that are filtered by multi-select listboxes), I  want to set the report's record source to equal a SQL string in vba  instead of as a saved query. So, on my f_ParamFamilyGifts form when the  user clicks "Preview Report" I have this vba:

Dim strSQL As String

strSQL = "SELECT etc... FROM etc... WHERE etc..."

Now, I don't know what to do next to set the report's recordsource to equal strSQL. I've tried this:

Report_r_FamilyGifts.RecordSource = strSQL
DoCmd.OpenReport "r_FamilyGifts", acViewPreview

But that doesn't work.  I've also tried:

DoCmd.OpenReport "r_FamilyGifts", acViewPreview
Reports!r_FamilyGifts!RecordSource = strSQL

But that doesn't work either. How can I do this? 

Get the answer below
 Sponsored Links MS Office 2010 Price -$69SoftwareSavingsWorld.com/Office2010Complete Full Version! Only $69... Order Today And Download Instantly
 SPSS Data Mining Secretswww.IBM.com/SPSS_data_miningLearn The Keys To Data Mining. Get Your Free SPSS Whitepaper.
 SQL Database Accesswww.MatrikonOPC.com/OPCDatabasesEasily store OPC to databases. Easy to use, Free to try.
 
 
Answer
There are two ways to do this. Pass the SQL to the report as an OpenArgs or reference a control on a form.

If you use the Open Args, it would look something like this:

DoCmd.OpenReport "r_FamilyGifts", acViewPreview,,,,strSQL

Then, in the On Open event of the report use:

If NOT IsNull(Me.OPenArgs) Then
Me.Recordsopurce = Me.OpenArgs
End If

If you use the form, the On Open event would look like:

If NOT IsNull(Forms!formname!txtSQL) Then
Me.Recordsopurce = Me.Forms!formname!txtSQL
End If

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

[oly]

Must be your end, because it works for me.

I've copied the material here.

Using MS Access/setting report's recordsource


               Advertisement
   
          Expert: Scottgem - 5/11/2009
     
Question
Due to the complexity of my one report's query  (utilizing subqueries that are filtered by multi-select listboxes), I  want to set the report's record source to equal a SQL string in vba  instead of as a saved query. So, on my f_ParamFamilyGifts form when the  user clicks "Preview Report" I have this vba:

Dim strSQL As String

strSQL = "SELECT etc... FROM etc... WHERE etc..."

Now, I don't know what to do next to set the report's recordsource to equal strSQL. I've tried this:

Report_r_FamilyGifts.RecordSource = strSQL
DoCmd.OpenReport "r_FamilyGifts", acViewPreview

But that doesn't work.  I've also tried:

DoCmd.OpenReport "r_FamilyGifts", acViewPreview
Reports!r_FamilyGifts!RecordSource = strSQL

But that doesn't work either. How can I do this? 

Get the answer below
 Sponsored Links

 MS Office 2010 Price -$69SoftwareSavingsWorld.com/Office2010Complete Full Version! Only $69... Order Today And Download Instantly
 SPSS Data Mining Secretswww.IBM.com/SPSS_data_miningLearn The Keys To Data Mining. Get Your Free SPSS Whitepaper.
 SQL Database Accesswww.MatrikonOPC.com/OPCDatabasesEasily store OPC to databases. Easy to use, Free to try.
 
 
Answer
There are two ways to do this. Pass the SQL to the report as an OpenArgs or reference a control on a form.

If you use the Open Args, it would look something like this:

DoCmd.OpenReport "r_FamilyGifts", acViewPreview,,,,strSQL

Then, in the On Open event of the report use:

If NOT IsNull(Me.OPenArgs) Then
Me.Recordsopurce = Me.OpenArgs
End If

If you use the form, the On Open event would look like:

If NOT IsNull(Forms!formname!txtSQL) Then
Me.Recordsopurce = Me.Forms!formname!txtSQL
End If

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

see my code below. Please let me know if I am msising something

Private Sub Command7_Click()

strSQL = "select * from tbl_colour" 
strSQL = strSQL & " where ID = '" & Me.Combo4.Value & "'" 
strSQL = strSQL & " and Place != 'NewYork'" 
strSQL = strSQL & " order by place"
DoCmd.OpenReport "testreport", acViewPreview,,,,strSQLEnd Sub

[orange]

Did you put this in the Open event for the form


From the article:
Then, in the On Open event of the report use:

If NOT IsNull(Me.OPenArgs) Then
   Me.Recordsource = Me.OpenArgs
End If

Go to the Report design, look at the properties, for the Open Event,
put the code in green in the event procedure

[oly]

Did you put this in the Open event for the form


From the article:
Then, in the On Open event of the report use:

If NOT IsNull(Me.OPenArgs) Then
   Me.Recordsource = Me.OpenArgs
End If

Go to the Report design, look at the properties, for the Open Event,
put the code in green in the event procedure

I did...see code below

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
   Me.RecordSource = Me.OpenArgs
End If


End Sub

[orange]

I just tried, with my Animal report:

Private Sub Report_Open(Cancel As Integer)
MsgBox "i'm opening the form", vbOKOnly
'
' as you can see I commented the code below
' ------------------------------------------------
'If Not IsNull(Me.OpenArgs) Then
'   Me.RecordSource = Me.OpenArgs
'End If
Me.RecordSource = "SELECT A.AnimalId, A.SightingDate, A.GPSLat, A.GPSLong, Animal.AName, AnimalCapture.CaptureDate" _
 & " FROM (AnimalLocs AS A INNER JOIN Animal ON A.AnimalId = Animal.AnimalId) " _
 & " INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId;"

End Sub

I thought it might be too late to set the recordsource at the Open Event, but it seems to work fine.
In my case the set up was to pass the combo selection as a WHERE argument to the
DoCmd.OpenReport, and that works as well.

So follow this pattern and it should work.

I haven't open reports like this before.

[oly]

I just tried, with my Animal report:

Private Sub Report_Open(Cancel As Integer)
MsgBox "i'm opening the form", vbOKOnly
'
' as you can see I commented the code below
' ------------------------------------------------
'If Not IsNull(Me.OpenArgs) Then
'   Me.RecordSource = Me.OpenArgs
'End If
Me.RecordSource = "SELECT A.AnimalId, A.SightingDate, A.GPSLat, A.GPSLong, Animal.AName, AnimalCapture.CaptureDate" _
 & " FROM (AnimalLocs AS A INNER JOIN Animal ON A.AnimalId = Animal.AnimalId) " _
 & " INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId;"

End Sub

I thought it might be too late to set the recordsource at the Open Event, but it seems to work fine.
In my case the set up was to pass the combo selection as a WHERE argument to the
DoCmd.OpenReport, and that works as well.

So follow this pattern and it should work.

I haven't open reports like this before.

what did you include in your button_click event.....because I have to get the associated colour from the dropdownlist

[orange]

In my animal form, the click event for the button is

Private Sub Command10_Click()
DoCmd.OpenReport "rptAnimalInfo", acViewPreview, , "AnimalId=" & Me.Combo6
End Sub

[oly]

hi Orange, thanks so far for your help but i will continue work on it tomorrow and see if i can solve this problem and if I have more questions, can I ask you...

[orange]

Good luck.