drop down list restrict for new records

[Maggs]

Hi
I have a time sheet form associated with employees. The form uses a drop down list to select the employee.
I want to the ability to hide old staff in the staff field for the time sheet but still have their name displayed in old time sheet records for when they were employed.

When I change the query for the drop down list to only show current staff it is fine for the new timesheet records but any records associated with the old staff display the staff name as blank.

I hope this makes sense.
Thanks

 

[isladogs]

You could add a Boolean field Active to the table set to true for current employees and false when they leave. Then filter for Active=True

Or if the table has a field LeavingDate, filter by LeavinDate is Null

 

[Galaxiom]

Test for NewRecord property in the Current Event and, if True, change the RowSource of the listbox to show all the records and if False, the filtered records, using the one of the technique outlined by ridders.

 

[Maggs]

I will try what you have suggested Galaxiom as I think this is exactly what I am looking for

 

[Maggs]

Hi Ridders I already have a boolean field called archive but I the LeavingDate is a nice touch that I could also use.
Thanks

 

[Galaxiom]

Hi Ridders I already have a boolean field called archive but I the LeavingDate is a nice touch that I could also use.
Thanks

If you have a field for LeavingDate then an Archive field is a duplication of the information. As such that makes the design a breach of Normalization so it should not be stored.

 

[isladogs]

If you have a field for LeavingDate then an Archive field is a duplication of the information. As such that makes the design a breach of Normalization so it should not be stored.

True except for the scenario where someone leaves & returns in which case Archive=False but LeavingDate stays as a date value.
If that isn't relevant , use one or other field ONLY

Test for NewRecord property in the Current Event and, if True, change the RowSource of the listbox to show all the records and if False, the filtered records, using the one of the technique outlined by ridders.

Galaxiom - not sure why you added the Form_Current event code. It seems superfluous to me. Can you enlighten me?

 

[Galaxiom]

Galaxiom - not sure why you added the Form_Current event code. It seems superfluous to me. Can you enlighten me?

Maggs wants the list limited to current employees when new records are entered but still display the archived employees on old records. This can be done by testing for the NewRecord. Otherwise, unless I am mistaken, with LimitToList and only current employees in the RowSource, the old records for archived employees won't display properly.

An alternative would be to use the Form BeforeInsert event to change the listbox's RowSource to current employees.

A third alternative would be to configure the listbox RowSource separately for data entry and historical display.

 

[isladogs]

A third alternative would be to configure the listbox RowSource separately for data entry and historical display.

Exactly.
The way I would do it is to have the top half of the form for data entry for an employee selected using a combo box with the filter applied.
The lower part of the form would be a listbox or subform with no filter.
Or better still the lower part would have option buttons for current / all employees used to determine the record source.

Anyway horses for courses etc

 

[Maggs]

I have entered the following VBA in the on Current event.

Private Sub Form_Current()

cboEmployee.RowSourceType = "Table/Query"

If Me.NewRecord Then
cboEmployee.RowSource = "SELECT [Employees].ID, [Employees].[Name] FROM [Employees] WHERE [archive] = FALSE ORDER BY [Employees].[Name]; "
Else
cboEmployee.RowSource = "SELECT [Employees].ID, [Employees].[Name] FROM [Employees] ORDER BY [Employees].[Name];"
End If

End Sub

Now when I open the form a message box wants the Employee Name entered as a parameter.

Any thoughts on why this now happens.
Thanks

 

[isladogs]

Have you tried the approach I outlined?

 

[Galaxiom]

Name is a reserved word and likely to cause problems.

 

[Maggs]

Hi Galaxiom
changed Name to Employee_Name and still the same problem

 

[Maggs]

I decided that LeavingDate was not needed