Hello all!



Let me lay out the db for you first

frm_Main_Form - form holding all fields

frm_List_Edit - Form containing Tabcontrol - each tab containing a subform

6 Subforms - these hold the lists for combo boxes on the main form.

The 'frm_List_Edit' form is there so that the users (in this case with NO Access experience) can edit the information in the drop down lists - add/remove/alter

I have got code that allows the user on DblClick and NotInList to receive a message that asks if they wish to open the 'frm_List_Edit'

Up to here, I've managed to get it all working and safeguarded (as much as I can with the limitations of the project). What I would like to be able to do, though is to alter the code so that, not only open the List_Edit form, but also the correct tab - an also add the New Data to the table, too.

Example: (db attached to post - just in case it gets too complicated!)

frm_Main_Form
cboCustomer needs to have another customer name added, so the user enters the name in the box

The NotInList function kicks in

The NewData is then parsed and the frm_List_Edit form is opened, the tab "Customer" is brought into focus
The NewData is added to the table in the subform 'sub_tbl_lst_Customer' which contains the table 'tbl_lst_Customer'

At the moment, I can get it to save, close the current for, then open the List_Edit form, but not on any specific tab, nor table. See annotations below in code.

Code:
Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
    
    On Error GoTo cboCustomer_NotInList_Err
    Dim intAnswer As Integer
    
Creates a popup message box letting the user know that what they have typed is not in the list,
' and asks them if they wish to open the Edit Form.
intAnswer = MsgBox("The entry, " & Chr(34) & NewData & _
    Chr(34) & " is not currently in the 'Customer' list." & vbCrLf & _
    "Would you like to open the editing form?" _
    , vbQuestion + vbYesNo, "Customer Not in List")

'This is the section I'm having issues with!'  
' If user responds by clicking 'Yes', the form is saved/closed and the edit form opens
If intAnswer = vbYes Then
'Incorrect entry is removed to prevent errors'
    cboCustomer = ""
'calls code to save/close current form then open List_Edit form'
    Call cmdOpenListEdit_Click
    Response = acDataErrContinue
    
' If user responds by clicking 'No', the form creates a message
Else
    MsgBox "Please choose a Customer title from the list." _
        , vbInformation, "Customer"
    cboCustomer = ""
    Response = acDataErrContinue

End If

cboCustomer_NotInList_Exit:
    Exit Sub
cboCustomer_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboCustomer_NotInList_Exit
End Sub
Here's the called command:
Code:
Private Sub cmdOpenListEdit_Click()
    DoCmd.Save acForm, "frm_Main_Form"
    DoCmd.Close
    DoCmd.OpenForm "frm_List_Edit"
End Sub
I first tried to edit the second piece of code to select the correct tab for each of the combo lists, but just couldn't seem to get it to work - SO, I thought that while I'm here I may as well ask if any of you can help me edit the original piece of code to allow me to not only open the List_Edit form on the correct tab, but also to add the NewData from the combo box into the correct table!

Any help is most appreciated.

Bird