Access. How to Create a record and select that record in CBO on another form?

Question

I have two forms frmProductCreate and frmColourCreate.

In frmProductCreate I have:

• Combobox: colourID
• Button: btnColCreate

The idea is that if a user needs to create a new colour, they can click on the create button which opens frmColourCreate, name the new colour and click save button. Which will save the new colour in the colours table (which is the record source for the cbo ColourID in frmProductCreate). Then requery colourID in frmProductCreate and close frmColourCreate.

What I also want this save button to do is after the requery to select the cbo colourID and go to the last created colour. i.e. the last record. I have tried a few codes but failed to make it work. Any help will be greatly appreciated.

Private Sub btnSavecol_Click()
    Dim cancel As Integer

    If Me.ColName = "" Then
        MsgBox "You must enter a Colour Name."
        DoCmd.GoToControl "ColName"

        cancel = True
    Else
        If MsgBox("Are you sure you want to create new Colour?", vbYesNo) = vbNo Then
            cancel = True
        Else
            CurrentDb.Execute " INSERT INTO Colours (ColName) VALUES ('" & Me.ColName & "')"

            Me.ColName = ""

            DoCmd.Close

            If CurrentProject.AllForms("frmProductCreate").IsLoaded = False Then
                cancel = True
            Else
                Forms!frmproductCreate!ColourID.Requery
                'Forms!frmproductCreate!ColourID.SetFocus
                'Forms!frmproductCreate!ColourID.items.Count = -1
                'Forms!frmproductCreate!ColourID.Selected(Forms!frmproductCreate!ColourID.Count - 1) = False

                'YourListBox.SetFocus
                'YourListBox.ListIndex = YourListBox.ListCount - 1
                'YourListBox.Selected(YourListBox.ListCount - 1) = False
            End If

            If CurrentProject.AllForms("frmProductDetails").IsLoaded = False Then
                cancel = True
            Else
                Forms!frmproductDetails!ColourID.Requery
            End If
        End If
    End If
End Sub

Answer 1

Some remarks:

• Whatfor is the variable cancel? Because it is not used, I removed it.
• I have no really idea whatfor you need Me.ColName = "".
• Why do you close the current form so early? I moved DoCmd.Close to the end.
• I made your code a bit more readable, by removing 'arrow-code' (those nested IFs).

Finally try this:

Private Sub btnSavecol_Click()
    If Me.ColName.Value = "" Then
        MsgBox "You must enter a Colour Name."
        DoCmd.GoToControl "ColName"
        Exit Sub
    End If

    If MsgBox("Are you sure you want to create new Colour?", vbYesNo) = vbNo Then Exit Sub

    CurrentDb.Execute "INSERT INTO Colours (ColName) VALUES ('" & Me.ColName.Value & "')"

    If Not CurrentProject.AllForms("frmProductCreate").IsLoaded Then GoTo Done

    Forms!frmproductCreate!ColourID.Requery

    'This sets the ComboBox 'ColourID' to the new colour:
    'Forms!frmproductCreate!ColourID.Value = Me.ColName.Value

    'If you use an automatic generated ID in the table 'Colours', then you will have to get that ID from the color and set it to the ComboBox:
    Forms!frmproductCreate!ColourID.Value = DLookup("ColID", "Colours", "ColName = '" & Me.ColName.Value & "'")

    Me.ColName.Value = ""

    If Not CurrentProject.AllForms("frmProductDetails").IsLoaded Then GoTo Done

    Forms!frmproductDetails!ColourID.Requery

Done:
    DoCmd.Close
End Sub