How to create an Add-to Combo Box with a Data Entry Form.
Access Archon #161
Creating an Add-to Combo Box with a Data Entry Form
If you want to add new entries to a table that has more than one or two fields, a different technique is needed: opening up a form that has all the fields needed to create a new record, with code on its buttons to either discard or save the new entry. As with the simple add-to combo box code, the words highlighted in yellow are the only text you have to change when using this code in your databases.
VBA Code
Private txt As Access.TextBox
Private cbo As Access.ComboBox
Private frm As Access.Form
Private prj As Object
Private strCallingForm As String
Private Sub cmdDiscard_Click()
On Error GoTo ErrorHandler
Set prj = Application.CurrentProject
‘Name of the form with the add-to combo box
strCallingForm = “frmCategoriesComplex“
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
If prj.AllForms(strCallingForm).IsLoaded Then
Forms(strCallingForm).Visible = True
Else
DoCmd.OpenForm strCallingForm
End If
Set frm = Forms(strCallingForm)
‘Name of add-to combo box
Set cbo = frm![cboCategoryID]
cbo.Value = Null
ErrorHandlerExit:
DoCmd.Close acForm, Me.Name
Exit Sub
ErrorHandler:
If Err.Number = 2467 Then
Resume ErrorHandlerExit
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Sub
Private Sub cmdSave_Click()
On Error GoTo ErrorHandler
Set prj = Application.CurrentProject
‘Name of the form with the add-to combo box
strCallingForm = “frmProductsComplex“
‘The textbox control on this form that holds the key value
Set txt = Me![txtCategoryID]
If prj.AllForms(strCallingForm).IsLoaded Then
Forms(strCallingForm).Visible = True
Else
DoCmd.OpenForm strCallingForm
End If
Set frm = Forms(strCallingForm)
‘Name of add-to combo box
Set cbo = frm![cboCategoryID]
cbo.Requery
cbo.Value = Nz(txt.Value)
ErrorHandlerExit:
DoCmd.Close acForm, Me.Name
Exit Sub
ErrorHandler:
If Err.Number = 2467 Then
Resume ErrorHandlerExit
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Sub
To test this code, open frmProductsComplex and type a new entry into the Category combo box; this time, after clicking Yes on the message box, frmNewCategory opens, where you can fill in all the fields in tblCategoriesComplex, and click one of two buttons to either discard or save the new category, as shown in Figure C.
Figure C. The New Category form, where all fields can be filled in before saving the new category.
References
The code in the sample database doesn’t need any special references.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch161.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Add-to Combo Boxes (AA 161).mdb |
Access 2000 database; can also be used in higher versions of Access. |
Wherever you want |
