Variable from Form to Report

[TA]

Hi,
I am a newbie with coding for MS Access Please help.
I create a form have 2 button: OK & Cancel, and 1 list box
contain year 1990,1991,...,2010 (I assign var is batch)
If the user select year 2003 AND click OK button.
How do I call a procedure or subprogram to do like this:
"Select * from tblEmployee where tblEmployee.year=batch
insert into tblTempReport (batch,id,sex,code)
values (tblEmployee.year,tblEmployee.id,tblEmployee.sex,
tblEmployee.code)"
This code will be in Modules (?) How can I do this.
Thank you in advance,
TA

 

[Newbie]

Not quite sure what it is but assuming I have understood correctly

i.e Select records from a table where year = batch,
insert these records into a temporary table

1. Create a recordset form the main table
2. Create an empty recordset based on the Temp table and add records to it

eg (Using DAO)

Dim rs as DAO.recordset
Dim rsTemp as DAO.recordset
Dim strSQL as String
Dim lfields as long
Dim lindex as long

'Empty the temp table
docmd.runSQL "DELETE * FROM tblTempReport"

strSQL = "Select * from tblEmployee where tblEmployee.year = '" & batch &
"'"

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.recordcount > 0 then
Set rsTemp = CurrentDB.OpenRecordset("tblTempReport")
Do Until rs.EOF
rsTemp.AddNew
lfields = rs.Fields.Count - 1
For lindex = 0 To lfields
rsTemp.Fields(lindex) = rs.Fields(lindex)
Next
rsTemp.Update
rs.MoveNext
Loop
Else
Msgbox "No records match the criteria"
End If


Hope this helps

Ali

 

[TA]

Hi ALi,
Yes it is help me a lot but where cann I put this code in?
Is it in CmdOK.Click event?
Thanks,
MyN

-----Original Message-----
Not quite sure what it is but assuming I have understood correctly

i.e Select records from a table where year = batch,
insert these records into a temporary table

1. Create a recordset form the main table
2. Create an empty recordset based on the Temp table and add records to it

eg (Using DAO)

Dim rs as DAO.recordset
Dim rsTemp as DAO.recordset
Dim strSQL as String
Dim lfields as long
Dim lindex as long

'Empty the temp table
docmd.runSQL "DELETE * FROM tblTempReport"

strSQL = "Select * from tblEmployee where

tblEmployee.year = '" & batch &

 

[Newbie]

You can put this code in the OnClick event of the OK command button

HTH

Ali