asked on

Add a record to a table when enter value in a combobox

I have a subform that has a combobox. The combobox display a list using a calculated field like "Smith, John". If the user enters a value in the combobox that doesn't exist in the rowsource of the combobox, I want the user to be informed that the record doesn't exist and ask them if they want to add it. If they answer "Yes" I want the form that is normally used to add the record to open with the values they entered in the combobox already entered in the form that opens. The combo box looks for a first name and last name.

I've tried this but it isn't working.

Private Sub cboParticipantID_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String

    CR = Chr$(13)

    If NewData = "" Then Exit Sub

    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add that participant?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Participants form in data entry
       ' mode as a dialog form, passing the new first and last names in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "frmParticipants", , , , acAdd, acDialog, NewData
    End If

    ' Look for the customer the user created in the Customers form.
    Result = DLookup("[FullName]", "tblParticipants", _
             "[FullName]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the customer 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 customer was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

So you are storing "FullName" in a table instead of calculating it?

crystal (strive4peace) - Microsoft MVP, Access

in the beginning, dimension a variable to hold ParticipantID, assuming it is a long integer and the primary key of the new record

dim nParticipantID as long

Open in new window

initialize it to be -1, assuming you have no negative ParticipantIDs -- this will become the value of the ParticipantID on the new record

nParticipantID = -1

Open in new window

then on the AfterUpdate event of the frmParticipants form, set a database property (my preference--if you want more info, please ask), global variable, or tempvar to the value of the ParticipantID just added. Also use -1 to be a flag that it was not done. When control comes back, store this in nParticipantID (which I am using to make it easier to understand)

then, instead of If IsNull(Result) Then, use

If nParticipantID <> -1 Then
   Response = acDataErrAdded

Open in new window

and so on

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

Ultimately your sticking point here will be that you are trying to "add" a concatenated string to a list.
Comboboxes are set up to add one value.
So how are you validating that the user enters the value in the correct format:
"Smith, John"
"Smith John"
"Smith, John"
"John, Smith"
"John Smith"
"John, Smith"
"John"
"Smith"

Jeffrey Coachman

I'll make a few comments then leave this to the other experts.
Please see my comments and Pat's comments on why this approach may cause you issues down the line.

1. If a value is not in the list, ...then sometimes the best approach is to stop and add it to the list directly. (Not via a combobox)
2. Have the user search for the name using separate comboboxes (txtFirstName and txtLastName)
3. You also appear to be storing the FullName? If so, ...this is a bad idea in general.
4. With your approach:

I want the form that is normally used to add the record to open with the values they entered in the combobox already entered in the form that opens.

You have no validation to ensure that the names were entered in the correct format: "Smith, John"
Then you have to go through some additional machinations to first, Validate the string, ...then break it into two separate names.
5. Combo boxes are designed to add one value, into one field ...not take a concatenated string, separate it, ...then add it to two fields.

Ultimately an expert here will give you what you need.

Please know that sometimes the question should be:
"I am thinking of doing it this way, ...does this technique make sense?"
...rather than:
"This is the way I want to do it, ..and it is not working, ...can you help me make it work?"

In other words, ...sometimes you need to state your ultimate goal (without stating how, (or with what code), it should be done) , ...then ask the experts what the best approach might be.

JeffCoachman

SteveL13

ASKER

How are you ensuring that the value they enter is really valid? -- Good point. I've decided to have them use the "Names" table/form first.

You can end up with a long list of names that were "added" by the data entry person, that were not actually valid. -- Excellent point.