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.

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

There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.

Any Ideas Would Be Appreciated.

: 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.
:
:

: 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
: 

:
: 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:

 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
 

: : 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.
: :
: :

: : 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
: : 

: :
: : 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:
:
:

:  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
:  

:
:

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.

Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This

Compile Error:

Expected: End Of Statement

Is There Any Way To Get Around This?

There's several ways to reference the field and each has tradeoffs.

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
:

: Compile Error:
: 
: Expected: End Of Statement
: 

:
: Is There Any Way To Get Around This?
:

: There's several ways to reference the field and each has tradeoffs.
:
: 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
: :

: : Compile Error:
: : 
: : Expected: End Of Statement
: : 

: :
: : 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.


infidel

$ select * from users where clue > 0
no rows returned