No Current Record Error Message (1 Viewer)

[psimpson]

I get a 'No Current Record' MS Access error message on the first line of code below:

If rst("PROFESSIONAL_REG_NUMBER") > 0 Then Set db1 = CurrentDb()
strSQL1 = "SELECT * FROM Table2 WHERE Professional_REG_Number = " & rst("PROFESSIONAL_REG_NUMBER")
'Set db1 = CurrentDb()
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
With rst1
If Not (.BOF Or .EOF) Then
.Edit
.Delete
.Edit
.Update
End If
.Close
End With
Set rst1 = Nothing
Set db1 = Nothing
Else
'Do Nothing
End If

The expected result is for the 'Else' statement to be executed if any records do not exist.

Can anyone tell me why this is happening?

Any help is appreciated.

Thanks.

 

[VilaRestal]

Firstly please post code as code.

Secondly, What is rst? A function?

If it's a recordset then recordsets aren't collections or arrays and can't be used like that.

Thirdly,

If rst("PROFESSIONAL_REG_NUMBER") > 0 Then Set db1 = CurrentDb()

Is a single-line If statement. No Else or End If can be added to it. So the lines

Else
'Do Nothing
End If

at the end mean nothing to the compiler and so won't compile.

Also,

.Edit
.Delete
.Edit
.Update

is not how to delete a record from a recordset.

And if you want to just delete all the records from that query then just

DoCmd.RunSQL "DELETE FROM Table2 WHERE Professional_REG_Number = " & rst("PROFESSIONAL_REG_NUMBER")

So, having said all that, if rst is a function, then the code should be:

    If rst("PROFESSIONAL_REG_NUMBER") > 0 Then 
        DoCmd.RunSQL "DELETE FROM Table2 WHERE Professional_REG_Number = " & rst("PROFESSIONAL_REG_NUMBER")
    End If

If however rst is a recordset and PROFESSIONAL_REG_NUMBER is one of its fields then it should be:

    If rst!PROFESSIONAL_REG_NUMBER > 0 Then 
        DoCmd.RunSQL "DELETE FROM Table2 WHERE Professional_REG_Number = " & rst!PROFESSIONAL_REG_NUMBER
    End If

or

    If rst.Fields("PROFESSIONAL_REG_NUMBER") > 0 Then 
        DoCmd.RunSQL "DELETE FROM Table2 WHERE Professional_REG_Number = " & rst.Fields("PROFESSIONAL_REG_NUMBER")
    End If

 

[jdraw]

Where do you DIM and SET rst? We're only seeing a piece of your code.

You are referencing

rst("PROFESSIONAL_REG_NUMBER")

but you have not set up rst (at least not in the code you've provided).

 

[psimpson]

VilaRestal

rst references a recordset. Each of the suggested options where tried, but still gets the same error message on that same line.

Do you have any other suggestions, which I might try?

 

[psimpson]

JDRAW - The beginning of the code looks like:

Dim db As DAO.Database
Dim db1 As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String

Set db = CurrentDb()
strSQL = "SELECT * FROM table1 WHERE table1.Project_Number='" & Me.PROJECT_NUMBER & "'"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If rst("PROFESSIONAL_REG_NUMBER") > 0 Then
DoCmd.RunSQL "DELETE FROM table2 WHERE Professional_REG_Number = " & rst("PROFESSIONAL_REG_NUMBER")
End If

Thanks.

 

[jdraw]

It appears to me that you are opening a recordset rst - which will get you the first record only - unless there are no records in that recordset.

If, in that first record PROFESSIONAL_REG_NUMBER > 0 then Delete records in Table2 where the PROFESSIONAL_REG_NUMBER = PROFESSIONAL_REG_NUMBER in the first record.

You are not looping through a recordset.

I think it would be helpful if you told us WHAT exactly you are trying to do in plain English, and then see what options/suggestions are offered.

 

[psimpson]

Basically, there is a form which contains data from multiple tables. When a record is to be deleted, any associated records are to be deleted too. So, for a particular registration number in the master table, if there is a professional related to it, the professional record should be deleted from the corresponding professional table using the professional_reg_number and then the registration record. Otherwise, the registration only can be deleted.

I hope this helps.

Thanks.