?
Solved

Update Query with DAO  Object Required

Posted on 2015-02-04
13
Medium Priority
?
221 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
Industry Leaders: 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 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…
Suggested Courses

752 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