Requery SubForm based on Checkbox

[ShredDude]

I'm attempting to have an unbound Subform updated based on the click of a Checkbox on my main form.

In an attempt to achieve this I've done the following in the chkBox's Before Update Event:

Private Sub chkIgnoreDNC_BeforeUpdate(Cancel As Integer)

Dim strSQL As String, strWhere As String

strWhere = "WHERE (((nz([tblSP]![DNCUntil]))<=Date()))"

strSQL = "SELECT (the big SQL Statement here...)" & _
        strWhere & ";"


If chkIgnoreDNC Then 

strWhere = "" 
strSQL = "SELECT (the big SQL Statement here...)" & _
        strWhere & ";"

End if

Forms![MainForm].[subForm].Form.RecordSource = strSQL


End Sub

The "big SQL Statement" is the same Query used to successfully populate the subform upon the main form's original load and at every change of the main form's recordsource, and it works flawlessly. The problem I'm getting here is that upon clicking the checkbox on the mainform, I get this error:

Runtime error: 3021

No Current Record

What am I missing here? (This is an unbound subform, displaying results independent of the mainform's dataset.)

I tried to achieve this task using the Filter and Filter On properties of the subform but couldn't make that work.

Any ideas most welcome!!

Thanks,

Shred

 

[dkinley]

I really didn't read all of it ... but I spotted ...

If chkIgnoreDNC .... try If chkIgnoreDNC = True (or False depending on what you wanted it to be).

-dK

 

[ShredDude]

Thanks for the response...

IF chkIgnoreDNC = TRUE results in same error.

 

[dkinley]

Tried the usual = -1 then?

I am wondering if it is because you are using BeforeUpdate. In a text control you can refer to .Text or .Value depending on where you are in the process but a checkbox doesn't have a .Text property. Perhaps moving to AfterUpdate would be more appropriate?

-dK

 

[ShredDude]

Just tried it in AfterUpdate, same error.

I guess I'm too much of a newbie too understand your "tried the -1 thing"

 

[dkinley]

Oh. A checkbox value is stored in a table as 0 (unchecked), -1 (checked), and null (nothing been done to it).

I was wondering if evaluating chkIgnoreDNC = - 1 worked or not.

Okay .. other than that, I just reread your post. In your SQL statement. Your Where = "" ... I am supposing that you are looking for an empty string?

-dK

 

[ShredDude]

Just found through the immediate window that

?forms![mainform].chkignorednc
-1

when the box is checked. so, "-1" = true? That's odd to me.

Tried that too in the If statement, same error.

I'm stumped. Need fresh air.

 

[dkinley]

Okay .. reread again ... structure your logic as follows .....

Dim strSQL As String, strWhere As String

If chkIgnoreDNC = -1 Then 
     strWhere = "WHERE (((nz([tblSP]![DNCUntil]))<=Date()))"
     strSQL = "SELECT (the big SQL Statement here...)" & _
          strWhere & ";"
Else
     strWhere = "" 
     strSQL = "SELECT (the big SQL Statement here...)" & _
          strWhere & ";"
End if

Forms![MainForm].[subForm].Form.RecordSource = strSQL

-dK

 

[ShredDude]

No, I was just trying to eliminate the WHERE clause from the SQL string.

the same error occurs whether the SQL has been modified in the SUB or not from it's original form, that is if the chk box was checked or not.

So, if the WHERE clause is in there or not, I get the same error.

I use the original SQL string to requery this subform based on other events that occur during the app's use (not changing the recordsource), and have no issues. But now that i'm trying to change the recordsource, I get this error.??

 

[ShredDude]

Tried your restructure, same thing. Actually I reversed it to reflect that if chkbox = -1 I do no want a filter applied.

I think your example is the same as mine since I set the condition a the top that only gets changed if the chkbox=-1

thanks for the cycles on it...

 

[ShredDude]

this was my other unsuccessful approach:

''If chkIgnoreDNC Then
''
''With Forms![mainform].[subform]
''    .Form.Filter = ""
''    .Form.FilterOn = False
''    .Requery
''End With
''
''
''Else
''
''With Forms![mainform].[subform]
''    .Form.Filter = "nz([DNCUntil])<=Date()"
''    .Form.FilterOn = True
''    .Requery
''End With
''
''End If

It would start popping up parameter Value dialogs for whatever I placed win the Filter property. The [DNCUntil] field is a member of the Query assigned to the subform's Record Source

 

[dkinley]

No problem. Because of the error and just in case it is a referential issue, try adding ... DoCmd.RunCommand acCmdSaveRecord ... before running all of this - to see if it goes away.

-dK

 

[ShredDude]

Inserted DoCmd.RunCommand acCmdSaveRecord at top of SUB...

Same Error, just took longer.

I don't get it. The underlying query doesn't rely on any information contained on the main form. To validate the integrity of my "strSQL" I ran it independently and it generated the expected results. I then replaced the strSQL in the offending line of code with the original Query Name that is in the subform's Record Source before you'd get to this code.

eg:

Forms![mainform].[subform].Form.RecordSource = qryOriginal

Same Error results, even though that query is what got the sub form populated in the first place upon the mainform's initial load.

 

[Franko]

Just tried this and it works fine.

Me.SubformName.Form.RecordSource = strSql
Me.SubformName.Requery