MS Access database support - Changing querydef sql to match form!control parameters

Author

Changing querydef sql to match form!control parameters

Bill R via AccessMonster.com

2006-02-17, 8:24 pm

I'm trying to use the same subforms for two different mainforms. The first
main form is "frmSwaps" and the second main form is named "frmSwapsLive".
The recordsources for the subforms have parameters such as: [Forms]![frmSwaps]
![grpPayFixed]
So I have code in the On Open event of frmSwapsLive as follows:

strLive = "frmSwapsLive"
strTest = "frmSwaps"

frmName = frm.Name

If frm.Name = strLive Then blLive = True

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySFrmPEData")
strSQL = qdf.SQL

If blLive Then
If InStr(strSQL, strLive) = 0 Then
strSQL = Replace(strSQL, strTest, strLive, , , vbTextCompare)
qdf.SQL = strSQL
End If
Else
If InStr(strSQL, strLive) > 0 Then
strSQL = Replace(strSQL, strLive, strTest, , , vbTextCompare)
qdf.SQL = strSQL
End If
End If

Set qdf = Nothing

I'm confident that this code will perform as advertised, but I never get
around to finding out, because before the code executes in the On Open event,
I get a series of parameter dialogs asking for "frmSwaps" parameters.

I was hoping that the qdf.sql would update before the subform had a chance to
try to run it's underlying query.

Any suggestions?

Thanks,

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.webservertalk.com

Anthony England

2006-02-17, 8:25 pm

"Bill R via webservertalk.com" <u9289@uwe> wrote in message
news:5c0814d230a7a@u
we...
> I'm trying to use the same subforms for two different mainforms. The first
> main form is "frmSwaps" and the second main form is named "frmSwapsLive".
> The recordsources for the subforms have parameters such as:
> [Forms]![frmSwaps]
> ![grpPayFixed]
> So I have code in the On Open event of frmSwapsLive as follows:
>
> strLive = "frmSwapsLive"
> strTest = "frmSwaps"
>
> frmName = frm.Name
>
> If frm.Name = strLive Then blLive = True
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("qrySFrmPEData")
> strSQL = qdf.SQL
>
> If blLive Then
> If InStr(strSQL, strLive) = 0 Then
> strSQL = Replace(strSQL, strTest, strLive, , , vbTextCompare)
> qdf.SQL = strSQL
> End If
> Else
> If InStr(strSQL, strLive) > 0 Then
> strSQL = Replace(strSQL, strLive, strTest, , , vbTextCompare)
> qdf.SQL = strSQL
> End If
> End If
>
> Set qdf = Nothing
>
> I'm confident that this code will perform as advertised, but I never get
> around to finding out, because before the code executes in the On Open
> event,
> I get a series of parameter dialogs asking for "frmSwaps" parameters.
>
> I was hoping that the qdf.sql would update before the subform had a chance
> to
> try to run it's underlying query.
>
> Any suggestions?
>
> Thanks,
>
> Bill
>
> --
> Bill Reed

You do realise that in a form/subform situation, the subform loads first,
don't you? There are a number of ways you could do this and I suppose it
depends how important it is for you to re-use the subform.
You could set the subform's recordsource to a query so it returns no
records, something like:
SELECT Null As Field1, Null As Field 2, Null As Field3 FROM MyTable WHERE
1=0
This would mean the subform opens quickly but displays no records. Then
each of the main forms can set the recordsource accordingly, eg
Me.sbfSub1.Form.Recordsource="qryLive"
or
Me.sbfSub1.Form.Recordsource="qryNotLive"

Bill R via AccessMonster.com

2006-02-21, 7:24 am

Anthony,

Thanks for your suggestion. I will redesign my procedure.

Bill

Anthony England wrote:
>[quoted text clipped - 42 lines]
>
>You do realise that in a form/subform situation, the subform loads first,
>don't you? There are a number of ways you could do this and I suppose it
>depends how important it is for you to re-use the subform.
>You could set the subform's recordsource to a query so it returns no
>records, something like:
>SELECT Null As Field1, Null As Field 2, Null As Field3 FROM MyTable WHERE
>1=0
>This would mean the subform opens quickly but displays no records. Then
>each of the main forms can set the recordsource accordingly, eg
>Me.sbfSub1.Form.Recordsource="qryLive"
>or
>Me.sbfSub1.Form.Recordsource="qryNotLive"

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200602/1

Sponsored Links