asked on

Access 2010 VBA automation error

Hello Experts,

I am new to programming and especially new to VBA. So any help would be greatly appreciated.

In Access 2010 I need to be able to click a command button that will run a query that returns a small one field recordset. (This feild is a Hyperlink.) Then put the value of the field in that recordset into a string variable.

This string variable is a link to a word document on the network. the second part of the code will then open the word document.

I am getting the error: "Object variable or With block variable not set"

I am unsure which line is giving me trouble however I think it is erroring out before the line"

MsgBox "Spec sheet name: " & specSheet

Open in new window

because this MsgBox never shows up.

My code looks like this:

Option Compare Database

Sub cmdCESpec_Click()


On Error GoTo Err_cmdCESpec_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim s As String
Dim specSheet As String

s = "SELECT p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = " & [Forms]![frmSpecSheet]![cboPartNum] 'Chooses the correct Spec Sheet.
Set rs = db.OpenRecordset(s)

specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field
MsgBox "Spec sheet name: " & specSheet


rs.Close

    Dim oApp As Object
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    With oApp
        .Documents.Open (specSheet)
    End With
    
Exit_cmdCESpec_Click:
    Exit Sub

Err_cmdCESpec_Click:
    MsgBox Err.Description
    Resume Exit_cmdCESpec_Click
    
End Sub

Open in new window

mbizup

Part Number is TEXT, correct? I don't think you implemented my solution from the last question quite right.

It should be:

s = "SELECT p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = '" & [Forms]![frmSpecSheet]![cboPartNum]  & "'" 'Chooses the correct Spec Sheet.
Set rs = db.OpenRecordset(s)

Open in new window

(Note the embedded single quotes)

mbizup

Also, to pinpoint which line is failing, and the error message, temporarily comment out the On Error line. If you get an error, click "Debug" and let us know which line appears highlighted in the debugger.

Try this:

Option Compare Database

Sub cmdCESpec_Click()


'On Error GoTo Err_cmdCESpec_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim s As String
Dim specSheet As String

s = "SELECT p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = '" & [Forms]![frmSpecSheet]![cboPartNum] & "'" 'Chooses the correct Spec Sheet.
Set rs = db.OpenRecordset(s)

specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field
MsgBox "Spec sheet name: " & specSheet


rs.Close

    Dim oApp As Object
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    With oApp
        .Documents.Open (specSheet)
    End With
    
Exit_cmdCESpec_Click:
    Exit Sub

Err_cmdCESpec_Click:
    MsgBox Err.Description
    Resume Exit_cmdCESpec_Click
    
End Sub

Open in new window

UbiqutyDegins

ASKER

Yes, PartNum is text. I have made the changes you suggested and there appears to be no change in the programs responce.

Option Compare Database

Sub cmdCESpec_Click()


On Error GoTo Err_cmdCESpec_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim s As String
Dim specSheet As String

s = "SELECT p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = '" & [Forms]![frmSpecSheet]![cboPartNum] & "'"  'Chooses the correct Spec Sheet.
Set rs = db.OpenRecordset(s)


specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field
MsgBox "Spec sheet name: " & specSheet


rs.Close

    Dim oApp As Object
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    With oApp
        .Documents.Open (specSheet)
    End With
    
Exit_cmdCESpec_Click:
    Exit Sub

Err_cmdCESpec_Click:
    MsgBox Err.Description
    Resume Exit_cmdCESpec_Click
    
End Sub

Open in new window

mbizup

Okay - take a look at my last comment and try the code exactly as I have posted it. Note the tick mark before the On Error:

'On Error GoTo Err_cmdCESpec_Click

Open in new window

Now when you get your error, click Debug... and let me know which line is highlighted.

ASKER CERTIFIED SOLUTION

mbizup

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

UbiqutyDegins

ASKER

The code is erroring out on:

Set rs = db.OpenRecordset(s)

Open in new window

mbizup

Try this:

Option Compare Database

Sub cmdCESpec_Click()


On Error GoTo Err_cmdCESpec_Click
Dim db As Database
Dim rs As DAO.Recordset
Dim s As String
Dim specSheet As String

Set db = CurrentDB  '<---- add this line here

s = "SELECT p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = '" & [Forms]![frmSpecSheet]![cboPartNum] & "'"  'Chooses the correct Spec Sheet.
Set rs = db.OpenRecordset(s)


specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field
MsgBox "Spec sheet name: " & specSheet


rs.Close

    Dim oApp As Object
    
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    
    With oApp
        .Documents.Open (specSheet)
    End With
    
Exit_cmdCESpec_Click:
    Exit Sub

Err_cmdCESpec_Click:
    MsgBox Err.Description
    Resume Exit_cmdCESpec_Click
    
End Sub

Open in new window

UbiqutyDegins

ASKER

Good News! That solved this error.

The bad news, now I have a new one, lol.

I'll post a new question.

UbiqutyDegins

ASKER

Thanks for all your help!

mbizup

You're welcome! Keep that trick of commenting out the ON Error line in mind. It always helps us resolve these questions more quickly if you can let us know the line of code that is raising an error.