MsgBox instead of Run-time error 3101

[BPBP]

This run-time error 3101 is caused by the entry of an ID into a textbox which is not inside the "One" side of the table. As I have form.refresh in the afterupdate event of this textbox, immediately it pops out this error and prompts me to end or debug.

How do i make it prompt a msgbox instead of going into VB?

I tried inputting an if statement on error 3101 in the On_error event of the form below as shown

Private Sub Form_Error(DataErr As Integer, Response As Integer)
' To prevent multiple user opening new TRF getting same number PART1
On Error GoTo Err_Form_Error


Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub
Err_Form_Error:

MsgBox Err.Description
Resume Exit_Form_Error
End Sub
' To prevent multiple user opening new TRF getting same number PART2
Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If
If DataErr = 3101 Then
MsgBox ("No Such TRF Number! Please double check")
End If

End Function




This only half worked. It comes out only after getting going into the VB debug screen and only about quitting the VB screen then the msgbox will show.
Please advise.

 

[RuralGuy]

How about just doing a DCount() or DLookup() in the BeforeUpdate event of the TextBox and deny the entry if you can not find it?

 

[BPBP]

How about just doing a DCount() or DLookup() in the BeforeUpdate event of the TextBox and deny the entry if you can not find it?

erm... how do i code this for Dlookup, its the primary key in the other table. Does the below look right?

dim testing as string
testing = DLookup("[Job No]", "[QT Job Record]", "[Job No]" = " & Forms![QT Job Update]![Job No])

THen put an IF statement to test the testing string.... hmmm what happens with the Dlookup if it doesnt find anything?\

and whats the command for denying entry?

 

[RuralGuy]

If [Job No] is numeric then:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = " & Me.[Job No]) Then
...and if it is a string then:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = '" & Me.[Job No] & "'") Then
MsgBox "The Job Number of [" & Me.[Job No] & "] is a duplicate."
Cancel = True
End If

 

[BPBP]

If [Job No] is numeric then:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = " & Me.[Job No]) Then
...and if it is a string then:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = '" & Me.[Job No] & "'") Then
MsgBox "The Job Number of [" & Me.[Job No] & "] is a duplicate."
Cancel = True
End If

Actually the the opposite of what you posted is what i actually want. But based on your example i added a goto to jump till the end and it works.
Thanks.

 

[RuralGuy]

Instead of a nasty GoTo, all you needed to do was:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = " & Me.[Job No]) = 0 Then

 

[BPBP]

Instead of a nasty GoTo, all you needed to do was:
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = " & Me.[Job No]) = 0 Then

Why is Goto nasty?

 

[RuralGuy]

It leads to spaghetti code and generally frowned on by current developers. It is almost always possible to write structured code that avoids *all* GoTo's except the On Error GoTo at the beginning of the procedure for error handling.

 

[BPBP]

Thanks i've implemented the dcount = 0 code.

It works but there is a small issue, if the user doesnt key anything into the field and hits enter, run-time error '3075' will result. It states ...Syntax error (missing operator) in query expression '[Job No]'=' ...

 

[gemma-the-husky]

it doesnt like nulls, but it looks slightly wrong

make it

... "[Job No] = " & nz([Job No],0) ) = 0

you had an extra quote in the blue bit
the nz in red bit allows for a blank - nz is a function that turns a blank (null) into something else - in this case, if its blank, treat it as zero - then your search will work, but find no records
you shouldnt need the me. generally

 

[RuralGuy]

I just noticed there is an extra quote in the code.
If DCount("[Job No]", "[QT Job Record]", "[Job No]" = " & Me.[Job No]) = 0 Then
...should be:
If DCount("[Job No]", "[QT Job Record]", "[Job No] = " & Me.[Job No]) = 0 Then

 

[BPBP]

yes, i noticed the additional quote earlier and corrected it in the first implementation with Goto.

The NZ solved my current issue.

Thanks Both.

 

[RuralGuy]

Excellent! It looks like you are off and rolling. Glad we could help.