Limit to List Question

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi,

I have a combo box [cmbTranRef] on a form [frmTransDetails] the can be used
to select Transaction Reference numbers and display the corresponding data
from the underlying table [tblTransDetails] based on that selection.

Given that the Combo box is unbound and the limit to list method is set to
"yes" what would be the best way to allow users to create a new record by
entering a new Transaction Reference in the combobox.

Thanks in advance

...pc
 
Hi,

I have a combo box [cmbTranRef] on a form [frmTransDetails] the can be used
to select Transaction Reference numbers and display the corresponding data
from the underlying table [tblTransDetails] based on that selection.

Given that the Combo box is unbound and the limit to list method is set to
"yes" what would be the best way to allow users to create a new record by
entering a new Transaction Reference in the combobox.

Thanks in advance

..pc
Click to expand...

You would use the Combo's NoInList event to either add a new name to
the list, or if more data than just a new name is needed (such as
Address, Phone, etc., open a form for data entry.

You haven't mentioned the type of new data needed, so I'll send you a
method to open a form for new record entry.

Code the Combo's NotInList event like this:

MsgBox "Double-click this field to add an entry to the list.", , "Not
In List"
Response = acDataErrContinue
====
Next code the combo's Double-click event:

' Note: Change MemberID to whatever the actual name
' of your Combo Box is (cmbTranRef).
' Change "frmMembership" to whatever the actual form name is.

On Error GoTo Err_MemberID_DblClick
Dim lngMemberID As Long

If IsNull(Me![MemberID]) Then
Me![MemberID].Text = ""
Else
lngMemberID = Me![MemberID]
Me![MemberID] = Null
End If
DoCmd.OpenForm "frmMembership", , , , , acDialog, "GoToNew"
Me![MemberID].Requery

If lngMemberID <> 0 Then Me![MemberID] = lngMemberID

Exit_MemberID_DblClick:
Exit Sub
Err_MemberID_DblClick:
MsgBox "Error #: " & Err.Number & " " & Err.Description, ,
Me.Name
Resume Exit_MemberID_DblClick
====
Then code the frmMembership Load event:

If Me.OpenArgs = "GotoNew" And Not IsNull(Me![MemberID]) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
====

When you enter a name into the combo box that is not in the list, a
message will appear to double-click the control if the user wishes to
add the name. Double-clicking the Combo Box will open the
frmMembership form to a new record for data entry.
Close the form when done, and the new name will appear in the combo
box.
 
Back
Top