Open form to blank record so I can search

[BrookieOU]

I have a table where we have all of our candidate and employee information.
I have already created a form to enter the information needed for the
candidates. I would like to create a form to enter New Hire Information once
we hire them.

I have created a query that has only the new hire fields I want (name,
address, pay rate, etc), which is information we don't have when the are only
candidates. However, I want to be able to open the form on a blank record
and be able to search by name for a candidate (the query is set to pull
someone to the new hire query once a hire date is entered), so the
information we do know (address, phone number) are automatically entered.
That way they can be verified and the new information entered.

Did I make sense?

 

[Ken Sheridan]

Here are a couple of possible methods:

1. Set the form's DataEntry property to True (Yes) and add an unbound combo
box to the form set up as follows:

RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM
lEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In the combo box's AfterUpdate event procedure put:

Dim rst As Object
Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
Me.DataEntry = False
Set rst = Me.Recordset.Clone

With rst
.FindFirst "EmployeeID = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End if

Ken Sheridan
Stafford, England

2. The above method will navigate to the selected employee, but other
employee records will be available and the user could navigate to them via
the navigation buttons or keyboard. The second method does the same, using
the same unbound combo box, but makes only the selected employee record
available until the user selects another via the combo box. In this case set
the form's DataEntry property to False (No) and put the following in the
form's open event procedure:

Me.Filter = "EmloyeeID = 0"
Me.FilterOn = True

The code for the combo box's AfterUpdate event procedure in this case is:

Dim ctrl As Control
Dim strFilter As String

Set ctrl = Me.ActiveControl

strFilter = "EmployeeID = " & Nz(ctrl, 0)
Me.Filter = strFilter

Note that for both the above methods the table's primary key EmployeeID
column must be returned by the query to which the form is bound. No control
bound to it is necessary on the form, however.

Ken Sheridan
Stafford, England