asked on Dinamic Query filtering on MS/Access - circular reference
I have a multi-record form that needs to display the result of a query. The query itself is predefined and has come computed fileds, regarding to the original tables.
However, dependeing on criteria, only some of the records are to be presented.
My current code, which I believe isw ay out of line is below:
Dim IBool As Boolean
Dim strUserID As String, _
strFilterSQL As String, _
strFilter As String
Dim Q01_UserToDoTasksSet As DAO.Recordset
Set cdiDB = CurrentDb
'On Error GoTo ProcErrorFilter
strUserID = Nz(Forms![F91_LoggedUser]!
If strUserID <> "" Then
strFilterSQL = "SELECT * FROM Q01_UserToDoTasks WHERE " & "[Q01_UserToDoTasks].[T15_
Else
strFilterSQL = "SELECT * FROM Q01_UserToDoTasks WHERE " & "[Q01_UserToDoTasks].[T15_
End If
cdiDB.QueryDefs("Q01_UserT
'Me.Form.RecordSource = "Q01_UserToDoTasks"
Set Q01_UserToDoTasksDef = cdiDB.QueryDefs("Q01_UserT
Set Q01_UserToDoTasksDefSet = Q01_FichasSelecionadasDef.
Me.Form.Recalc
Exit Sub
ProcErrorFilter:
IBool = InsertLog("F0001 Form_Load", "Load", "", "", "", 0, 0, strUserID, "Erro em Filtro. Filtro: '" & Me.Filter & "'")
The Query is Q01_UserToDoTasks and the record source of the form (this is its load event) is linked to the query.
I am getting the aobece circular reference error and can't tell what is wrong as I am a rookie on this pprogramming
can someone help?
However, dependeing on criteria, only some of the records are to be presented.
My current code, which I believe isw ay out of line is below:
Dim IBool As Boolean
Dim strUserID As String, _
strFilterSQL As String, _
strFilter As String
Dim Q01_UserToDoTasksSet As DAO.Recordset
Set cdiDB = CurrentDb
'On Error GoTo ProcErrorFilter
strUserID = Nz(Forms![F91_LoggedUser]!
If strUserID <> "" Then
strFilterSQL = "SELECT * FROM Q01_UserToDoTasks WHERE " & "[Q01_UserToDoTasks].[T15_
Else
strFilterSQL = "SELECT * FROM Q01_UserToDoTasks WHERE " & "[Q01_UserToDoTasks].[T15_
End If
cdiDB.QueryDefs("Q01_UserT
'Me.Form.RecordSource = "Q01_UserToDoTasks"
Set Q01_UserToDoTasksDef = cdiDB.QueryDefs("Q01_UserT
Set Q01_UserToDoTasksDefSet = Q01_FichasSelecionadasDef.
Me.Form.Recalc
Exit Sub
ProcErrorFilter:
IBool = InsertLog("F0001 Form_Load", "Load", "", "", "", 0, 0, strUserID, "Erro em Filtro. Filtro: '" & Me.Filter & "'")
The Query is Q01_UserToDoTasks and the record source of the form (this is its load event) is linked to the query.
I am getting the aobece circular reference error and can't tell what is wrong as I am a rookie on this pprogramming
can someone help?
Microsoft DevelopmentMicrosoft Access
Last Comment
João serras-pereira
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Take a look at the query... It has been affected by running your code:
<<
SELECT *
FROM Q01_UserToDoTasks
WHERE [Q01_UserToDoTasks].[T15_C
>>
The name of your query is Q01_UserToDoTasks, and it is trying to select from itself (that is your circular reference).
You need to restore your original SQL for Q01_UserToDoTasks, without the WHERE clause. The filtering is done in the code I posted.
Just explaining the solution a little - the query as a whole should be the form's recordsource. The code applies a filter in the form (you don't need to modify the query itself for the filtering).
<<
SELECT *
FROM Q01_UserToDoTasks
WHERE [Q01_UserToDoTasks].[T15_C
>>
The name of your query is Q01_UserToDoTasks, and it is trying to select from itself (that is your circular reference).
You need to restore your original SQL for Q01_UserToDoTasks, without the WHERE clause. The filtering is done in the code I posted.
Just explaining the solution a little - the query as a whole should be the form's recordsource. The code applies a filter in the form (you don't need to modify the query itself for the filtering).
ASKER
Done!
Actually, I had a similar code (using the filter) but as I could not put it to work, I have tried the new approach I have submitted to you.
But still something is wrong. The filter now does not show any record at all.
I have modified a bit the code - in my opinion there were some ' missing, but it still doe not work
code now is:
Private Sub Form_Load()
Dim IBool As Boolean
Dim strUserID As String, _
strFilterSQL As String, _
strFilter As String
Dim Q01_UserToDoTasksSet As DAO.Recordset
Set cdiDB = CurrentDb
strUserID = Nz(Forms![F91_LoggedUser]!
If strUserID <> "" Then
strFilterSQL = "[Q01_UserToDoTasks].[T15_
Else
strFilterSQL = "[Q01_UserToDoTasks].[T15_
End If
Me.Filter = strFilterSQL
Me.FilterOn = True
Me.Form.Recalc
End Sub
![screenshot of the form]()
below is the screenshot i took shown the problem, and, again, I am uloading the database
CDI.08.08.zip
Actually, I had a similar code (using the filter) but as I could not put it to work, I have tried the new approach I have submitted to you.
But still something is wrong. The filter now does not show any record at all.
I have modified a bit the code - in my opinion there were some ' missing, but it still doe not work
code now is:
Private Sub Form_Load()
Dim IBool As Boolean
Dim strUserID As String, _
strFilterSQL As String, _
strFilter As String
Dim Q01_UserToDoTasksSet As DAO.Recordset
Set cdiDB = CurrentDb
strUserID = Nz(Forms![F91_LoggedUser]!
If strUserID <> "" Then
strFilterSQL = "[Q01_UserToDoTasks].[T15_
Else
strFilterSQL = "[Q01_UserToDoTasks].[T15_
End If
Me.Filter = strFilterSQL
Me.FilterOn = True
Me.Form.Recalc
End Sub
below is the screenshot i took shown the problem, and, again, I am uloading the databaseCDI.08.08.zip
ASKER
I am closing the question as I think the problem lies elsewhere; actually, the subform is not updated when I change to main form.
So, thanks for the help.
BTW: I have open the a follow up related question "programmatically forcing a recalc of embeedded form" so maybe you are able to help.
So, thanks for the help.
BTW: I have open the a follow up related question "programmatically forcing a recalc of embeedded form" so maybe you are able to help.
same problem (with the 2 version). I actually pasted in in the code. The form has a recordsource set sou it should work with the second version.
I am attaching 2 screenshots as wel as the database so you can check better.
The form itself is "F0001_ListaToDoTasks" and the problem is on loading when there are no records to show for a user (he did not yet identified himself)
Now the forms open as blanks and not with the normal heeading /detail look