A query changes fields, the requery option does not refresh the fields in subform.

**offie** · 07-16-2013, 08:39 AM

I have a form in which the user selects what fields they want to have in the query, a display of the query, and then a display of another query built from the first one.

When I open the form, when everything is blank, and I enter data, press the command button (has the requery) its works perfect, but if I want to change the data, the next time I press the command button the query information refreshes but the fields show do not, so I get fields that are empty and the other fields are expressions that come from data the user cannot see

Ex: User can choose 1-10 and A-J

1st Time: Chose 5 and D
5 l D l (5+D)
Shows all data

2nd Time Choose 6 and E

5 l D l (6+E)
fields 5 and D are empty

I don't understand why this is happening, when I open the 1st query, it shows the right fields, it is just the subform in the form that retains the wrong fields.
Any ideas?
I'll attach my code for the form, really only the botton pertains to this question

Code:

Option Compare Database
Private Sub Frame206_AfterUpdate()
Dim DateFrom As String
  Select Case Frame206.Value
   Case 1
      TempVars.Add "tvFromField", "[DateAssigned]"
   Case 2
      TempVars.Add "tvFromField", "[COToContractorDate]"
   Case 3
      TempVars.Add "tvFromField", "[COFromContractorDate]"
   Case 4
      TempVars.Add "tvFromField", "[COToCostControlDate]"
   Case 5
      TempVars.Add "tvFromField", "[COFromCostControlDate]"
   Case 6
      TempVars.Add "tvFromField", "[COToAgencyDate]"
   Case 7
      TempVars.Add "tvFromField", "[COFromAgencyDate]"
   Case 8
      TempVars.Add "tvFromField", "[COToOSCDate]"
   Case 9
      TempVars.Add "tvFromField", "[COFromOSCDate]"
   Case Else
      TempVars.Add "tvFromField", "[DateAssigned]"
   End Select

End Sub
Private Sub Frame296_AfterUpdate()
Dim DateTo As String
Select Case Frame296.Value
   Case 1
      TempVars.Add "tvToField", "[COToContractorDate]"
   Case 2
      TempVars.Add "tvToField", "[COFromContractorDate]"
   Case 3
      TempVars.Add "tvToField", "[COToCostControlDate]"
   Case 4
      TempVars.Add "tvToField", "[COFromCostControlDate]"
   Case 5
      TempVars.Add "tvToField", "[COToAgencyDate]"
   Case 6
      TempVars.Add "tvToField", "[COFromAgencyDate]"
   Case 7
      TempVars.Add "tvToField", "[COToOSCDate]"
   Case 8
      TempVars.Add "tvToField", "[COFromOSCDate]"
   Case 9
      TempVars.Add "tvToField", "[IssueDate]"
   Case Else
      TempVars.Add "tvToField", "[COToContractorDate]"
   End Select

End Sub
Private Sub Command203_Click()

Dim srtSQL As String
    strSQL = "SELECT dbo_ChangeOrder.ProjectCode, dbo_ChangeOrder.TradeCode, dbo_ChangeOrder.ChangeOrderCode, dbo_ChangeOrder." & _
    [TempVars]![tvFromField] & ", dbo_ChangeOrder." & [TempVars]![tvToField] & ", IIf(DatePart(""q"",DateAdd(""m"",-3," & _
    [TempVars]![tvFromField] & "))=4,DatePart(""yyyy""," & [TempVars]![tvFromField] & ")-1,DatePart(""yyyy""," & _
    [TempVars]![tvFromField] & ")) AS [Fisca lYear], DatePart(""q"",DateAdd(""m"",-3," & _
    [TempVars]![tvFromField] & ")) AS Quarter, DateDiff(""d""," & [TempVars]![tvFromField] & "," & _
    [TempVars]![tvToField] & ") AS Days FROM dbo_ChangeOrder WHERE (((IIf(DatePart(""q"",DateAdd(""m"",-3," & _
    [TempVars]![tvFromField] & "))=4,DatePart(""yyyy""," & [TempVars]![tvFromField] & ")-1,DatePart(""yyyy""," & _
    [TempVars]![tvFromField] & "))) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYTo])) ORDER BY DateDiff(""d""," & [TempVars]![tvFromField] & "," & _
    [TempVars]![tvToField] & ")"
    
'   Do view code before applying, uncomment line below
''    MsgBox strSQL
    
'   Assign SQL code to Query
    CurrentDb.QueryDefs("CODateQuery").SQL = strSQL
    
'   Open query to view results
    DoCmd.OpenQuery "CODateQuery", acViewNormal, acEdit
    DoCmd.Close acQuery, "CODateQuery"
    
    DoCmd.OpenQuery "CODateNumber"
    DoCmd.Close acQuery, "CODateNumber"
    
[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].Requery
End Sub

**Dal Jeanis** · 07-16-2013, 12:50 PM

Things to try -
1) Check the link fields in the subform control on the main form, before and after you execute the requery. You probably want them blank.
2) debug.print the SQL for the "CODateQuery" and "CODateNumber" query after each test and see if they look different or the same.

**offie** · 07-16-2013, 01:22 PM

All linked fields are empty, and the SQL for both is fine, I think that it has to do with the SQL changing.
I got rid of the DoCmd.Close, and when they open they have the right sql and format.

The subforms retain the first sql made
I know the requery function works because it works the first time, and even when I click on the queries and click the refresh all button up top nothing happens, the only thing that changes are the fiscal year and quarter, but that has to do with the form not the sql changing.

Is there a way to open and requery and close a query/subform in a form, not out of it? Or will I have to go with just opening them?
I think I bit off more than I can chew...
Thank you so much for helping!

**Dal Jeanis** · 07-16-2013, 01:49 PM

Okay, so after you change the query, set the subform's record source again, and it should pick up the new SQL.

**offie** · 07-17-2013, 01:11 PM

Hopefully my last question:

I made 2 unbound subforms, called them ChildDays and ChildDaysSums, I wrote:

Me.ChildDays.Form.RecordSource = "Query.CODateQuery"
Me.ChildDaysSums.Form.RecordSource = "Query.CODateNumber"

What am I doing wrong, I always get the error "the expression you entered refers to an object that is closed or doesn't exist"
The thing is that when I start typing it, I get a drop down menu to select all the way up to the =

Will this do the trick? Sorry, I'm so bad/new to this
Thanks!

Code:

Option Compare Database
Private Sub Frame206_AfterUpdate()
Dim DateFrom As String
Select Case Frame206.Value
Case 1
TempVars.Add "tvFromField", "[DateAssigned]"
Case 2
TempVars.Add "tvFromField", "[COToContractorDate]"
Case 3
TempVars.Add "tvFromField", "[COFromContractorDate]"
Case 4
TempVars.Add "tvFromField", "[COToCostControlDate]"
Case 5
TempVars.Add "tvFromField", "[COFromCostControlDate]"
Case 6
TempVars.Add "tvFromField", "[COToAgencyDate]"
Case 7
TempVars.Add "tvFromField", "[COFromAgencyDate]"
Case 8
TempVars.Add "tvFromField", "[COToOSCDate]"
Case 9
TempVars.Add "tvFromField", "[COFromOSCDate]"
Case Else
TempVars.Add "tvFromField", "[DateAssigned]"
End Select

End Sub
Private Sub Frame296_AfterUpdate()
Dim DateTo As String
Select Case Frame296.Value
Case 1
TempVars.Add "tvToField", "[COToContractorDate]"
Case 2
TempVars.Add "tvToField", "[COFromContractorDate]"
Case 3
TempVars.Add "tvToField", "[COToCostControlDate]"
Case 4
TempVars.Add "tvToField", "[COFromCostControlDate]"
Case 5
TempVars.Add "tvToField", "[COToAgencyDate]"
Case 6
TempVars.Add "tvToField", "[COFromAgencyDate]"
Case 7
TempVars.Add "tvToField", "[COToOSCDate]"
Case 8
TempVars.Add "tvToField", "[COFromOSCDate]"
Case 9
TempVars.Add "tvToField", "[IssueDate]"
Case Else
TempVars.Add "tvToField", "[COToContractorDate]"
End Select

End Sub
Private Sub Command203_Click()

Dim srtSQL As String
strSQL = "SELECT dbo_ChangeOrder.ProjectCode, dbo_ChangeOrder.TradeCode, dbo_ChangeOrder.ChangeOrderCode, dbo_ChangeOrder." & _
[TempVars]![tvFromField] & ", dbo_ChangeOrder." & [TempVars]![tvToField] & ", IIf(DatePart(""q"",DateAdd(""m"",-3," & _
[TempVars]![tvFromField] & "))=4,DatePart(""yyyy""," & [TempVars]![tvFromField] & ")-1,DatePart(""yyyy""," & _
[TempVars]![tvFromField] & ")) AS [Fisca lYear], DatePart(""q"",DateAdd(""m"",-3," & _
[TempVars]![tvFromField] & ")) AS Quarter, DateDiff(""d""," & [TempVars]![tvFromField] & "," & _
[TempVars]![tvToField] & ") AS Days FROM dbo_ChangeOrder WHERE (((IIf(DatePart(""q"",DateAdd(""m"",-3," & _
[TempVars]![tvFromField] & "))=4,DatePart(""yyyy""," & [TempVars]![tvFromField] & ")-1,DatePart(""yyyy""," & _
[TempVars]![tvFromField] & "))) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form].[CODateFYTo])) ORDER BY DateDiff(""d""," & [TempVars]![tvFromField] & "," & _
[TempVars]![tvToField] & ")"

' Do view code before applying, uncomment line below
MsgBox strSQL

DoCmd.Close acQuery, "CODateNumber"
DoCmd.Close acQuery, "CODateQuery"

' Assign SQL code to Query
CurrentDb.QueryDefs("CODateQuery").SQL = strSQL


' Open query to view results
DoCmd.OpenQuery "CODateQuery", acViewNormal, acEdit

DoCmd.OpenQuery "CODateNumber"
DoCmd.Close acQuery, "CODateNumber"
DoCmd.Close acQuery, "CODateQuery"

Me.ChildDays.Form.RecordSource = "Query.CODateQuery"
Me.ChildDaysSums.Form.RecordSource = "Query.CODateNumber"

[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].Requery
End Sub

A query changes fields, the requery option does not refresh the fields in subform.

Thread Tools

A query changes fields, the requery option does not refresh the fields in subform.

Similar Threads

Make fields visible with selection in Option Group

Requery vs. Refresh

Refresh Requery Subform

Importing weekley exces spreadsheets into Access and have it update changed fields

Identifying Changed Fields in Two Tables

Posting Permissions