Solved

Adodc1.CommandType

Posted on 2002-07-10
5
359 Views
Last Modified: 2010-05-02
Hello.

I am new to Visual Basic and I am trying to write a simple program.  I have an Access Database that I have set up as an ODBC connection usign the ADODC form.  When I draw the text boxes I see the data and I can scroll through it using the ADODC buttons.

However, I wanted to set something up so it could ask a user what date they needed and only display that data.  Below is my code:Private Sub cmdgetdata_Click()
Dim dtDate As Date
Dim sSQL As String


dtDate = InputBox("Enter Date Needed")
sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date = '" & dtDate & "'"

Adodc1.CommandType = adCmdText
Adodc1.RecordSource = sSQL
Adodc1.Refresh

End Sub

But when it runs it gets to the Adodc1.CommandType = adCMdTxt and errors - saying missing an object.

I can't figure this out although I am sure it is pretty easy.  

Thanks.

0
Comment
Question by:nnaxor
[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
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 7144931
you've spelled the command type two different ways:
adCmdText
adCMdTxt

That might be the source of your error.
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7145969
try this:

sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date = '" & format(dtDate,"dd-mmm-yy") & "'"

Hope this helps

If still it gives an error check at what line you get the error!!

-priya
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7146042
if the SQL is your problem, I'd recommend:
sSQL = sSQL & " WHERE Date = #" & dtDate & "#"
0
 
LVL 2

Accepted Solution

by:
priya_pbk earned 50 total points
ID: 7146072
aikimark is right, use "#" in access.

Also small change to sSql statement(use >=) like this

sSQL = "Select * from MainData"

sSQL = sSQL & " WHERE Date >= #" & format(dtDate,"dd-mmm-yy") & "#"

-----------------------------------
or use a between clause. coz using equal to retreive a date, does'nt return any records. this way

sSQL = sSQL & " WHERE Date between #" & format(dtDate,"dd-mmm-yy") & "# and #" & format(dtdate,"dd-mmm-yy") & "#"


Also I would suggest you to use dtpicker or a Monthview control so that there would be no confusion as to which part of the date is month part and which is day part.

I tried out yr code, at least did'nt give me error at the line you said!

-priya

0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7146074
sorry this way..

sSQL = sSQL & " WHERE Date between #" & format(dtDate,"dd-mmm-yy") & "# and #" & format(dtdate+1,"dd-mmm-yy") & "#"


0

Featured Post

Technology Partners: 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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

726 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