change form record source SQL

[Lance]

hi all,
I am creating a form which changes the form "recordsource" query
according to selection on combo box. Any idea how I can do this??.
Thanks ,
Kumar

 

[Lance]

This is what I tried, but its not working

Private Sub getRecord()
Dim strSQL As String

If IsNull(Me.txtSdpth) Then
strSQL = "SELECT qrySoilst1.Domain, qrySoilst1.Analyte,
Sum(qrySoilst1.NDVal) AS SumOfNDVal, Avg(qrySoilst1.Result) AS
AvgOfResult, Max(qrySoilst1.Result) AS MaxOfResult,
StDev(qrySoilst1.Result) AS StDevOfResult, Count(qrySoilst1.Result) AS
CountOfResult, [Domain] & '_' & [Analyte] AS JoinID" & _
"FROM qrySoilst1" & _
"GROUP BY qrySoilst1.Domain, qrySoilst1.Analyte" & _
"HAVING (((qrySoilst1.Domain)=[Forms]![frmSoilAnal]![cmbZone]) AND
((qrySoilst1.Analyte)=[Forms]![frmSoilAnal]![cmbAnalyte]));"
Else
strSQL = " Some other SQL"

End If

Me.RecordSource = strSQL
Me.Requery

End Sub

 

[Ron Weiner]

Lance

You havent given us much to work with, as you do not tell us what the error
is, but here is how you can debug your Sql String.

Just above the line Me.RecordSource = strSQL add debug.print strsql. Put a
break point at Me.RecordSource = strSQL and run your app. When the app gets
to the break type <CTRL>G to bring the immediate window to to front. Copy
the Sql statement from the immediate window to a new query in Sql View.

Most times the error will be self evident, but you can run the query and see
if Access is willing to direct you to the offending part.

Ron W

This is what I tried, but its not working

Private Sub getRecord()
Dim strSQL As String

If IsNull(Me.txtSdpth) Then
strSQL = "SELECT qrySoilst1.Domain, qrySoilst1.Analyte,
Sum(qrySoilst1.NDVal) AS SumOfNDVal, Avg(qrySoilst1.Result) AS
AvgOfResult, Max(qrySoilst1.Result) AS MaxOfResult,
StDev(qrySoilst1.Result) AS StDevOfResult, Count(qrySoilst1.Result) AS
CountOfResult, [Domain] & '_' & [Analyte] AS JoinID" & _
"FROM qrySoilst1" & _
"GROUP BY qrySoilst1.Domain, qrySoilst1.Analyte" & _
"HAVING (((qrySoilst1.Domain)=[Forms]![frmSoilAnal]![cmbZone]) AND
((qrySoilst1.Analyte)=[Forms]![frmSoilAnal]![cmbAnalyte]));"
Else
strSQL = " Some other SQL"

End If

Me.RecordSource = strSQL
Me.Requery

End Sub

hi all,
I am creating a form which changes the form "recordsource" query
according to selection on combo box. Any idea how I can do this??.
Thanks ,
Kumar

 

[Marshall Barton]

This is what I tried, but its not working

Private Sub getRecord()
Dim strSQL As String

If IsNull(Me.txtSdpth) Then
strSQL = "SELECT qrySoilst1.Domain, qrySoilst1.Analyte,
Sum(qrySoilst1.NDVal) AS SumOfNDVal, Avg(qrySoilst1.Result) AS
AvgOfResult, Max(qrySoilst1.Result) AS MaxOfResult,
StDev(qrySoilst1.Result) AS StDevOfResult, Count(qrySoilst1.Result) AS
CountOfResult, [Domain] & '_' & [Analyte] AS JoinID" & _
"FROM qrySoilst1" & _
"GROUP BY qrySoilst1.Domain, qrySoilst1.Analyte" & _
"HAVING (((qrySoilst1.Domain)=[Forms]![frmSoilAnal]![cmbZone]) AND
((qrySoilst1.Analyte)=[Forms]![frmSoilAnal]![cmbAnalyte]));"
Else
strSQL = " Some other SQL"

End If

Me.RecordSource = strSQL
Me.Requery

End Sub

First, get rid of the Requery, setting the RecordSource
property causes an automatic requery and using an
unnecessary Requery might be a (serious?) performance drag.

Next, when asking a question, "its not working" just doesn't
cut it as a description of your problem. The least you
could do is provide a detailed explanation of what actually
happened and the number and description from whatever error
messages that appeared.

Given the lack of clues, I'll take a guess that the query
won't run because there is a non-aggregated field, i.e. the
calculated field [Domain] & '_' & [Analyte]. Try including
it in the Group By clause

"GROUP BY qrySoilst1.Domain, qrySoilst1.Analyte," & _
"[Domain] & '_' & [Analyte] " & _

Also, I don't see any reason to use a HAVING clause, change
it to a WHERE clause. If nothing else, it may make the
query run faster.