Data Entry/Record Retrieval

[JeepsR4Mud]

I'm doing a database with employee records and a subform of employee work assignments.

The primary key is the employee ID.

I know this is a common procedure, but...

How can I have the form take me to the employee's record if it already exists, or begin entry of a new record if it doesn't?

Currently, I receive an error message if I enter the same EMPLOYEE ID twice, and have to use a find record button to find the record.

Isn't there a way to just make it flow easily? Record appears if it is there, new data entry if it isn't?

Thanks.

Gayle Ann

 

[Autoeng]

Use a combo box to select the employee record. If you have the wizard on while creating the combo box it will ask you if you want the combo box to provide information from a particular table or query. Point to the field that contains the employee information that you want to look at. Now you can use the drop down box to scroll through the records or you can manually type it in. If you manually type it in you will want a warning message telling the user that it doesn't exist and you are going to have to vba code it. Here is the code. It goes in the AfterUpdate event of the combo box.

Private Sub yourcombobox_AfterUpdate()
    Dim varReturn As Variant

    Me.RecordsetClone.FindFirst "[yourtablefield] = '" & Me![yourcombobox] & "'" 'finds the first record and makes it available via the combo box
    Me.Bookmark = Me.RecordsetClone.Bookmark
    If Me.yourtablefield <> Me.yourcombobox Then ' if what you enter and the table field don't agree it returns an error
            varReturn = MsgBox("The record that you entered does not exist." & vbCrLf & _
                "You are about to enter a new record into the database." & vbCrLf & _
                "Is this what you want to do?", vbYesNo)
            If varReturn = vbYes Then 'what do you want to do about the record not existing?
                DoCmd.GoToRecord , , acNewRec 'starts a new record if you answered yes
                Me.yourtablefield = Me.yourcombobox
            Else
                Me.yourcombobox = ""
                Me.yourcombobox.SetFocus 'resets the combobox to blank if you answered no
            End If
        End If
        End Sub

I think this is right but I had to do a lot of cutting to take out stuff you won't need. Replace yourtablefield and yourcombobox with the appropriate names.

 

[JeepsR4Mud]

I'm getting an error message:

Block IF without End IF

Gayle Ann

 

[Autoeng]

You're right. Had 2 If's and only 1 End If. I have corrected it now.

 

[JeepsR4Mud]

Did I do something wrong?

I still get the message about creating duplicate values.

Private Sub idbox_AfterUpdate()
Dim varReturn As Variant

Me.RecordsetClone.FindFirst "[employeenumber] = '" & Me![IDBox] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.[employeeNumber] <> Me.[IDBox] Then
varReturn = MsgBox("The record that you entered does not exist." & vbCrLf & _
"You are about to enter a new record into the database." & vbCrLf & _
"Is this what you want to do?", vbYesNo)
If varReturn = vbYes Then
DoCmd.GoToRecord , , acNewRec
Me.[employeeNumber] = Me.[IDBox]
Else
Me.[employeeNumber] = ""
Me.IDBox.SetFocus
End If
End if

 

[Autoeng]

I've corrected your code.

Did I do something wrong?

I still get the message about creating duplicate values.

Private Sub Idbox_AfterUpdate()
Dim varReturn As Variant

Me.RecordsetClone.FindFirst "[employeenumber] = '" & Me![IDBox] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.employeeNumber <> Me.IDBox Then
varReturn = MsgBox("The record that you entered does not exist." & vbCrLf & _
"You are about to enter a new record into the database." & vbCrLf & _
"Is this what you want to do?", vbYesNo)
If varReturn = vbYes Then
DoCmd.GoToRecord , , acNewRec
Me.employeeNumber = Me.IDBox
Else
Me.IDBox = ""
Me.IDBox.SetFocus
End If
End if

 

[Autoeng]

Your fields don't always match either. I didn't correct employeenumber vs. employeeNumber as I didn't know which was correct.

 

[JeepsR4Mud]

THANKS!

It works perfectly. It will save me a lot of time!

Gayle Ann

 

[Autoeng]

Glad I could help and that we finally got it worked out. The code I have utilizes the workgroup security function and checks to see if the person is authorized to enter a new record. If so it follows the code I gave you. If not it tells them "You are not authorized to create new records". If you need that let me know.