I Am Using SQL Alot In A Project, But For Some Reason I Feel I'm Doing Way More Work Then I Have To. Curently, I Am Storing Variables By Checking Each Field, Then Storing Accordingly.
[code]
Do Until rs.EOF
EmpSum.CurRec = EmpSum.CurRec + 1
CmdEdit(EmpSum.CurRec).Visible = True
For Each fld In rs.Fields
If fld.Name = "11a" Then
EmpSum.SumHours(1, 1, 1) = fld.Value
ElseIf fld.Name = "12a" Then
EmpSum.SumHours(1, 2, 1) = fld.Value
ElseIf fld.Name = "13a" Then
EmpSum.SumHours(1, 3, 1) = fld.Value
ElseIf fld.Name = "14a" Then
EmpSum.SumHours(1, 4, 1) = fld.Value
End If
Next
Loop
[/code]
There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.
Any Ideas Would Be Appreciated.
Comments
:
: [code]
: Do Until rs.EOF
: EmpSum.CurRec = EmpSum.CurRec + 1
: CmdEdit(EmpSum.CurRec).Visible = True
: For Each fld In rs.Fields
: If fld.Name = "11a" Then
: EmpSum.SumHours(1, 1, 1) = fld.Value
: ElseIf fld.Name = "12a" Then
: EmpSum.SumHours(1, 2, 1) = fld.Value
: ElseIf fld.Name = "13a" Then
: EmpSum.SumHours(1, 3, 1) = fld.Value
: ElseIf fld.Name = "14a" Then
: EmpSum.SumHours(1, 4, 1) = fld.Value
: End If
: Next
: Loop
: [/code]
:
: There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.
:
: Any Ideas Would Be Appreciated.
:
Have you considered referencing each field directly? ie:
[code]
Do Until rs.EOF
EmpSum.CurRec = EmpSum.CurRec + 1
CmdEdit(EmpSum.CurRec).Visible = True
EmpSum.SumHours(1, 1, 1) = rs!11a
EmpSum.SumHours(1, 2, 1) = rs!12a
EmpSum.SumHours(1, 3, 1) = rs!13a
EmpSum.SumHours(1, 4, 1) = rs!14a
rs.MoveNext
Loop
[/code]
: :
: : [code]
: : Do Until rs.EOF
: : EmpSum.CurRec = EmpSum.CurRec + 1
: : CmdEdit(EmpSum.CurRec).Visible = True
: : For Each fld In rs.Fields
: : If fld.Name = "11a" Then
: : EmpSum.SumHours(1, 1, 1) = fld.Value
: : ElseIf fld.Name = "12a" Then
: : EmpSum.SumHours(1, 2, 1) = fld.Value
: : ElseIf fld.Name = "13a" Then
: : EmpSum.SumHours(1, 3, 1) = fld.Value
: : ElseIf fld.Name = "14a" Then
: : EmpSum.SumHours(1, 4, 1) = fld.Value
: : End If
: : Next
: : Loop
: : [/code]
: :
: : There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.
: :
: : Any Ideas Would Be Appreciated.
: :
:
:
: Have you considered referencing each field directly? ie:
:
: [code]
: Do Until rs.EOF
: EmpSum.CurRec = EmpSum.CurRec + 1
: CmdEdit(EmpSum.CurRec).Visible = True
:
: EmpSum.SumHours(1, 1, 1) = rs!11a
: EmpSum.SumHours(1, 2, 1) = rs!12a
: EmpSum.SumHours(1, 3, 1) = rs!13a
: EmpSum.SumHours(1, 4, 1) = rs!14a
: rs.MoveNext
: Loop
: [/code]
:
:
Yes I Have, I Just Didn't Know How To Go About It In VB. I've Done It In PHP, So That's Why I Was Wondering If I Could Do It In VB. Thanks Alot.
[code]
Compile Error:
Expected: End Of Statement
[/code]
Is There Any Way To Get Around This?
rs!field - good performance but not for fieldnames with a space.
rs("field name") - slow but fine with any valid fieldname
rs(1), rs(2), etc. - best perf. and fieldname irrelevant but poor for code readability
In Access you can also use: rs![field name] but that may not apply
: Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This
: [code]
: Compile Error:
:
: Expected: End Of Statement
: [/code]
:
: Is There Any Way To Get Around This?
:
:
: rs!field - good performance but not for fieldnames with a space.
: rs("field name") - slow but fine with any valid fieldname
: rs(1), rs(2), etc. - best perf. and fieldname irrelevant but poor for code readability
:
: In Access you can also use: rs![field name] but that may not apply
:
:
: : Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This
: : [code]
: : Compile Error:
: :
: : Expected: End Of Statement
: : [/code]
: :
: : Is There Any Way To Get Around This?
rs.Fields("FIELDNAME").Value & vbnullstring
This is the safest way to refer to recordset fields in my experience. The vbnullstring is to prevent "Invalid Use of NULL" errors when assigning a database value to a non-Variant variable.
I have found the ! shortcut also has other odd behavior in some cases.
[size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
[code]
$ select * from users where clue > 0
no rows returned
[/code]