Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    61

    Unanswered: Variables for field name is this possible?

    Ok I am updating many fields for many users. Here is a Dlookup I use

    vPhone = DLookup("[C Home #]", "MembershipUpdate", "[ID] = " & lngRecordNumber)

    Is it possible to change the value of [C Home #]? such as doing

    vPhone = DLookup("[" & FieldName & "]", "MembershipUpdate", "[ID] = " & lngRecordNumber)

    I've tried this but it does not work. I just posted to give an example of what I'm trying to do.

    Thanks again.

    Mythos

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Each part of the DLookup function is a String, so using variables should work.

    Dim strField As String, strTable As String, strSQL As String, vPhone As Variant

    strField = "[C Home #]"
    strTable = "MembershipUpdate"
    strSQL = "[ID] = " & lngRecordNumber

    vPhone = DLookup(strField, strTable, strSQL)

    Perhaps it is the value of FieldName that is causing the error - I'm suprised it let you use C Home # as a field name. It's generally not a good idea to include spaces and non alphanumeric characters in field names.

    tc

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Dxxxx() functions are wonderfully handy...
    ...but notoriously slow.

    you should NOT be running Dxxx() to update many fields - it is simply wrong.

    if this is many-fields/many-records filled from Dxxx(): throw the whole thing in the bin and start again - it is far worse than wrong.

    assuming this is many-fields/one-record, consider grabbling a one-record-recordset from the db and using code to place the field-data. radically less db (and LAN) activity, and after the single-record-recordset is received from the db, all the action takes place locally on the client.

    in DAO-speak (ADO equivalent exists), and assuming recs is your one-record-recordset and your formfields have the same name as the tablefields:
    Code:
    For Each rfld In recs.Fields
       Me(rfld.Name) = rfld
    Next
    this is soooooooooo much faster than messing with a dozen Dxxxx()

    izy

    LATER: I notice that you might not be trying to fill a form but perhaps trying to UPDATE a table: Dxxx() is still a horrible way to go! Dxxx() is fine to lookup a single value once... you really should NOT have repeated calls to domain aggregate functions
    Last edited by izyrider; 07-03-06 at 15:10.
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2005
    Posts
    61

    Thank you.

    Thanks tcase it's working now.
    and thank you izyrider. I'm not farmiliar with what you provided me but I will look it up. If it's faster and better to do it that way then that's what I should do. Just right now I don't understand what your code is saying exactly becuase I've never used it. So I will do a little reading on ADO to better understand.


    Thanks again.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Mythos - you are welcome.

    Izzy - you are absolutely right as well. I was focussing on the trees and missed the forest, so to speak.

    Mythos, here's a snipit that will get you going.

    1) Make sure DAO is referenced (some day I'll learn ADO well enough to think up code on the fly). Do by opening a module window, select Tools > References, scroll down to Microsoft DAO 3.6 Object Library (or the latest version shown), check it and press ok.

    2) Make a query that contains each field you want to be updated. If the field names on the form don't match the field names in the table, either mask the names in the query or change the names in the form. Mask like so:

    FormFieldName: TableFieldName

    3) The code for updating is then a cinch:
    Code:
    Dim rsDat As DAO.Recordset
    Dim rsFld As DAO.Field
    Dim strSQL As String
    
    Set strSQL = "Select * From YourQueryName"
    
    Set rsDat = CurrentDB.OpenRecordset(strSQL)
    
    rsDat.AddNew
    
    For Each rsFld In rsDat.Fields
        rsFld.Value = Me(rsFld.Name)
    Next
    
    rsDat.Update
    
    Set rsDat = Nothing
    PS: this is "Air Code" but I don't see any glaring errors!

    This is the very tip of the iceberg for the functionality you can do with recordsets - learning this object (DAO or ADO) can take you from a DB tinkerer to a DB programmer.

    Good luck,
    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •