Error 3021 - No current record -

[Tiziana Venturini]

I am trying to open a recordset using this code.

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("select
Cod_Via,Num_Civ,Bis,Int,Let,Num_second,sc,num_zona FROM Dettagli
WHERE (([sc]='" & Me![cboSc] & "') OR ('" & Me![cboSc] & "' is null))
AND (([Num_Zona]='" & Me![cboZona] & "') OR ('" & Me![cboZona] & "' is
null))
AND (([Cod_Via]='" & Me![cboCod] & "') OR ('" & Me![cboCod] & "' is
null))")

but the OR statement don't work!!!!!

sc, num_zona and cod_via are TEXT.

Where is the error?

Thank in advanced


Tiziana Venturini

 

[Allen Browne]

I'm not sure which of the following you want:

1. You want all records there the value in the field matches or is null:
....
WHERE (([sc] = '" & Me![cboSc] & "') OR ([sc] Is Null))
AND (([Num_Zona] = '" & Me![cboZona] & "') OR ([Num_Zona] Is Null))
AND (([Cod_Via]='" & Me![cboCod] & "') OR ([Cod_Via] Is Null))"


2. You only want to use the combo in the WHERE clause if it has a value:

Dim strSQL As String
Dim lngLen As Long

'Build the WHERE clause from the non-null combos
If Not IsNull(Me![cboSc]) Then
strSQL = strSQL & "([sc] = """ & Me![cboSc] & """) AND "
End If
If Not IsNull(Me![cboZona]) Then
strSQL = strSQL & "([Num_Zona] = """ & Me![cboZona] & """) AND "
End If
'etc.

'Now chop of the trailing " AND " and prepend the "WHERE".
'Do nothing if no combos supplied a value.
lngLen = Len(strSQL) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strSQL = " WHERE " & Left$(strSQL, lngLen)
End If

'Now put the WHERE clause into the full SQL statement.
strSQL = "select Cod_Via, Num_Civ, Bis, Int, Let,Num_second, sc,num_zona
FROM Dettagli" & strSQL & ";"

Set rs = db.OpenRecordset(strSQL)
....


Please note that if the bound column of the combo is of type Number (not
Text), you should drop the extra quotes.

 

[Van T. Dinh]

('" & Me![cboSc] & "' is null) is ALWAYS False and the same for the other 2
"is Null" expressions.

If Me![cboSc] is actually Null, your expression becomes:

('' is Null) and an empty String '' is NOT Null.

 

[Tiziana Venturini]

Thanks for your help

I believe that there is an error here:

lngLen = Len(strSQL) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strSQL = " WHERE " & Left$(strSQL, lngLen)
End If

When I execute the routine I receive this message: syntax error 3075...

In debug window the value of strSQL = "select Cod_Via, Num_Civ, Bis, Int,
Let,Num_second, sc,num_zona FROM Dettagli WHERE ([sc] = "R1") AND
([Num_Zona] = "2071") AND " :
if the value of cboCod is null.

I have tried to correct the error....but I don't succeed.

Can you still help me?

Tiziana Venturini

I'm not sure which of the following you want:

1. You want all records there the value in the field matches or is null:
...
WHERE (([sc] = '" & Me![cboSc] & "') OR ([sc] Is Null))
AND (([Num_Zona] = '" & Me![cboZona] & "') OR ([Num_Zona] Is Null))
AND (([Cod_Via]='" & Me![cboCod] & "') OR ([Cod_Via] Is Null))"


2. You only want to use the combo in the WHERE clause if it has a value:

Dim strSQL As String
Dim lngLen As Long

'Build the WHERE clause from the non-null combos
If Not IsNull(Me![cboSc]) Then
strSQL = strSQL & "([sc] = """ & Me![cboSc] & """) AND "
End If
If Not IsNull(Me![cboZona]) Then
strSQL = strSQL & "([Num_Zona] = """ & Me![cboZona] & """) AND "
End If
'etc.

'Now chop of the trailing " AND " and prepend the "WHERE".
'Do nothing if no combos supplied a value.
lngLen = Len(strSQL) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strSQL = " WHERE " & Left$(strSQL, lngLen)
End If

'Now put the WHERE clause into the full SQL statement.
strSQL = "select Cod_Via, Num_Civ, Bis, Int, Let,Num_second, sc,num_zona
FROM Dettagli" & strSQL & ";"

Set rs = db.OpenRecordset(strSQL)
...


Please note that if the bound column of the combo is of type Number (not
Text), you should drop the extra quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I am trying to open a recordset using this code.

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("select
Cod_Via,Num_Civ,Bis,Int,Let,Num_second,sc,num_zona FROM Dettagli
WHERE (([sc]='" & Me![cboSc] & "') OR ('" & Me![cboSc] & "' is null))
AND (([Num_Zona]='" & Me![cboZona] & "') OR ('" & Me![cboZona] & "' is
null))
AND (([Cod_Via]='" & Me![cboCod] & "') OR ('" & Me![cboCod] & "' is
null))")

but the OR statement don't work!!!!!

sc, num_zona and cod_via are TEXT.

Where is the error?

Thank in advanced


Tiziana Venturini