asked on

Opening forms with an input box

I am trying to open a Form in Access with an input box, but I want to make sure the record exists in the table.

Private Sub Command6_Click()
On Error GoTo Err_ExistingExamID

    Dim strInput As String
    Dim recordCount As Integer
    Dim tryAgain As Integer
    
    'enter loop
    Do
        'get input
        strInput = InputBox("Please enter a valid Exam ID")

        'check input
        If Len(strInput) <> 0 Then
            'look for Booking reference number in tblHABookings
            recordCount = Nz(DCount("*", "tblExams", "[ExID] = '" & strInput & "'"), 0)
            
            If recordCount <> 0 Then 'booking reference was found
                tryAgain = vbNo
                'open Amend form
                DoCmd.OpenForm "frmExaminations", , , "ExID= '" & strInput & "'"
            Else ' booking reference was NOT found
                'prompt user again
                tryAgain = MsgBox("Exam ID '" & strInput & "' was not found. Try again?", vbYesNo)
            End If
        Else
            tryAgain = vbNo
        End If
    Loop Until tryAgain = vbNo

Exit_ExistingExamID:
    Exit Sub

Err_ExistingExamID:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    'MsgBox Err.Description
    Resume Exit_ExistingExamID

End Sub

Open in new window