Table Setup - Access 2003

[Steve C]

I am building a database of employee information for our Accounting
department. They want to store information on existing employees and new
hires. In tblEmployees, existing employees are uniquely identified by an 8
character combination of their last and first names, i.e., SMITROBE for
Robert Smith, JONEMARY for Mary Jones, etc.

They want potential new hires to be initially entered and identified as
NEWHIR01, NEWHIR02, etc. for budgeting planning purposes (these identifiers
would be changed to the last & first name combination described above once
officially hired).

My challenge is that they do not want to reuse a NEWHIR number once already
used. If NEWHIR01 through NEWHIR08 had already been entered previously, they
want to be able to choose the next NEWHIR number (NEWHIR09) from a list.
While it's easy to override the NEWHIR number with the employees last/first
name once officially hired, I'm struggling with a way to make used NEWHIR
numbers unavailable to be used again.

Any ideas on how to solve this would be greatly appreciated!

 

[KARL DEWEY]

Make the field your primary key field or create an index that is set as unique.

 

[Steve C]

Karl,

Thanks, that works. I created a field called NEWHIREID and made it unique.

One more question: I've placed the NEWHIREID field on a form as a combo box
which shows the user the ones that have already been used. After a new entry
is made, I have to choose Records > Refresh to make the combo box display the
latest entry. To make things more user friendly, I'd like to place a command
button on the form that users can click to automatically refresh the form.
Can you help with the macro code I would need for that? Thanks again,

 

[Pieter Wijnen]

Me.Requery 'For The form
Me.Combo0.Requery 'For a Combo /ListBox
Me.SubFormControl.Form.Requery ' For a SubForm

HtH

Pieter

 

[Steve C]

Thanks, Pieter. I knew it was something simple. Instead of a button, I tied
it to the AfterUpdate event of the form as follows:

Private Sub Form_AfterUpdate()
Me.cboNEWHIR.Requery
End Sub

Works like a charm. Thanks again.