Dim rec As ADODB.Recordset - twice in EVENT procedure?

[lmn]

Hi there,
Below is code I have in an On Current Event item. I want it to run 2 SQL statements - one to see if they have an expired license and one to see if they have expired insurance. I can run it fine with just one of the statements, but it doesn't like when I have 2 statements. Am I missing a line in between or should I be utilizing something else? I'm already using the On Open slot for maximizing the form and the On Activate....

any ideas?

---------------------


Private Sub Form_Current()

If Me!MailAddressSameAsPhys = No Then
Me!OpenSubformMailAddress.Visible = True
Else
Me!OpenSubformMailAddress.Visible = False
End If

If Me!CanDoRemodelWork = Yes Then
Me!OpenVendorRemodelDetails.Visible = False
Else
Me!OpenVendorRemodelDetails.Visible = True
End If

Dim rec As ADODB.Recordset
Dim SQLstring As String
SQLstring = &quot;SELECT TBLVendorLicensesByState.StateAbbr,TBLVendorLicensesByState.VendorID FROM TBLVendorLicensesByState WHERE(((TBLVendorLicensesByState.VendorID)=&quot; & Me![VendorID] & &quot AND(LicenseExpDt < #&quot; & Now & &quot;#));&quot;

Set rec = CurrentProject.Connection.Execute(SQLstring)

If Not (rec.EOF) Then
MsgBox (&quot;General Contractor has expired license(s). Please update.&quot
End If

rec.Close
Set rec = Nothing

Dim rec As ADODB.Recordset
Dim SQLstring As String
SQLstring = &quot;SELECT TBLVendorInfo.VendorID FROM TBLVendorInfo WHERE(((TBLVendorInfo.VendorID)=&quot; & Me![VendorID] & &quot AND(InsuranceExpDt < #&quot; & Now & &quot;#));&quot;
Set rec = CurrentProject.Connection.Execute(SQLstring)

If Not (rec.EOF) Then
MsgBox (&quot;General Contractor has expired insurance certificate. Please update.&quot
End If

rec.Close
Set rec = Nothing


End Sub

 

[jbehrne]

Either rename the second bit like this:

Dim rec As ADODB.Recordset
Dim rec2 As ADODB.Recordset
Dim SQLstring As String
Dim SQLstring2 As String

SQLstring = &quot;SELECT TBLVendorLicensesByState.StateAbbr,TBLVendorLicensesByState.VendorID FROM TBLVendorLicensesByState WHERE(((TBLVendorLicensesByState.VendorID)=&quot; & Me![VendorID] & &quot AND(LicenseExpDt < #&quot; & Now & &quot;#));&quot;

Set rec = CurrentProject.Connection.Execute(SQLstring)

If Not (rec.EOF) Then
MsgBox (&quot;General Contractor has expired license(s). Please update.&quot
End If

rec.Close
Set rec = Nothing

SQLstring2= &quot;SELECT TBLVendorInfo.VendorID FROM TBLVendorInfo WHERE(((TBLVendorInfo.VendorID)=&quot; & Me![VendorID] & &quot AND(InsuranceExpDt < #&quot; & Now & &quot;#));&quot;
Set rec2= CurrentProject.Connection.Execute(SQLstring2

If Not (rec2EOF) Then
MsgBox (&quot;General Contractor has expired insurance certificate. Please update.&quot
End If

rec.Close
rec2.Close
Set rec = Nothing
set rec2 = Nothing

Or try running the code on different events... However, you would probably be best to incorporate the code into a module and then run the module.


If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations