Results 1 to 15 of 15
  1. #1
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61

    How to reference a fieldname with a variable in DAO's rs!FieldName syntax

    I pass a fieldname argument (SourceFldName) to this sub. Let's say the field name is [FirstName]

    Sub TESTING(TblName As String, SourceFldName As String, TargetFldName As String)
    Dim rs As DAO.Recordset, MySQL As String, PString As String, MyBool As Boolean
    On Error GoTo Bottom
    MySQL = "SELECT " & SourceFldName & " FROM " & TblName & ";"
    Set rs = DBEngine(0)(0).OpenRecordset(MySQL)
    rs.MoveFirst
    Do While Not rs.EOF
    Debug.Print rs!FirstName (THIS IS WHAT I WANT - HOW CAN I USE THE ARGUMENT VARIABLE???)
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing


    Bottom:
    MsgBox "end of the line"
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    rs(SourceFldName)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by pbaldy View Post
    Try

    rs(SourceFldName)
    xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

    Thank-you. For updating a field in a DAO recordset, I am not having too much luck here. I did utilize your rs(variablenamepointing2Afieldname) syntax...
    rs.Edit
    Debug.Print rs(SourceFldName)
    rs(TargetFldName) = "x" ==> attempting to set the value of [FlagField]
    rs.Update

    Above lines did not write "x" values to [MyTable].[FlagField] Darn! Am I s'posed 2B using Set statement?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Just tested and it worked as expected. Does this give the expected name?

    Debug.Print TargetFldName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Yes. That worked. It gave the expected name. But I'm unsuccessful in writing an "x" to that Text-type field. Hmmm???

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the full code or a sample db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    try: rs.fields(targetfldname)="x"

  8. #8
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    I can't believe it! That was the very next thing I was gonna ask ya. I'll try now. Letcha know.

    Nope. Didn't work. Something else is in the way. something more basic. Here's a snapshot of the code...

    Sub FindAndMarkRecordsWithPunctuation(TblName As String, SourceFldName As String, TargetFldName As String)
    Dim rs As DAO.Recordset, MySQL As String, PString As String, MyBool As Boolean
    On Error GoTo Bottom
    MySQL = "SELECT " & SourceFldName & " FROM " & TblName & ";"
    Set rs = DBEngine(0)(0).OpenRecordset(MySQL)
    rs.MoveFirst
    Do While Not rs.EOF
    PString = rs(SourceFldName)
    MyBool = DoesTextHavePunctuation(PString)
    If MyBool = True Then
    rs.Edit
    Debug.Print rs(SourceFldName)
    Debug.Print SourceFldName
    Debug.Print TargetFldName
    'rs(TargetFldName) = "x"
    rs.Fields(TargetFldName) = "x"
    rs.Update
    End If
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Bottom:
    MsgBox "end of the line"
    End Sub

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In my test I did not use the ".Fields" portion and it worked fine, as expected (it should not be required). Anyhow, glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by pbaldy View Post
    In my test I did not use the ".Fields" portion and it worked fine, as expected (it should not be required). Anyhow, glad you got it working.
    Oops. I sure gave you the wrong impression. I still can't get the fields written. I opened the table in table-view & manually placed "x" values in there just 2C if I was a babbling idiot. That went without a hitch. I wrote an update query 2C if I could write "x" values in there that way. No problem. Am at a loss. COULD I BE DEALING WITH A NON-UPDATABLE RECORDSET?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are the source and target field names the same? If not, the target field name is not in the recordset. Is DoesTextHavePunctuation returning True? I assume you can't post the db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by pbaldy View Post
    Are the source and target field names the same? If not, the target field name is not in the recordset. Is DoesTextHavePunctuation returning True? I assume you can't post the db?
    Damn! That's gotta be it. I'll try it. I feel good about this one. I probably should have used the table rather than the SQL.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I just noticed your "error handling" would ignore the error you'd get if it's the field names. I'd use proper error handling:

    http://www.baldyweb.com/ErrorTrap.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    I was really throwing this thing together quickly. Thank-you for helping me arrive at a solution. All's writing now. I'll run 'n snatch that error handler & pop it in there. When you don't know as much as you should, the error handler is your ONLY reliable source of info when crap isn't working.

    Thx again frriend.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, glad we got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Variable within form control reference
    By Tyork in forum Programming
    Replies: 2
    Last Post: 10-13-2010, 09:55 AM
  2. Should I have an "Or" in fieldname? (shudder)
    By trb5016 in forum Database Design
    Replies: 6
    Last Post: 06-29-2010, 10:33 AM
  3. Replies: 2
    Last Post: 05-09-2010, 04:10 AM
  4. Syntax-reference to pages
    By AmyHill in forum Access
    Replies: 5
    Last Post: 08-27-2009, 10:04 AM
  5. Replies: 0
    Last Post: 08-08-2008, 08:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums