Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: using variable in DAO for field name

    I am trying to to pass a variable as a fieldname in code something like

    dim f as field, rst as recordset
    f.name = "CustomerID"
    set rst = currentdb.openrecordset("select myid from mytable")
    rst.addnew
    rst!(f.name) = "Jones"
    rst.update

    fails miserably

    any ideas on how to pass a field name as a variable for this?
    Dale Houston, TX

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Try this....

    I think the issue is how you are opening your recordest. If you change it to the below, it should work OK.

    Set rstImport = currentdb.OpenRecordset(mytable, dbOpenTable)

    rst.addnew
    rst!(f.name) = "Jones"
    rst.update


    Good luck, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    No - I know how to save data via DAO - I am trying to pass in a field name into my DAO code'

    so Instead of rst!CustomerName

    I might want to dim x as Customername and my code be
    rst!x
    Dale Houston, TX

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *wave to Dale*

    Try dropping the exclamation mark?
    George
    Home | Blog

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Axsprog,

    Sorry I misunderstood you earlier. I've tried several different ways and I can't find a way to pass a variable. Good luck, and let me know if you do find a way to do this. Oh yeah, dropping the exclamation doesn't work either.

    Thanx, Stu
    --If its free, take it for what its worth!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a go with:

    dim f as string, rst as recordset
    f = "myid"
    set rst = currentdb.openrecordset("select myid from mytable")
    rst.addnew
    rst(f) = "Jones"
    rst.update

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup... drop the ! and don't use a dot character in a variable name.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha, didn't even notice the period in the variable name! Let us know when it's all working Dale
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    drop the ! and don't use a dot character in a variable name

    ..and
    dim f as field should be As String

    ..and
    make sure f holds the name of a field in your SELECT

    ..and (for safety (oooops, forgotten in my earlier post))
    dim rst as DAO.recordset

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would also suggest referencing the Fields collection of the recordset:

    rst.Fields(f)

  11. #11
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Yes - Izy you're right - I did a similar thing before checking my email and posting the resolution -

    Geo - I know , I know - reference the fields - lose the * - but I am just so bloody lazy.

    I have a theory that one is only born with so many keystrokes in their life before they have a stroke - so I conserve as many as I can.

    Thanks guys for the feed back

    Izy where are the photos from Africa?
    Dale Houston, TX

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by axsprog
    I have a theory that one is only born with so many keystrokes in their life before they have a stroke - so I conserve as many as I can.
    I wish someone had told me that before now...
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're trying to set up a SQL Statement where you tell the SQL Statement what field in the table to compare to some value, this is the way I do it in ADO:

    'Note: Passes FLDName to function (FLDName equals the FieldName and is defined as variant). Returns boolean depending on if FieldName = some integer value (RID) - (False if no records exist, True if records do exist)

    Function GetACodeFOE(FLDName As Variant, RID As Integer) As Boolean
    Dim rv As ADODB.Recordset
    Dim strSQX As String
    Set rv = New ADODB.Recordset

    'Now to construct the strSQX statement utilizing the FLDName in the ChecksFOE table passed to test on.
    strSQX = "Select * from ChecksFOE where [" & FLDName & "] = " & RID & ""

    rv.Open strSQX, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rv.EOF And rv.BOF Then
    rv.Close
    Set rv = Nothing
    GetACodeFOE = False
    Else
    rv.Close
    Set rv = Nothing
    GetACodeFOE = True
    Exit Function
    End If
    End Function

    Or am I way off base on reading what you're trying to do axsprog?
    Last edited by pkstormy; 05-29-08 at 23:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    PK - Actually I have a situation in which I need to pass variables (fieldnames) into sql statements - so I will be contacting you about that.
    Dale Houston, TX

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Dale, I used the code in the previous post a few times. But the way I always did it was to define a variable as a variant and then (frame it) ie.. [" & variablename & "] into the sql statement. It worked fairly well and it wasn't too complex but if you have any difficulties let me know. I did this 4 years ago so I might be a tad rusty on getting more complex with it but I'll do what I can to help.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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