Rajesh-
You cannot assign a SELECT statement to an integer variable - that should
give you an error. You must either open a recordset or use DLookup to fetch
the value.
LeaveYearID = DLookup("ID", "tblLeaveYearMaster", _
"Year = " & Me.LeaveYear)
Also, you have declared LeaveYearID as a local variable. The above should
assign the ID of the record to that variable, but the variable is destroyed
as soon as the Sub ends. Do you really mean to set a control on the form?
If so, don't declare the variable, and reference the control as
Me.LeaveYearID.
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of RAJESH DESAI
Sent: Thursday, January 05, 2006 12:05 PM
To: Access1; Access2; Access3
Subject: [ms_access] Help with select statement
Hi,
Please refer the following code. I have put this code in the code window
of the form which is bound to tblEmployeeMaster.
What I basically want is, from the year entered in the form by the user, I
want to pick the YearID from tblLeaveYearMaster. "ID" is a PK of data type
AutoNumber and Year is of data type long integer.
On running the following code, no matter what the year is selected in the
form, it invariably store value 0 in LeaveYearID variable.
Please guide me.
Private Sub Form_AfterUpdate()
LeaveYearID As Integer
Dim dbs As Database
Set dbs = CurrentDb
Months = NoOfMonths(Fdate, LDate, DOJ, DOD)
LeaveYearID = "Select tblLeaveYearMaster.ID from tblLeaveYearMaster " _
& " WHERE tblLeaveYearMaster.Year = " & LeaveYear
dbs.Close
End Sub
Regards,
Rajesh Desai
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
