How to add two or more “New Data” to a Combo Box and then opening a form to add more.

[spenz]

Good Day. I'm using access 2007 and I'm trying to find a way to allow users to add two or more "New Data" to a combo box, and then opening the client form reflecting the new data entered from the combo box. So that the user can add more fields that are required before allowing them to save the form and after the form is saved and closed, i want the combo box to be automatically refreshed and the new data selected itself. By then all that is left to the user is to push tab to go to the next control.

What I've accomplished so far below my code example is adding only a single "NewData" from the combo box and then opening the client form to add some more. but what if i want to add (2)two or more first names and a last name like for example: "Marky Mark Whalberg"
Where Marky Mark is the (2)two first names and Whalberg is of course the last name? I want this automatically reflected on the client form upon opening it and then after the user entered the other required fields,saved and closed the form. I want the calling combo box to be automatically refreshed and the new name selected and if possible would also automatically go to the next control. Thank you in advance.

Dim strSQL As String
Dim strMSG As String
Dim lngClientID As Long
Dim ctl As Control
Set ctl = Screen.ActiveControl

    strMSG = "The name, """ & NewData & """, you entered is not listed. Do you want to add it?"
    
    If MsgBox(strMSG, vbYesNo, "NOT LISTED") = vbYes Then
        strSQL = "INSERT INTO tblClients (FirstName)" & "SELECT """ & NewData & """"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
        lngClientID = DMax("ClientID", "tblClients")
        DoCmd.OpenForm "frmClients", , , "ClientID = " & lngClientID
        DoCmd.GoToControl "LastName"
    
    Else
        ctl.Undo
        Response = acDataErrContinue
    
    End If

 

[DCrake]

I can understand where you are coming from but I don't like the way it is going. Why? For a start typing in a persons forename does not necessarly make the record unique. The use rmay have mispelled the intended forename, who knows? What I would do is Ok lets ask them if they type in a new word if they want to add a new record.

Then take them to an unbound MDS form (Minimum Data Set) this is a form that contains the minimum amount of data that enables the application to test for uniqueness. Lets say; Forename, Surname, Date of Birth & Postcode. (You could have 2 John Smith's born on the same day, but I doubt if they live at the same address.) Let the user enter all these items THEN check for duplicates if found ask them again stating you already have someone with that combination in the system. If they still want to continue then at this point add the record to the table.

David

 

[spenz]

I can understand where you are coming from but I don't like the way it is going. Why? For a start typing in a persons forename does not necessarly make the record unique. The use rmay have mispelled the intended forename, who knows? What I would do is Ok lets ask them if they type in a new word if they want to add a new record.

Then take them to an unbound MDS form (Minimum Data Set) this is a form that contains the minimum amount of data that enables the application to test for uniqueness. Lets say; Forename, Surname, Date of Birth & Postcode. (You could have 2 John Smith's born on the same day, but I doubt if they live at the same address.) Let the user enter all these items THEN check for duplicates if found ask them again stating you already have someone with that combination in the system. If they still want to continue then at this point add the record to the table.

David

Hi David thanks for your excellent suggestion. I have made a few adjustments earlier after I've posted this thread awhile ago. I decided to copy the on load event of the Northwind's Customer details form which is a macro using open args and put it on my client form named frmClients(this is the 2nd form) to catch the new data coming from combo box of the first form.

But i've discovered several deficiencies using this macro code:
1.) I cannot use my own message box to ask the user if they want to add the new data they just entered. This could be helpful because i found that some user including me finds the access standard edit new item message box to vague. Likethe other user commented "It says would you like to edit it?, but what i'd like to do is add this new data, why does it keep on telling me to edit it?." I can only say to him just interpret it as edit = add meant the same. so if i can just use the not in list event by using vba code i can change the message box for sure.

2.) if the user just entered a first name and then decided to enter the last on the 2nd form along with other required fields. I found out that after the form is saved and closed it does not automatically refresh the calling combo box on the first form and worst the user had to reselect it on the drop down list or else access will ask again if i want to add this new item which is just currently been added. in another situation if the user adds both first name and last name in the combo box of the first form, the combo box automatically refreshes with no problem at all after saving and closing the 2nd form.

To sum it up, that macro code is not user friendly and i find it too buggy and error prone.

With regards to the testing and validating of the duplicate names entered. My 2nd form before update event validation handles it perfectly well. So i don't have problems with duplicate names. Only misspelled names which i advised them to just watch out for it.

So again my problem is the vba code to use in order accomplish a "multiple new data not in list addition" that should reflect to the 2nd form whatever name they typed on it and then when i save and close the form, I need it to refresh the calling combo box automatically and select it as well so that the user can just tab to the next control right away. I hope you could help me with the code. Thanks again.

 

[spenz]

I think my problem is getting complicated. I've browsed and tested different codes and have read many articles regrading this multiple field addition to combo box using not in list and I got no clear solution for it. All buggy and error prone. Or I guess this multiple field addition to combo box is really not recommended? Can anyone help me with your opinion here? I also appreciate a suggestion to simplify things out. Thanks a lot.