Combo box choices from Existing Table

  • Thread starter Thread starter BMSpell
  • Start date Start date
B

BMSpell

Good Morning
I have a form where reports are generated. The report ask for a
employees name. I am using a combo box for this entry. There are several
other fields of information which is entered in the record as well. All
entries are entered into a table. I would like the employee combo box to
pull up the existing names entered in the main table so that the employee
does not have to re-enter their name on every record produced, and allow new
names to be entered as well. Thanks.
 
I'm not following your question very well. How does the report ask for the
employee's name? Is the report based on a parameter query? Where is the
combo box located? What is its row source? Is it bound to a field? You
mention other fields of information that are entered in the record. What
record is that? You can't add or modify fields in a report; that needs to be
done before generating the report. Are you storing employee names and other
information in a second table. If so, consider whether you want to do that.
If you need a record to contain the employee's name and other information as
it was at the time the record was created you will probably do best to store
the actual fields. If you need an employee's current name and information to
appear on all records past and future you should store the employee's unique
ID. For instance, a payroll record would be linked to an unchanging employee
ID, so even if the employee's name changes the payroll would still be
associated with that employee.
Some information on table structure and just what you need to accomplish
will be helpful in answering your question.
 
Hi;

This is what is used in my database. Not sure if this is the best answer
but it is an answer that works.

Be sure You change the Table/Forms/Controls Names in the following code to
match the Names of Your Table/Forms/Controls.

Andy

Below is the entire answer. Read it carefully and modify it as needed. It
works.

First created "tblEmployees" with a field named EmployeeID as the primary
key and set it's "Data Type" to text.

Then created "frmEmployees" with it's Record Source as "tblEmployees"

In the "tblThatStoresMainInformation" added a field for Employees.

On the Form where the user enters the data,
("tblThatStoresMainInformation"), set the Combo box's control source to the
Employees field.

Set the Combo Box's "Row Source Type" to Table/Query

Set the Combo Box's "Row Source" to:
SELECT [tblEmployee].[EmployeeID] FROM tblEmployee;

Set "Limit to List" to Yes

In the "On Not in List" event add this code:

On Error GoTo msgbxNotInList_Err

Dim Response As Integer
Beep
MsgBox "That name is not in the list." & vbCrLf & "Please select from
the list." & vbCrLf & "To add a name to the list, Double-Click the Combo box
to open the Employees Form.", vbExclamation, ""

Response = acDataErrContinue

msgbxNotInList_Exit:
Exit Function

msgbxNotInList_Err:
MsgBox Error$
Resume msgbxNotInList_Exit

Modify and place the following code in the "On Dbl Click" event.

Private Sub cbxEmployeeID_DblClick(Cancel As Integer)
' NOTE: cbxEmployeeID is the name of my Combo box.
On Error GoTo Err_cbxEmployeeID_DblClick
Dim cbxEmployeeID As Long

If IsNull(Me![cbxEmployeeID]) Then
Me![cbxEmployeeID].text = ""
Else
lngcbxEmployeeID = Me![cbxEmployeeID]
Me![cbxEmployeeID] = Null
End If
DoCmd.OpenForm "frmEmployees", , , , , acDialog, "GotoNew"
Me![cbxEmployeeID].requery
If lngcbxEmployeeID <> 0 Then Me![cbxEmployeeID] = lngcbxEmployeeID

Exit_cbxEmployeeID_DblClick:
Exit Sub

Err_cbxEmployeeID_DblClick:
MsgBox "This Control is not working correctly."
Resume Exit_cbxEmployeeID_DblClick
End Sub
 
Back
Top