Jim Pockmire said:See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] & _
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Ken Snell (MVP) said:Post the code that you're using, and post the SQL statement of the query
if it's not being built in the code.
Ken Snell (MVP) said:What is the SQL statement of the "SecRptEnabled_part3" query?
--
Ken Snell
<MS ACCESS MVP>
Jim Pockmire said:See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] & _
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Ken Snell (MVP) said:Post the code that you're using, and post the SQL statement of the query
if it's not being built in the code.
--
Ken Snell
<MS ACCESS MVP>
I am trying to loop through code and pass a value to a field on a form.
The value in this field is then used as criteria in a query. The form
field is populated yet the query does not seem to recognize the value on
the form and returns "0" results.
Ken Snell (MVP) said:What is the SQL statement of the "SecRptEnabled_part3" query?
--
Ken Snell
<MS ACCESS MVP>
Jim Pockmire said:See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] & _
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Ken Snell (MVP) said:Post the code that you're using, and post the SQL statement of the query
if it's not being built in the code.
--
Ken Snell
<MS ACCESS MVP>
I am trying to loop through code and pass a value to a field on a form.
The value in this field is then used as criteria in a query. The form
field is populated yet the query does not seem to recognize the value on
the form and returns "0" results.
Ken Snell (MVP) said:What is the SQL statement of the "SecRptEnabled_part3" query?
--
Ken Snell
<MS ACCESS MVP>
Jim Pockmire said:See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] & _
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Ken Snell (MVP) said:Post the code that you're using, and post the SQL statement of the query
if it's not being built in the code.
--
Ken Snell
<MS ACCESS MVP>
I am trying to loop through code and pass a value to a field on a form.
The value in this field is then used as criteria in a query. The form
field is populated yet the query does not seem to recognize the value on
the form and returns "0" results.
Jim Pockmire said:..hope this helps - contains extra code.
INSERT INTO SecRptEnabledTable ( USER_ID, ReportModule, [Sec Type],
ReportsEnabled, [Module Desc], [Sec Type Desc], RptCode1, Desc1 )
SELECT SecRptEnabled_part2.USER_ID, SecRptEnabled_part2.Module AS
ReportModule, SecRptEnabled_part2.[Sec Type],
SecRptEnabled_part2.ReportsEnabled, [Menu Module].[Module Desc], [Menu Sec
Type].[Sec Type Desc], SecRptEnabled_part2.RptCode1,
SecRptEnabled_part2.Desc1
FROM (SecRptEnabled_part2 LEFT JOIN [Menu Sec Type] ON
SecRptEnabled_part2.[Sec Type] = [Menu Sec Type].[Sec Type]) LEFT JOIN
[Menu Module] ON SecRptEnabled_part2.Module = [Menu Module].Module
GROUP BY SecRptEnabled_part2.USER_ID, SecRptEnabled_part2.Module,
SecRptEnabled_part2.[Sec Type], SecRptEnabled_part2.ReportsEnabled, [Menu
Module].[Module Desc], [Menu Sec Type].[Sec Type Desc],
SecRptEnabled_part2.RptCode1, SecRptEnabled_part2.Desc1
HAVING (((SecRptEnabled_part2.[Sec Type]) Not In ("S","M")) AND (([Menu
Module].[Module Desc]) Is Not Null))
WITH OWNERACCESS OPTION;
Ken Snell (MVP) said:What is the SQL statement of the "SecRptEnabled_part3" query?
--
Ken Snell
<MS ACCESS MVP>
Jim Pockmire said:See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] &
_
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Post the code that you're using, and post the SQL statement of the
query if it's not being built in the code.
--
Ken Snell
<MS ACCESS MVP>
I am trying to loop through code and pass a value to a field on a form.
The value in this field is then used as criteria in a query. The form
field is populated yet the query does not seem to recognize the value
on the form and returns "0" results.
Ken Snell (MVP) said:The append query that you're running does not read the value from the
form's control at all... so there is no way for the query to use the value
from the form as part of its operation. This query uses only values from
tables, and your code that writes a value into the form's USER_ID control
does not save the data to a table where this query can read it. What is
the append query supposed to do with the form's control's value if it were
reading it?
If you can tell us what the USER_ID value is for this query's purpose, we
can suggest an alternative way of doing what you seek.
Also, I note that you're using Module as the name of a field in a table
(see your append query). It and many other words are reserved words in
ACCESS and should not be used for field names, etc. See these Knowledge
Base articles for more information about reserved words and characters
that should not be used:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
See this site for code that allows you to validate your names as not being
VBA keywords:
basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
--
Ken Snell
<MS ACCESS MVP>
Jim Pockmire said:..hope this helps - contains extra code.
INSERT INTO SecRptEnabledTable ( USER_ID, ReportModule, [Sec Type],
ReportsEnabled, [Module Desc], [Sec Type Desc], RptCode1, Desc1 )
SELECT SecRptEnabled_part2.USER_ID, SecRptEnabled_part2.Module AS
ReportModule, SecRptEnabled_part2.[Sec Type],
SecRptEnabled_part2.ReportsEnabled, [Menu Module].[Module Desc], [Menu
Sec Type].[Sec Type Desc], SecRptEnabled_part2.RptCode1,
SecRptEnabled_part2.Desc1
FROM (SecRptEnabled_part2 LEFT JOIN [Menu Sec Type] ON
SecRptEnabled_part2.[Sec Type] = [Menu Sec Type].[Sec Type]) LEFT JOIN
[Menu Module] ON SecRptEnabled_part2.Module = [Menu Module].Module
GROUP BY SecRptEnabled_part2.USER_ID, SecRptEnabled_part2.Module,
SecRptEnabled_part2.[Sec Type], SecRptEnabled_part2.ReportsEnabled, [Menu
Module].[Module Desc], [Menu Sec Type].[Sec Type Desc],
SecRptEnabled_part2.RptCode1, SecRptEnabled_part2.Desc1
HAVING (((SecRptEnabled_part2.[Sec Type]) Not In ("S","M")) AND (([Menu
Module].[Module Desc]) Is Not Null))
WITH OWNERACCESS OPTION;
Ken Snell (MVP) said:What is the SQL statement of the "SecRptEnabled_part3" query?
--
Ken Snell
<MS ACCESS MVP>
See code below.
'This creates a recordset of users selected from AdvanSec RptsFrm
strSQL1 = "SELECT [USER_CODE] FROM SYSADM_APP_SECURITY " & _
"WHERE ([USER_CODE] >= """ & [Forms]![AdvanSec RptsFrm]![FromUser] &
_
""" And [USER_CODE] <= """ & [Forms]![AdvanSec RptsFrm]![ToUser] & _
""") WITH OWNERACCESS OPTION;"
'This loops through the user recordset appending _
each users reports to tblSecurityReportEnabled
Set rsUserList = db.OpenRecordset(strSQL1)
Do Until rsUserList.EOF
'This sets the value on the form
Forms![AdvanSec RptsFrm]![USER_ID] = Trim(rsUserList!USER_CODE)
'This query uses the value on the form
DoCmd.OpenQuery "SecRptEnabled_part3" 'Append query
rsUserList.MoveNext
Loop
GoSub PrintReport
Exit Function
Post the code that you're using, and post the SQL statement of the
query if it's not being built in the code.
--
Ken Snell
<MS ACCESS MVP>
I am trying to loop through code and pass a value to a field on a
form. The value in this field is then used as criteria in a query. The
form field is populated yet the query does not seem to recognize the
value on the form and returns "0" results.