Variable from Form to Report

  • Thread starter Thread starter TA
  • Start date Start date
T

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
 
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
 
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
Click to expand...
tblEmployee.year = '" & batch &
 
Back
Top