Combo box – add new record – code or name

[groengoen]

I have a problem with adding new records to a combo box.

Control source is usually a numeric code (primary key of table) usually not visible (col size 0.0cm)

Name is usually visible because it is more user friendly.

Adding new record:
The user must key in numeric code which is the actual key because the code adds a record using this key.

Eg. I have a table of doctors with doctor codes, surnames and forenames.

tblDoctor: DoctorCode, Surname, Forename.

My primary key is DoctorCode which is the Control Source with a column width of 0cm
The Surname and Forename are visible when the drop down list is clicked.

If the user wishes to add a Doctor to the table they will have to key the DoctorCode in the combo box to follow the notinlist code and open the form for adding a doctor.

In summary, the user is used to seeing Surname and Forename in this field and then when adding a new record must key a number.


Is there a more user friendly way of doing this or am I coming at it wrongly?

I have tried including the DoctorCode with a size > 0 but then that is the field displayed when the form is first opened, rather than the Surname and it is not obvious to the user which Doctor it is at first glance.

 

[Mr. B]

You do not need to have users try to type in the Code. In fact they can't because it is an autonumber type field.

They will just type in the Doctors name.

You must set up the On Not In List property of the control and use VBA code to manage the On Not In List event.

Take a look at this line. It is just what you need to do.

http://www.mvps.org/access/forms/frm0015.htm

HTH

 

[groengoen]

Thanks for your prompt response. At the moment the field I have for DoctorCode is not an Autonumber but a long integer. Do I have to change it to an autonumber for the code in the link to work?

 

[groengoen]

Here is the code in the Notinlist:


Private Sub cmbGP_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new Doctor.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Doctors form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Doctor form's Form_Load event
' procedure.
DoCmd.OpenForm "frmDoctor", , , , acAdd, acDialog, NewData
End If

' Look for the Doctor the user created in the Doctors form.
Result = DLookup("[DoctorCode]", "tblDoctor", _
"[DoctorCode]= " & Val(NewData))

If IsNull(Result) Then
' If the Doctor was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the Doctor was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If

End Sub

 

[Mr. B]

There should always be a Unique value that is the Primary Key for your table.

In this case, I just assumed that the DoctorCode was that field, If you need some other value for the DoctorCode then I would add an autonumber field. If not then I would just change that field to an autonumber type field.

 

[groengoen]

DoctorCode is the unique primary key, but it isn't an autonumber. I will try your suggestions, Thanks

 

[groengoen]

That worked fine, I just have to requery the combo box after returning from the add form.