recordset query

[tizwaz]

I am trying to write code to run which will check if there is an existing record in my employment table with no End Date. ie staffID matches one on existing form and End Date is not null.

Thought about trying to open a recordset and if the recordset.record count was >0 giving a message. I'm trying to do it with DAO but am getting nowhere.

 

[Golom]

Your statements appear to be in conflict

... with no End Date ... implies END DATE IS NULL

... and End Date is not null states the opposite.

Which is it?

What have you done so far?

 

[tizwaz]

sorry I mean is null

so far done
Dim rs As dao.Recordset
Dim intstaffID As Integer
Dim strSQL As String
Set db = CurrentDb()
intstaffID = Me.txtStaffID
strSQL = "Select * from TblEmployment whereStaffID = intstaffID and isNull (ActualFinish)"
If rs.RecordCount > 0 Then
MsgBox "There is a live employment record for this person - please check!"
End If
Exit Sub
DoCmd.Close acForm, "frmStaff"
DoCmd.OpenForm "frmNewEmploymentRecord"
DoCmd.GoToRecord , , acNewRec
Forms!frmNewEmploymentRecord!txtStaffID.DefaultValue = intstaffID

End Sub

but when I try and click the cmd button I get run time error 91
object variable or with block variable not set and the
If rs.RecordCount > 0 Then
line is highlighted

 

[Golom]

You have created the SQL but haven't created the recordset. You need something like

strSQL = _
"Select * " & _
"From TblEmployment " & _ _
"Where StaffID = " &  intstaffID & " and ActualFinish IS NULL"
[b]
Set rs = CurrentDB.OpenRecordset ( strSQL )[/b]

 

[thendrickson]

I do not know as much about access, but I have had troubles with ADO in VB6 and record count so I always check for eof and bof instead of > 0

so....

If not rs.eof and not rs.bof then etc.

And yes checking for both eof and bof is probably overkill

 

[thendrickson]

oops I should have read his post closer Sorry Golom is of course correct

 

[Golom]

You also have a problem here

If rs.RecordCount > 0 Then
    MsgBox "There is a live employment record for this person - please check!"
    End If
    Exit Sub
[COLOR=blue]
DoCmd.Close acForm, "frmStaff"
DoCmd.OpenForm "frmNewEmploymentRecord"
DoCmd.GoToRecord , , acNewRec
Forms!frmNewEmploymentRecord!txtStaffID.DefaultValue = intstaffID
[/color]
End Sub

The code in blue will never be executed because the Exit Sub is outside the IF statement and is always executed. Should it be before End If?

 

[tizwaz]

thanks I thought I had the set rs = db.openrecordset (strSQL) line in there but I must have deleted it by mistake.

It runs now and I've moved the exit sub to before the end if. However the 2nd part of the code never runs. It always gives the msgbox. Don't understand.

Private Sub Command5_Click()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim intstaffID As Integer
Dim strSQL As String
Set db = CurrentDb()
intstaffID = Me.txtStaffID
strSQL = "Select * from TblEmployment where staffID = " & intstaffID & " and ActualFinish IS NULL"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
MsgBox "There is a live employment record for this person - please check!"
Exit Sub
End If

DoCmd.Close acForm, "frmStaff"
DoCmd.OpenForm "frmNewEmploymentRecord"
DoCmd.GoToRecord , , acNewRec
Forms!frmNewEmploymentRecord!txtStaffID.DefaultValue = intstaffID

End Subub

 

[tizwaz]

So sorry - it is working I was picking the wrong record to test. Thanks Gollom for all your help - this was driving me mad.