Solved

Update Query with DAO  Object Required

Posted on 2015-02-04
13
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 - Microsoft MVP, Access and Data Platform) 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

732 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