Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Query with DAO  Object Required

Posted on 2015-02-04
13
Medium Priority
?
223 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 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