Subform Datasheet With Changing Query Crashes

G

guyuz

New member
Local time
Today, 16:13
Joined
Apr 2, 2014
Messages
9
Hello everyone, I have an issue with a table embedded in a form, with a query that changes based on what is done in the form. The problem is a bit long, so please bear with me.

I have a table of events, tblActivitiesDates which have an activity type and a date.
I have a form which has a datasheet with a query source (a separate datasheet form embedded in this one), and 2 radio buttons. The query that is used uses radio buttons this way:
-Activity Type Filter: A radio button, that when clicked a combo box appears, with a list of activity types to choose from. Basically, filtering by Activity Type.
-Date Type Filter: A radio button, that when clicked 2 text boxes appear, in which you write the dates between which you want to see the events organized in the table. Basically filtering by dates.

HOW IT WORKS:
To make reading easier, Activity Type Filter=ATF, Data Type Filter=DTF.
There are 4 possible options:
-ATF=ON and DTF=ON
-ATF=ON and DTF=OFF
-ATF=OFF and DTF=ON
-ATF=OFF and DTF=OFF.

For each option, I have written a line of code in the appropriate OnChange/AfterUpdate/Whatnot events in the form:
CurrentDb.QueryDef("qryActivitiesDates").SQL = "SELECT..."
according to whatever option is currently active.

THE PROBLEM:
IMPORTANT: When I enter values into the form, and instead of embedding my datasheet form I simply open it every time from the navigation pane in a new window, the datasheet is filled appropriately and works 100%. Of course, I have to reopen it every time I change something so the query will rerun.

When the datasheet is embedded though, I have the following problem:

After the table crashes (will be explained soon), I re-embed it to the form before trying it again. The first time I re-embed, no matter what I do, the query doesn't update nor crash. It's pretty much dead. The second time I re-embed, this is what happens:
Say I start with the option ATF=ON, DTF=OFF, it will work fine, updating when needed, but if I tick DTF=ON, regardless of the dates I enter, the table will only change according to the first option (While when open in a new window will show the correct table entries), ignoring the fact that the QueryDef.Sql has changed. Once I tick ATF=OFF, the table crashes and I get the error:"Run-time error'3420': Object invalid or no longer set".
The exact same thing happens when I go DTF=ON,ATF=OFF. It ignores the event types I enter, and once I tick DTF=OFF, it crashes with the exact same error.

Please note that I added Me.Activities_DS.Form.Requery (datasheet's name is Activities_DS) in every place it should update.

For your convenience, I'm trying to keep my thread as concise as possible (Because nobody likes reading walls of text). For that reason, I'm not immediately posting all my code. If there's any info/code you want to see, please just ask, and I will reply immediately with everything I can offer.

Sincerly, Guy.
 
Oh, sorry I got lost. :)
Show the (whole) code in where you get the runtime error.
Else post a stripped version of your database with some sample data, (zip it) + a short description how to reproduce the error.
 
JHB said:
Oh, sorry I got lost. :)
Show the (whole) code in where you get the runtime error.
Else post a stripped version of your database with some sample data, (zip it) + a short description how to reproduce the error.
Click to expand...

Thanks for replying!
This is the entire code for the form, most of it isn't problematic as far as I know but since all the code work together, no harm in having it here. Error happens only inside code snippets 2 and 3 (Which are identical but for the different buttons), at the lines: Me.Activities_DS.Form.Requery

Code: 
Private Sub cboActivity_Change()
If cboActivity.Value <> "" Then
'Activity type combo box change
    Me.Activities_DS.Form.Requery
End If
End Sub

Code: 
Private Sub optActivityType_AfterUpdate()
'Filter by Activity Type radio button
If optActivityType.Value = True Then
    cboActivity.Visible = True
Else
    cboActivity.Value = ""
    cboActivity.Visible = False
End If

If optActivityType.Value = False And optDates.Value = True Then
'Query for when filter only by dates
    CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE (((tblActivitiesDates.Date) Between [Forms]![frmActivities]![txtDate1] And [Forms]![frmActivities]![txtDate2])) ORDER BY tblActivitiesDates.Date ASC;"
    If (txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString) Then
        Me.Activities_DS.Form.Requery
    End If
Else
    If optActivityType.Value = True And optDates.Value = False Then
    'Query for when filter only by Activity Type
        CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE [Forms]![frmActivities]![cboActivity] = tblActivitiesDates.Activity_ID ORDER BY tblActivitiesDates.Date ASC;"
        If cboActivity.Value <> "" Then
            Me.Activities_DS.Form.Requery
        End If
    Else
    'Query for when filter by none (empty query)
        If optActivityType.Value = False And optDates.Value = False Then
            CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE 1=0 ORDER BY tblActivitiesDates.Date ASC;"
            Me.Activities_DS.Form.Requery
        Else
        'Query for when filter by both date and activity type
            CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE (((tblActivitiesDates.Date) Between [Forms]![frmActivities]![txtDate1] And [Forms]![frmActivities]![txtDate2])) AND [Forms]![frmActivities]![cboActivity] = tblActivitiesDates.Activity_ID ORDER BY tblActivitiesDates.Date ASC;"
            MsgBox (CurrentDb.QueryDefs("qryActivitiesDates").SQL)
            If cboActivity.Value <> "" And (txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString) Then
                 Me.Activities_DS.Form.Requery
            End If
        End If
    End If
End If
End Sub

Code: 
Private Sub optDates_AfterUpdate()
'Filter by dates radio button
If optDates.Value = True Then
    txtDate1.Visible = True
    txtDate2.Visible = True
Else
    txtDate1.Value = vbNullString
    txtDate2.Value = vbNullString
    txtDate1.Visible = False
    txtDate2.Visible = False
End If

If optActivityType.Value = False And optDates.Value = True Then
'Query for when filter only by dates
    CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE (((tblActivitiesDates.Date) Between [Forms]![frmActivities]![txtDate1] And [Forms]![frmActivities]![txtDate2])) ORDER BY tblActivitiesDates.Date ASC;"
    If (txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString) Then
        Me.Activities_DS.Form.Requery
    End If
Else
    If optActivityType.Value = True And optDates.Value = False Then
    'Query for when filter only by Activity Type
        CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE [Forms]![frmActivities]![cboActivity] = tblActivitiesDates.Activity_ID ORDER BY tblActivitiesDates.Date ASC;"
        If cboActivity.Value <> "" Then
            Me.Activities_DS.Form.Requery
        End If
    Else
    'Query for when filter by none (empty query)
        If optActivityType.Value = False And optDates.Value = False Then
            CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE 1=0 ORDER BY tblActivitiesDates.Date ASC;"
            Me.Activities_DS.Form.Requery
        Else
        'Query for when filter by both date and activity type
            CurrentDb.QueryDefs("qryActivitiesDates").SQL = "SELECT tblActivities.[Activity Name], tblActivities.[Activity Type], tblActivitiesDates.[Client Name], tblActivitiesDates.Date, tblActivitiesDates.Lecturer, tblActivitiesDates.[Head Instructor] FROM tblActivitiesDates INNER JOIN tblActivities ON tblActivitiesDates.Activity_ID = tblActivities.ID WHERE (((tblActivitiesDates.Date) Between [Forms]![frmActivities]![txtDate1] And [Forms]![frmActivities]![txtDate2])) AND [Forms]![frmActivities]![cboActivity] = tblActivitiesDates.Activity_ID ORDER BY tblActivitiesDates.Date ASC;"
            MsgBox (CurrentDb.QueryDefs("qryActivitiesDates").SQL)
            If cboActivity.Value <> "" And (txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString) Then
                 Me.Activities_DS.Form.Requery
            End If
        End If
    End If
End If
End Sub

Code: 
Private Sub txtDate1_AfterUpdate()
'Updating date #1
If txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString Then
    If DateDiff("d", txtDate1.Value, txtDate2.Value) < 0 Then
        Dim temp As Date
        temp = txtDate1.Value
        txtDate1.Value = txtDate2.Value
        txtDate2.Value = temp
    End If
    Me.Activities_DS.Form.Requery
End If
End Sub
Code: 
Private Sub txtDate2_AfterUpdate()
'Updating date #2
If txtDate1.Value <> vbNullString And txtDate2.Value <> vbNullString Then
    If DateDiff("d", txtDate1.Value, txtDate2.Value) < 0 Then
        Dim temp As Date
        temp = txtDate1.Value
        txtDate1.Value = txtDate2.Value
        txtDate2.Value = temp
    End If
    Me.Activities_DS.Form.Requery
End If
End Sub

I would gladly upload a sample DB, only problem is that I can't copy the tables and forms into a new accdb file ("The database has been placed in a state by user 'Admin' on machine that prevents it from being opened or locked" error), so once I fix this issue I will upload that too.

Thanks again.
 
Immediately's nothing in your code that sticks in the eyes and say this is wrong.
However, there are some things that can be improved, always use Me in front of the control names, like Me.optActivityType.Value
Likewise, it is unnecessary to use the word Value.
I can also see that you call one of your field names = Date, Date is a key word in MS-Access and using key words can sometimes cause problems. Also, avoid having spaces and special characters in table names.

I have read post #1 again, and what I stumble over is that you write "table embedded in a form", does it means that your subform is not a real subform, but a table/ query you placed on the form?

guyuz said:
... I would gladly upload a sample DB, only problem is that I can't copy the tables and forms into a new accdb file ("The database has been placed in a state by user 'Admin' on machine that prevents it from being opened or locked" error), so once I fix this issue I will upload that too.
Click to expand...
Use Pathfinder/ Explorer/ File Manager to create a copy of your original database file.
 
Once again, thanks for the reply!
I've taken your tips into account, though I'd like to ask how would you name a table that connects two others in a many-to-many relationship? I've seen it done with an underscore, but since you said it's normal not to have special characters in the table name, I don't know what the naming convention is.

In addition, as you can see further in the DB I uploaded, when I say "table embedded in form", I mean that I created a standalone datasheet form (through the wizard) that can be opened through the navigation pane by itself in a window of its own, but for the sake of the program it should be only seen inside the form with the buttons/filters as a table inside that form.

Finally, the sample DB. All field names are written in English (So you can understand what is what), but the records themselves are in Hebrew. It's a little problematic to change them since all the tables are related, but it shouldn't really be a problem though.

SUPER SHORT GUIDE ON HOW TO RECREATE THE ERROR:

a.
1. Open frmActivities
2. Tick "Filter By Activity Type" on
3. From the combo box, choose the first or second value (Since the rest don't have any records for the table to show)
4. Tick "Filter By Activity Type" off. Booyah! "Run-time error '3420': Object invalid or no longer set".

b.
Do exactly what you do in the first guide, except this time skip step 3. You will now receive a new error I just discovered: "Run time error '3071': This expression is typed incorrectly, or it is too complex to be evaluated..."

NOTES:
1. If you tick stuff and add values and nothing happens at all and the table is "dead", you need to delete Activities_DS from frmActivities and drag it back from the navigation pane twice. First time it will stay dead, second time it's suppose to allow you to recreate the error. In odd scenarios it might take only one "redrag" or three.
2. The exact same errors will happen if you try to filter by dates. Look at table ActivitiesDates to choose any range of dates to filter from.
 

Attachments

guyuz said:
For each option, I have written a line of code in the appropriate OnChange/AfterUpdate/Whatnot events in the form:
CurrentDb.QueryDef("qryActivitiesDates").SQL = "SELECT..."
according to whatever option is currently active.
Click to expand...
Guy, I've not looked at your db but all of this changing the SQL of the Record Source business is not necessary.
guyuz said:
Once I tick ATF=OFF, the table crashes and I get the error:"Run-time error'3420': Object invalid or no longer set".
Click to expand...
You mentioned that you get this error but you haven't mentioned which line of code it takes you to when you click Debug.

Your main problem seems to stem from the fact that you're continuously changing the SQL of the record source of the form and not even closing the querydef.
There are better ways to do this.
 
Change the below code line, (database attached):
Code: 
from
    Me.cboActivity.Value = ""
to    
    Me.cboActivity.Value = Null
The problem with your code is, that you are comparing an empty string to a number.
WHERE ((([Forms]![frmActivities]![cboActivity])=[tblActivitiesDates].[Activity_ID]))
Click to expand...
As vbaInet mention, there are other ways to do it.
 

Attachments

vbaInet said:
Guy, I've not looked at your db but all of this changing the SQL of the Record Source business is not necessary.
You mentioned that you get this error but you haven't mentioned which line of code it takes you to when you click Debug.

Your main problem seems to stem from the fact that you're continuously changing the SQL of the record source of the form and not even closing the querydef.
There are better ways to do this.
Click to expand...


Hello vbaInet, I realize that changing the SQL of the record source is problematic, but I'm short on alternatives. I've read online about various syntaxes of IF statements (IF-ELSE, IF-THEN-ELSE, IF-THEN-END-ELSE), but Access Recognizes none.

As for closing the Querydef, that's my fault. I'd never used Querydef before, I just looked up a way to change a query's SQL, I hadn't realized it wasn't a simple function you can run at will.

JHB said:
Change the below code line, (database attached):
Code: 
from
    Me.cboActivity.Value = ""
to    
    Me.cboActivity.Value = Null
The problem with your code is, that you are comparing an empty string to a number.
As vbaInet mention, there are other ways to do it.
Click to expand...

Ah! Can't believe I missed something like that. Thank you very much for helping me out (And sorry it forced you to do dirty work like analyzing code)!

So, for future reference, and possibly replacing this clumsy method of changing the SQL to avoid any other problems, is there a way to create a query that changes based on these demands without relying on changing the SQL?

Thank you both,
Guy.
 
You must log in or register to reply here.

Similar threads

S
Solved Datasheet: Different back colors for columns and alternate row colors
Replies
17
Views
917
AHeyne
A
B
Edit a row on continuous form while the other rows are locked.
Replies
7
Views
861
MajP
MajP
A
Filtering on a crosstab queried subform
Replies
7
Views
289
DHookom
DHookom
J
Command button not responding to double click
Replies
9
Views
291
John Sh
J
B
Using date from a text box in a where condition
Replies
6
Views
807
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom