|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 18th, 2006, 04:04 PM
posted to microsoft.public.access.forms
|
|||
|
|||
How Do You Pass a Parameter Value from Existing Form?
New subscriber here, but over in comp.database.ms-access, Gord set me
up with a cool SQL query that puts my student attendance records in a calendar format in Access 2003. I have a simple entry form that allows students to find their name on a pull-down list, then select Student or Instructor (it verifies if they choose Instructor), then they click a button "Submit" to enter their data and move to the next record for another student. Although they look up their name as [First Name] & " " & [Last Name], it enters their [ID Number] in the attendance table. This structure is: Record No, ID Number, Date, and Type. It pulls Date from the current date. What I want is to have the report that I based on the below query run when the user clicks "Submit", and I want to pass the student's name ([First Name] & " " & [Last Name]) to the Parameter [Enter Name] so that they won't have to enter it twice. My problem is that the form I use enters [ID Number] in the attendance table, instead of [Name]. PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble; TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND ((DatePart("yyyy",[Date]))=[Enter Year])) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28*,29,30,31); Any tip would be helpful! 
|
|
#2
December 18th, 2006, 04:22 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
How Do You Pass a Parameter Value from Existing Form?
The syntax for using a form control as a parameter in a query is:
= Forms!YourFormName!YourControlName Substitute that, with appropriate changes, in the place of [Enter Name] in your criteria. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message ps.com... New subscriber here, but over in comp.database.ms-access, Gord set me up with a cool SQL query that puts my student attendance records in a calendar format in Access 2003. I have a simple entry form that allows students to find their name on a pull-down list, then select Student or Instructor (it verifies if they choose Instructor), then they click a button "Submit" to enter their data and move to the next record for another student. Although they look up their name as [First Name] & " " & [Last Name], it enters their [ID Number] in the attendance table. This structure is: Record No, ID Number, Date, and Type. It pulls Date from the current date. What I want is to have the report that I based on the below query run when the user clicks "Submit", and I want to pass the student's name ([First Name] & " " & [Last Name]) to the Parameter [Enter Name] so that they won't have to enter it twice. My problem is that the form I use enters [ID Number] in the attendance table, instead of [Name]. PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble; TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND ((DatePart("yyyy",[Date]))=[Enter Year])) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28*,29,30,31); Any tip would be helpful!
|
|
#3
December 18th, 2006, 07:33 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
How Do You Pass a Parameter Value from Existing Form?
I tried that. Running the query does not pull up the appropriate form.
It simply asks me for the [ID Number]. This is the query that I modified to look for the form's input. Maybe someone can spot an error. TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number]=Attendance_tbl.[ID Number] WHERE (((Member_List_tbl![ID Number])=Forms.Attendance_Entry_Temp_frm![ID Number]) And ((DatePart("yyyy",[Date]))=Year(Date()))) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31); I want this query to execute from another control on the same form, after the Name is selected. I'm not having much luck at that, nor in my other task, totalling up how many days each person has per month as a student (Type="S"). Any help would be wonderful. Lynn Trapp wrote: The syntax for using a form control as a parameter in a query is: = Forms!YourFormName!YourControlName Substitute that, with appropriate changes, in the place of [Enter Name] in your criteria. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message ps.com... New subscriber here, but over in comp.database.ms-access, Gord set me up with a cool SQL query that puts my student attendance records in a calendar format in Access 2003. I have a simple entry form that allows students to find their name on a pull-down list, then select Student or Instructor (it verifies if they choose Instructor), then they click a button "Submit" to enter their data and move to the next record for another student. Although they look up their name as [First Name] & " " & [Last Name], it enters their [ID Number] in the attendance table. This structure is: Record No, ID Number, Date, and Type. It pulls Date from the current date. What I want is to have the report that I based on the below query run when the user clicks "Submit", and I want to pass the student's name ([First Name] & " " & [Last Name]) to the Parameter [Enter Name] so that they won't have to enter it twice. My problem is that the form I use enters [ID Number] in the attendance table, instead of [Name]. PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble; TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND ((DatePart("yyyy",[Date]))=[Enter Year])) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28*,29,30,31); Any tip would be helpful!
|
|
#4
December 18th, 2006, 08:08 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
How Do You Pass a Parameter Value from Existing Form?
You need to put a reference to Forms!Attendance_Entry_Temp_frm![ID Number]
in the Parameters section of the query. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message oups.com... I tried that. Running the query does not pull up the appropriate form. It simply asks me for the [ID Number]. This is the query that I modified to look for the form's input. Maybe someone can spot an error. TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number]=Attendance_tbl.[ID Number] WHERE (((Member_List_tbl![ID Number])=Forms.Attendance_Entry_Temp_frm![ID Number]) And ((DatePart("yyyy",[Date]))=Year(Date()))) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31); I want this query to execute from another control on the same form, after the Name is selected. I'm not having much luck at that, nor in my other task, totalling up how many days each person has per month as a student (Type="S"). Any help would be wonderful. Lynn Trapp wrote: The syntax for using a form control as a parameter in a query is: = Forms!YourFormName!YourControlName Substitute that, with appropriate changes, in the place of [Enter Name] in your criteria. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message ps.com... New subscriber here, but over in comp.database.ms-access, Gord set me up with a cool SQL query that puts my student attendance records in a calendar format in Access 2003. I have a simple entry form that allows students to find their name on a pull-down list, then select Student or Instructor (it verifies if they choose Instructor), then they click a button "Submit" to enter their data and move to the next record for another student. Although they look up their name as [First Name] & " " & [Last Name], it enters their [ID Number] in the attendance table. This structure is: Record No, ID Number, Date, and Type. It pulls Date from the current date. What I want is to have the report that I based on the below query run when the user clicks "Submit", and I want to pass the student's name ([First Name] & " " & [Last Name]) to the Parameter [Enter Name] so that they won't have to enter it twice. My problem is that the form I use enters [ID Number] in the attendance table, instead of [Name]. PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble; TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND ((DatePart("yyyy",[Date]))=[Enter Year])) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28*,29,30,31); Any tip would be helpful!
|
|
#5
December 19th, 2006, 08:19 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
How Do You Pass a Parameter Value from Existing Form?
I had the reference already as below. Running this query causes the
[ID Number] to be prompted for. It does not attempt to retrieve it from the form. Maybe I'm trying to do this backwards. I want my form to cause a report based on this query to run after the [ID Number] is selected. PARAMETERS [Forms]![Attendance_Entry_frm]![ID Number] IEEEDouble; TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name, Count(Attendance_tbl.[Mem Type]) AS Total FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([Member_List_tbl]![ID Number])=[Forms].[Attendance_Entry_frm]![ID Number]) AND ((DatePart("yyyy",[Date]))=Year(Date()))) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31); Lynn Trapp wrote: You need to put a reference to Forms!Attendance_Entry_Temp_frm![ID Number] in the Parameters section of the query. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message oups.com... I tried that. Running the query does not pull up the appropriate form. It simply asks me for the [ID Number]. This is the query that I modified to look for the form's input. Maybe someone can spot an error. TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number]=Attendance_tbl.[ID Number] WHERE (((Member_List_tbl![ID Number])=Forms.Attendance_Entry_Temp_frm![ID Number]) And ((DatePart("yyyy",[Date]))=Year(Date()))) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31); I want this query to execute from another control on the same form, after the Name is selected. I'm not having much luck at that, nor in my other task, totalling up how many days each person has per month as a student (Type="S"). Any help would be wonderful. Lynn Trapp wrote: The syntax for using a form control as a parameter in a query is: = Forms!YourFormName!YourControlName Substitute that, with appropriate changes, in the place of [Enter Name] in your criteria. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "ShadesOfGrey" wrote in message ps.com... New subscriber here, but over in comp.database.ms-access, Gord set me up with a cool SQL query that puts my student attendance records in a calendar format in Access 2003. I have a simple entry form that allows students to find their name on a pull-down list, then select Student or Instructor (it verifies if they choose Instructor), then they click a button "Submit" to enter their data and move to the next record for another student. Although they look up their name as [First Name] & " " & [Last Name], it enters their [ID Number] in the attendance table. This structure is: Record No, ID Number, Date, and Type. It pulls Date from the current date. What I want is to have the report that I based on the below query run when the user clicks "Submit", and I want to pass the student's name ([First Name] & " " & [Last Name]) to the Parameter [Enter Name] so that they won't have to enter it twice. My problem is that the form I use enters [ID Number] in the attendance table, instead of [Name]. PARAMETERS [Enter Name] Text ( 255 ), [Enter Year] IEEEDouble; TRANSFORM First(IIf([Mem Type]=1,"S","I")) AS Type SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] & " " & [Last Name] AS Name FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID Number] = Attendance_tbl.[ID Number] WHERE ((([First Name] & " " & [Last Name])=[Enter Name]) AND ((DatePart("yyyy",[Date]))=[Enter Year])) GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name] PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28*,29,30,31); Any tip would be helpful!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
