AutoInsert Field into another form using command button?

[wordsmith]

Hi,

I have a form which lists Employees (flds EmployeeID (pk), Surname etc.) and a form listing Absences (flds AbsenceID(pk), EmployeeID(fk), StartDate...etc.).

from the employee form, I want to use a command button that will take me to the Abscences form but auto insert the EmployeeID and then I can fill out the rest.

I tried the go to form command for the button but its only looking for existing absences with that ID?

can anyone help please?

much gratitude!

regards all

Keji

 

[antomack]

You can use the DataEntry property of the openform method in code to set the form to open for adding data only, this will stop it displaying any current records. You then need to set the values of the employeeID to the value from the opening form, and lock this field so the user cannot overtype the value. I've assumed that your absence form is called 'frmAddAbsence', change as required.

DoCmd.OpenForm "frmAddAbsence", , , , acFormAdd
Forms!frmAddAbsence!EmpID = Me!EmpID
Forms!frmAddAbsence!EmpID.Enabled = false

If you want to limit user to only one absence entry at a time then add the following line, otherwise if they add a second absence it will not default to the employeeID without further coding. Also allowing addition of more than one absence at a time means that if the user moves to the second record it will automatically save changes to the first new record which may no be ideal.

Forms!frmAddAbsence.AllowAdditions = False

The other thing to watch for is if the user closes the form without saving it you need to ensure that the record is cancelled and not automatically saved by Access.

DoCmd.RunCommand acCmdUndo