Dynamically loading another record from table

[parth00]

Hi All,
Do any one know how to load new record dynamically in the form from table rather then going sequentially?

I have Employee Info Form, it shows all relevant information for given Employee.
(See attached Form image). Now when I manually enter another valid employee ID, Before_Update events pulls record from Employee table, which works fine.

Some how none of the buttons works (Next and Prev) after that. When clicked it gives following error Error : You can’t go to specified record.
My assumption is record-set still points to old record and which causes an error

If you want to review Before_Update code please follow this link.. http://www.access-programmers.co.uk/forums/showthread.php?t=95897

I am even open to any alternate solution …please comment

Thanks in advance

 

[KeithIT]

How does your Load_New_Employee_Data function work? Are you working on a filter? It sounds like you are hitting the end of the file, which may be a simple solution depending on your code. Can you post more info here?

 

[parth00]

Here We go...


Private Function Load_New_Employee_Data()

Dim SSQL As String
Dim Employee_ID As String
Dim db As Database
Dim rs As Recordset

Employee_ID = Employee_ID_Text
SSQL = "Select Employee_Last_Name, Employee_First_Name, Resource_Type, Employee_Title, Home_Office_City,"
SSQL = SSQL & " Home_Office_State, Home_Office_Region, Join_Date, Leave_Date, Comments, Employee_Status"
SSQL = SSQL & " From Employee_Info where Employee_ID = " & Employee_ID & ";"

Set db = CurrentDb()
Set rs = db.OpenRecordSet(SSQL)

'Perf_Mgr_ID = Rs1.fields("Employee_ID")
Me.First_Name_Text = rs.Fields("Employee_Last_Name")
Me.Last_Name_Text = rs.Fields("Employee_First_Name")
Me.Title_Text = rs.Fields("Resource_Type")
Me.Resource_Type_Text = rs.Fields("Employee_Title")
Me.Home_Office_City_Text = rs.Fields("Home_Office_City")
Me.Home_Office_Region_Text = rs.Fields("Home_Office_Region")
Me.Home_Office_State_Text = rs.Fields("Home_Office_State")
Me.Join_Date_Text = rs.Fields("Join_Date")
Me.Leave_Date_Text = rs.Fields("Leave_Date")
Me.Comments = rs.Fields("Comments")
Me.Status_Text = rs.Fields("Employee_Status")

Load_Perf_Manager

End Function

 

[KeithIT]

Your SQL statement just pulls data for the one record where employee ID matches. You want to use findfirst and bookmark to do what you are thinking of. The problem isn't your original recordset, its that your SQL statement will only display the one record that matches the criteria, which means when you try to go next or previous you hit EOF or BOF respectively.

This is akin to using the "filter" statement, where only a limited number of records (in this case only one) are shown.

Try using a recordset clone and setting a bookmark, or, alternatively, use a SQL statement without a "WHERE" clause. Then use the .seek method of an ADO recordset to move to the record you want.

Check out this site for more info on the .seek method: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthseek.asp?frame=true