• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Confirming a record exists on load event of a form

I know nothing about VBA.. but i use access regularly. (my VBA class is soon thank god)

here is my issue..

I have a report and a form that ask for "Lot_Number" when they are launched.

once the user enters that "Lot_Number" it launches the approite report or form.

If the user enters an incorrect lot number it still launches the form to the first "lot_number" record.

I want to return an error if the lot number is not in the system when they input it.


This is all i have so far..

Private Sub Form_Load()

Form.OnLoad

End Sub
0
Zerofun
Asked:
Zerofun
1 Solution
 
clarkscottCommented:
In your first form... open the second form with
docmd.openform "yourform",,,,"Lot_Number = " & me.YourLotNumberField
 
in the Load event of the second form...
If isnull(me.YourLotNumber) then
       msgbox "no data"
       docmd.close
end if
 
Scott C
0
 
ZerofunAuthor Commented:
i need it to return the error even if they enter data... if the number they enter is not matched to a lot number in the database i need it to let them know and stop.
0
 
Rey Obrero (Capricorn1)Commented:
then, you have to check the existence of the record first before opening the form

how are you opening the form right now?
how is the data entered?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim P.Commented:
Try this:
Private Sub Form_Load()
Dim Lookup As String
Dim ValidLot As Boolean
 
Dim Msg As String
Dim Response As Integer
 
Lookup = ""
ValidLot = False
 
Do Until Lookup <> "" And ValidLot = True
    Lookup = InputBox("Please enter the lot number", "Lot Number", Lookup)
    
    If Lookup = "" Then
        MsgBox "Nothing entered. Please enter a valid lot number.", _
                vbOKOnly + vbCritical, "No Selection"
        ValidLot = False
    End If
    If Lookup <> "" Then
        If Lookup = DLookup("Lot_Number", "MyTable", "Lot_Number = '" & Trim(Lookup) & "'") Then
            Me.Filter = "Lot_Number = '" & Trim(Lookup) & "'"
            Me.FilterOn = True
            ValidLot = True
        Else
            Msg = "The lot number " & Lookup & " does not " & _
                "appear to be valid. " & _
                Chr(13) & _
                "Please enter a valid lot number " & Chr(13) & _
                "or cancel to exit."
                
            Response = MsgBox(Msg, vbCritical + vbOKCancel, "Invalid lot number")
            If Response = vbCancel Then
                ValidLot = False
                DoCmd.Close acForm, Me.NAME, acSaveYes
                Exit Sub
            End If
        End If
    End If
Loop
 
End Sub

Open in new window

0
 
ZerofunAuthor Commented:
Thanks, you went above and beyond. this helped me a lot.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now