Code Help mnsebastian[ at ]msn.com 29.09.2006 18:06:10 I have the following code: Private Sub cboAssetNumber_AfterUpdate() If Not IsNull(Me!cboAssetNumber) Then Me!txtAddress1 = Me!cboAssetNumber.Column(1) Me!txtCity = Me!cboAssetNumber.Column(2) Me!txtState = Me!cboAssetNumber.Column(3) Me!txtZipCode = Me!cboAssetNumber.Column(4) Dim db As Database Dim Rst As DAO.Recordset Dim qdf As QueryDef Dim strSQL As String Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] = 1") qdf.Parameters(0) = Forms![frmMain]![txtZipCode] Set Rst = qdf.OpenRecordset(dbOpenDynaset) If Rst.RecordCount = 0 Then DoCmd.OpenForm ("frmError"), acNormal End If Set Rst = Nothing Set db = Nothing End If End Sub Unfortunately, the strsql is selecting all records instead of just the source = 1, can someone help? Thanks!

Re: Code Help "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> 29.09.2006 18:21:19 You left out a line. Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] = 1") qdf.SQL = strSQL -- Bill Mosca, MS Access MVP <mnsebastian[ at ]msn.com> wrote in message news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... [Quoted Text] >I have the following code: > > > Private Sub cboAssetNumber_AfterUpdate() > If Not IsNull(Me!cboAssetNumber) Then > Me!txtAddress1 = Me!cboAssetNumber.Column(1) > Me!txtCity = Me!cboAssetNumber.Column(2) > Me!txtState = Me!cboAssetNumber.Column(3) > Me!txtZipCode = Me!cboAssetNumber.Column(4) > > Dim db As Database > Dim Rst As DAO.Recordset > Dim qdf As QueryDef > Dim strSQL As String > > > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE > [Source] = 1") > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] > Set Rst = qdf.OpenRecordset(dbOpenDynaset) > > > If Rst.RecordCount = 0 Then > DoCmd.OpenForm ("frmError"), acNormal > > End If > > Set Rst = Nothing > Set db = Nothing > End If > End Sub > > Unfortunately, the strsql is selecting all records instead of just the > source = 1, can someone help? > > Thanks! >

Re: Code Help mnsebastian[ at ]msn.com 29.09.2006 19:20:50 Thank you for the reply, I get the error: Circular Reference by Get20Mile_SelQry Bill Mosca, MS Access MVP wrote: [Quoted Text] > You left out a line. > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] = > 1") > qdf.SQL = strSQL > > -- > Bill Mosca, MS Access MVP > > > <mnsebastian[ at ]msn.com> wrote in message > news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... > >I have the following code: > > > > > > Private Sub cboAssetNumber_AfterUpdate() > > If Not IsNull(Me!cboAssetNumber) Then > > Me!txtAddress1 = Me!cboAssetNumber.Column(1) > > Me!txtCity = Me!cboAssetNumber.Column(2) > > Me!txtState = Me!cboAssetNumber.Column(3) > > Me!txtZipCode = Me!cboAssetNumber.Column(4) > > > > Dim db As Database > > Dim Rst As DAO.Recordset > > Dim qdf As QueryDef > > Dim strSQL As String > > > > > > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE > > [Source] = 1") > > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] > > Set Rst = qdf.OpenRecordset(dbOpenDynaset) > > > > > > If Rst.RecordCount = 0 Then > > DoCmd.OpenForm ("frmError"), acNormal > > > > End If > > > > Set Rst = Nothing > > Set db = Nothing > > End If > > End Sub > > > > Unfortunately, the strsql is selecting all records instead of just the > > source = 1, can someone help? > > > > Thanks! > >

Re: Code Help "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> 29.09.2006 20:09:30 Can you post the SQL for Get20Mile_SelQry? -- Bill Mosca, MS Access MVP <mnsebastian[ at ]msn.com> wrote in message news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... [Quoted Text] > Thank you for the reply, > I get the error: > > Circular Reference by Get20Mile_SelQry > > > Bill Mosca, MS Access MVP wrote: >> You left out a line. >> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] >> = >> 1") >> qdf.SQL = strSQL >> >> -- >> Bill Mosca, MS Access MVP >> >> >> <mnsebastian[ at ]msn.com> wrote in message >> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... >> >I have the following code: >> > >> > >> > Private Sub cboAssetNumber_AfterUpdate() >> > If Not IsNull(Me!cboAssetNumber) Then >> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) >> > Me!txtCity = Me!cboAssetNumber.Column(2) >> > Me!txtState = Me!cboAssetNumber.Column(3) >> > Me!txtZipCode = Me!cboAssetNumber.Column(4) >> > >> > Dim db As Database >> > Dim Rst As DAO.Recordset >> > Dim qdf As QueryDef >> > Dim strSQL As String >> > >> > >> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >> > [Source] = 1") >> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] >> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) >> > >> > >> > If Rst.RecordCount = 0 Then >> > DoCmd.OpenForm ("frmError"), acNormal >> > >> > End If >> > >> > Set Rst = Nothing >> > Set db = Nothing >> > End If >> > End Sub >> > >> > Unfortunately, the strsql is selecting all records instead of just the >> > source = 1, can someone help? >> > >> > Thanks! >> > >

Re: Code Help mnsebastian[ at ]msn.com 29.09.2006 20:22:08 Yes (thank you for your help) SELECT GetDistance_selqry.FNFPreferredBrokerFlag, GetDistance_selqry.ActiveAssets, GetDistance_selqry.[MLA Signed], ([GetDistance_selqry].[Miles]) AS Miles, GetDistance_selqry.CompanyName, GetDistance_selqry.Zip, GetDistance_selqry.Address1, GetDistance_selqry.City, GetDistance_selqry.StateCode, GetDistance_selqry.OfficePhone, GetRucaSubClass_SelQry.RUCA_SubClass, GetDistance_selqry.VendorId, GetDistance_selqry.ContactName, GetDistance_selqry.MasterListingAgreementSignedOnDate, GetDistance_selqry.[Licensed Expired], GetDistance_selqry.LicenseExpiryDate, GetDistance_selqry.VendorId, GetDistance_selqry.MaxAssetsAssigned, GetDistance_selqry.Fax, GetDistance_selqry.Mobile, IIf([Ruca_subclass]="a",[miles]<5,IIf([Ruca_subclass]="b",[miles]<5,IIf([Ruca_subclass]="c",[miles]<25,IIf([Ruca_subclass]="d",[miles]<25)))) AS Expr1, "P" As Source FROM GetDistance_selqry, GetRucaSubClass_SelQry WHERE (((IIf([Ruca_subclass]="a",[miles]<5,IIf([Ruca_subclass]="b",[miles]<5,IIf([Ruca_subclass]="c",[miles]<25,IIf([Ruca_subclass]="d",[miles]<25)))))<>False)) ORDER BY GetDistance_selqry.FNFPreferredBrokerFlag DESC , GetDistance_selqry.ActiveAssets, GetDistance_selqry.[MLA Signed] DESC ,Miles WITH OWNERACCESS OPTION; UNION SELECT Top 1 GetDistance_selqry.FNFPreferredBrokerFlag, GetDistance_selqry.ActiveAssets, GetDistance_selqry.[MLA Signed], ([GetDistance_selqry].[Miles]) AS Miles, GetDistance_selqry.CompanyName, GetDistance_selqry.Zip, GetDistance_selqry.Address1, GetDistance_selqry.City, GetDistance_selqry.StateCode, GetDistance_selqry.OfficePhone, GetRucaSubClass_SelQry.RUCA_SubClass, GetDistance_selqry.VendorId, GetDistance_selqry.ContactName, GetDistance_selqry.MasterListingAgreementSignedOnDate, GetDistance_selqry.[Licensed Expired], GetDistance_selqry.LicenseExpiryDate, GetDistance_selqry.VendorId, GetDistance_selqry.MaxAssetsAssigned, GetDistance_selqry.Fax, GetDistance_selqry.Mobile, IIf([Ruca_subclass]="a",[miles]<5,IIf([Ruca_subclass]="b",[miles]<5,IIf([Ruca_subclass]="c",[miles]<25,IIf([Ruca_subclass]="d",[miles]<25)))) AS Expr1, "S" as Source FROM GetDistance_selqry, GetRucaSubClass_SelQry ORDER BY GetDistance_selqry.FNFPreferredBrokerFlag DESC , GetDistance_selqry.ActiveAssets, GetDistance_selqry.[MLA Signed] DESC , Miles WITH OWNERACCESS OPTION; Bill Mosca, MS Access MVP wrote: [Quoted Text] > Can you post the SQL for Get20Mile_SelQry? > > -- > Bill Mosca, MS Access MVP > > > <mnsebastian[ at ]msn.com> wrote in message > news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... > > Thank you for the reply, > > I get the error: > > > > Circular Reference by Get20Mile_SelQry > > > > > > Bill Mosca, MS Access MVP wrote: > >> You left out a line. > >> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > >> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] > >> = > >> 1") > >> qdf.SQL = strSQL > >> > >> -- > >> Bill Mosca, MS Access MVP > >> > >> > >> <mnsebastian[ at ]msn.com> wrote in message > >> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... > >> >I have the following code: > >> > > >> > > >> > Private Sub cboAssetNumber_AfterUpdate() > >> > If Not IsNull(Me!cboAssetNumber) Then > >> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) > >> > Me!txtCity = Me!cboAssetNumber.Column(2) > >> > Me!txtState = Me!cboAssetNumber.Column(3) > >> > Me!txtZipCode = Me!cboAssetNumber.Column(4) > >> > > >> > Dim db As Database > >> > Dim Rst As DAO.Recordset > >> > Dim qdf As QueryDef > >> > Dim strSQL As String > >> > > >> > > >> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > >> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE > >> > [Source] = 1") > >> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] > >> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) > >> > > >> > > >> > If Rst.RecordCount = 0 Then > >> > DoCmd.OpenForm ("frmError"), acNormal > >> > > >> > End If > >> > > >> > Set Rst = Nothing > >> > Set db = Nothing > >> > End If > >> > End Sub > >> > > >> > Unfortunately, the strsql is selecting all records instead of just the > >> > source = 1, can someone help? > >> > > >> > Thanks! > >> > > >

Re: Code Help "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 29.09.2006 21:06:00 Excuse me for jumping in, Bill, but it seems to me that the SQL statement that selects records from Get20Mile_SelQry is being assigned to the .SQL property of the QueryDef named 'Get20Mile_SelQuery'. After the assignment, the query will be trying to select from itself ... <quote> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] = 1") qdf.SQL = strSQL </quote> -- Brendan Reynolds Access MVP "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> wrote in message news:uDXlyMA5GHA.1012[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > Can you post the SQL for Get20Mile_SelQry? > > -- > Bill Mosca, MS Access MVP > > > <mnsebastian[ at ]msn.com> wrote in message > news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... >> Thank you for the reply, >> I get the error: >> >> Circular Reference by Get20Mile_SelQry >> >> >> Bill Mosca, MS Access MVP wrote: >>> You left out a line. >>> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >>> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >>> [Source] = >>> 1") >>> qdf.SQL = strSQL >>> >>> -- >>> Bill Mosca, MS Access MVP >>> >>> >>> <mnsebastian[ at ]msn.com> wrote in message >>> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... >>> >I have the following code: >>> > >>> > >>> > Private Sub cboAssetNumber_AfterUpdate() >>> > If Not IsNull(Me!cboAssetNumber) Then >>> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) >>> > Me!txtCity = Me!cboAssetNumber.Column(2) >>> > Me!txtState = Me!cboAssetNumber.Column(3) >>> > Me!txtZipCode = Me!cboAssetNumber.Column(4) >>> > >>> > Dim db As Database >>> > Dim Rst As DAO.Recordset >>> > Dim qdf As QueryDef >>> > Dim strSQL As String >>> > >>> > >>> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >>> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >>> > [Source] = 1") >>> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] >>> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) >>> > >>> > >>> > If Rst.RecordCount = 0 Then >>> > DoCmd.OpenForm ("frmError"), acNormal >>> > >>> > End If >>> > >>> > Set Rst = Nothing >>> > Set db = Nothing >>> > End If >>> > End Sub >>> > >>> > Unfortunately, the strsql is selecting all records instead of just the >>> > source = 1, can someone help? >>> > >>> > Thanks! >>> > >> > >

Re: Code Help mnsebastian[ at ]msn.com 29.09.2006 21:17:42 All I want is the following: rivate Sub cmdFilter_Click() Dim db As Database Dim Rst As DAO.Recordset Dim qdf As QueryDef Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") qdf.Parameters(0) = Forms![FrmMain]![txtZipCode] Set Rst = qdf.OpenRecordset(dbOpenDynaset) If Rst.RecordCount = 0 Then DoCmd.OpenForm ("frmError"), acNormal End If Set Rst = Nothing Set db = Nothing End Sub Where a field within the Get20Mile_SelQry (Source) is equal to P. I don't know how to incorporate the where clause into the above code. Again I appreciate your help! Brendan Reynolds wrote: [Quoted Text] > Excuse me for jumping in, Bill, but it seems to me that the SQL statement > that selects records from Get20Mile_SelQry is being assigned to the .SQL > property of the QueryDef named 'Get20Mile_SelQuery'. After the assignment, > the query will be trying to select from itself ... > > <quote> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] > = 1") > qdf.SQL = strSQL > </quote> > > -- > Brendan Reynolds > Access MVP > > "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> wrote in message > news:uDXlyMA5GHA.1012[ at ]TK2MSFTNGP05.phx.gbl... > > Can you post the SQL for Get20Mile_SelQry? > > > > -- > > Bill Mosca, MS Access MVP > > > > > > <mnsebastian[ at ]msn.com> wrote in message > > news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... > >> Thank you for the reply, > >> I get the error: > >> > >> Circular Reference by Get20Mile_SelQry > >> > >> > >> Bill Mosca, MS Access MVP wrote: > >>> You left out a line. > >>> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > >>> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE > >>> [Source] = > >>> 1") > >>> qdf.SQL = strSQL > >>> > >>> -- > >>> Bill Mosca, MS Access MVP > >>> > >>> > >>> <mnsebastian[ at ]msn.com> wrote in message > >>> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... > >>> >I have the following code: > >>> > > >>> > > >>> > Private Sub cboAssetNumber_AfterUpdate() > >>> > If Not IsNull(Me!cboAssetNumber) Then > >>> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) > >>> > Me!txtCity = Me!cboAssetNumber.Column(2) > >>> > Me!txtState = Me!cboAssetNumber.Column(3) > >>> > Me!txtZipCode = Me!cboAssetNumber.Column(4) > >>> > > >>> > Dim db As Database > >>> > Dim Rst As DAO.Recordset > >>> > Dim qdf As QueryDef > >>> > Dim strSQL As String > >>> > > >>> > > >>> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > >>> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE > >>> > [Source] = 1") > >>> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] > >>> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) > >>> > > >>> > > >>> > If Rst.RecordCount = 0 Then > >>> > DoCmd.OpenForm ("frmError"), acNormal > >>> > > >>> > End If > >>> > > >>> > Set Rst = Nothing > >>> > Set db = Nothing > >>> > End If > >>> > End Sub > >>> > > >>> > Unfortunately, the strsql is selecting all records instead of just the > >>> > source = 1, can someone help? > >>> > > >>> > Thanks! > >>> > > >> > > > >

Re: Code Help "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> 29.09.2006 21:42:54 Brendan You are absolutely right. I didn't check to see if the target query was the same as the one in the SQL statement. Thanks for catching that. -- Bill Mosca, MS Access MVP "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> wrote in message news:einITsA5GHA.932[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > > Excuse me for jumping in, Bill, but it seems to me that the SQL statement > that selects records from Get20Mile_SelQry is being assigned to the .SQL > property of the QueryDef named 'Get20Mile_SelQuery'. After the assignment, > the query will be trying to select from itself ... > > <quote> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE [Source] > = 1") > qdf.SQL = strSQL > </quote> > > -- > Brendan Reynolds > Access MVP > > "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> wrote in message > news:uDXlyMA5GHA.1012[ at ]TK2MSFTNGP05.phx.gbl... >> Can you post the SQL for Get20Mile_SelQry? >> >> -- >> Bill Mosca, MS Access MVP >> >> >> <mnsebastian[ at ]msn.com> wrote in message >> news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... >>> Thank you for the reply, >>> I get the error: >>> >>> Circular Reference by Get20Mile_SelQry >>> >>> >>> Bill Mosca, MS Access MVP wrote: >>>> You left out a line. >>>> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >>>> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >>>> [Source] = >>>> 1") >>>> qdf.SQL = strSQL >>>> >>>> -- >>>> Bill Mosca, MS Access MVP >>>> >>>> >>>> <mnsebastian[ at ]msn.com> wrote in message >>>> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... >>>> >I have the following code: >>>> > >>>> > >>>> > Private Sub cboAssetNumber_AfterUpdate() >>>> > If Not IsNull(Me!cboAssetNumber) Then >>>> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) >>>> > Me!txtCity = Me!cboAssetNumber.Column(2) >>>> > Me!txtState = Me!cboAssetNumber.Column(3) >>>> > Me!txtZipCode = Me!cboAssetNumber.Column(4) >>>> > >>>> > Dim db As Database >>>> > Dim Rst As DAO.Recordset >>>> > Dim qdf As QueryDef >>>> > Dim strSQL As String >>>> > >>>> > >>>> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >>>> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >>>> > [Source] = 1") >>>> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] >>>> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) >>>> > >>>> > >>>> > If Rst.RecordCount = 0 Then >>>> > DoCmd.OpenForm ("frmError"), acNormal >>>> > >>>> > End If >>>> > >>>> > Set Rst = Nothing >>>> > Set db = Nothing >>>> > End If >>>> > End Sub >>>> > >>>> > Unfortunately, the strsql is selecting all records instead of just >>>> > the >>>> > source = 1, can someone help? >>>> > >>>> > Thanks! >>>> > >>> >> >> > >

Re: Code Help "Brendan Reynolds" <brenreyn[ at ]discussions.microsoft.com> 30.09.2006 13:16:42 You could add a second parameter to the query for the 'Source' ... WHERE (... existing condition here ...) AND (Source = [Source?]) Then in your code you could assign a value to that parameter in the same way that you are already assigning a value to the existing parameter ... qdf.Parameters(0) = Forms![FrmMain]![txtZipCode] '<---existing code qdf.Parameters(1) = "P" '<---new code On the other hand, looking at the SQL of the UNION query you posted elsewhere in this thread, it appears that Source will always be equal to 'P' for all the records returned by the first SELECT clause, and always be equal to 'S' for all the records returned by the second SELECT clause, so possibly an even simpler and more efficient solution might be to save the first SELECT clause (everything up to but not including the keyword 'UNION') as a new query, and use that query as the source for your recordset. -- Brendan Reynolds Access MVP <mnsebastian[ at ]msn.com> wrote in message news:1159564662.814159.301330[ at ]h48g2000cwc.googlegroups.com... [Quoted Text] > All I want is the following: > > rivate Sub cmdFilter_Click() > Dim db As Database > Dim Rst As DAO.Recordset > Dim qdf As QueryDef > > > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") > qdf.Parameters(0) = Forms![FrmMain]![txtZipCode] > Set Rst = qdf.OpenRecordset(dbOpenDynaset) > > > If Rst.RecordCount = 0 Then > DoCmd.OpenForm ("frmError"), acNormal > > End If > > Set Rst = Nothing > Set db = Nothing > End Sub > > Where a field within the Get20Mile_SelQry (Source) is equal to P. > > I don't know how to incorporate the where clause into the above code. > > Again I appreciate your help! > Brendan Reynolds wrote: >> Excuse me for jumping in, Bill, but it seems to me that the SQL statement >> that selects records from Get20Mile_SelQry is being assigned to the .SQL >> property of the QueryDef named 'Get20Mile_SelQuery'. After the >> assignment, >> the query will be trying to select from itself ... >> >> <quote> >> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >> [Source] >> = 1") >> qdf.SQL = strSQL >> </quote> >> >> -- >> Brendan Reynolds >> Access MVP >> >> "Bill Mosca, MS Access MVP" <nospam[ at ]domain.com> wrote in message >> news:uDXlyMA5GHA.1012[ at ]TK2MSFTNGP05.phx.gbl... >> > Can you post the SQL for Get20Mile_SelQry? >> > >> > -- >> > Bill Mosca, MS Access MVP >> > >> > >> > <mnsebastian[ at ]msn.com> wrote in message >> > news:1159557650.125060.185420[ at ]i3g2000cwc.googlegroups.com... >> >> Thank you for the reply, >> >> I get the error: >> >> >> >> Circular Reference by Get20Mile_SelQry >> >> >> >> >> >> Bill Mosca, MS Access MVP wrote: >> >>> You left out a line. >> >>> Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >> >>> strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >> >>> [Source] = >> >>> 1") >> >>> qdf.SQL = strSQL >> >>> >> >>> -- >> >>> Bill Mosca, MS Access MVP >> >>> >> >>> >> >>> <mnsebastian[ at ]msn.com> wrote in message >> >>> news:1159553170.349695.211150[ at ]m73g2000cwd.googlegroups.com... >> >>> >I have the following code: >> >>> > >> >>> > >> >>> > Private Sub cboAssetNumber_AfterUpdate() >> >>> > If Not IsNull(Me!cboAssetNumber) Then >> >>> > Me!txtAddress1 = Me!cboAssetNumber.Column(1) >> >>> > Me!txtCity = Me!cboAssetNumber.Column(2) >> >>> > Me!txtState = Me!cboAssetNumber.Column(3) >> >>> > Me!txtZipCode = Me!cboAssetNumber.Column(4) >> >>> > >> >>> > Dim db As Database >> >>> > Dim Rst As DAO.Recordset >> >>> > Dim qdf As QueryDef >> >>> > Dim strSQL As String >> >>> > >> >>> > >> >>> > Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry") >> >>> > strSQL = ("SELECT Get20mile_selqry.* FROM Get20Mile_SelQry WHERE >> >>> > [Source] = 1") >> >>> > qdf.Parameters(0) = Forms![frmMain]![txtZipCode] >> >>> > Set Rst = qdf.OpenRecordset(dbOpenDynaset) >> >>> > >> >>> > >> >>> > If Rst.RecordCount = 0 Then >> >>> > DoCmd.OpenForm ("frmError"), acNormal >> >>> > >> >>> > End If >> >>> > >> >>> > Set Rst = Nothing >> >>> > Set db = Nothing >> >>> > End If >> >>> > End Sub >> >>> > >> >>> > Unfortunately, the strsql is selecting all records instead of just >> >>> > the >> >>> > source = 1, can someone help? >> >>> > >> >>> > Thanks! >> >>> > >> >> >> > >> > >