Extracting Date Fields from a ADODB RecordSet into unbound TextBox

  • Thread starter Thread starter Ian Millward
  • Start date Start date
I

Ian Millward

I have a form with a ListBox which shows a couple of fields from a table.
When the user scrolls down the list and stops at a record, the remaining
fields of the table are shown in several unbound TextBoxes. I do this by
connecting them to an ADO RecordSet at run time, coded in the ListBox's
AfterUpdate event. This works fine except for DateTime fields. I cannot get
them to work properly. I have tried putting # around the field name and
putting it in Str, CStr, DateValue and a host of other functions and I have
formatted the TextBox's Format property to ShortDate , all to no avail.



This is accomplished simply and elegantly in Delphi with the DateToStr
function. Is there something similar I am missing in this benighted
Development Environment.



Can someone give me a steer in the right direction.

Ian Millward
Edinburgh
 
Jonathan

<< if I understand correctly, you want to put a
value from a recordset field, that happens to be a
datetime data type, into a textbox. If this is the case
you don't have to do anything special.

txtMyDateTime = rsData.field("myDateTime")>>

I thought I'd done that, perhaps you would be kind enough to have a look at
my syntax and see if the problem lies there

Here is the snippet of code containing the bits which don't work

Private Sub List21_AfterUpdate()

StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\MaysDB\Investigations\investigations.mdb;Persist Security
Info=False"
Set cnn = New ADODB.Connection
cnn.Open StrConn
Set rst = New ADODB.Recordset

StrSQL = "SELECT * FROM Main "
StrSQL = StrSQL & "WHERE Main.PI=" & Me.List21
rst.Open StrSQL, cnn, adStatic, adLockReadOnly, adCmdText

Me.Text23 = rst("InvestigationID") 'These two txt fields work fine
Me.Text25 = rst("HRSC")
Me.Text27 = rst("DateCleared")
Me.Text29 = rst("DateNotified") 'These two date fields are the offending
items and throw an error message: "Item cannot be found in the
collection coresponding
to the requested name or ordinal"
End Sub
 
Sorry to state the obvious, but you only get that error
when the column is not in the recordset...

What is the 'main' object, and are you sure it contains
these fields ?


Damien
 
Back
Top