Solved

Adodc1.CommandType

Posted on 2002-07-10
5
343 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Window placement 17 75
Specify ip address in my connection string for sas 2 54
Adding to a VBA? 6 61
Need Nag Screen for Reboot if system up time is over 14 days 12 9
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now