asked on

Access 2010 VBA to put one field from a query in a variable

In Access 2010 I need to be able to click a command button that will run a query that returns a small two field recordset. Then put the second 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.(This part I have working)

I am getting the error Too few parameters. Expected 1.

Any help is GREATLY appreciated

Private Sub cmdCESpec_Click()

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

s = "SELECT tblParts.PartNum, tblParts.CE_SpecSheet FROM tblParts WHERE (((tblParts.PartNum)=[Forms]![frmSpecSheet]![cboPartNum]));" 'Chooses the Correct Spec Sheet
Set db = CurrentDb
Set rs = db.OpenRecordset(s)

specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field

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

Anthony Berenguel

try changing line 23 to this

.Documents.Open (specSheet)

Open in new window

mbizup

Try this if PartNum is numeric:

s = "SELECT tblParts.PartNum, tblParts.CE_SpecSheet FROM tblParts WHERE (((tblParts.PartNum)= " & [Forms]![frmSpecSheet]![cboPartNum] & "));" 'Chooses the Correct Spec Sheet

Open in new window

If it is Text, try this:

s = "SELECT tblParts.PartNum, tblParts.CE_SpecSheet FROM tblParts WHERE (((tblParts.PartNum)= '" & [Forms]![frmSpecSheet]![cboPartNum] & "'));" 'Chooses the Correct Spec Sheet

Open in new window

UbiqutyDegins

ASKER

I have tried the methods suggested here and I am still getting the same results.

PartNum is a Text field.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UbiqutyDegins

ASKER

@mbizup that makes sences and seems to have gotten past the origional error. I now have a new error. I am still new to VBA and programming in general. I appreciate the help.

The new error is "Object variable or With block variable not set"

My code now looks like this:

Private 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.PartNum, p.CE_SpecSheet FROM tblParts p WHERE p.PartNum = '" & [Forms]![frmSpecSheet]![cboPartNum] & "'"
Set rs = db.OpenRecordset(s)

specSheet = rs.Fields("CE_SpecSheet")  'Chooses the Spec Sheet Field


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

Since we seem to have resolved the original recordset issue, can you close this question and post a new one to address the object variable issue and get other Experts looking at it (I assume it is in the MS Word automation)?

When you post the new question, be sure to mention exactly which line of code is causing the error (whichever line is highlighted when you click 'debug' from the error message popup).

mbizup

Before you do that, however try adding a message box as a quick check to ensure that the recordset is returning the correct name for the specsheet:

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

Open in new window

Does the message box correctly show the expected Spec Sheet name?