Compile Error: Object Required (Access 2K3)

Topic

New Reply

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!

Reply | Quote

Replies

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

gdr

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

What was your error when NOT using Set at first?

gdr

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

OK I’m confused. Could you post and attach a copy of your db according to post 401925 ?
It doesn’t make sense that you received that error without using Set. You are using Acc2k3 but is your db in 2k3? Under 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:

[indent]

---

And ((tblDailyProduction.JobNumber) = ‘” & JobNum & “‘)

---

[/indent]
Since you’ve “Dimmed” JobNum as an Integer this portion should read…
….And ((tblDailyProduction.JobNumber) = ” & JobNum & “)…
…without any single quotes.

gdr

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

My apologies….got a little delete happy…

Reply | Quote

From what I gathered from your first attachment here is how I would change command7

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 ...
 
DoCmd.OpenQuery "qryHubDistance"
 
db.Close
Set db = Nothing

This should open your query.

Reply | Quote

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.

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

Reply | Quote

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!

Reply | Quote