setfocus error

[tracktraining]

Hi All,

I have the following code to check if the doc+revision entered is in the doc
table. If it is not in the doc table, then a message appears and the user
needs to enter a new doc or revision; no data (entry) should go to the
empdocstatus until the information is correct. In my case, when I finish
entering the doc+revision (incorrectly) then click close, the message
appears, but the user is unable to enter the correct information and the
whole form is closed and the information is written to the empdocstatus
table.

How can I fix this? Please help if possible.

'Check If Document+Revision Exist
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim DataErr As String
Dim Response As String
newDocID = Me.DocID
newRevision = Me.Revision
chkdocstatus = True

strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

If rst.EOF Then
chkdocstatus = False
MsgBox "Document Number/Revision Wrong form_beforeupdate."
Me.Revision.SetFocus
Me.DocID.SetFocus
End If

End Sub

Thanks,
Tracktraining

 

[Lord Kelvan]

the fact you are using + and not & conserns me i may be wrong but as
far as i know you are supose to use & to concatinate strings in vba

anywhos i see no reason why the form is closing based on this code and
it seems you mis-understand code

Me.Revision.SetFocus
Me.DocID.SetFocus

that is the same as

Me.DocID.SetFocus

just for your information

this event is being activated on the closing of a form so of course
the form is going to close.

what you need to do is have a button such as save or close and on that
button you add your code.

you seem to have variables in your code that are not doing anything
such as chkdocstatus and DataErr

here is the code modified from yours i woudl put on a button.

basically if the eof thing happens then it displays the message and
sets focus to docid else it closes the form.

Private Sub btnsave_click()


Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Response As String
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" &
newDocID &
"' AND " & _
" DocInfo.Revision = '" & newRevision & "'"


Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong form_beforeupdate."
Me.DocID.SetFocus
else
DoCmd.Close
End If


End Sub


hope this helps

Regards
Kelvan

 

[tracktraining]

THANKS LOT FOR THE HELP. The code works great.

 

[Lord Kelvan]

no problem glad to help

Regards
Kelvan