Open a form from combo w no record - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Open a form from combo w no record

Garrett Sommers

2 years ago

I would like to know if the recordset of a cascading combo box can be checked for the presence of an existing record on gotfocus, which happens on form load from a DoCmd. I want the form to open with a msg box that prompts the user, then opens a form to add the record. Customer name is passed from new customer form, so after update event occurs to trigger the requery on the child combo.

Example, create a new appointment from a new customer form. I deal with motorcycles, so if new customer a new bike will need to be added to create the appointment. I want to automate the new bike form opening if the customer has no associated bike in the recordset.

Details

Private Sub cboMCID_GotFocus()
    Dim rst As DAO.Recordset
    Dim strSearchName As String
 
    Set rst = Me.Recordset
    strSearchName = CStr(Me!cboMCID) ---I get a RTE 94 on this
    rst.FindFirst "cboMCID = " & strSearchName
    If rst.NoMatch Then
        MsgBox "New customer, add new MC."
    End If
    rst.Close
End Sub

Hope I make sense! Thanks!

	Thomas Gonder @Reply 2 years ago
@ Garrett Please tell us how many forms we're talking about here. The procedure you show is in which form? RTE 94 means you're trying to put a null into a string variable, which you can't do. I'm not sure, but it looks like you're looking in the record set for the form (Me.Recordset), and not for the combo box. How about testing if cboMCID is null?

Garrett Sommers OP @Reply

2 years ago

Hello, Thomas! thanks for taking a look! I have three forms, begin w/ frmAdd_Customer, then on to frmAdd_appointment, then to frmAdd_MC. Complete frmAdd_Customer, pass CustomerID to frmAdd_Appointment, but becasue it is a new customer there is no bike (MC) related to the CustomerID. frnAdd_Customer is good to go. My concern is on frmAdd_Appointment and how to check the recordset for MCID on frmAdd_Appointment before opening frmAdd_MC. If the record set IsNull, open frmAdd_MC. Once i complete the add MC form users can go back and finish the add appointment form. I post the wrong code. Here is the actual code with the open form routine:

Details

Private Sub cboMCID_GotFocus()

    Dim rst As DAO.Recordset

    Dim strSearchName As String

 

    Set rst = Me.Recordset

    strSearchName = CStr(Me!cboMCID)

    rst.FindFirst "cboMCID = " & strSearchName

    If rst.NoMatch Then

        If (MsgBox("Add MC for this customer?", vbQuestion + vbOKCancel, "Add MC?")) = vbOK Then

            DoCmd.OpenForm "frmAdd_MC", , , "CustomerID=" & CustomerID

        Else

            If (MsgBox("Cancel changes and close form?", vbQuestion + vbOKCancel, "Cancel and Close?")) = vbOK Then

            Me.Undo

            DoCmd.Close , , acSaveNo

            End If

        End If

    End If

    rst.Close

End Sub

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View