Text Boxes disappear in Formview (because of VBA code?!)

[JoeyB]

I have made a Form that got 6 comboboxes. So it is completely arbitrary for the user. The comboboxes work perfectly, they filter the information when for example when there are no Airports with a private operator in the state of Queensland, it will not show Private as an option in the combobox State.

The information what will show up in the detail section, hence every combobox is in the Header, dissapears once I go in Form view. When I but the Text boxes in the header section, weirdly enough they wont disappear but wont show the information either.
Pictures of Form view, Designview and the Code below.

Option Compare Database
Option Explicit


Private Sub cboAirport_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT StateT.State, StateT.StateID, AirportT.Airport, AirportT.AirportID, YearT.Year, YearT.YearID, OperatorT.OperatorType, OperatorT.OperatorID, TierT.Tier, TierT.TierID, TypeOfOperationT.TypeOfOperation, TypeOfOperationT.TypeOfOperationID, PassengerT.PassengerMDAInbound, PassengerT.PassengerMDAOutbound, PassengerT.PassengerRegionalInbound, PassengerT.PassengerRegionalOutbound, PassengerT.PassengerInternationalInbound, PassengerT.PassengerInternationalOutbound, PassengerT.PassengerTotalTotal, AircraftMovementT.AirMovementMDAInbound, AircraftMovementT.AirMovementMDAOutbound, AircraftMovementT.AirMovementRegionalInbound, AircraftMovementT.AirMovementRegionalOutbound, AircraftMovementT.AirMovementINTLInbound, AircraftMovementT.AirMovementINTLOutbound, AircraftMovementT.AirMovementTotalTotal " & _
"FROM OperatorT INNER JOIN (ICAOCodeT INNER JOIN (TypeOfOperationT INNER JOIN ((TierT INNER JOIN (TierJointAirport INNER JOIN ((YearT INNER JOIN ((StateT INNER JOIN AirportT ON StateT.StateID = AirportT.StateID) INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) ON YearT.YearID = AircraftMovementT.YearID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (YearT.YearID = PassengerT.YearID) AND (AirportT.AirportID = PassengerT.AirportID)) ON TierJointAirport.AirportID = AirportT.AirportID) ON TierT.TierID = TierJointAirport.TierID) INNER JOIN TypeOfOperationJointT ON (TierJointAirport.AirportID = TypeOfOperationJointT.AirportID) AND (YearT.YearID = TypeOfOperationJointT.YearID) AND (AirportT.AirportID = TypeOfOperationJointT.AirportID)) ON TypeOfOperationT.TypeOfOperationID = TypeOfOperationJointT.TypeOfOperationID) ON ICAOCodeT.ICAOID = AirportT.ICAOID) ON OperatorT.OperatorID = AirportT.OperatorID " & _
"WHERE YearT.YearID = " & Me.cboYear.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & " AND AirportT.AirportID = " & Me.cboAirport.Column(0) & " AND TypeOfOperationT.TypeOfOperationID = " & Me.cboOperation.Column(0) & " AND OperatorT.OperatorID = " & Me.cboOperator.Column(0) & " AND TierT.TierID = " & Me.cboTier.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & _
" ORDER BY StateT.State, AirportT.Airport, YearT.Year"

Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery


End Sub

Private Sub cboOperation_AfterUpdate()

If Not IsNull(Me.cboOperation) Then

Me.cboOperator.RowSource = "SELECT DISTINCT ArbitraryQ.OperatorID, ArbitraryQ.OperatorType" & _
                            " FROM ArbitraryQ" & _
                            " WHERE TypeOfOperationID = " & Me.cboOperation & _
                            " ORDER BY OperatorType"

Me.cboOperator = Null
Me.cboOperator.Requery

End If

End Sub

Private Sub cboOperator_AfterUpdate()

If Not IsNull(Me.cboOperator) Then

Me.cboTier.RowSource = "SELECT DISTINCT ArbitraryQ.TierID, ArbitraryQ.Tier" & _
                        " FROM ArbitraryQ" & _
                        " WHERE OperatorID = " & Me.cboOperator & _
                        " ORDER BY Tier"
                        
Me.cboTier = Null
Me.cboTier.Requery

End If

End Sub

Private Sub cboState_AfterUpdate()

If Not IsNull(Me.cboState) Then

Me.cboAirport.RowSource = "SELECT DISTINCT ArbitraryQ.AirportID, ArbitraryQ.Airport" & _
                            " FROM ArbitraryQ" & _
                            " WHERE StateID = " & Me.cboState & _
                            " ORDER BY Airport"
                            
Me.cboAirport = Null
Me.cboAirport.Requery

End If

End Sub

Private Sub cboTier_AfterUpdate()

If Not IsNull(Me.cboTier) Then

Me.cboState.RowSource = "SELECT DISTINCT ArbitraryQ.StateID, ArbitraryQ.State" & _
                        " FROM ArbitraryQ" & _
                        " WHERE TierID = " & Me.cboTier & _
                        " ORDER BY State"
                        
Me.cboState = Null
Me.cboState.Requery

End If

End Sub

Private Sub cboYear_AfterUpdate()

If Not IsNull(Me.cboYear) Then

Me.cboOperation.RowSource = "SELECT DISTINCT ArbitraryQ.TypeOfOperationID, ArbitraryQ.TypeOfOperation" & _
                       " FROM ArbitraryQ" & _
                       " WHERE YearID = " & Me.cboYear & _
                       " ORDER BY TypeOfOperation"
                       
Me.cboOperation = Null
Me.cboOperation.Requery

End If


End Sub

[June7]

The form is in Continuous View? Dependent (cascading) comboboxes with lookup alias don't work nice in Continuous or Datasheet view.

Instead of setting the form RecordSource, could just set the form Filter property.

If you want to provide db for analysis, follow instructions at bottom of my post.

[JoeyB]

The form is in Continuous View? Dependent (cascading) comboboxes with lookup alias don't work nice in Continuous or Datasheet view.

Instead of setting the form RecordSource, could just set the form Filter property.

If you want to provide db for analysis, follow instructions at bottom of my post.

It is in Single Form.

I got another question for you. I saw you answering another question in 2012, so I didnt want to open that thread. When you open my database you will see one form called: "Formtest". I would like to do it to all of the forms, but this is a good example. How can I get the data I am asking for in that form in a graph, specifically for that airport? That is basically the only thing that this database requires, so if you could answer that, that would be great.

I also set a password on the file, which I will send to you private.

Edit: had to compact and repair and didnt got the latest version:

Attachment 15084

[June7]

I open the ArbitraryF form and no records show because of filter criteria saved in the query. When closing form after record search, don't save changes.

Of the 12 tables in your db, 11 are included in that form RecordSource. There so many circular relationships, the query designer looks like a spider web. Review http://www.codeproject.com/Articles/...atabase-Design

All the joins are INNER.

This form could not be used for data entry. Do you just want this to display records, not edit?

The PassengerT and TypeOfOperationJointT and AircraftMovementT tables all have a many relationship to AirportsT. Multiple many tables in the same query shouldn't work well, especially with INNER joins. However, somehow this query seems to correctly display records. There are 2884 records in AircraftMovementT and 2884 records display on unfiltered form. I am surprised.

Why is there TierJointAirport table? There is only one record for each airport. Why not put tier code in AirportsT?


How do you want the data represented on graph? Do this test. Create XY Scatter Chart (use any settings, doesn't matter). Replace the RowSource with the following SQL:
SELECT AirportID, AirMovementMDAInbound, AirMovementMDAOutbound, AirMovementRegionalInbound, AirMovementRegionalOutbound, AirMovementINTLInbound, AirMovementINTLOutbound FROM AircraftMovementT WHERE AirportID=1 AND YearID=1;

[Missinglinq]

As to your original question, Controls don't appear in the Detail Section of a Form, in Form View, when three conditions exist at the same time:

1. The Form is Bound to a Table or Query
2. There are no Records in the underlying Recordset
3. The Form cannot have Records added to it
   

In a Split Database the Recordset may at least appear to be Empty, to the Front End, if the user on the Front End doesn't have access to the data in the Back End. This can be caused by network problems or the user simply not having the access privileges.

Several things can cause a Form to not be updateable:

• AllowAdditions is set to No.
• RecordSet Type Property is set to Snapshot
• It is based on a Query, where data comes from two or more Tables, and the Query is Read-Only (The RecordSource for your Form is, indeed, based on multiple Tables)
  

Read-Only Queries are explained clearly by Allen Browne:

http://allenbrowne.com/ser-61.html

Linq ;0)>

[JoeyB]

My apologies for my absence, something at work came in between.

I have found what the problem is. I picked an Form which works fine, displayed the info of Brisbane airport in year 2012-13 and entered the info I got from their in the comboboxes. When the correct info is being put in, the textboxes do not disappear and the correct info is shown.

The problem with the comboboxes is that the data is not being filtered good enough

To come back to your questions:

It is only for showing records no editing. If you say reports are the way to go then and it is similar to forms, I would be happy to that... I am on a tight schedule though, so if you could show me the way, please

I fear the TierJointAirportT table is residue from when I was a real novice with Access. If I get the chance to change it, I will.

Now then, find a proper way to filter the data in the comboboxes

[JoeyB]

I thought the extra code that has been marked Red would help. Instead, it once I come to Operator it, cboOperator is blanc. Any suggestions?

Edit: the blue marked stuff--> DISTINCT needed to be added and ORDER BY needed some space ..

Thank you guys for the input, I will work on the graphs tomorrow, or rather in a few hours haha

Private Sub cboOperation_AfterUpdate()

If Not IsNull(Me.cboOperation) Then

Me.cboOperator.RowSource = "SELECT DISTINCT ArbitraryQ.OperatorID, ArbitraryQ.OperatorType" & _
                            " FROM ArbitraryQ" & _
                            " WHERE TypeOfOperationID = " & Me.cboOperation & " AND YearID = " & Me.cboYear & _
                            "ORDER BY OperatorType"
                            
                            

Me.cboOperator = Null
Me.cboOperator.Requery

End If

End Sub

Private Sub cboOperator_AfterUpdate()

If Not IsNull(Me.cboOperator) Then

Me.cboTier.RowSource = "SELECT DISTINCT ArbitraryQ.TierID, ArbitraryQ.Tier" & _
                        " FROM ArbitraryQ" & _
                        " WHERE OperatorID = " & Me.cboOperator & " AND TypeOfOperationID = " & Me.cboOperation & " AND YearID = " & Me.cboYear & _
                        " ORDER BY Tier"
                        
                        
                        
Me.cboTier = Null
Me.cboTier.Requery

End If

End Sub

Private Sub cboState_AfterUpdate()

If Not IsNull(Me.cboState) Then

Me.cboAirport.RowSource = "SELECT DISTINCT ArbitraryQ.AirportID, ArbitraryQ.Airport" & _
                            " FROM ArbitraryQ" & _
                            " WHERE StateID = " & Me.cboState & " AND TierID = " & Me.cboTier & " AND OperatorID = " & Me.cboOperator & " AND TypeOfOperationID = " & Me.cboOperation & " AND YearID = " & Me.cboYear & _
                            " ORDER BY Airport"
                            
                            
Me.cboAirport = Null
Me.cboAirport.Requery

End If

End Sub

Private Sub cboTier_AfterUpdate()

If Not IsNull(Me.cboTier) Then

Me.cboState.RowSource = "SELECT DISTINCT ArbitraryQ.StateID, ArbitraryQ.State" & _
                        " FROM ArbitraryQ" & _
                        " WHERE TierID = " & Me.cboTier & " AND OperatorID = " & Me.cboOperator & " AND TypeOfOperationID = " & Me.cboOperation & " AND YearID = " & Me.cboYear & _
                        " ORDER BY State"
                        
                        
Me.cboState = Null
Me.cboState.Requery

End If

End Sub

Private Sub cboYear_AfterUpdate()

If Not IsNull(Me.cboYear) Then

Me.cboOperation.RowSource = "SELECT DISTINCT ArbitraryQ.TypeOfOperationID, ArbitraryQ.TypeOfOperation" & _
                       " FROM ArbitraryQ" & _
                       " WHERE YearID = " & Me.cboYear & _
                       " ORDER BY TypeOfOperation"
                       
Me.cboOperation = Null
Me.cboOperation.Requery

End If


End Sub

[Missinglinq]

...When the correct info is being put in, the textboxes do not disappear and the correct info is shown...

So, #1, #2 and #3, above, are true; the Form is Bound to a Query, is Read-Only and when the wrong data is entered, has no Records, hence the 'disappearing' Controls!

Linq ;0)>

[June7]

Forms are intended for data entry and on-screen display, reports are intended for printout. Either can be made to do the other duty.

[JoeyB]

So, #1, #2 and #3, above, are true; the Form is Bound to a Query, is Read-Only and when the wrong data is entered, has no Records, hence the 'disappearing' Controls!

Linq ;0)>

The Form wasnt bound to a query, I have made that mistake before haha. Apparently Access doesnt remember the input you gave once you have gone a couple comboboxes, it only remembers the previous one. To prevent this, each combobox afterupdate-WHERE clause needed to have all the previous combobox input instead of only the previous one.

Hope it makes sense. Thank you

[Missinglinq]

...The Form wasnt bound to a query...

This says it is::

Dim strSQL As String

strSQL = "SELECT StateT.State, StateT.StateID, AirportT.Airport, AirportT.AirportID, YearT.Year, YearT.YearID, OperatorT.OperatorType, OperatorT.OperatorID, TierT.Tier, TierT.TierID, TypeOfOperationT.TypeOfOperation, TypeOfOperationT.TypeOfOperationID, PassengerT.PassengerMDAInbound, PassengerT.PassengerMDAOutbound, PassengerT.PassengerRegionalInbound, PassengerT.PassengerRegionalOutbound, PassengerT.PassengerInternationalInbound, PassengerT.PassengerInternationalOutbound, PassengerT.PassengerTotalTotal, AircraftMovementT.AirMovementMDAInbound, AircraftMovementT.AirMovementMDAOutbound, AircraftMovementT.AirMovementRegionalInbound, AircraftMovementT.AirMovementRegionalOutbound, AircraftMovementT.AirMovementINTLInbound, AircraftMovementT.AirMovementINTLOutbound, AircraftMovementT.AirMovementTotalTotal " & _
"FROM OperatorT INNER JOIN (ICAOCodeT INNER JOIN (TypeOfOperationT INNER JOIN ((TierT INNER JOIN (TierJointAirport INNER JOIN ((YearT INNER JOIN ((StateT INNER JOIN AirportT ON StateT.StateID = AirportT.StateID) INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) ON YearT.YearID = AircraftMovementT.YearID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (YearT.YearID = PassengerT.YearID) AND (AirportT.AirportID = PassengerT.AirportID)) ON TierJointAirport.AirportID = AirportT.AirportID) ON TierT.TierID = TierJointAirport.TierID) INNER JOIN TypeOfOperationJointT ON (TierJointAirport.AirportID = TypeOfOperationJointT.AirportID) AND (YearT.YearID = TypeOfOperationJointT.YearID) AND (AirportT.AirportID = TypeOfOperationJointT.AirportID)) ON TypeOfOperationT.TypeOfOperationID = TypeOfOperationJointT.TypeOfOperationID) ON ICAOCodeT.ICAOID = AirportT.ICAOID) ON OperatorT.OperatorID = AirportT.OperatorID " & _
"WHERE YearT.YearID = " & Me.cboYear.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & " AND AirportT.AirportID = " & Me.cboAirport.Column(0) & " AND TypeOfOperationT.TypeOfOperationID = " & Me.cboOperation.Column(0) & " AND OperatorT.OperatorID = " & Me.cboOperator.Column(0) & " AND TierT.TierID = " & Me.cboTier.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & _
" ORDER BY StateT.State, AirportT.Airport, YearT.Year"

Me.RecordSource = strSQL

Linq ;0)>

[JoeyB]

The form has no record source, the textboxes and comboboxes get there data from the vba code.

[Missinglinq]

What, then, is this doing:

Me.RecordSource = strSQL

[JoeyB]

Sorry mate, I do not want to question your knowledge, I honestly thought you meant this?!

[Missinglinq]

That's just one way to set the RecordSource; but you're setting it through VBA code, which is another way to do it!

My signature here, and on other Access forums, used to the

'There's always more than one way to skin a cat!'

and that's especially true of Access! Almost any Property can be set through code, and there frequently is more than one correct syntax for that code! That's why it's fairly common to see multiple different solutions, here, to a single problem, all of them correct! Different developers will have their favorite approaches, to a given problem, based on their individual training as well as their personal experience.

Good luck with your project!

Linq ;0)>