Requery doesn't work

B

b_bds

Registered User.
Local time
Today, 06:18
Joined
Jan 17, 2008
Messages
37
Hi

I have a query that gives me the information that I want. I have a form and a subform. The subform as the query as his "source object".
The query selects the info based on combo boxes that are in the Form. when I change their value, I don't get a refresh. In debug mode I see that the line "Forms![frmSelection]!sfrmSelection.Form.Refresh" is executed.

If I open the query by the database window (where you have access to all the foms, queries, etc..) I have all the results I should have. But they don't appear in the subform

any idea why?
thanks
 
perhaps you need to use the requery method instead?
Code: 
Forms![frmSelection]!sfrmSelection.Form.Requery
 
Hi

I should of said that I do have that line and I tried the refresh because I couldn't find anyting else to try.

Sorry for the mislead

thanks
 
I'm not sure where the problem lies then. The line I gave you should work with no problems. Perhaps you can upload your file, and one of us can further assist you.
 
Could you please send the sample of code that you running so that i will go close to your problem.
 
Hi

The database is pretty big but I can show you the line that updates the queries upon changing the values of the combo box (see http://www.access-programmers.co.uk/forums/showthread.php?t=155755)

Here is the function that I use and where the requery is asked :
Private Function UpdateFields()

Dim strSQL As String
strSQL = "1=1"

If IsNull(Me.cboPO) = True Then
If IsNull(Me.cboProject) = False Then

strSQL = "tblCommandes.NoUltragen = [Forms]![frmSelection]![cboProject]" & "AND"

ElseIf IsNull(Me.cboClient) = False Then

If Right(strSQL, 3) <> "AND" Then
strSQL = "tblCommandes.Client = [Forms]![frmSelection]![cboClient]"
Else

strSQL = strSQL & "tblCommandes.Client = [Forms]![frmSelection]![cboClient]" & "AND"

End If

ElseIf IsNull(Me.cboNom) = False Then

If Right(strSQL, 3) <> "AND" Then
strSQL = "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]"
Else

strSQL = strSQL & "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]" & "AND"

End If


ElseIf IsNull(Me.cboDiscipline) = False Then

If Right(strSQL, 3) <> "AND" Then
strSQL = "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]"
Else

strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & "AND"

End If

ElseIf IsNull(Me.cboFamille) = False Then

If Right(strSQL, 3) <> "AND" Then
strSQL = "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]"
Else

strSQL = strSQL & "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]" & "AND"

End If
End If

If Right(strSQL, 3) = "AND" Then
strSQL = Left(strSQL, Len(strSQL) - 3)
End If

Else

strSQL = "tblCommandes.PO = [Forms]![frmSelection]![cboPO]"

End If

strSQL = "SELECT tblCommandes.PO, tblCommandes.[OR], tblCommandes.NoUltragen, tblCommandes.Client, tblCommandes.NoClient, tblCommandes.Nom, tblCommandes.Description, tblCommandes.ValeurPO, tblCommandes.ValeurREAL, [ValeurReal]-[ValeurPO] AS ValeurDiff, tblCommandes.LivraisonPrévu, tblCommandes.DateButoir, tblCommandes.LivraisonREAL, CalcWorkdays([LivraisonPrévu],[LivraisonREAL]) AS LivraisonDiff, tblCommandes.NoReception, tblCommandes.[Conformité Tech], tblCommandes.[Service QLTY], tblCommandes.[Service Ap/V], tblCommandes.Suivit, tblCommandes.Soumission, tblCommandes.Docs, tblCommandes.DocsLink, tblFournisseurs.Discipline, tblFournisseurs.Famille" & _
" FROM tblCommandes INNER JOIN tblFournisseurs ON tblCommandes.Nom = tblFournisseurs.ID" & " WHERE " & strSQL

'Where((tblCommandes.NoUltragen =[Forms]![frmSelection]![cboProject] OR [Forms]![frmSelection]![cboProject] is null )
'AND (tblCommandes.Client = [Forms]![frmSelection]![cboClient] OR [Forms]![frmSelection]![cboClient] is null)
'AND (tblCommandes.Nom = [Forms]![frmSelection]![cboNom] OR [Forms]![frmSelection]![cboNom] is null)
'AND (tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline] OR [Forms]![frmSelection]![cboDiscipline] is null )
'AND (tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille] OR [Forms]![frmSelection]![cboFamille] is null ))
'OR (tblCommandes.PO = [Forms]![frmSelection]![cboPO]);"

CurrentDb.QueryDefs("qrySelection").SQL = strSQL

Forms![frmSelection]!sfrmSelection.Form.Requery

'Me.sfrmSelection.Requery
Me.Text14.Value = 0
Me.Text14.Value = DSum("ValeurREAL", "qrySelection")
Me.Text14.Requery
Me.Text16.Value = DAvg("[Service QLTY]", "[qrySelection]")
Me.Text16.Requery
Me.Text18.Value = DAvg("[Service ap/v]", "[qrySelection]")
Me.Text18.Requery
Me.Text24.Value = DSum("ValeurPO", "qrySelection")
Me.Text24.Requery
End Function


Any idea where is the problem?

Thanks
 
b_bds said:
Hi

The database is pretty big but I can show you the line that updates the queries upon changing the values of the combo box (see http://www.access-programmers.co.uk/forums/showthread.php?t=155755)

Here is the function that I use and where the requery is asked :
Code: 
Private Function UpdateFields()
 
     Dim strSQL As String
     strSQL = "1=1"
 
     If IsNull(Me.cboPO) = True Then
          If IsNull(Me.cboProject) = False Then
             strSQL = "tblCommandes.NoUltragen = [Forms]![frmSelection]![cboProject]" & "AND"
          ElseIf IsNull(Me.cboClient) = False Then
               If Right(strSQL, 3) <> "AND" Then
                    strSQL = "tblCommandes.Client = [Forms]![frmSelection]![cboClient]"
               Else
                    strSQL = strSQL & "tblCommandes.Client = [Forms]![frmSelection]![cboClient]" & "AND"
               End If
          ElseIf IsNull(Me.cboNom) = False Then
               If Right(strSQL, 3) <> "AND" Then
                    strSQL = "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]"
               Else
                    strSQL = strSQL & "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]" & "AND"
               End If
          ElseIf IsNull(Me.cboDiscipline) = False Then
               If Right(strSQL, 3) <> "AND" Then
                    strSQL = "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]"
               Else
                    strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & "AND"
               End If
          ElseIf IsNull(Me.cboFamille) = False Then
               If Right(strSQL, 3) <> "AND" Then
                    strSQL = "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]"
               Else
                    strSQL = strSQL & "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]" & "AND"
               End If
          End If
 
          If Right(strSQL, 3) = "AND" Then
               strSQL = Left(strSQL, Len(strSQL) - 3)
          End If
     Else
          strSQL = "tblCommandes.PO = [Forms]![frmSelection]![cboPO]"
     End If
 
     [B][COLOR=royalblue]strSQL = "SELECT tblCommandes.PO, tblCommandes.[[COLOR=red]OR[/COLOR]], tblCommandes.NoUltragen, tblCommandes.Client, tblCommandes.NoClient, tblCommandes.Nom, tblCommandes.Description, tblCommandes.ValeurPO, tblCommandes.ValeurREAL, [ValeurReal]-[ValeurPO] AS ValeurDiff, tblCommandes.LivraisonPrévu, tblCommandes.DateButoir, tblCommandes.LivraisonREAL, CalcWorkdays([LivraisonPrévu],[LivraisonREAL]) AS LivraisonDiff, tblCommandes.NoReception, tblCommandes.[Conformité Tech], tblCommandes.[Service QLTY], tblCommandes.[Service Ap/V], tblCommandes.Suivit, tblCommandes.Soumission, tblCommandes.Docs, tblCommandes.DocsLink, tblFournisseurs.Discipline, tblFournisseurs.Famille" & _[/COLOR][/B]
[B][COLOR=royalblue]" FROM tblCommandes INNER JOIN tblFournisseurs ON tblCommandes.Nom = tblFournisseurs.ID" & " WHERE " & strSQL[/COLOR][/B]
 
     [B][COLOR=royalblue][COLOR=black]'Where[/COLOR]((tblCommandes.NoUltragen =[Forms]![frmSelection]![cboProject] OR [Forms]![frmSelection]![cboProject] is null )[/COLOR][/B]
[B][COLOR=royalblue]     'AND (tblCommandes.Client = [Forms]![frmSelection]![cboClient] OR [Forms]![frmSelection]![cboClient] is null)[/COLOR][/B]
[B][COLOR=royalblue]     'AND (tblCommandes.Nom = [Forms]![frmSelection]![cboNom] OR [Forms]![frmSelection]![cboNom] is null)[/COLOR][/B]
[B][COLOR=royalblue]     'AND (tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline] OR [Forms]![frmSelection]![cboDiscipline] is null )[/COLOR][/B]
[B][COLOR=royalblue]     'AND (tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille] OR [Forms]![frmSelection]![cboFamille] is null ))[/COLOR][/B]
[B][COLOR=royalblue]     'OR (tblCommandes.PO = [Forms]![frmSelection]![cboPO]);"[/COLOR][/B]
 
     CurrentDb.QueryDefs("qrySelection").SQL = strSQL
 
     Forms![frmSelection]!sfrmSelection.Form.Requery
 
     'Me.sfrmSelection.Requery
     Me.Text14.Value = 0
     Me.Text14.Value = DSum("ValeurREAL", "qrySelection")
     Me.Text14.Requery
     Me.Text16.Value = DAvg("[Service QLTY]", "[qrySelection]")
     Me.Text16.Requery
     Me.Text18.Value = DAvg("[Service ap/v]", "[qrySelection]")
     Me.Text18.Requery
     Me.Text24.Value = DSum("ValeurPO", "qrySelection")
     Me.Text24.Requery
End Function

Any idea where is the problem?

Thanks
Click to expand...

It was difficult to read the code as presented, so I reformatted it and enclosed it in tags to keep it readable.

You appear to have a column called OR? OR is an SQL Keyword and whould not be used as a column name. I have no idea if this is the source of your problem, but it would be a good Idea to change the column name anyway.
 
Thanks for putting it in a code box Rookie. My comment is, since this is simply a query, then no data has changed and the requery is not required. May I suggest you single step through the code to confirm the if structure is flowing according to your combo box selections. If you put a stop immediately prior to the call and display strSql, you may also find the value is not what you expected.
 
Well, one thing I can spot right away that is going to cause you a problem is that you aren't putting spaces in places where spaces are needed. For example, you have

strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & "AND"

Which would actually need to have spaces around the AND

strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & " AND "

Or
strSQL = strSQL & " tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline] " & "AND"

in other spots.

The best way to find out what your final SQL statement looks like is to put a

Debug.Print strSQL

at the end of your code to see in the immediate window what the actual output is. It is no good trying to requery if your SQL statement is actually invalid.
 
boblarson said:
Well, one thing I can spot right away that is going to cause you a problem is that you aren't putting spaces in places where spaces are needed. For example, you have

strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & "AND"

Which would actually need to have spaces around the AND

strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & " AND "

Or
strSQL = strSQL & " tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline] " & "AND"

in other spots.

The best way to find out what your final SQL statement looks like is to put a

Debug.Print strSQL

at the end of your code to see in the immediate window what the actual output is. It is no good trying to requery if your SQL statement is actually invalid.
Click to expand...

Rats, I was so busy reformatting the query, that i missed the lack of spaces. I suspect that between that and the suggestion of debug.print, the problem will resolve itself.
 
Thanks guys for the reformating... I'm sorry I couldn't do it but I keep having problems here (at work) with the buttons (like insert image and code). Once in a while they work...

I will work on those suggestions and keep you posted...

thanks you very much

edit: as for the OR column it stands for Order Request
 
b_bds said:
edit: as for the OR column it stands for Order Request
Click to expand...

In case you have not already, try to change OR to something like OrdReq. It may not resolve your problem, but it will prevent any conflict related to the fact that OR is an SQL Keyword.
 
Hi

I changed the OR to OrdReq as suggested but guess what... same thing... but I found something...

When I put my form in "form design" then put it back in "form view" it does work for some time but when I add a second combo box value it stops to work. But when I open the query by itself I have all the values I'm supposed to have.... Im really clueless..

any Idea why?

thanks

P.S. I corrected the if conditions and now I have the correct SQL string for all situations... but can't show them... :(
Let me use the buttons and format it since they are working now...

Code: 
Private Function UpdateFields()

Dim strSQL As String
strSQL = "1=1"

If IsNull(Me.cboPO) = True Then
        If IsNull(Me.cboProject) = False Then
    
        strSQL = "tblCommandes.NoUltragen = [Forms]![frmSelection]![cboProject]" & " AND "
              
        End If
        If IsNull(Me.cboClient) = False Then
       
            If Right(strSQL, 5) <> " AND " Then
                strSQL = "tblCommandes.Client = [Forms]![frmSelection]![cboClient]" & " AND "
            Else
            
            strSQL = strSQL & "tblCommandes.Client = [Forms]![frmSelection]![cboClient]" & " AND "
            
            End If
        End If
        If IsNull(Me.cboNom) = False Then
            
            If Right(strSQL, 5) <> " AND " Then
                strSQL = "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]" & " AND "
            Else
            
            strSQL = strSQL & "tblCommandes.Nom = [Forms]![frmSelection]![cboNom]" & " AND "
            
            End If
            
        End If
        If IsNull(Me.cboDiscipline) = False Then
        
            If Right(strSQL, 5) <> " AND " Then
                strSQL = "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & " AND "
            Else
            
            strSQL = strSQL & "tblFournisseurs.Discipline = [Forms]![frmSelection]![cboDiscipline]" & " AND "
            
            End If
        End If
        If IsNull(Me.cboFamille) = False Then
            
            If Right(strSQL, 5) <> " AND " Then
                strSQL = "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]" & " AND "
            Else
            
            strSQL = strSQL & "tblFournisseurs.Famille = [Forms]![frmSelection]![cboFamille]" & " AND "
            
            End If
        
        End If
        
        If Right(strSQL, 5) = " AND " Then
            strSQL = Left(strSQL, Len(strSQL) - 5)
        End If
           
    Else

strSQL = "tblCommandes.PO = [Forms]![frmSelection]![cboPO]"

End If

strSQL = "SELECT tblCommandes.PO, tblCommandes.OrdReq, tblCommandes.NoUltragen, tblCommandes.Client, tblCommandes.NoClient, tblCommandes.Nom, tblCommandes.Description, tblCommandes.ValeurPO, tblCommandes.ValeurREAL, [ValeurReal]-[ValeurPO] AS ValeurDiff, tblCommandes.LivraisonPrévu, tblCommandes.DateButoir, tblCommandes.LivraisonREAL, CalcWorkdays([LivraisonPrévu],[LivraisonREAL]) AS LivraisonDiff, tblCommandes.NoReception, tblCommandes.[Conformité Tech], tblCommandes.[Service QLTY], tblCommandes.[Service Ap/V], tblCommandes.Suivit, tblCommandes.Soumission, tblCommandes.Docs, tblCommandes.DocsLink, tblFournisseurs.Discipline, tblFournisseurs.Famille" & _
            " FROM tblCommandes INNER JOIN tblFournisseurs ON tblCommandes.Nom = tblFournisseurs.ID" & " WHERE " & strSQL

CurrentDb.QueryDefs("qrySelection").SQL = strSQL

Debug.Print strSQL
Forms![frmSelection]!sfrmSelection.Form.Requery

[COLOR="SeaGreen"]'Me.sfrmSelection.Requery[/COLOR]
Me.Text14.Value = 0
Me.Text14.Value = DSum("ValeurREAL", "qrySelection")
Me.Text14.Requery
Me.Text16.Value = DAvg("[Service QLTY]", "[qrySelection]")
Me.Text16.Requery
Me.Text18.Value = DAvg("[Service ap/v]", "[qrySelection]")
Me.Text18.Requery
Me.Text24.Value = DSum("ValeurPO", "qrySelection")
Me.Text24.Requery
End Function

Edit : More information for you :

I have a text box that calculates the sum of the results of the query and when I change the values of the comboboxes the sum changes too so the query is done but doesn't show.
 
Last edited:
Anyone has an idea?

I have the good results in my query but I can't get the subform(that gets the results from the query) to show it... the requery doesn't work

Thanks
 
The .value is used to manipulate values in the current recordset. The function at this point, depending on which event it is dispatched from, may not be able to update the current recordset with your result set. You either have to do a (form).recordset in the function to refresh the recordset or remove the .value reference to the current recordset, or move the function call to another event procedure. I haven't tried this with your code, but I have encountered this in the past...just guessing that could be the problem.
By the way, which event does this occur in?
 
Hi

thanks for the answer

Everytime I click a combo box it calls the fonction UpdateFields(). That way everytime a combobox changes values it will update the query to incorporate the new criteria

I'll try to see what I could do with your suggestion and I'll keep you posted

Thanks

Edit : I'm not sure I'm following the .value part... The only time i use the .value term is for the text box 14, 16 and 24 but those text boxes work 100%.. they are the ones that tell me that the query is good because the value of the text boxes are calculated from the query and they give me the right value
 
Last edited:
I may have a hint for you

When I right-click on the form and choose "filter by form" then right-click again and choose "Apply filter/sort" It works!!!!

I have to do that in order to be able to show the results.

Is it possible to do that in vba? or is it possible to do a work around?

thanks
 
I'm speachless

It's working... I played with the filter option in the right click menu and now it works...

but now every time I close my form it asks if I want to save the new query (since I change the query everytime I change the values of the comboboxes).

Is there a way to save the query in vba so the client doesn't have to click on the mesage box everytime.

Thanks
EDIT :
Ok it doesn't work :(

I still have to right click and apply the filter...:S what is going on? I restarted the db to make sure everyting was ok and it worked... now I still have to right click to show the results

any idea?
 
Last edited:
You must log in or register to reply here.

Similar threads

D
Subform requery is starting a new record
Replies
4
Views
364
dullster
D
P
Solved Show "no" results
Replies
8
Views
162
plog
P
C
Solved No Current Record on Access Close
Replies
11
Views
646
Pat Hartman
P
N
FollowHyperlink error when referred to from a different control
Replies
4
Views
477
nortonm
N
T
Solved row number in subform
Replies
4
Views
261
TipsyWolf
T

Users who are viewing this thread

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