Solved

Update Query with DAO  Object Required

Posted on 2015-02-04
13
213 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 - 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

786 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