form verification (1 Viewer)

[benh76135]

I'm not sure how to see if anyone has done this yet. I searched a bit with no results.

My work log form has had a flaw pop up mostly due to the end users being dumb in my opinion but i know it can be programmed around so now to do it.

the form works like this : hit some buttons to fill in current user, date, time, ect. then from a combo box pick an active [PO Number], then from a combo box pick a [part name] based on that active po, then from a combo box pick a [job] based on that [part name]. theres a few more fields but you get the point.

the problem, one guy endlessly goes back after filling out the form and changes the [po number] but all the other fields he does not change which are now wrong.

so i need a way for the form to check itself. and here is how far ive gotten and i know its wrong. If the IIF statement would work I might be able to figure out the rest.



Private Sub Form_AfterUpdate()

If IsNull([Part Name]) Then
GoTo mark2115
Else
IIf ([part name] = (select[purchase order list].[part name] from [purchase order list] where [purchase order list].[po number]=forms![work done]![po number]),GoTo mark2115,GoTo mark2114)
mark2114
DoCmd.OpenForm "Part Name Error"
mark2115
end if
End Sub

 

[static]

Private Sub Form_AfterUpdate()
    dim pn as string
    If not IsNull([Part Name]) Then
        pn=nz(currentdb.openrecordset("select [part name] from [purchase order list] where [po number]=" & forms![work done]![po number])(0),"")
        if [part name] <> pn then
            DoCmd.OpenForm "Part Name Error"
        end if
    end if
End Sub

 

[BeeJayEff]

Private Sub Form_AfterUpdate()

If IsNull([Part Name]) Then
GoTo mark2115
Else
IIf ([part name] = (select[purchase order list].[part name] from [purchase order list] where [purchase order list].[po number]=forms![work done]![po number]),GoTo mark2115,GoTo mark2114)
mark2114
DoCmd.OpenForm "Part Name Error"
mark2115
end if
End Sub

Ouch ! It's a long long time since I've seen GoTos used to determine control flow other than in error handling. Takes me back to Fortran IV. Please compare and contrast the readability of your code with that of static.

An alternative answer might be for you to use the OnEntry event of the [PONumber] to set the [PartName], [Job], etc controls to null.

 

[CJ_London]

also not sure if mark2114/5 are actually goto targets

they need colons at the end

 

[benh76135]

yea i use goto, sorry very old habit from learning basic on ms dos i left out the colons when i was retyping in message

i did try statics idea, the nz gave me nothing literally, so it didnt compair.

some po numbers do have multiple part names on their po number, and i think that is what causes the error on that idea ?

btw thanks for the help

edit : oh, i read it a couple times and i like the idea you had about on entry, im gonna try that out

 

[CJ_London]

 i left out the colons when i was retyping in message

always better to copy and paste - leaving things out just leads others down the wrong track to solving the problem

 

[static]

i did try statics idea, the nz gave me nothing literally, so it didnt compair.

It wasn't my idea, I just rewrote what you wrote.
nz() just replaces a null with something else.
Copy the sql to the query editor and see what it does.


Users going back to the beginning of the current record instead of going to a new record (or whatever is supposed to happen), suggests something is wrong with your form design. Probably better to work out what that is rather than adding bits of code hither and yon.

 

[Wayne]

I'm not sure if Static is right, and why your form doesn't go to a new record once a complete record is entered. But to warn your user not to change an existing record you could try a message box in the Before Update event of the PO Number field:

If Not IsNull(Me.PONumber) Then
     If MsgBox ("Are you trying to add a new PO Number?",vbYesNo) = vbYes Then
     DoCmd.GoToRecord acDataForm,"yourformname", acNewRec
     End If
     Else
     MsgBox "You cannot change an existing PO Number.",vbInformation
     Cancel=True
     Me.PONumber.Undo
     Exit Sub
End If

At least that would give the user a warning before he/she screws up the data. But I agree with Static - you should look into why your form isn't moving to a new record once data entry is complete.