CODEWITHSUNDEEP
sqlvbams-accessms-access-2003
user3208525
user3208525

Reputation: 1

Access VBA Object Required Error

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

Upvotes: 0

Views: 4356

Answers (2)

donPablo
donPablo

Reputation: 1959

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

Upvotes: 1

Floris
Floris

Reputation: 46415

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.

Upvotes: 1

Related Questions