By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

ADODC by date problem

P: 15
I am trying to move through records in a table using the ADODC tool. But I want to move through those records that occur on a specigic date, a date that was selected on a calandar using another form.

I was using the following code and I cannot get it to work.

Any Ideas?

Thank You in advance.


RecordSource = SELECT * FROM tblLog WHERE EventDate = #Forms!EditEntryByDate!calLog#
Dec 26 '07 #1
Share this Question
Share on Google+
6 Replies


Dököll
Expert 100+
P: 2,364
I am trying to move through records in a table using the ADODC tool. But I want to move through those records that occur on a specigic date, a date that was selected on a calandar using another form.

I was using the following code and I cannot get it to work.

Any Ideas?

Thank You in advance.


RecordSource = SELECT * FROM tblLog WHERE EventDate = #Forms!EditEntryByDate!calLog#
Hello, opie!

I got just the thing. I urge you to search through the pages under adodc1...If it means looking through specific records, I can show you how to do this in DAO or you can look it up yourself here:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim myDatabase As Database
  3. Dim myRecord As Recordset
  4. Set myDatabase = OpenDatabase("C:\DataMining\Data_Central.mdb")
  5. Set myRecord = myDatabase.OpenRecordset("SELECT * FROM YourTable WHERE UserID='" & Text1(0).Text & "'")
  6.  
  7.  
You can turn UserID into the date field to search for specific date.

Please look for that post for added support. Adodc1 helps you page through the data... I thought to have read ADO is not that far off, you also fetch results here or Google on it; perhaps something of a difference between the two is posted.

Good luck:-)

Dököll
Dec 26 '07 #2

P: 15
I have looked through the post several times. I cannot get this to work after about a week of working at it off and on.

It work fine when I insert the the exact date as the recordSource as shown below.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblLog WHERE EventDate = #12/7/2007#
But when I try to refer it to a form that is still in memory (I have not unloaded the form) it gives me a syntax error. Now I am using MS Access Syntax so maybe that is the problem, but in this instance I figured it would be the same and I am unable to find the proper syntax.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblLog WHERE EventDate = #forms!EditEntryByDate!calLog#
Dec 27 '07 #3

daniel aristidou
100+
P: 491
If your getting a data type error or the wrong records.
Try using
The CDate This converts the the record feild and criteria into the same date format.
Expand|Select|Wrap|Line Numbers
  1. WHERE Cdate(EventDate) = cdate(forms!EditEntryByDate!calLog)
Ps im not too sure about the " ' " that are needed.
i usually just use trial error.
When i first got this problem took me ages to find solution :)

Hope this helps you......... if not it may others.

Holiday Greetings
Daniel
Dec 27 '07 #4

Dököll
Expert 100+
P: 2,364
I have looked through the post several times. I cannot get this to work after about a week of working at it off and on.

It work fine when I insert the the exact date as the recordSource as shown below.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblLog WHERE EventDate = #12/7/2007#
But when I try to refer it to a form that is still in memory (I have not unloaded the form) it gives me a syntax error. Now I am using MS Access Syntax so maybe that is the problem, but in this instance I figured it would be the same and I am unable to find the proper syntax.

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblLog WHERE EventDate = #forms!EditEntryByDate!calLog#
Bummer, let's try to muscle through it then togheter, how's that for a deal?

Are you saying you did not find the post mentioned?

If you click on my handle and go through, has to be further in the pages, I'll race ya:-)

I am having look by the way because I must fire my notebook to get a real look...

Also there are bits of information you can use from others that have posted.
I'll attempt to type it up an try it.

In a bit!
Dec 28 '07 #5

Dököll
Expert 100+
P: 2,364
Beat ya:-)

Alright, this is one of two links, my apologies I thought I had it all in one post...

I must fetch another old post:-)

http://www.thescripts.com/forum/thre...65-adodc1.html


Got it. I decided to just strip it for spare parts, it is a rather large post...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()  
  2.  
  3.         Dim my_database As Database 
  4.  
  5.         Set my_database = OpenDatabase("C:\Yoda1.mdb")
  6.  
  7.         my_database.Execute "insert into Yoda1.Yoda(UserID, Name, Address, Phone, Email) Values('" & Text1.Text & "','" & Text2.Text & "' , '" & Text3.Text & "' , '" & Text4.Text & "','" & Text5.Text & "')"
  8.         my_database.Close ' 
  9.  
  10. Text1.Text="" 
  11. Text2.Text="" 
  12. Text3.Text="" 
  13. Text4.Text="" 
  14. Text5.Text="" 
  15.  
  16. End Sub
  17.  
You'll need to reference DAO 3.6, I think.

So if you have a date field in your access database, you should be able to load into VB using this code.

Try it, and good luck:-)
Dec 28 '07 #6

Dököll
Expert 100+
P: 2,364
Beat ya:-)

Alright, this is one of two links, my apologies I thought I had it all in one post...

I must fetch another old post:-)

http://www.thescripts.com/forum/thre...65-adodc1.html


Got it. I decided to just strip it for spare parts, it is a rather large post...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()  
  2.  
  3.         Dim my_database As Database 
  4.  
  5.         Set my_database = OpenDatabase("C:\Yoda1.mdb")
  6.  
  7.         my_database.Execute "insert into Yoda1.Yoda(UserID, Name, Address, Phone, Email) Values('" & Text1.Text & "','" & Text2.Text & "' , '" & Text3.Text & "' , '" & Text4.Text & "','" & Text5.Text & "')"
  8.         my_database.Close ' 
  9.  
  10. Text1.Text="" 
  11. Text2.Text="" 
  12. Text3.Text="" 
  13. Text4.Text="" 
  14. Text5.Text="" 
  15.  
  16. End Sub
  17.  
You'll need to reference DAO 3.6, I think.

So if you have a date field in your access database, you should be able to load into VB using this code.

Try it, and good luck:-)
Sorry wrong code, you need SELECT FROM, I have been doing this lately, fogetting stuff:-)

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'this is searching for existing data in your access
  3.  
  4. Private Sub Seek_Click() 'this is your button
  5. Dim my_database As Database           'dimension database as database 
  6. Dim my_record As Recordset              'dimension recordset as recordset
  7.  
  8. Set my_database = OpenDatabase("C:\DataMining\Yoda.mdb")  
  9.  
  10. 'You can change above path/folder name to location of your own access 
  11. 'database
  12.  
  13. 'Going forward, above function will open your database 
  14. 'Make sure your databse is closed
  15.  
  16. Set my_record = my_database.OpenRecordset("select * from Yodamania where UserID like '" & Text1.Text & "'")    ' this is used to search by user id
  17.  
  18.    Do While Not my_record.EOF  
  19.  
  20. 'this function will keep searching for fields matching each textbox
  21.  
  22.         Text1.Text = my_record.Fields("UserID")
  23.         Text2.Text = my_record.Fields("Name")
  24.         Text3.Text = my_record.Fields("Address")
  25.         Text4.Text = my_record.Fields("Phone")
  26.         Text5.Text = my_record.Fields("Email")
  27.  
  28.  my_record.MoveNext 
  29.    Loop
  30.    my_database.Close
  31. End Sub
  32.  
  33.  
Remember to use DAO, you will get an error without a reference; I am sure you know that, just saying it before I forget:-)

Also, rest assure your adodc1 control will work just fine with DAO...

In a bit!
Dec 28 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.