Access VBA Object Required Error

Question

This has probably been answered before, but in looking, I could not find an answer that suited my situation. I am coding an Access 2003 form button to run an If/Then and do some commands based on that statement when clicked. However I am getting the 'Object Required' error on every click. I am still relatively new to VBA code, so please be gentle.

Here is the code:

Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim Db As Object

Set Db = CurrentDb

Dim waveset As DAO.Recordset

Set waveset = Db.OpenRecordset("SELECT countervalue FROM dbo_tblSettings")

Dim orderfile As String

orderfile = "\\serverfolder\serverfile.csv"

If Value.waveset > 0 Then

MsgBox "Orders Already Imported; Please Proceed to Next Step", vbOKOnly, "Step Complete"

GoTo Exit_Button_Click
    ElseIf Value.waveset = 0 Then

    DoCmd.RunSQL "DELETE FROM dbo_tblOrders", True

    DoCmd.TransferText acImportDelim, , "dbo_tblOrders", orderfile, True

    DoCmd.RunSQL "UPDATE dbo_tblOrders INNER JOIN dbo_MainOrderTable ON dbo_tblOrders.[channel-order-id]=dbo_MainOrderTable.[order-id] " _
    & "SET dbo_MainOrderTable.[order-id] = dbo_tblOrders.[order-id], dbo_MainOrderTable.[channel-order-id] = dbo_tblOrders.[channel-order-id], " _
    & "dbo_MainOrderTable.[Order-Source] = 'Amazon'" _
    & "WHERE dbo_tblOrders.[sales-channel]='Amazon';", True

    DoCmd.RunSQL "UPDATE dbo_AmazonOrderTable INNER JOIN dbo_tblOrders ON dbo_AmazonOrderTable.[order-id]=dbo_tblOrders.[channel-order-id] " _
    & "SET dbo_AmazonOrderTable.[order-id] = dbo_tblOrders.[order-id], dbo_AmazonOrderTable.[channel-order-id] = dbo_tblOrders.[channel-order-id], " _
    & "dbo_AmazonOrderTable.[sales-channel] = 'Amazon' " _
    & "WHERE dbo_tblOrders.[sales-channel]='Amazon';", True

    DoCmd.RunSQL "UPDATE dbo_tblSettings SET countervaule=1", True
Else
GoTo Exit_Button_Click
End If

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

-OH! forgot to mention I want to do it this way because the tables are actually linked tables to a SQL server back-end... I've also been trying to figure out how to open the connection to my SQL server and manipulate the tables via the VBA code without having to link them in Access... but that's another post altogether

Any help will be greatly appreciated. Thanks

`Value.waveset` looks suspicious. What is it supposed to be? Make sure you include `Option Explicit` in the Declarations section of your code module and then run Debug->Compile from the VB Editor's main menu. Does your code compile without error when you do that? — HansUp, Jan 18 '14 at 15:55
I would start by making sure that the `On Error GoTo Err_Button_Click` has a corresponding label / code . I only see `Exit_Button_Click` and `Err_DotComBBWTableRefresh_Click:`... — Floris, Jan 18 '14 at 15:56
sorry i tried to take out all the references to the actual business...Err_DotComBBWTableRefresh_Click should be Err_Button_Click... :/ — user3208525, Jan 18 '14 at 17:34

score 1 · Answer 1 · answered Jan 18 '14 at 15:58

1

I think the problem is the line

If Value.waveset > 0 Then

Which should be

waveset.Value > 0 Then

.Value is a property - it comes after the object, but it is not itself an object. Thus, asking for the .waveset property of Value will give an error.

The same thing happens again a few lines later:

ElseIf Value.waveset = 0 Then

The advice that @HansUp gave in his comment is a good one. Write Option Explicit at the top of your module, then hit Debug->Compile. Your code will generate errors. Add statements of the form

Dim waveset

to the top of your function. Only declare variables you intend to use. Any remaining errors are now due to typos or other syntax / logic errors, and will be spotted more easily.

If you are sure you know what type a particular variable will be, it is marginally more efficient to declare as that type; so

Dim ii As Integer

For ii = 0 To 10000
  ...

is marginally more efficient than

Dim ii
For ii = 0 To 10000

But that's not what your question is about.

answered Jan 18 '14 at 15:58

Floris

45,857
6
70
122

All great info! Even if it doesn't have to do with my question :D I love learning new code/languages, thanks! – user3208525 Jan 18 '14 at 17:36
@user3208525 did you try these debugging steps? Did they help in any way? Did you get any closer to finding out what object was causing the problem? You can step through the code one line at a time (set a breakpoint near the start of the function, then single step). Or turn off error handling and hit "debug" when the error occurs. You can then look at the variables in the line where the error occurred. – Floris Jan 19 '14 at 03:54
Yes, perfect advise for a newb like me :) it was the ordering of the attributes of the object... i guess is how you would say. donPablo's answer actually fixed my issue. Thanks to all for the great help! Love the site. – user3208525 Jan 21 '14 at 16:54
@user3208525 since donPablo's answer solved your problem, you should mark his answer as "accepted" (with the little check mark). – Floris Jan 21 '14 at 21:12

score 1 · Answer 2 · answered Jan 20 '14 at 05:03

1

Yes, ...If Value.waveset > 0 ... does give an error Object Required

BOTH lines need to be changed, but need to be this syntax ---

If  waveset.Fields("countervalue").Value > 0
...
ElseIf waveset.Fields("countervalue").Value = 0 Then

answered Jan 20 '14 at 05:03

donPablo

1,937
1
13
18

+1 for reading the code carefully enough to figure out the correct syntax for the solution. – Floris Jan 21 '14 at 21:12

Access VBA Object Required Error

2 Answers2