Combo box opens form to current selection, Else if blank opens to New

  • Thread starter Thread starter Terry B via AccessMonster.com
  • Start date Start date
T

Terry B via AccessMonster.com

On my form I have a combo box which lists employees.
I have set the Double Click event of the combo box to open the Employee form
providing the ability to add an employee if it does not appear in the list.
I currently have the Employee form opening to a new record.

What Im trying to do is modify my current code with a little intelligence.
If the Combo box is empty on Dbl Click....Open to a new record.
If the Combo box already contains an employee on Dbl Click....Open to that
employee.

This is my current code.

Private Sub EmployeeID_DblClick(Cancel As Integer)
On Error GoTo Err_EmployeeID_DblClick
Dim lngEmployeeID As Long

If IsNull(Me![EmployeeID]) Then
Me![EmployeeID].Text = ""
Else
lngEmployeeID = Me![EmployeeID]
Me![EmployeeID] = Null
End If
DoCmd.OpenForm "frmEmployees", , , , , acDialog, "GotoNew"
Me![EmployeeID].Requery
If lngEmployeeID <> 0 Then Me![EmployeeID] = lngEmployeeID

Exit_EmployeeID_DblClick:
Exit Sub

Err_EmployeeID_DblClick:
MsgBox Err.Description
Resume Exit_EmployeeID_DblClick
End Sub

Thanks!
 
Assuming that no employee has ID of zero, and you don't need all employees
in the form, and the form is not already open, you could achieve your aim
with just this line:

DoCmd.OpenForm "frmEmployees", _
WhereCondition= "EmployeeID = " & Nz(Me.EmployeeID,0), _
WindowMode:=acDialog
 
Terry said:
On my form I have a combo box which lists employees.
I have set the Double Click event of the combo box to open the Employee form
providing the ability to add an employee if it does not appear in the list.
I currently have the Employee form opening to a new record.

What Im trying to do is modify my current code with a little intelligence.
If the Combo box is empty on Dbl Click....Open to a new record.
If the Combo box already contains an employee on Dbl Click....Open to that
employee.

This is my current code.

Private Sub EmployeeID_DblClick(Cancel As Integer)
On Error GoTo Err_EmployeeID_DblClick
Dim lngEmployeeID As Long

If IsNull(Me![EmployeeID]) Then
Me![EmployeeID].Text = ""
Else
lngEmployeeID = Me![EmployeeID]
Me![EmployeeID] = Null
End If
DoCmd.OpenForm "frmEmployees", , , , , acDialog, "GotoNew"
Me![EmployeeID].Requery
If lngEmployeeID <> 0 Then Me![EmployeeID] = lngEmployeeID

Exit_EmployeeID_DblClick:
Exit Sub

Err_EmployeeID_DblClick:
MsgBox Err.Description
Resume Exit_EmployeeID_DblClick
End Sub

Thanks!
Click to expand...

You're better off having a command button to "Add An Employee", and putting
the combobox code in the AfterUpdate event, which will only fire if an
employee is selected. Shortcuts are good, but cause more heartache than
necessary if used in the wrong places. Also, your DoCmd.OpenForm syntax
should be as follows: DoCmd.OpenForm "frmEmployees", , , ,acFormAdd, acDialog.
You can probably delete the OpenArgs in the form as well. See the OpenForm
method in the Access help file for details.
 
Allen,

Thanks!
It works exactly as I wanted now.
I ended up getting an error telling me to save the record before I requery,
so I popped a line in to save it after the Docmd and now its great!
Code is much shorter now.
Thanks again.
 
Back
Top