How Do You Pass a Parameter Value from Existing Form?

S

ShadesOfGrey

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,20,21,22,23,24,25,26,27,28­,29,30,31);


Any tip would be helpful!
 
L

Lynn Trapp

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


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,20,21,22,23,24,25,26,27,28­,29,30,31);


Any tip would be helpful!
 
S

ShadesOfGrey

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,20,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 said:
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


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,20,21,22,23,24,25,26,27,28­,29,30,31);


Any tip would be helpful!
Click to expand...
 
L

Lynn Trapp

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


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,20,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 said:
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


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,20,21,22,23,24,25,26,27,28­,29,30,31);


Any tip would be helpful!
Click to expand...
 
S

ShadesOfGrey

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,20,21,22,23,24,25,26,27,28,29,30,31);

Lynn said:
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


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,20,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 said:
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


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,20,21,22,23,24,25,26,27,28­,29,30,31);


Any tip would be helpful!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I add a total column to this query? 3
Help -- Is my there any way to not count duplicate values here? 5
recall of infor on a form 2
Continuous Form data entry problems 3
Should I use a query in a form to do this 1
How to Pass Option Group values in OpenArg 5
How to compare and combine information from 2 different excelworksheets (VLOOKUP ? Pivot Table ?) 1
can you combine columns on a combo box? 1

Top