?
Solved

Object Required Error on VBA

Posted on 2011-09-21
24
Medium Priority
?
287 Views
Last Modified: 2012-06-22
   Dim sSQL As String
   
    sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS 500,"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS 1M,"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS 2M,"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS 5M,"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS 10M,"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS 20M"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE P2CSameTx65Table.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & ""
    sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """
    sSQL = sSQL & " ORDER BY ZONE.CITY, INTER.Class"
   
    CurrentDb.Execute sSQL, dbFailOnError


Hi,

I am getting 'Object Required' error on this line

sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """

Open in new window


my guess is some quote missing or something o.O

I tried breaking my head but couldn't find it.

Any help is appreciated!
0
Comment
Question by:Shanan212
[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
  • 13
  • 9
  • 2
24 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 36576180
you duplicated  
 
<& getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI>

try changing this part

    sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """

with

    sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = " & chr(34) &  userinput & Chr(34) & ""
 
0
 
LVL 75
ID: 36576185
The part in bold below does not look correct ... ?

sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576203
the bold part is a combination of both texts pulled from one of the tables. Is there any other way to do this?

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

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36576228

Shanan212,

have you read my comment ?
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576257
Oops! I didn't

Looking at it, I didn't duplicate. It was intentional as I was combining the same text with same again to find a possible combination

That is

    sSQL = sSQL & " And text1text2text1text2 =" text1text2 & text1text2" And text1text2 = " & chr(34) &  userinput & Chr(34) & """

userinput = text1text2

Is this wrong to do? It worked as a query-design :o
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 36576261
Maybe this:

dim XCrit = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]

sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = " & Chr(34) & XCrit & Chr(34) & "  And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36576294
ok then

change this

 
    sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """


with


    sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
   sSQL = sSQL & "  And getDestinBeyond.TCOMBI = " & chr(34) &  userinput & Chr(34) & """
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576624
   sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS 500,"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS 1M,"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS 2M,"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS 5M,"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS 10M,"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS 20M"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
    sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
   
    sSQL = sSQL & " ORDER BY getDestinBeyond.[DESTIN CITY], P2CSameTx65Table.Class"

   
    CurrentDb.Execute sSQL, dbFailOnError

This is what I have latest. The object error is gone but now I am getting too few parameters; expected 2 error.

I ran simlary query by building a quick one (just now) via query-design and it worked perfectly!

Thanks for continues help (*me very tired*)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36576656
try this, make sure your table names and fields are not mispelled

 sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
    sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576770
Nope same thing!

So this is the query I created via query-design and outputting required results like a charm!

SELECT P2CSameTx65Table.[ORIGIN CITY], P2CSameTx65Table.[ORIGIN PROVINCE], P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE], P2CSameTx65Table.TCOMBO, getDestinBeyond.TCOMBI, P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, P2CSameTx65Table.MIN, P2CSameTx65Table.LTL, P2CSameTx65Table.[500], P2CSameTx65Table.[1M], P2CSameTx65Table.[2M], P2CSameTx65Table.[5M], P2CSameTx65Table.[10M], P2CSameTx65Table.[20M]
FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]
WHERE (((P2CSameTx65Table.TCOMBO)=[getDestinBeyond]![TCOMBI] & [getDestinBeyond]![TCOMBI]) AND ((getDestinBeyond.TCOMBI)="HALIFAXNOVA SCOTIA"))
ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;

You can see that only thing changed is that

(getDestinBeyond.TCOMBI)="HALIFAXNOVA SCOTIA"

instead of which I am using variable called 'userinput'

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36576786
what are the values in field

P2CSameTx65Table.TCOMBO ?

and what is the value of "userinput", where you are getting errors
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576883
If it were to run, the result table would have

TCOMBO
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
etc

TCOMBI (from getDestinBeyond)
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA


So what my logic behind this query is, get a combination out of main table (which contains TCOMBO) where the origin and destination combination are same

aka
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576920
Error on this line:
CurrentDb.Execute sSQL, dbFailOnError

regardless of values, the error shouldn't be there eh! I even eliminated the line

 'sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"

and the error is still there

See my current screen (double screen) On left the query I created to run the same query (I posted the sql behind it above) and on the right is the function.

Maybe I have to redefine the "currentdb" part?


1.PNG
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36576943
upload your db..
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36576993
ok ok :) I figured it out. I will upload it after this one (if we can't solve it)

The problem was that this line, my original coding was wrong and this is correct

sSQL = sSQL & " P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"

but now I am getting this error; syntax one so it should be easy to solve  

see
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36577014
Progress but still syntax error in the bottom block

I changed this: so current code is

   sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![MIN]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"""
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
   
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

   
    CurrentDb.Execute sSQL, dbFailOnError
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36577035
Solved it :)

   sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO =  [getDestinBeyond].[TCOMBI] & getDestinBeyond.[TCOMBI] "
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
   
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

Thank you for breaking your head with me :)
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36577043
Thanks all!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36577048
you should have copied and paste my post at http:#a36576656 
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36577083
I did but at that time it gave a diff error since there was no

P2CSameTx65Table.CITY

There was already the column in the table called this

P2CSameTx65Table.[DESTIN CITY]

so I aborted your code :o
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36577209
so you did not read this part

"try this, make sure your table names and fields are not mispelled "

at http:#a36576656 
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36580622
I did :o

Its not mispelled. What happened is, I am running this query for 2 senarios. 1st senario, the table's column name is spelled as "CITY" and 2nd senario (which I am working) its spelled as "DESTIN CITY"

But I didn't actually go into table and look at it as I copied the query sql from previous senario and thought everything is ok. When you posted that comment, I actually checked for 'spelling mistakes' rather than the whole field being different; which I checked later.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36580657
oh, sorry i should have posted

"try this, make sure your table names and fields are correct or are not mispelled "
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36581117
Its all good. I got the program working now as I wished. The project is done for good (for now)

Again, what I did is this

Generate 17 cases x 10 columns for each city

3 senarios (3 diff forms)

each form does

1. Point(city) to Point
2. Point to Country
3. Province to Country

So the last one...say a province has 880 cities and country has 3370 cities (this is the approximate data I am working with)

This would result in a table output

880 x 3370 x 17 = nearly 5M rows x 20 colms

^ this being the max. I haven't actually run this yet as testing just begun. I doubt I will be/user will eb able to save it as access. So I am writing a manual to tell the user to use the data, then clear the output table (via button) before exiting as maximum file size in access is 2GB and access is 1M lines.


Thanks for all the help! I learned alot!
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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…
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