I am developing a database where I have to create a report with VBA.
This report is a main/sub report with different subreports attached and selected by the user. Also there are sometimes several blank fields in the subreport depending on de the main record and I don't want those blank records occupy spaces on the paper.
Now I am stuck with this error trying to read data from a table to check if a field have a value or not.
This is the code so far:
Sub Test()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim Aantal As Integer
Dim Apparaat As String
Apparaat = Forms!Toestelgegevens!cboToestel.Value
If Len(Apparaat) = 0 Then
MsgBox "Geen toestel geselecteerd!", vbCritical, "Fout!"
Exit Sub
End If
DoCmd.OpenReport "Toestelgegevens", acViewDesign, , "[Toestel] = '" & Apparaat & "'"
If selKalibratie.Value = True Then
strSQL = "SELECT * FROM Kalibratie WHERE Kalibratie.[Toestel] = '" & Apparaat & "' ;"
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
If Len(rst.Fields("Middel")) > 0 Then
Debug.Print rst.Fields("Middel")
End If
rst.MoveNext
Loop
End If
rst.Close
db.Close
End Sub
Sorry, for some dutch words in it.
When I run this code I get error 91 Objectvariable or Block variable With... in the line in Red. The variable Apparaat have the correct value in it.
I cannot find what is wrong here. I am using the latest version of MS Access.
This report is a main/sub report with different subreports attached and selected by the user. Also there are sometimes several blank fields in the subreport depending on de the main record and I don't want those blank records occupy spaces on the paper.
Now I am stuck with this error trying to read data from a table to check if a field have a value or not.
This is the code so far:
Sub Test()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim Aantal As Integer
Dim Apparaat As String
Apparaat = Forms!Toestelgegevens!cboToestel.Value
If Len(Apparaat) = 0 Then
MsgBox "Geen toestel geselecteerd!", vbCritical, "Fout!"
Exit Sub
End If
DoCmd.OpenReport "Toestelgegevens", acViewDesign, , "[Toestel] = '" & Apparaat & "'"
If selKalibratie.Value = True Then
strSQL = "SELECT * FROM Kalibratie WHERE Kalibratie.[Toestel] = '" & Apparaat & "' ;"
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
If Len(rst.Fields("Middel")) > 0 Then
Debug.Print rst.Fields("Middel")
End If
rst.MoveNext
Loop
End If
rst.Close
db.Close
End Sub
Sorry, for some dutch words in it.
When I run this code I get error 91 Objectvariable or Block variable With... in the line in Red. The variable Apparaat have the correct value in it.
I cannot find what is wrong here. I am using the latest version of MS Access.