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? - Private Sub txtAirline_Click()
-
-
Airline_set = 1
-
-
Call ReDoFlightList
-
cmbFlight.SetFocus
-
-
End Sub
-
Public Sub ReDoFlightList()
-
' This Sub checks all 3 selections and re-creates the UpdateForecastPax and HistoryForecastPax query
-
On Error GoTo Err_ReDoMenuList
-
-
Dim temp As String
-
Dim db As Database
-
Dim sSQL1 As String
-
Dim sSQL As String
-
Set db = CurrentDb
-
' Refresh Querydefs collection
-
db.QueryDefs.Refresh
-
' If MenuIdSelect query exists delete it.
-
For Each qrytemp In db.QueryDefs
-
If qrytemp.Name = "HistoryForecastPax" Then
-
db.QueryDefs.Delete qrytemp.Name
-
ElseIf qrytemp.Name = "UpdateForecastPax" Then
-
db.QueryDefs.Delete qrytemp.Name
-
End If
-
Next qrytemp
-
' Add to HistoryForecastForm Customer Flight and Class
-
sSQL = "SELECT ForecastMealsMaster.Customer, ForecastMealsMaster.Flight,ForecastMealsMaster.Class,"
-
sSQL = sSQL & "ForecastMealsMaster.Flight_Date , ForecastMealsMaster.Aircraft_Type, "
-
sSQL = sSQL & "ForecastMealsMaster.Origin, ForecastMealsMaster.Destination,"
-
sSQL = sSQL & "ForecastMealsMaster.Forecast_Pax, ForecastMealsMaster.Actual_Pax,"
-
sSQL = sSQL & "ForecastMealsMaster.Fpax_ShortTerm, ForecastMealsMaster.Fpax_LongTerm,"
-
sSQL = sSQL & "ForecastMealsMaster.FConfig, ForecastMealsMaster.ActualConfig,"
-
sSQL = sSQL & "ForecastMealsMaster.LastWkAvgPax, ForecastMealsMaster.LyPax, ForecastMealsMaster.LastWkPax,"
-
sSQL = sSQL & "ForecastMealsMaster.Last2WkPax, ForecastMealsMaster.comment,"
-
sSQL = sSQL & "[Actual Pax Last update date].LastUpdateDate"
-
sSQL = sSQL & " FROM ForecastMealsMaster, [Actual Pax Last update date]"
-
sSQL = sSQL & " WHERE ((ForecastMealsMaster.Flight_Date) Between NOW()-30"
-
sSQL = sSQL & " AND [Actual Pax Last update date].LastUpdateDate ) "
-
-
sSQL1 = " "
-
' Add code for Airline
-
If Airline_set > 0 Then
-
txtAirline.SetFocus
-
If txtAirline.Value <> "" Then ' Has a value
-
sSQL1 = sSQL1 & " AND ForecastMealsMaster.Customer LIKE " & """*"
-
sSQL1 = sSQL1 & txtAirline.Value & "*"""
-
End If
-
End If
-
' Add code for Class
-
If A_Class_set > 0 Then
-
cmbClass.SetFocus
-
If cmbClass.Value <> "" Then 'Has a value
-
' reduce txtclass to characters only
-
temp = Left(cmbClass.Value, 1)
-
sSQL1 = sSQL1 & " AND ForecastMealsMaster.Class LIKE " & """* "
-
sSQL1 = sSQL1 & temp & " *"""
-
End If
-
End If
-
-
' Add code for Flight
-
If Flight_set > 0 Then 'Has a value
-
cmbFlight.SetFocus
-
sSQL1 = sSQL1 & " AND ForecastMealsMaster.Flight LIKE " & """*"
-
sSQL1 = sSQL1 & cmbFlight.Value & "*"""
-
End If
-
sSQL = sSQL & sSQL1
-
Set qrytemp = db.CreateQueryDef("HistoryForecastPax", sSQL)
-
-
-
' Add to HistoryForecastForm customer,flight and class
-
sSQL = "SELECT ForecastMealsMaster.Customer, ForecastMealsMaster.Flight,ForecastMealsMaster.Class,"
-
sSQL = sSQL & "ForecastMealsMaster.Flight_Date, ForecastMealsMaster.Aircraft_Type,"
-
sSQL = sSQL & "ForecastMealsMaster.Origin, ForecastMealsMaster.Destination,"
-
sSQL = sSQL & "ForecastMealsMaster.Forecast_Pax, ForecastMealsMaster.Actual_Pax,"
-
sSQL = sSQL & "ForecastMealsMaster.Fpax_ShortTerm, ForecastMealsMaster.Fpax_LongTerm,"
-
sSQL = sSQL & "ForecastMealsMaster.FConfig, ForecastMealsMaster.ActualConfig,"
-
sSQL = sSQL & "ForecastMealsMaster.LastWkAvgPax, ForecastMealsMaster.LyPax, ForecastMealsMaster.LastWkPax,"
-
sSQL = sSQL & "ForecastMealsMaster.Last2WkPax, ForecastMealsMaster.comment,"
-
sSQL = sSQL & "[Actual Pax Last update date].LastUpdateDate"
-
sSQL = sSQL & " FROM ForecastMealsMaster, [Actual Pax Last update date]"
-
sSQL = sSQL & " WHERE ((ForecastMealsMaster.Flight_Date) Between "
-
sSQL = sSQL & " NOW() AND NOW()+30 ) "
-
sSQL = sSQL & sSQL1
-
Set qrytemp = db.CreateQueryDef("UpdateForecastPax", sSQL)
-
UpdateForecastPax.Requery
-
Exit_ReDoMenuList:
-
On Error Resume Next
-
Exit Sub
-
-
Err_ReDoMenuList:
-
-
End Sub
6  3516  NeoPa 32,556
Expert Mod 16PB
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.
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
NeoPa 32,556
Expert Mod 16PB
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.
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
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Baker |
last post by:
Hi:
I have a Pop Up form that involves a main form and sub form. The sub form has the results
of a query on the main form.
I need to automatically perform exactly he same action that takes...
|
by: Andrew Chanter |
last post by:
I have a situation where I am using an unbound dialog form to update data in
an Access 2002 split back end / front end scenario. The data update is done
via an ADO call (direct to the back end...
|
by: S. van Beek |
last post by:
Dear reader,
I have a strange situation.
In a Form with a list box to search for a record in the form it self. The
field in the list box is also available in the form. On an other...
|
by: Peter Oliphant |
last post by:
I now have graphics being drawn in the Paint event of my form (yeah, and
it's very cool). Problem was that it only updated any changes to these
graphics when Paint was called.
So, I then made it...
|
by: tasmisr |
last post by:
This is an old problem,, but I never had it so bad like this before,,
the events are refiring when clicking the Browser refresh button.
In the Submit button in my webform, I capture the server side...
|
by: scott |
last post by:
I guess this is a simple one but I can't seem to get it to work. I want to
refresh an open form "onClose" of another.
I have a Close button on a form which has User Details on it. If I update...
|
by: Robert |
last post by:
I am trying to give the user dynamic search capabilities to select
almost any record in the database from criteria they select. Everything
seems to work except when I open the display form to...
|
by: martin1 |
last post by:
All,
is there window form refresh property? I try to set up window form refresh
per minute. Thanks
|
by: tshad |
last post by:
I have an interface I am using to get access to some of the objects on my
form: a textbox (Status) and my statusbar (StatusBar). In my class, which
is actually in another class from my form I have...
|
by: BlackBox |
last post by:
I have a Listbox1 in the main form and I can add listbox1 items in the
parent form. But when I close the parent form Listbox1 in the main form does
not get refresh. I need to re-open the app.
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
