Update on Exit

  • Thread starter Thread starter Erika
  • Start date Start date
E

Erika

I have an employee table and I created a form based on the
table when I enter an employee number and tab to the next
field if that number is already being used I was the first
name, last name and department field to automatically
populate pulling the info from the table - is that
possible?
 
Erika said:
I have an employee table and I created a form based on the
table when I enter an employee number and tab to the next
field if that number is already being used I was the first
name, last name and department field to automatically
populate pulling the info from the table - is that
possible?
Click to expand...

So, as I understand it, the idea is that, if the employee number already
exists, the form will move to the record for that employee; otherwise
the employee number of the current record will be updated. Is that
right? Something like this will do it:

'----- start of example code -----
Private Sub EmployeeNumber_AfterUpdate()

With Me.RecordsetClone
.FindFirst "EmployeeNumber = " & Me.EmployeeNumber
If Not .NoMatch Then
Me.Undo
Me.Bookmark = .Bookmark
End If
End With

End Sub

'----- end of example code -----

That assumes that EmployeeNumber is the name of the field (and control)
in question, and that it's a numeric field. If it's a text field, then
the .FindFirst line might have to be modified like this:

.FindFirst "EmployeeNumber = '" & Me.EmployeeNumber & "'"
 
Yes that is exactly how I would like it to work - I will
give that a try. Thank you so much for your help.
 
Ok - it works great but I have no more question - is there
something we can add to the code so if it doesn't
recognize the employee number being entered it will start
a new record? Currently if I enter a new employee number
the information that was there (first name, Last name,
etc.) stays the same and doesn't update. I am wondering
if there is some why to say if it doesn't recognize the
number entered - make this a new record?

Does that make sense?
 
Erika said:
Ok - it works great but I have no more question - is there
something we can add to the code so if it doesn't
recognize the employee number being entered it will start
a new record? Currently if I enter a new employee number
the information that was there (first name, Last name,
etc.) stays the same and doesn't update. I am wondering
if there is some why to say if it doesn't recognize the
number entered - make this a new record?

Does that make sense?
Click to expand...

So, then, if you're on an existing record, entering a recognized
employee number should take you to that record, and entering an
unrecognized one should take you to a new record with that employee
number pre-entered? How about this:

'----- start of example code -----
Private Sub EmployeeNumber_AfterUpdate()

Dim varNewEmpNo As Variant

If Not Me.NewRecord Then
varNewEmpNo = Me.EmployeeNumber
Me.Undo
With Me.RecordsetClone
.FindFirst "EmployeeNumber = " & varNewEmpNo
If .NoMatch Then
RunCommand acCmdRecordsGoToNew
Me.EmployeeNumber = varNewEmpNo
Else
Me.Bookmark = .Bookmark
End If
End With
End If

End Sub
'----- end of example code -----

That's untested "air code", and it will prove awkward if someone goes to
a record, modifies fields other than the employee number, then modifies
the employee number to go to another record. In that case, the user's
changes to the first record won't be saved. I don't see a good way
around that, off the top of my head, unless you force the current record
to be saved (if it's Dirty) every time control enteres the
EmployeeNumber control. Further, what will you do if you need to change
an employee's EmployeeNumber? You will not be able to do it with this
form.

I have to say that I don't like overloading a control to make it serve
both for data entry and navigation. It's too susceptible to problems
like the ones I've described.
 
Back
Top