Lost focus event

[Guest]

Hi,

I have below code in lost focus event.

****************************

Private Sub SSN_LostFocus()
Dim dbslog As Database, rstlog As Recordset, zsql As String, App As String

Set dbslog = CurrentDb
zsql = "SELECT AppSeq FROM tbl_appraisalreview WHERE SSN ='" & SSN & "'and
Audit_Type = 'Processor'"
Set rstlog = dbslog.OpenRecordset(zsql, dbOpenSnapshot)

With rstlog
rstlog.MoveLast
If rstlog.RecordCount > 0 Then
App = !AppSeq
MsgBox "There is already one BE audit processed with this social and
App Sequence# " & App & " "
End If
End With

End Sub

****************************

I am trying to show Appseq # for a Social in the mssg box when a duplicate
social is entered. The above code works fine if the recordcount = 1. But I
have more than one record count for a social. Is ther any way I can show all
AppSeq's number for a social in the mSsg box.

For example: If I have 000-00-0000(SSn)/00(AppSeq)
000-00-0000/01
000-00-0000/02

Is it possible t modify the above code to get the mssg as "There exsists 3
BE audits processed with this social and App Sequence# "00","01","02"" wheh I
enter the social as 000-00-0000 in the form. Can anyone please advise me on
this.

Thanks.

 

[Guest]

I would suggest you use the After Update event rather than Lost Focus. The
After Update only fires after you make a change to the control's value and
then move the focus away from the control. The Lost Focus will fire even if
you don't make any changes to the control's value.

I am not sure how long the message in a message box can be, how long the
field can be or how many rows you may get returned, so I would be apprhensive
about concatenationg the values in a text box. If you feel save doing it,
you could do this:
Dim strMsg As String

With rstlog
.MoveLast
If .RecordCount > 0 Then
strMsg = "There is already 1 BE audit processed with this social
and
App Sequence#" & !AppSeq
If .RecordCount > 1 Then
strMsg = Replace(strMsg, "There is already 1", _
"There are already & .RecordCount")
End If
.MoveFirst
Do While Not .EOF
strMsg = strMsg &vbNewLIne & !App
Loop
MsgBox strMsg
End If
End With

 

[OldPro]

Hi,

I have below code in lost focus event.

****************************

Private Sub SSN_LostFocus()
Dim dbslog As Database, rstlog As Recordset, zsql As String, App As String

Set dbslog = CurrentDb
zsql = "SELECT AppSeq FROM tbl_appraisalreview WHERE SSN ='" & SSN & "'and
Audit_Type = 'Processor'"
Set rstlog = dbslog.OpenRecordset(zsql, dbOpenSnapshot)

With rstlog
rstlog.MoveLast
If rstlog.RecordCount > 0 Then
App = !AppSeq
MsgBox "There is already one BE audit processed with this social and
App Sequence# " & App & " "
End If
End With

End Sub

****************************

I am trying to show Appseq # for a Social in the mssg box when a duplicate
social is entered. The above code works fine if the recordcount = 1. But I
have more than one record count for a social. Is ther any way I can show all
AppSeq's number for a social in the mSsg box.

For example: If I have 000-00-0000(SSn)/00(AppSeq)
000-00-0000/01
000-00-0000/02

Is it possible t modify the above code to get the mssg as "There exsists 3
BE audits processed with this social and App Sequence# "00","01","02"" wheh I
enter the social as 000-00-0000 in the form. Can anyone please advise me on
this.

Thanks.

Sure. Just use a while loop to iterate through each record in the
recordset, creating your final message a piece at a time.

With rstlog
.MoveLast
If .RecordCount > 0 Then
.movefirst
while not .eof
strMessage=strMessage & !AppSeq & ","
.movenext
wend
App = !AppSeq
MsgBox "The Following BE audits were processed with this
social:" & vbcrlf & strMessage

End If
End With

 

[Guest]

Thanks for all your help. It works perfectly fine.