Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compile Error: Object Required (Access 2K3)

    Pulling my hair out because I can't figure this out. I've done this at least 5 times before and can't figure out what the error is. Here is the entire Block of code:

    Private Sub Command7_Click()

    Dim EquipNum As String
    Dim JobNum As Integer

    Set EquipNum = Combo2.Value

    Set JobNum = Combo4.Value

    Queries!qryHasHubMeterReading.SQL = "SELECT tblDailyProduction.Equip, tblDailyProduction.Date, tblDailyProduction.JobNumber, tblDailyProduction.Crew, tblDailyProduction.Code, tblDailyProduction.Hours, tblDailyProduction.PENE1, tblDailyProduction.PENE2, tblDailyProduction.HUBMeter, tblDailyProduction.LoadCount, tblDailyProduction.AvgCYLoad " & _
    "FROM tblDailyProduction " & _
    "WHERE (((tblDailyProduction.Equip) = '" & EquipNum & "') And ((tblDailyProduction.JobNumber) = '" & JobNum & "') And ((tblDailyProduction.PENE1) <> 0) And ((tblDailyProduction.PENE2) <> 0) And ((tblDailyProduction.HUBMeter) Is Not Null And (tblDailyProduction.HUBMeter) <> 0)) " & _
    "ORDER BY tblDailyProduction.Equip, tblDailyProduction.Date;"

    qryHubDistance.Open

    End Sub


    The error I'm getting is "Compile Error: Object Required". It occurs on the line "Set EquipNum =...".

    If anyone can help and put an end to my misery, I would be SINCERELY grateful.

    Thanks much!

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    You don't need to use Set when setting a variable equal to a value.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    Thanks for a SUPER quick reply.

    Problem is, I tried that first. This variant gives me the Debug Window, and shows the error message:

    "Run-Time Error '424':

    Object Required"

    Can't believe I'm having such a hard time with this.

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error: Object Required (Access 2K3)

    As Gary noted, the instructions

    Set EquipNum = Combo2.Value
    Set JobNum = Combo4.Value

    should NOT include Set, since the variables EquipNum and JobNum are a string an a number, not objects. It should be

    EquipNum = Combo2.Value
    JobNum = Combo4.Value

    If either of these cause an error, it would seem that Combo2 or Combo4 is not a valid name.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    What was your error when NOT using Set at first?

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    Thanks again!

    The error WAS occuring before I added SET in front of the assignments. I also double checked the Box names, and they are indeed Combo2 and Combo4. Here is another piece of code from the same form that runs successfully:

    Private Sub Combo2_LostFocus()

    Dim EquipNum As String
    EquipNum = Forms!frmHaulReport!Combo2.Text

    Forms!frmHaulReport!Combo4.SetFocus

    Forms!frmHaulReport!Combo4.RowSourceType = "Table/Query"
    Forms!frmHaulReport!Combo4.RowSource = "SELECT qryHasHubMeterReading.JobNumber " & _
    "FROM qryHasHubMeterReading WHERE ((qryHasHubMeterReading.Equip) = '" & EquipNum & "')" & _
    "GROUP BY qryHasHubMeterReading.JobNumber;"

    End Sub

    P.S. - I already tried adding Forms!frmHaulReport! in front of the ComboX.Value

    Thanks much, I really appreciate the help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error: Object Required (Access 2K3)

    Are Combo2 and Combo4 both on the form running the code?
    If you type Me. (that is, Me followed by a point/period), IntelliSense pops up a list of available properties and methods. Are Combo2 and Combo4 listed among them?

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    OK I'm confused. Could you post and attach a copy of your db according to <post#=401925 >post 401925 </post#> ?
    It doesn't make sense that you received that error without using Set. You are using Acc2k3 but is your db in 2k3? Under <Tools><References> do you have Microsoft DAO 3.6 (or 3.5) checked?...and if so is it listed above Microsoft ActiveX Data Objects 2.X? If not, it should be.

    I've also got concerns about your WHERE clause especially this portion:

    <hr>And ((tblDailyProduction.JobNumber) = '" & JobNum & "') <hr>
    Since you've "Dimmed" JobNum as an Integer this portion should read...
    ....And ((tblDailyProduction.JobNumber) = " & JobNum & ")...
    ...without any single quotes.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    Thanks ya'll so much for the help.

    OK...first verified the Combo boxes were on the form by using Me. and scrolling down. Next, I opened the references and verified DAO 3.6, which was right above Data Objects 2.1. I DID notice that the field JobNumber was a text field (someone changed it on me), but when I adjusted the code accordingly, I got the same result. Attached is a thinned-out version of the db (the whole thing was too large to attach).

    Thanks again, you guys are great!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error: Object Required (Access 2K3)

    You removed both too much and too little from the database.
    We can't do much with the form, since you removed the query qryEquipL2 that is referenced in the row source of Combo2.
    On the other hand, we don't need 2808 records in the table tblDailyProduction, just a few records should suffice.

  11. #11
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    My apologies....got a little delete happy...

  12. #12
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    From what I gathered from your first attachment here is how I would change command7
    <pre>Dim EquipNum As String
    Dim JobNum As String
    Dim db As Database
    Dim qdf As QueryDef

    EquipNum = Forms!frmHaulReport!Combo2.Value

    JobNum = Forms!frmHaulReport!Combo4.Value

    Set db = CurrentDb()

    Set qdf = db.QueryDefs("qryHasHubMeterReading")

    qdf.SQL = "SELECT ...<no change here>

    DoCmd.OpenQuery "qryHubDistance"

    db.Close
    Set db = Nothing
    </pre>



    This should open your query.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Error: Object Required (Access 2K3)

    In the first place, there are two errors in your code. There is no Queries collection, and a query isn't an object with methods. The On Click code for the command button should be

    Private Sub Command7_Click()
    Dim EquipNum As String
    Dim JobNum As String
    EquipNum = Me.Combo2.Value
    JobNum = Me.Combo4.Value
    CurrentDb.QueryDefs("qryHasHubMeterReading").SQL = _
    "SELECT * FROM tblDailyProduction " & _
    "WHERE Equip = '" & EquipNum & "' And JobNumber = '" & JobNum & _
    "' And PENE1 <> 0 And PENE2 <> 0 And HUBMeter Is Not Null " & _
    "And HUBMeter <> 0 " & _
    "ORDER BY Equip, Date"
    DoCmd.OpenQuery "qryHubDistance"
    End Sub

    But I don't understand what you're doing. By changing the SQL for qryHasHubMeterReading, you are changing the row source for Combo2 too, so next time it'll show only one item. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    BTW, I would use the After Update event of the combo box, not the On Lost Focus event.

  14. #14
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Error: Object Required (Access 2K3)

    Success!!!

    As usual, you guys are saviors, and for that I thank you. I had to develop this to use some old data that we quit collecting, and the way it was gathered required this work-around. Modifying qryHasHubMeterReading ultimately modifies the second query to give us usuable data. I guess I just blanked out on the whole QueryDef thing, but that's what fixed it. The form runs like a gem now.

    Thanks SO MUCH!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •