Hi I am trying to selected a page in a control tab (from another) but also set the record source at the same time. The following code will select the page and find the record that I want:
Dim frm As Form
If Not Me.NewRecord Then
Set frm = Forms!FormJobDescriptions!FormStaffLookup.Form
frm.Recordset.FindFirst "[Pay Number]='" & Me![txt3] & "'"
Forms!FormJobDescriptions!TabCtl0 = 0
Set frm = Nothing
End If
But as I have the form set to open with the records blank with this recordsource:
SELECT StaffList.Group, [forename] & ' ' & [surname] AS EmployeeName, StaffList.[Pay Number], StaffList.[Job Title/Description], StaffList.Grade, StaffList.Discipline, StaffList.Fincode_Location, StaffList.[Annual Salary], StaffList.[Date Left]
FROM StaffList
WHERE (((1)=(2)));
the form opens up blank.
The Idea behind the form is for the user to search for a staff member by pay number, so in the after update of "text1" I have:
Me.RecordSource = "SELECT StaffList.Group, [forename] & ' ' & [surname] AS EmployeeName, StaffList.[Pay Number], StaffList.[Job Title/Description], StaffList.Grade, StaffList.Discipline, StaffList.Fincode_Location, StaffList.[Annual Salary], StaffList.[Date Left] " & _
"FROM StaffList "
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
What I need is a combination of the record source of "text1" and the finding the records of the first bit of code but none of the things I have tried so far have worked. Can anyone give me any help or advice with this matter.
Dim frm As Form
If Not Me.NewRecord Then
Set frm = Forms!FormJobDescriptions!FormStaffLookup.Form
frm.Recordset.FindFirst "[Pay Number]='" & Me![txt3] & "'"
Forms!FormJobDescriptions!TabCtl0 = 0
Set frm = Nothing
End If
But as I have the form set to open with the records blank with this recordsource:
SELECT StaffList.Group, [forename] & ' ' & [surname] AS EmployeeName, StaffList.[Pay Number], StaffList.[Job Title/Description], StaffList.Grade, StaffList.Discipline, StaffList.Fincode_Location, StaffList.[Annual Salary], StaffList.[Date Left]
FROM StaffList
WHERE (((1)=(2)));
the form opens up blank.
The Idea behind the form is for the user to search for a staff member by pay number, so in the after update of "text1" I have:
Me.RecordSource = "SELECT StaffList.Group, [forename] & ' ' & [surname] AS EmployeeName, StaffList.[Pay Number], StaffList.[Job Title/Description], StaffList.Grade, StaffList.Discipline, StaffList.Fincode_Location, StaffList.[Annual Salary], StaffList.[Date Left] " & _
"FROM StaffList "
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
What I need is a combination of the record source of "text1" and the finding the records of the first bit of code but none of the things I have tried so far have worked. Can anyone give me any help or advice with this matter.
