recordset fields

[geoscan]

Hi all,

I am running Office 2003 Pro with Windows7-64. I have the following code fragment:

Dim rst As DAO.Recordset
Set rst = CurrentDb().OpenRecordset("SELECT * FROM tblOffices")
If rst.RecordCount <= 0 Then Exit Function
rst.MoveLast: rst.MoveFirst
Do While Not rst.EOF And Not rst.BOF
Debug.Print rst!officename
rst.MoveNext
Loop

My question is why the field officename in the debug line is not properly formatted (i.e. OfficeName) as it is named in the domain table tblOffices?. BTW, the records are listed correctly. Any help is greatly appriciated.

 

[MajP]

The key of a collection in vb is not case sensitive.

 

[geoscan]

Thansk MajP for your response. It used to be when i type rst!officename and hit the return key, the rst!officename turns into rst!OfficeName automatically. I did check the spelling etc. Maybe there is something turned off??

 

[MajP]

I do not have Access here to test, but I am not sure if you are correct. If it is property, intellisense will recognize it and fix it.
rst.boF would become rst.BOF

But intellisense does not recognize indexes in a collection. If you type
rst!officenom
I do not think it even throws and error until run time.

 

[TheAceMan1]

How are ya geoscan . . .

You are correct in this formatting issue. However Ive had times when the same problem poppedup ... doesn't format proper. Like you I've checked my spelling (this will happen if spelling is in error) but it stays the same. In the past I've simply formatted proper myself.

I just tested with an SQL statement as the recordset source and it works just fine. Here's the code I used:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT ClientID, [red][b]ln[/b][/red]ame, [red][b]fn[/b][/red]ame " & _
         "FROM tblClients;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   Do
      Debug.Print rst![green][b]LN[/b][/green]ame & " " & rst![green][b]FN[/b][/green]ame
      rst.MoveNext
   Loop Until rst.EOF
   
   Set rst = Nothing
   Set db = Nothing[/blue]

Note the lowercase characters in red in the SQL. I purposely entered them this way. In the table they are uppercase!

[tt]When I type: Debug.Print rst![red]ln[/red]ame & " " & rst![red]fn[/red]ame
I get: Debug.Print rst![green]LN[/green]ame & " " & rst![green]FN[/green]ame ... when I hit enter.[/tt]

This proves it does work and if I try to give a good guess ... I'd say it works for me a good 99% of the time.

BTW ... the code is in a standard module! So its isolated from possibly picking up the formatting from a form.

I don't have the answer but this is as good a time as any to try and pin this down. I'm currently researching ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Geoscan,
Although it is worth understanding when/why it works or not, you do not have to worry about case for your collections. For example ,if using dot notation it will definitely never autocorrect but the following will all work
rst.fields("officename")
rst.fields("OFFICENAME")
rst.fields("oFfIcNaMe")
or any other combination.

However, you do need to keep an eye out for when the intellisense stops working. If this happens there is a problem with your code that will not compile. You should find that before continuing.

 

[geoscan]

Thanks all for your input. i will just go on with it for now and will let you know if i have any problems in the future. Thanks a 10^6

 

[VB6AccessSQL]

Hi All,

is there someone who could help me as in basically.
codings for file maintenance (ADD,EDIT,DELETE,SAVE,SEARCH) using VB6 with Access and SQL format?

can i code each per command button?

Please check my below code;i have an error in {Run-time error '3265' item cannot be found in the collection corresponding to the requested name or ordinal

Option Explicit
Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim cmd As ADODB.Command


Private Sub Command1_Click()
Dim catNewDB As ADOX.Catalog
Dim sDBPath As String
Dim sCreateDBString As String
Dim meSQL As String
Dim sConnectString As String
Dim mymsg As String



If Dir("C:\My Lesson", vbDirectory) <> "" Then
Else
Call MkDir("C:\My Lesson")
End If

sDBPath = "C:\My Lesson\mydb.MDB"

If Dir(sDBPath, vbNormal) <> "" Then
Kill sDBPath
End If

sCreateDBString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & _
";Jet OLEDB:Engine Type=5;"

Set catNewDB = New ADOX.Catalog
catNewDB.Create sCreateDBString
Set catNewDB = Nothing

Set oCN = New ADODB.Connection
'Set oCN = Nothing

sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath

'gani to structure
'#1 open connection
'#2 ur transaction/procedure
'#optional close ur connection

oCN.Open sConnectString

meSQL = "CREATE TABLE tbFile1 (FileID1 Long Identity(1,1)"
meSQL = meSQL & ",FIELD1 Text(25)"
meSQL = meSQL & ",FIELD2 Double"
meSQL = meSQL & ",FIELD3 Integer"
meSQL = meSQL & ",FIELD4 Memo"
meSQL = meSQL & ",FIELD5 Date"
meSQL = meSQL & ")"
oCN.Execute meSQL

meSQL = "INSERT INTO tbFile1(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5)"
meSQL = meSQL & "VALUES ("
meSQL = meSQL & "'mae'"
meSQL = meSQL & "," & 1225.01
meSQL = meSQL & "," & 1225
meSQL = meSQL & "," & "'this is a sample of my first lesson creating database and connection'"
meSQL = meSQL & "," & "# 5 - 25 - 2010 #"
meSQL = meSQL & ")"
oCN.Execute meSQL

meSQL = "INSERT INTO tbFile1(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5)"
meSQL = meSQL & "VALUES ("
meSQL = meSQL & "'ann'"
meSQL = meSQL & "," & 925.03
meSQL = meSQL & "," & 925
meSQL = meSQL & "," & "'this is my second lesson populating database database in table'"
meSQL = meSQL & "," & "# 9 - 25 - 2003 #"
meSQL = meSQL & ")"
oCN.Execute meSQL

Set oRS = New ADODB.Recordset
meSQL = "SELECT * FROM tbFile1"

oRS.Open meSQL, oCN, adOpenForwardOnly, adLockReadOnly, -1

If Not oRS.EOF Then
Do Until oRS.EOF
mymsg = "FIELD1=" & Trim(oRS.Fields("FIELD1").Value & "")
mymsg = mymsg & vbCrLf & "FIELD2 =" & Val(Trim(oRS.Fields("FIELD2").Value & ""))
mymsg = mymsg & vbCrLf & "FIELD3 = " & Val(Trim(oRS.Fields("FIELD3").Value & ""))
mymsg = mymsg & vbCrLf & "FIELD4 = " & Trim(oRS.Fields("FIELD4").Value & "")
mymsg = mymsg & vbCrLf & "FIELD5 =" & Val(Trim(oRS.Fields("# FIELD5 #").Value & ""))
'error in highlighted yello for field5 which happen to be for date format. please help
MsgBox mymsg
oRS.MoveNext
Loop
End If
oRS.Close

End Sub

Thank you.

 

[JoeAtWork]

VB6AccessSQL - start your own thread to ask your question rather than posting in somebody else's thread.

 

[VB6AccessSQL]

??? meaning thread is per question? i thought if concerns are related, it may?!

thanks

 

[TheAceMan1]

VB6AccessSQL . . .

If you start your own thread your problem is exposed to the entire forum ... rather than being hidden inside another as this is.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[VB6AccessSQL]

ok thanks... i already made another thread...
thanks! hope you could help me also...
thanks!