Solved

Update Query with DAO  Object Required

Posted on 2015-02-04
13
210 Views
Last Modified: 2016-02-10
Trying to write my first query using DAO.  I am getting "Object Required" error on the following Update Query.

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strDb As String
   
   
    strDb = "C:\Users\Austin\Documents\Access Development\RateTool.accdb"
   
    On Error GoTo Err_Handler
   
    strSQL = "UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
    "WHERE (((Left([Forms]![frmRateTool]![ZipPostalOrigin],3)) " & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix]));"
   
    dbs.Execute strSQL, dbFailOnError
   
    Set db = OpenDatabase(strDb)
   
    Set qdf = db.CreateQueryDef("UpdateIndex", strSQL)
    qdf.Execute
ExitHere:
    Set db = Nothing
    Exit Sub
Err_Handler:
    If Err.Number = 3012 Then
        MsgBox "Query with this name already exists."
    Else
        MsgBox Err.Description
    End If
    Resume ExitHere
End Sub
0
Comment
Question by:ExpressMan1
  • 8
  • 4
13 Comments
 
LVL 75
ID: 40589951
dbs.Execute strSQL, dbFailOnError

You have not defined dbs anywhere
0
 
LVL 75
ID: 40589952
Maybe

Dim dbs as DAO.Database
Set dbs = CurrentDB

?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40589954
are this two tables " tblFedExCanadianIndex, tblIndex " have a common field?

assuming they are related though  a field called xID

 strSQL = "UPDATE  tblIndex " _
    & " inner join tblFedExCanadianIndex On tblIndex.xID = tblFedExCanadianIndex" _
    & " Set tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index]" _
    & " Where [tblFedExCanadianIndex].[StartPrefix] >= '" & Left([Forms]![frmRateTool]![ZipPostalOrigin], 3) & "' And" _
    & " [tblFedExCanadianIndex].[EndPrefix]<=  '" & Left([Forms]![frmRateTool]![ZipPostalOrigin], 3) & "'"
0
 

Author Comment

by:ExpressMan1
ID: 40590004
Tables do not have a common field.

Tried
Set dbs = CurrentDB
 Now error is Too few parameters. Expected 1
0
 
LVL 75
ID: 40590033
Make this change (in bold)


    strSQL = "UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
    "WHERE (((Left(EVAL(["Forms]![frmRateTool]![ZipPostalOrigin]"),3)) " & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix]));"
0
 
LVL 75
ID: 40590035
Wrap the Forms Reference in the Eval() function.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:ExpressMan1
ID: 40590052
Syntax error.  Tried

    "UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
    "WHERE (((Left(EVAL(["Forms]![frmRateTool]![ZipPostalOrigin]"),3)) " & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix]));"

Code turns red.
0
 
LVL 75
ID: 40590060
OH ... yeah ... the "" in the EVAL ... standby
0
 
LVL 75
ID: 40590061
Dim sFrm as String
sFrm = "Forms]![frmRateTool]![ZipPostalOrigin]"

Then

    "UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
    "WHERE (((Left(EVAL(sFrm),3)) " & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix]));"
0
 

Author Comment

by:ExpressMan1
ID: 40590100
Syntax error in query expression.

sFrm = "[Forms]![frmRateTool]![ZipPostalOrigin]"   ' I put missing [ before Forms

 "UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
    "WHERE (((Left(EVAL(sFrm),3)) " & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix]));"
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 40590118
Yes ... certainly need the bracket ..
Meanwhile ...humm - let's try this - grab the value from the Form ahead of time.

Dim sFrm as String
sFrm = Left(Forms]![frmRateTool]![ZipPostalOrigin],3)


"UPDATE tblFedExCanadianIndex, tblIndex " & _
    "SET tblIndex.IndexOrigin = [tblFedExCanadianIndex].[Index] " & _
   "WHERE " & Chr(34) & sFrm & Chr(34) & _
    "Between [tblFedExCanadianIndex].[StartPrefix] And [tblFedExCanadianIndex].[EndPrefix];"

Note Chr(34) is a double quote.
0
 

Author Closing Comment

by:ExpressMan1
ID: 40590130
Works!

I was trying to get the value from the form on earlier attempts but wasn't sure if possible or was standard practice.
Can't seem to find solutions like this in books.  Thank You very much.
0
 
LVL 75
ID: 40590183
You are welcome ...
btw ... the reason for "Too few parameters. Expected 1"  is ....

Courtsey of EE's harfang (with additional comments by DatabaseMX):

When your run a query that references a text box(s) on a Form, you normally do so from the Access

interface, or using DoCmd.
DoCmd "sees" the forms, so that your criteria works. For example:

qryYourQueryName  >> SQL

UPDATE tblX SET tblX.FIELD1 = "jammer"
WHERE tblX.[ID]=[Enter ID Number];

If you run this code, you will get a normal prompt for ID:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_CustomerConcerns"
    DoCmd.SetWarnings True

But ... if you run this code:

CurrentDb.Execute "qryYourQueryName", dbFailOnError

You will get the 'Too Few Parameters' (or similar) error!

This is because CurrentDb.Execute passes the query directly to JET in an instance that does *not* "see"

your Forms, so that you get "missing parameters", aka "Too few parameters".

However, VBA does "see" your Forms, so that you can (and need to) evaluate the parameters in VBA before

running the query using the Execute Method.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now