By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

How can I refresh a sub form

P: 9
I have two subforms in my main form.
In the main form I want the user to select a customer, class, and date range.
The 2 subforms are similar. One is the history records in the recent past and the other is future records. The user has to input a passenger forecast for the customer for the selected class for each record in the date range.
The subforms are populated by a query which is altered according to their selection.
I wish to refresh the subform after changing the queries as part of the main form field's _click subroutine. The UpdateForecastPax.Requery where UpdateForecastPax is the query does not seem to work.

I can't see how to link the form and subform as both are queries of the same table rather than different tables. Is there a command or commands which will force a subform to refresh with the changed query?

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtAirline_Click()
  2.  
  3.     Airline_set = 1
  4.  
  5.     Call ReDoFlightList
  6.     cmbFlight.SetFocus
  7.  
  8. End Sub
  9. Public Sub ReDoFlightList()
  10.  '  This Sub checks all 3 selections and re-creates the UpdateForecastPax and HistoryForecastPax query
  11. On Error GoTo Err_ReDoMenuList
  12.  
  13. Dim temp As String
  14. Dim db As Database
  15. Dim sSQL1 As String
  16. Dim sSQL As String
  17.     Set db = CurrentDb
  18.     ' Refresh Querydefs collection
  19.     db.QueryDefs.Refresh
  20.     ' If MenuIdSelect query exists delete it.
  21.     For Each qrytemp In db.QueryDefs
  22.         If qrytemp.Name = "HistoryForecastPax" Then
  23.            db.QueryDefs.Delete qrytemp.Name
  24.         ElseIf qrytemp.Name = "UpdateForecastPax" Then
  25.            db.QueryDefs.Delete qrytemp.Name
  26.         End If
  27.     Next qrytemp
  28.     ' Add to HistoryForecastForm Customer Flight and Class
  29.     sSQL = "SELECT ForecastMealsMaster.Customer, ForecastMealsMaster.Flight,ForecastMealsMaster.Class,"
  30.     sSQL = sSQL & "ForecastMealsMaster.Flight_Date , ForecastMealsMaster.Aircraft_Type, "
  31.     sSQL = sSQL & "ForecastMealsMaster.Origin, ForecastMealsMaster.Destination,"
  32.     sSQL = sSQL & "ForecastMealsMaster.Forecast_Pax, ForecastMealsMaster.Actual_Pax,"
  33.     sSQL = sSQL & "ForecastMealsMaster.Fpax_ShortTerm, ForecastMealsMaster.Fpax_LongTerm,"
  34.     sSQL = sSQL & "ForecastMealsMaster.FConfig, ForecastMealsMaster.ActualConfig,"
  35.     sSQL = sSQL & "ForecastMealsMaster.LastWkAvgPax, ForecastMealsMaster.LyPax, ForecastMealsMaster.LastWkPax,"
  36.     sSQL = sSQL & "ForecastMealsMaster.Last2WkPax, ForecastMealsMaster.comment,"
  37.     sSQL = sSQL & "[Actual Pax Last update date].LastUpdateDate"
  38.     sSQL = sSQL & " FROM ForecastMealsMaster, [Actual Pax Last update date]"
  39.     sSQL = sSQL & " WHERE ((ForecastMealsMaster.Flight_Date) Between NOW()-30"
  40.     sSQL = sSQL & " AND [Actual Pax Last update date].LastUpdateDate ) "
  41.  
  42. sSQL1 = " "
  43. ' Add code for Airline
  44.     If Airline_set > 0 Then
  45.         txtAirline.SetFocus
  46.         If txtAirline.Value <> "" Then    ' Has a value
  47.             sSQL1 = sSQL1 & " AND ForecastMealsMaster.Customer LIKE " & """*"
  48.             sSQL1 = sSQL1 & txtAirline.Value & "*"""
  49.         End If
  50.     End If
  51. ' Add code for Class
  52.     If A_Class_set > 0 Then
  53.         cmbClass.SetFocus
  54.         If cmbClass.Value <> "" Then      'Has a value
  55. '           reduce txtclass to characters only
  56.             temp = Left(cmbClass.Value, 1)
  57.             sSQL1 = sSQL1 & " AND ForecastMealsMaster.Class LIKE " & """* "
  58.             sSQL1 = sSQL1 & temp & " *"""
  59.         End If
  60.     End If
  61.  
  62. ' Add code for Flight
  63.     If Flight_set > 0 Then     'Has a value
  64.         cmbFlight.SetFocus
  65.         sSQL1 = sSQL1 & " AND ForecastMealsMaster.Flight LIKE " & """*"
  66.         sSQL1 = sSQL1 & cmbFlight.Value & "*"""
  67.     End If
  68.     sSQL = sSQL & sSQL1
  69.     Set qrytemp = db.CreateQueryDef("HistoryForecastPax", sSQL)
  70.  
  71.  
  72.      ' Add to HistoryForecastForm customer,flight and class
  73.     sSQL = "SELECT ForecastMealsMaster.Customer, ForecastMealsMaster.Flight,ForecastMealsMaster.Class,"
  74.     sSQL = sSQL & "ForecastMealsMaster.Flight_Date, ForecastMealsMaster.Aircraft_Type,"
  75.     sSQL = sSQL & "ForecastMealsMaster.Origin, ForecastMealsMaster.Destination,"
  76.     sSQL = sSQL & "ForecastMealsMaster.Forecast_Pax, ForecastMealsMaster.Actual_Pax,"
  77.     sSQL = sSQL & "ForecastMealsMaster.Fpax_ShortTerm, ForecastMealsMaster.Fpax_LongTerm,"
  78.     sSQL = sSQL & "ForecastMealsMaster.FConfig, ForecastMealsMaster.ActualConfig,"
  79.     sSQL = sSQL & "ForecastMealsMaster.LastWkAvgPax, ForecastMealsMaster.LyPax, ForecastMealsMaster.LastWkPax,"
  80.     sSQL = sSQL & "ForecastMealsMaster.Last2WkPax, ForecastMealsMaster.comment,"
  81.     sSQL = sSQL & "[Actual Pax Last update date].LastUpdateDate"
  82.     sSQL = sSQL & " FROM ForecastMealsMaster, [Actual Pax Last update date]"
  83.     sSQL = sSQL & " WHERE ((ForecastMealsMaster.Flight_Date) Between "
  84.     sSQL = sSQL & " NOW() AND NOW()+30 ) "
  85.     sSQL = sSQL & sSQL1
  86.     Set qrytemp = db.CreateQueryDef("UpdateForecastPax", sSQL)
  87.     UpdateForecastPax.Requery
  88. Exit_ReDoMenuList:
  89.   On Error Resume Next
  90.   Exit Sub
  91.  
  92. Err_ReDoMenuList:
  93.  
  94.  End Sub
Jan 9 '07 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Try :
FormID.SubFormControlName.Form.ReQuery
Where :
FormID is often Me. but can be a more fully referenced link to a form.
SubFormControlName is the name of the control on the form (SubForm type) which the SubForm is held in.
Form is simply the text 'Form'.
Let us know how this works for you.
Jan 9 '07 #2

P: 9
Try :
FormID.SubFormControlName.Form.ReQuery
Where :
FormID is often Me. but can be a more fully referenced link to a form.
SubFormControlName is the name of the control on the form (SubForm type) which the SubForm is held in.
Form is simply the text 'Form'.
Let us know how this works for you.
I tried your format but while my code has changed the query correctly the subforms are not refreshing with the new query.
Is there a better way?
What I have is a main table and the query selectes a subset of the table based on date.
This query is what is used by the subform to list the data.
Then the users refine that subset, by selecting an individual flight and passenger class from separate combo boxes in the main form. These combo boxes are themselves subqueries of the main query the main form. Basically I am filtering the data, but avoiding Access's filters to simplify it for users.

This results in one row per day for the time period chosen. The subform displays 18 of the tables' fields in each row, and the user can then update 3 of these as appropriate.

The second subform is only history and is read only, but also records from the same table.

I've tried having the subform in datasheet mode but found I couldn't set the font size. However this also did not refresh. I have also tried the refresh method. Setting the focus also seems to no nothing. Access help seems to be of no help to me at all.

I would appreciate if you could point me to any better ways as I seem to be getting bogged down. Thank you for your time.
Chris
Jan 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
I'm afraid I know of no better ways personally.
I would look at why this isn't working for you, rather than other ways to effect the same results.
There is almost certainly some (little) thing you've got wrong in your database. Find it, and the database will work in a logical way. If you try to go around it you will always have a kludge in your code.
That would be my advice.
Jan 10 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm afraid I know of no better ways personally.
I would look at why this isn't working for you, rather than other ways to effect the same results.
There is almost certainly some (little) thing you've got wrong in your database. Find it, and the database will work in a logical way. If you try to go around it you will always have a kludge in your code.
That would be my advice.
Chris

The problem may lie in the two queries running off the same table. Can you post the sql of both queries and tell us which is the main and which the subform query.

Mary
Jan 10 '07 #5

P: 9
Chris

The problem may lie in the two queries running off the same table. Can you post the sql of both queries and tell us which is the main and which the subform query.

Mary
Thanks Mary,
Based on your info I started again and have decided to teach users how to use Filters. I now have a working form.
Jan 24 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks Mary,
Based on your info I started again and have decided to teach users how to use Filters. I now have a working form.
That's great Chris. Glad to hear the problems solved.
Jan 24 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.