Check for Valid Value in a Form Field to Open a New Form

[Mike808]

Access Ninjas-
So I have been working on a bit of VBA to open another form in my project. The criteria to open the form is to have the correct ID number inputted into an InputBox. If the ID does not exist in the field as a value, the form does not open. If the value is in the field, the form opens to THAT ID number only.



I have everything pretty smooth except the cross checking of the ID number.

[ranman256]

on the button to open the form,check the ID

sub btnOpen_click()
dim sWhere as string

sWhere = "[ID]=" & txtBoxID

if IsNull(Dlookup("[id]","table",sWhere)) then
    msgbox "ID not found",,txtboxID
else
   docmd.openform "myForm",,,sWhere
endif
end sub

[Mike808]

Ranman-

Thanks for the reply.....but I don't think it is working because I think I may have an argument or line wrong. Below is my full code....maybe you can see the problem.

Private Sub Command51_Click()
Dim mainForm As String
Dim strMsg As String
Dim strForm As String
Dim strInput As String
Dim myVar As String
strForm = "frmCNAFCHGReqSubmitForm"
mainForm = "frmCNAFCHGReqMenu"
myVar = "[ID]=" & strInput
Beep
strMsg = "This form is to be edited by the original requester of the change request only!" & vbCrLf & vbLf & "Please enter ID number of original change request at this time."
strInput = InputBox(strMsg, "Change Request ID Number")
If strInput = myVar Then
    DoCmd.Close acForm, "frmCNAFCHGReqMenu", acSaveNo
    DoCmd.OpenForm strForm, acNormal, , myVar
Else 'password is incorrect
MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
End If
End Sub

Mahalo!! Mike808

[ssanfu]

 1 Private Sub Command51_Click()
 2     Dim mainForm As String
 3     Dim strMsg As String
 4     Dim strForm As String
 5     Dim strInput As String
 6     Dim myVar As String   '<<<-- initialized
    
 7     strForm = "frmCNAFCHGReqSubmitForm"
 8     mainForm = "frmCNAFCHGReqMenu"
 9     myVar = "[ID]=" & strInput             ' <<--- "myVar" value is now "[ID]=" because, at this point, "strInput" is equal to "" (empty string)
 10    Beep
    
 11   strMsg = "This form is to be edited by the original requester of the change request only!" & vbCrLf & vbLf & "Please enter ID number of original change request at this time."
 12   strInput = InputBox(strMsg, "Change Request ID Number") '<<<--- "strInput" should have a value
 13   If strInput = myVar Then     ' <<<---comparing an entry to an empty string
 14       DoCmd.Close acForm, "frmCNAFCHGReqMenu", acSaveNo
 15       DoCmd.OpenForm strForm, acNormal, , myVar
 16   Else    'password is incorrect
 17       MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
 18   End If
 19 End Sub

It looks to me that strInput will never equal myVar.



You said

If the ID does not exist in the field as a value,

If I may make a suggestion... maybe try this modified code:

Private Sub Command51_Click()
    Dim mainForm As String
    Dim strMsg As String
    Dim strForm As String
    Dim strInput As String
    Dim myVar As String

    strForm = "frmCNAFCHGReqSubmitForm"
    mainForm = "frmCNAFCHGReqMenu"
    
    'get the value from the current form record source
    myVar = Me.Your_ID_Field_Name      '<<<--- change to your field name
     'maybe:    
'    myVar = Me.ID
    Beep

    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."
    
    strInput = InputBox(strMsg, "Change Request ID Number")
    If strInput = myVar Then
        DoCmd.Close acForm, mainForm
        DoCmd.OpenForm strForm, acNormal, , "[ID]=" & myVar
    Else    'password is incorrect
        MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
    End If
End Sub

(My changes are in BLUE)

BTW, this line

    DoCmd.Close acForm, "frmCNAFCHGReqMenu", acSaveNo

doesn't do what you think it does.
The argument "acSaveNo" is for changes to the DESIGN of the form using VBA, not changes to the data.
Since you didn't makes changes to the design of the form, this argument doesn't have any effect.

[Mike808]

Steve-

Huge thanks....I know you got it in regards to the reference issue, but my current record source is not open. "mainForm" is a menu form for different views of essentially the same form, "strForm". mainForm has no records, just menu buttons, "strForm" has the ID field.

With all of that being said......I can't use the Me.ID for the string of myVar because the current form isn't open yet......there lies the rub.

I tried to use a Docmd to open the "strForm" in a hidden view just for the ID data field.....but I know that doesn't work.

Still looking around for the solution. It hurts being so close! Thanks for the info on the acSaveno argument......stripped it.

Mike808

[ssanfu]

OK, I am confused......

As a test, try the following code. This uses the WHERE clause of the "OpenForm" command. If the "Change Request ID Number" entered exists in the field "ID", the form will open to that record (assuming there is only one record with that id number).

Private Sub Command51_Click()
    Dim strMsg As String
    Dim strInput As String

    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."

    Beep
    strInput = InputBox(strMsg, "Change Request ID Number")

    DoCmd.Close acForm, "frmCNAFCHGReqMenu"
    DoCmd.OpenForm "frmCNAFCHGReqSubmitForm", acNormal, , "[ID]=" & strInput
End Sub

Another way would be to enter the "Change Request ID Number" (using the Inputbox command), then do a DLookup() or write a UDF to check to see if the "Change Request ID Number" entered is a valid ID.

Here is what the DLookup() version might look like:

Private Sub Command51_Click()
    Dim mainForm As String
    Dim strMsg As String
    Dim strForm As String
    Dim strInput As String
    Dim myVar As String

    strForm = "frmCNAFCHGReqSubmitForm"
    mainForm = "frmCNAFCHGReqMenu"


    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."

    Beep
    strInput = InputBox(strMsg, "Change Request ID Number")

    'get the value from the current form record source
    myVar = DLookup("ID", "YourTableName", "ID = " & strInput)  '<<-- "ID" is a number type field.

    If strInput = myVar Then
        DoCmd.Close acForm, mainForm
        DoCmd.OpenForm strForm, acNormal, , "[ID]=" & myVar
    Else    'password is incorrect
        MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
    End If
End Sub

Maybe you could post your dB with just a few test records.....

[Mike808]

Steve-

Your first code in this reply that is a TEST is identical to when I first started the project. But the outcome is why I changed my code to add the IF statement. If the user puts in a valid ID number, the correct record comes up in the new form. However, if the user puts in a number that is not valid in the ID field, the form opens for a new record. I don't want that outcome because it can "dirty" up my database with half edited or non-records with ID numbers.

Your second code is almost perfect, except if now I enter an ID number that is invalid, I have a Run-time error 94: Invalid use of Null and the Debug is calling out:

myVar = DLookup("ID", "CNAFCHGs", "ID = " & strInput)

If that is fixed......maybe with a loop on error??? So close!!

Mike808

[ssanfu]

It appears that "ID " is a number (Long??), so I've made a couple of changes.

Changed "strInput" to "lngInput"
Changed "lngInput" and "myVar" delcarations from String to Long
Added NZ() function to the Inputbox line.

How about this:

Private Sub Command51_Click()
    Dim mainForm As String
    Dim strMsg As String
    Dim strForm As String
    '    Dim strInput As String
    Dim lngInput As Long 
    Dim myVar As Long      'As String

    strForm = "frmCNAFCHGReqSubmitForm"
    mainForm = "frmCNAFCHGReqMenu"

    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."

    Beep
    lngInput = InputBox(strMsg, "Change Request ID Number")

    'get the value from the current form record source
    myVar = Nz(DLookup("ID", "CNAFCHGs", "ID = " & lngInput), 0)   '<<--will/can "ID" ever be zero???

    If lngInput = myVar Then
        DoCmd.Close acForm, mainForm
        DoCmd.OpenForm strForm, acNormal, , "[ID]=" & myVar
    Else    'password is incorrect
        MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
    End If
End Sub

[Mike808]

Steve-

That is it my man!!!! SOLID WORK!!! Thanks so much for the education and the fix!!!

Mike808

[Mike808]

Steve-

One problem. Run-time error 13 mismatch if I hit the cancel button or the ok button with no input into the inputbox. I would like it to just go back to the "frmCNAFCHGReqMenu" form.

Mike808

[ssanfu]

Mike,
Quit breaking the code!!


What about this??

Private Sub Command51_Click()
    Dim mainForm As String
    Dim strMsg As String
    Dim strForm As String

    Dim varInput As Variant
    Dim lngInput As Long
    Dim myVar As Long   

    strForm = "frmCNAFCHGReqSubmitForm"
    mainForm = "frmCNAFCHGReqMenu"

    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."

    Beep
    varInput = InputBox(strMsg, "Change Request ID Number")

    'check to see if a number is entered.
    If IsNumeric(varInput) Then
        'convert variant to a long integer.
        '(technically not required, but I didn't have to mess with the rest of the (working) code.
        lngInput = CLng(varInput)

        'get the value from the current form record source
        myVar = Nz(DLookup("ID", "CNAFCHGs", "ID = " & lngInput), 0)   '<<--will/can "ID" ever be zero???

        If lngInput = myVar Then
            DoCmd.Close acForm, mainForm
            DoCmd.OpenForm strForm, acNormal, , "[ID]=" & myVar
        Else    'password is incorrect
            MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
        End If
    Else
        'a number was not entered. Tell them
        MsgBox "Invalid input. MUST enter a number...Try again?", vbCritical, "Please Try Again"
    End If
End Sub

I prefer to have a text box on a form, instead of using the Inputbox command; it is easier to validate the value entered.



You should also take the time to rename objects. Which ones tells you more?

Private Sub Command51_Click()
    <code>
End Sub
or 
Private Sub btnOpenReport_Click()
   <code>
End Sub
or
Private Sub cmdMoveNext_Click()
   <code>
End Sub

[Mike808]

Steve-

Run time is fixed!!! You are the man!! Last thing that I see is if the user enters 0 as a Form ID number, it opens the form for a new record (there is no ID's that are null or 0). I would like it to be invalid input and please try again.

Mike 808

[ssanfu]

Two changes (in BLUE) should do it:

Private Sub Command51_Click()
    Dim mainForm As String
    Dim strMsg As String
    Dim strForm As String

    Dim varInput As Variant
    Dim lngInput As Long
    Dim myVar As Long

    strForm = "frmCNAFCHGReqSubmitForm"
    mainForm = "frmCNAFCHGReqMenu"

    strMsg = "This form is to be edited by the original requester of the change request only!"
    strMsg = strMsg & vbCrLf & vbLf
    strMsg = strMsg & "Please enter ID number of original change request at this time."

    Beep
    varInput = InputBox(strMsg, "Change Request ID Number")

    'check to see if a number >0 is entered.
    If IsNumeric(varInput) And varInput > 0 Then
        'convert variant to a long integer.
        '(technically not required, but I didn't have to mess with the rest of the (working) code.
        lngInput = CLng(varInput)

        'get the value from the current form record source
        myVar = Nz(DLookup("ID", "CNAFCHGs", "ID = " & lngInput), 0)   '<<--will/can "ID" ever be zero???

        If lngInput = myVar Then
            DoCmd.Close acForm, mainForm
            DoCmd.OpenForm strForm, acNormal, , "[ID]=" & myVar
        Else    'password is incorrect
            MsgBox "Incorrect CNAF Change Form ID Number!" & vbCrLf & vbLf & "You are not allowed access unless the correct Change Form ID number is used.", vbCritical, "Please Try Again"
        End If
    Else
        'a number was not entered. Tell them
        MsgBox "Invalid input. MUST enter a number greater than 0 ...Try again", vbCritical, "Please Try Again"
    End If
End Sub

[Mike808]

Steve-

That was it! I knew it was something simple.....just trying to figure out after testing all day.....couldn't figure it out!!! Thanks so much! You rock!

Mike808

[ssanfu]

Glad to help....


Ready to mark this solved???