473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Passing Parameter Value from Existing Query

Hello again. Thanks to Gord, I have this cool query that puts my
student attendance records in a calendar format in Access 2003.

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);

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 <Datefrom the current
date.

What I want is to have a report based on the above query run when they
click "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.

Any tips on how to do this?

Dec 17 '06 #1
1 2086
Hi,

If this query is used in in the SQL-property of a DAO-recordset you
could use the parameters-collection to store the entered name after
opening but before using the recordset. Another option is to creating
the sql-string including the parameters-clause containing the entered
name into the SQL-syntax before using the SQL. If you use a stored
query only the first method prevents recompilation of the SQL-query and
so should be more efficient.

Marc

ShadesOfGrey schreef:
Hello again. Thanks to Gord, I have this cool query that puts my
student attendance records in a calendar format in Access 2003.

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);

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 <Datefrom the current
date.

What I want is to have a report based on the above query run when they
click "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.

Any tips on how to do this?
Dec 21 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: kazack | last post by:
I am a little confused with code I am looking at. My c++ book does not go into passing a structure to a function so I pulled out a c book which does. and I do not understand the prototype verses...
3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
10
by: Resant | last post by:
I have a query : Exec 'Select * From Receiving Where Code In (' + @pCode + ')' @pCode will contain more than one string parameter, eg : A1, A2, A3 How can i write that parameters, I try use :...
3
by: MX1 | last post by:
I'm ready to pull the hair out of my head. I have a query with a couple of parameters that I want to get from combo boxes on a form. One parameter is a date with a dynamically calculated year and...
8
by: Dennis Myrén | last post by:
I have these tiny classes, implementing an interface through which their method Render ( CosWriter writer ) ; is called. Given a specific context, there are potentially a lot of such objects,...
3
by: Robert | last post by:
What is the best way to pass a parameter to an ObjectDataSource. I am able to add a new parameter to the SelectParameters, but I would like to just assign a value to an existing parmeter at...
4
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx...
1
by: Deecrypt | last post by:
Hi, Passing a string parameter to a webservice to get the dataset generated as the result, seems to give me an error suggesting that no parameter is being passed. However it works if I explicitly...
6
by: jej1216 | last post by:
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.