Access doesnt pick up recordsource

[JoeyB]

I have used the code underneath for another form as well, so it should be working. I have used SQL from the query builder as ITSME recommended. But I get the following message:


After clicking it away, the following message pops up:




It is what my vba code should be doing?! Thank you for all your help!

Option Compare Database

Private Sub cboState_AfterUpdate()

If Not IsNull(Me.cboState) Then

Me.cboYear.RowSource = "SELECT StateQ.YearID, StateQ.Year" & _
                       " FROM StateQ" & _
                       " WHERE StateID = " & Me.cboState & _
                       " ORDER BY Year"
  Me.cboYear = Null
  Me.cboYear.Requery
  
  End If
  
End Sub

Private Sub cboYear_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT StateT.[State/Territory], YearT.Year, Sum(AircraftMovementT.AirMovementMDAInbound) AS SumOfAirMovementMDAInbound,     Sum(AircraftMovementT.AirMovementMDAOutbound) AS " & _
         "SumOfAirMovementMDAOutbound, Sum(AircraftMovementT.AirMovementRegionalInbound) AS SumOfAirMovementRegionalInbound, Sum(AircraftMovementT.AirMovementRegionalOutbound) AS " & _
         "SumOfAirMovementRegionalOutbound, Sum(AircraftMovementT.AirMovementINTLInbound) AS SumOfAirMovementINTLInbound, Sum(AircraftMovementT.AirMovementINTLOutbound) AS " & _
         "SumOfAirMovementINTLOutbound, Sum(AircraftMovementT.AirMovementTotalTotal) AS SumOfAirMovementTotalTotal, Sum(PassengerT.PassengerMDAOutbound) AS SumOfPassengerMDAOutbound, " & _
         "Sum(PassengerT.PassengerRegionalInbound) AS SumOfPassengerRegionalInbound, Sum(PassengerT.PassengerRegionalOutbound) AS SumOfPassengerRegionalOutbound, " & _
         "Sum(PassengerT.PassengerInternationalInbound) AS SumOfPassengerInternationalInbound, Sum(PassengerT.PassengerInternationalOutbound) AS SumOfPassengerInternationalOutbound, " & _
         "Sum(PassengerT.PassengerTotalTotal) AS SumOfPassengerTotalTotal, Sum(PassengerT.PassengerMDAInbound) AS SumOfPassengerMDAInbound " & _
         "YearT.YearID, StateT.[State/TerritoryID] " & _
         "FROM TypeOfOperationT INNER JOIN ((StateT INNER JOIN (YearT INNER JOIN ((AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) INNER JOIN PassengerT ON " & _
         "(PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (AirportT.AirportID = PassengerT.AirportID)) ON (YearT.YearID = PassengerT.YearID) AND (YearT.YearID = AircraftMovementT.YearID)) " & _
         "ON StateT.[State/TerritoryID] = AirportT.[State/TerritoryID]) INNER JOIN TypeOfOperationJointT ON (YearT.YearID = TypeOfOperationJointT.YearID) AND (AirportT.AirportID = TypeOfOperationJointT.AirportID)) " & _
         "ON TypeOfOperationT.TypeOfOperationID = TypeOfOperationJointT.TypeOfOperationID " & _
         "GROUP BY StateT.[State/Territory], YearT.Year " & _
         "HAVING (((StateT.[State/Territory])='South Australia' Or (StateT.[State/Territory])='Tasmania' Or (StateT.[State/Territory])='New South Wales' Or (StateT.[State/Territory])='Queensland' Or " & _
         "(StateT.[State/Territory])='Western Australia' Or (StateT.[State/Territory])='Victoria' Or (StateT.[State/Territory])='Northern Territory' Or (StateT.[State/Territory])='Australian Capital Territory' Or " & _
         "(StateT.[State/Territory])='Australian Antartic Territory' Or (StateT.[State/Territory])='Christmas Island' Or (StateT.[State/Territory])='Cocos Keeling Islands' Or (StateT.[State/Territory])='Coral Sea Island' Or " & _
         "(StateT.[State/Territory])='Jervis Bay Territory' Or (StateT.[State/Territory])='Territory of Heard Island and McDonald Islands' Or (StateT.[State/Territory])='Norfolk Island') AND ((YearT.Year)='1985-86' Or " & _
         "(YearT.Year)='1986-87' Or (YearT.Year)='1987-88' Or (YearT.Year)='1988-89' Or (YearT.Year)='1989-90' Or (YearT.Year)='1990-91' Or (YearT.Year)='1991-92' Or (YearT.Year)='1992-93' Or (YearT.Year)='1993-94' Or " & _
         "(YearT.Year)='1994-95' Or (YearT.Year)='1995-96' Or (YearT.Year)='1996-97' Or (YearT.Year)='1997-98' Or (YearT.Year)='1998-99' Or (YearT.Year)='1999-00' Or (YearT.Year)='2000-01' Or (YearT.Year)='2001-02' Or " & _
         "(YearT.Year)='2002-03' Or (YearT.Year)='2003-04' Or (YearT.Year)='2004-05' Or (YearT.Year)='2005-06' Or (YearT.Year)='2006-07' Or (YearT.Year)='2007-08' Or (YearT.Year)='2008-09' Or (YearT.Year)='2009-10' Or " & _
         "(YearT.Year)='2010-11' Or (YearT.Year)='2011-12' Or (YearT.Year)='2012-13')) " & _
         "WHERE YearT.YearID = " & Me.cboYear.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & _
         " ORDER BY StateT.[State/Territory], YearT.Year"

Me.RecordSource = strSQL
Me.Requery

End Sub

Private Sub Form_Open(Cancel As Integer)
MsgBox "Summation of all passenger and aircraft numbers per state"


End Sub

[ItsMe]

Did you copy an older form to create your new one? It seems there may be an embedded macro in a control or the form's properties that is trying to apply a filter.

[JoeyB]

Did you copy an older form to create your new one? It seems there may be an embedded macro in a control or the form's properties that is trying to apply a filter.

I couldnt find a emb macro or anything at the form properties. One I copied and one I build from scratch. Both give the same error. I added the ZIP file. The names of the forms are SumStateF and SumStateF2.

[ItsMe]

You have an embeded macro in the combo.


.

[JoeyB]

You have an embeded macro in the combo.

That is the vba code, in the other Form (FormTest) this is not a problem though

[ItsMe]

maybe you can help me out and tell me what form and when you are getting the messages.

[JoeyB]

maybe you can help me out and tell me what form and when you are getting the messages.

I got something very strange going on. Apparently I deleted a query. Named querytest. And it disappeared in all of my backups and previous versions as well?! Even if I open the file I posted here, it is disappeared... Do you got this query in your file?

Edit: got it working again, apparently I deleted it. I will change the file I uploaded previously



And regarding this thread: it happens with the forms: StateF and StateF2, one is copied and one is build from scratch but both give the same error. And I am getting the message once I try to select a state or year.

[ItsMe]

That screenshot is from SumStateF2. Comment the code for the AfterUpdate, delete the text in the On After Update field in the ptoperty sheet, recreate the Event Handler.


There is not a QueryTest in the file you provided. This previous file from Dec 18 has one though.

[JoeyB]

Here you go. Apologies for being so messy.

Airport Database 0.8.zip

[ItsMe]

Did you see post #8?

[JoeyB]

Did you see post #8?

I have deleted the emb MACRO, deleted the text[event procedure] and 'connected' VBA to the Afterupdate. Now when you richt mouse click-> build, you go to vba instead of SearchforRecord MACRO.
I had a look at the Row source of the cbo's and made them the same as FormTest( the form which is working properly again).
I had a look at the Syntax for the HAVING clause and put the WHERE clause before the GROUP BY CLAUSE, as in the following link is stated: http://www.w3schools.com/sql/sql_having.asp

When I click on State and select a State, I get a "Enter a parameter value", close that one (because I do not know what to enter there) and after that click on year and nothing is in the cboYear.
Click on state again and select a State, click somewhere random and I get a Run-Time error:



Airport Database 0.81.zip

[ItsMe]

Try placing your WHERE clause before your GROUP BY. Not sure that will fix it. Why do you have so many Or operators? Does not the State ID an Year ID manage this? It seems odd that you would hard code these options into a query and then ask the user to make selections from a combo to create a WHERE clause.

[JoeyB]

Try placing your WHERE clause before your GROUP BY. Not sure that will fix it. Why do you have so many Or operators? Does not the State ID an Year ID manage this? It seems odd that you would hard code these options into a query and then ask the user to make selections from a combo to create a WHERE clause.

I already replaced the WHERE clause, but indeed, it did not solve my problems.
Regarding the Or operators, I basically followed your advice by building the query, and then use the SQL code in the VBA. The only way I could figure out and made sense for me, was to use the Or operator in the query, otherwise it would not SUM up the numbers per state and per year. If you would know a better way, I am eager to learn ^^

Edit: You were right, I could delete the operators and still get the SUM for each year and State. This makes the code a lot smaller. However I am still getting the Runtime 3075 error.

And as rowsource for my cbo's in the Form I got:
cboState:

SELECT DISTINCT [StateQ2].[State], [StateQ2].[StateID] FROM StateQ2;

cboYear:

SELECT DISTINCT [StateQ2].[YearID], [StateQ2].[Year] FROM StateQ2 WHERE AirportID = 4 ORDER BY Year;

VBA code:

Option Compare Database
Option Explicit


Private Sub cboState_AfterUpdate()

  ' Set the Phylum combo box to be limited by the selected Kingdom


If Not IsNull(Me.cboState) Then

Me.cboYear.RowSource = "SELECT StateQ2.YearID, StateQ2.Year" & _
                       " FROM StateQ2" & _
                       " WHERE StateID = " & Me.cboState & _
                       " ORDER BY Year"
  Me.cboYear = Null
  Me.cboYear.Requery

End If



End Sub

Private Sub cboYear_AfterUpdate()



Dim strSQL As String

strSQL = "SELECT StateT.State, YearT.Year, YearT.YearID, StateT.StateID, Sum(AircraftMovementT.AirMovementMDAInbound) AS SumOfAirMovementMDAInbound, " & _
"Sum(AircraftMovementT.AirMovementMDAOutbound) AS SumOfAirMovementMDAOutbound, Sum(AircraftMovementT.AirMovementRegionalInbound) AS " & _
"SumOfAirMovementRegionalInbound, Sum(AircraftMovementT.AirMovementRegionalOutbound) AS SumOfAirMovementRegionalOutbound, " & _
"Sum(AircraftMovementT.AirMovementINTLInbound) AS SumOfAirMovementINTLInbound, Sum(AircraftMovementT.AirMovementINTLOutbound) AS SumOfAirMovementINTLOutbound, Sum(AircraftMovementT.AirMovementTotalTotal) AS SumOfAirMovementTotalTotal, Sum(PassengerT.PassengerMDAOutbound) AS SumOfPassengerMDAOutbound, Sum(PassengerT.PassengerRegionalInbound) AS SumOfPassengerRegionalInbound, Sum(PassengerT.PassengerRegionalOutbound) AS SumOfPassengerRegionalOutbound, Sum(PassengerT.PassengerInternationalInbound) AS SumOfPassengerInternationalInbound, Sum(PassengerT.PassengerInternationalOutbound) AS SumOfPassengerInternationalOutbound, Sum(PassengerT.PassengerTotalTotal) AS SumOfPassengerTotalTotal, Sum(PassengerT.PassengerMDAInbound) AS SumOfPassengerMDAInbound " & _
"FROM StateT INNER JOIN (YearT INNER JOIN ((AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (AirportT.AirportID = PassengerT.AirportID)) ON (YearT.YearID = AircraftMovementT.YearID) AND (YearT.YearID = PassengerT.YearID)) ON StateT.StateID = AirportT.[StateID]" & _
"WHERE YearT.YearID = " & Me.cboYear.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & _
" ORDER BY StateT.State, YearT.Year "

                  
        
Me.RecordSource = strSQL
Me.Requery




End Sub

[ItsMe]

Few things. The combo you have for Year is corrupt. Create a new one. Recommend using a slightly different name also. Also, the SQL for your first combo does not have enough columns. The SQL should contain the ID
SELECT DISTINCT StateQ.[StateID], StateQ.[State] FROM StateQ;

and you will need to have column count = 2 the column width should be something like 0";2" and the bound column should be 1. binding column 1 will give you the value of StateID.

With that, the following should work. It did not before and something else was happening because the Year combo is corrupt.

If Not IsNull(Me.cboState) Then
Me.cmbTest.RowSource = "SELECT StateQ.YearID, StateQ.Year" & _
                       " FROM StateQ" & _
                       " WHERE StateID = " & Me.cboState & _
                       " ORDER BY Year"
  Me.cmbTest = Null
  Me.cmbTest.Requery
  End If

Not sure what you did with the latest SQL but I took some an example of the previous forms recordsource and created some SQL that you can build an object with. This way you can check to make sure it is what you want. I hard coded the ID's to = 7 in the where clause

SELECT StateT.State, YearT.Year, Sum(AircraftMovementT.AirMovementMDAInbound) AS SumOfAirMovementMDAInbound, Sum(AircraftMovementT.AirMovementMDAOutbound) AS
SumOfAirMovementMDAOutbound, Sum(AircraftMovementT.AirMovementRegionalInbound) AS SumOfAirMovementRegionalInbound, Sum(AircraftMovementT.AirMovementRegionalOutbound) AS SumOfAirMovementRegionalOutbound, Sum(AircraftMovementT.AirMovementINTLInbound) AS SumOfAirMovementINTLInbound, Sum(AircraftMovementT.AirMovementINTLOutbound) AS SumOfAirMovementINTLOutbound, Sum(AircraftMovementT.AirMovementTotalTotal) AS SumOfAirMovementTotalTotal, Sum(PassengerT.PassengerMDAOutbound) AS SumOfPassengerMDAOutbound, Sum(PassengerT.PassengerRegionalInbound) AS SumOfPassengerRegionalInbound, Sum(PassengerT.PassengerRegionalOutbound) AS SumOfPassengerRegionalOutbound, Sum(PassengerT.PassengerInternationalInbound) AS SumOfPassengerInternationalInbound, Sum(PassengerT.PassengerInternationalOutbound) AS SumOfPassengerInternationalOutbound, Sum(PassengerT.PassengerTotalTotal) AS SumOfPassengerTotalTotal, Sum(PassengerT.PassengerMDAInbound) AS SumOfPassengerMDAInbound, YearT.YearID, StateT.StateID
FROM StateT INNER JOIN (YearT INNER JOIN ((AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (AirportT.AirportID = PassengerT.AirportID)) ON (YearT.YearID = AircraftMovementT.YearID) AND (YearT.YearID = PassengerT.YearID)) ON StateT.StateID=AirportT.[StateID]
WHERE YearT.YearID =7 AND StateT.StateID =7
GROUP BY StateT.State, YearT.Year, YearT.YearID, StateT.StateID
HAVING (((StateT.State)= 'South Australia' Or (StateT.State) = 'Tasmania' Or (StateT.State) = 'New South Wales' Or (StateT.State)='Queensland' Or (StateT.State) = 'Western Australia' Or (StateT.State) = 'Victoria' Or
(StateT.State) = 'Northern Territory' Or (StateT.State) = 'Australian Capital Territory' Or (StateT.State) = 'Australian Antartic Territory' Or (StateT.State) = 'Christmas Island' Or (StateT.State) = 'Cocos Keeling Islands' Or (StateT.State) = 'Coral Sea Island' Or (StateT.State) = 'Jervis Bay Territory' Or (StateT.State) = 'Territory of Heard Island and McDonald Islands' Or (StateT.State) = 'Norfolk Island') AND ((YearT.Year) = '1985-86' Or (YearT.Year) = '1986-87' Or (YearT.Year) = '1987-88' Or (YearT.Year) = '1988-89' Or (YearT.Year) = '1989-90' Or (YearT.Year) = '1990-91' Or (YearT.Year) = '1991-92' Or (YearT.Year) = '1992-93' Or (YearT.Year) = '1993-94' Or (YearT.Year) = '1994-95' Or (YearT.Year) = '1995-96' Or (YearT.Year)='1996-97' Or (YearT.Year) = '1997-98' Or (YearT.Year) = '1998-99' Or (YearT.Year) = '1999-00' Or
(YearT.Year) = '2000-01' Or
(YearT.Year) = '2001-02' Or (YearT.Year) = '2002-03' Or (YearT.Year) = '2003-04' Or (YearT.Year) = '2004-05' Or (YearT.Year) = '2005-06' Or (YearT.Year) = '2006-07' Or (YearT.Year) = '2007-08' Or (YearT.Year)='2008-09' Or (YearT.Year)= '2009-10' Or (YearT.Year) = '2010-11' Or (YearT.Year) = '2011-12' Or (YearT.Year) = '2012-13'))
ORDER BY StateT.State, YearT.Year;

Here is code you can place in VBA module

strSQL = "SELECT StateT.State, YearT.Year, Sum(AircraftMovementT.AirMovementMDAInbound) AS SumOfAirMovementMDAInbound, Sum(AircraftMovementT.AirMovementMDAOutbound) AS " & _
         "SumOfAirMovementMDAOutbound, Sum(AircraftMovementT.AirMovementRegionalInbound) AS SumOfAirMovementRegionalInbound, Sum(AircraftMovementT.AirMovementRegionalOutbound) AS SumOfAirMovementRegionalOutbound, Sum(AircraftMovementT.AirMovementINTLInbound) AS SumOfAirMovementINTLInbound, Sum(AircraftMovementT.AirMovementINTLOutbound) AS SumOfAirMovementINTLOutbound, Sum(AircraftMovementT.AirMovementTotalTotal) AS SumOfAirMovementTotalTotal, Sum(PassengerT.PassengerMDAOutbound) AS SumOfPassengerMDAOutbound, Sum(PassengerT.PassengerRegionalInbound) AS SumOfPassengerRegionalInbound, Sum(PassengerT.PassengerRegionalOutbound) AS SumOfPassengerRegionalOutbound, Sum(PassengerT.PassengerInternationalInbound) AS SumOfPassengerInternationalInbound, Sum(PassengerT.PassengerInternationalOutbound) AS SumOfPassengerInternationalOutbound, Sum(PassengerT.PassengerTotalTotal) AS SumOfPassengerTotalTotal, Sum(PassengerT.PassengerMDAInbound) AS SumOfPassengerMDAInbound, YearT.YearID, StateT.StateID " & _
         "FROM StateT INNER JOIN (YearT INNER JOIN ((AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (AirportT.AirportID = PassengerT.AirportID)) ON (YearT.YearID = AircraftMovementT.YearID) AND (YearT.YearID = PassengerT.YearID)) ON StateT.StateID=AirportT.[StateID] " & _
         "WHERE YearT.YearID = " & Me.cmbTest.Column(0) & " AND StateT.StateID = " & Me.cboState.Column(0) & _
         " GROUP BY StateT.State, YearT.Year, YearT.YearID, StateT.StateID " & _
         "HAVING (((StateT.State)= 'South Australia' Or (StateT.State) = 'Tasmania' Or (StateT.State) = 'New South Wales' Or (StateT.State)='Queensland' Or (StateT.State) = 'Western Australia' Or (StateT.State) = 'Victoria' Or " & _
         "(StateT.State) = 'Northern Territory' Or (StateT.State) = 'Australian Capital Territory' Or (StateT.State) = 'Australian Antartic Territory' Or (StateT.State) = 'Christmas Island' Or (StateT.State) = 'Cocos Keeling Islands' Or (StateT.State) = 'Coral Sea Island' Or (StateT.State) = 'Jervis Bay Territory' Or (StateT.State) = 'Territory of Heard Island and McDonald Islands' Or (StateT.State) = 'Norfolk Island') AND ((YearT.Year) = '1985-86' Or (YearT.Year) = '1986-87' Or (YearT.Year) = '1987-88' Or (YearT.Year) = '1988-89' Or (YearT.Year) = '1989-90' Or (YearT.Year) = '1990-91' Or (YearT.Year) = '1991-92' Or (YearT.Year) = '1992-93' Or (YearT.Year) = '1993-94' Or (YearT.Year) = '1994-95' Or (YearT.Year) = '1995-96' Or (YearT.Year)='1996-97' Or (YearT.Year) = '1997-98' Or (YearT.Year) = '1998-99' Or (YearT.Year) = '1999-00' Or " & _
         "(YearT.Year) = '2000-01' Or " & _
         "(YearT.Year) = '2001-02' Or (YearT.Year) = '2002-03' Or (YearT.Year) = '2003-04' Or (YearT.Year) = '2004-05' Or (YearT.Year) = '2005-06' Or (YearT.Year) = '2006-07' Or (YearT.Year) = '2007-08' Or (YearT.Year)='2008-09' Or (YearT.Year)= '2009-10' Or (YearT.Year) = '2010-11' Or (YearT.Year) = '2011-12' Or (YearT.Year) = '2012-13')) " & _
         "ORDER BY StateT.State, YearT.Year;"

Form SumStateF
and
qry_A_A_A

Are the objects I used.

[JoeyB]

I am happy to see that some of the things I also thought off haha, but some things I would have never thought off, which I am grateful for. Learn something every day. Now I am going to apply it to the rest of the database. Thanks again!

edit: cant see where you used the object qry_A_A_A?