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

    Unanswered: Code to fill unbound controls on an unbound form from a recordset

    This works if the control names are the same name of the recorset columns (fields)

    On Error Resume Next
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb
    Dim myrecordsetname
    Dim myid As Integer
    On Error Resume Next
    myid = InputBox("Please enter ID", "Enter Claim ID")
    Set rst = db.OpenRecordset("select * from tblclaim where claimnumber =" & myid)
    Dim ctl As Control
    Dim i As Integer
    i = 0
    Dim myname As String
    Do Until i = rst.Fields.Count
    myname = rst.Fields(i).Name
    Me(myname) = rst.Fields(i)
    i = i + 1
    Loop
    rst.close
    'db.close
    Set rst = Nothing
    'Set db = Nothing
    Dale Houston, TX

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dale, one fatal issue in your above code, can you spot it?

    I'll give you a clue... Line 9
    EDIT: Any reason why you've commented out lines 20 and 22?

    EDIT EDIT: Sorry, I couldn't resist. It's a well thought out and implemented idea
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    the dim ctl is not needed - i did not use it - but i ran this on a recorset with 97 fields and behind the on open event of a form with the 97 controls - i tested it with 10 different records (myid) and it worked perfectly

    but if there is a flaw - please tell me - but like I said it worked
    Dale Houston, TX

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I said line 9
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Set rst = db.OpenRecordset("select * from tblclaim where claimnumber =" & myid)


    I am confused

    myid is an integer and the claimnumber is an integer

    it only returns one record
    Dale Houston, TX

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *_________ *cough*
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Dale,

    here's an alternative route to consider
    (ignores George's * objection)
    (ignores my loathing of inputbox)
    ...it should be slightly more efficient than your loop.

    Code:
    Private Sub doFormFill()
    
        Dim dabs As DAO.Database
        Dim recs As DAO.Recordset
        Dim rfld As DAO.Field
        
        On Error GoTo err_doFormFill
        
        Set dabs = CurrentDb
    
        myid = InputBox("Please enter ID", "Enter Claim ID")
        Set recs = dabs.OpenRecordset("select * from tblclaim where claimnumber =" & myid)
        
        With recs
            if .EOF then
                msgbox "no data for " & myid
            else
                For Each rfld In recs.Fields
                    Me(rfld.Name) = rfld
                Next
            endif
        End With
    
    exit_doFormFill:
        Set rfld = Nothing
        recs.close
        set recs = nothing
        Set dabs = Nothing
        Exit Sub
    
    err_doFormFill:
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error: doFormFill"
        Resume exit_doFormFill
    
    End Sub
    in reality, i declare recs formwide and do not close/nothing recs after form fill.

    saving edits then repeats the WithRecs loop comparing form with recs and building sql to UPDATE edited fields (only) and WHERE tblfield = recs

    ...for an optimistic concurrency no-lock "lock".

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    The input box is only t here for me to quickly test the data as I knew what data was in the first five fields for the 10 id's I was testing - I am acualling passing that value from a combo box - and I agree I loathe the inputbox.

    Geo - how else would you retrieve 97 fields in a sql statement without the *?

    I work in a RAD atmosphere (much like the old child factory slave shops of old) If my manager caught me doing anything that can be done in a shorter time (other than his coding for every freaking field) I would be in hot water.

    Izy - great code - now I have two ways to do something really cool. I think someone should add this to the code bank - It is a superior way to add data to unbound forms.
    Dale Houston, TX

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by axsprog
    Geo - how else would you retrieve 97 fields in a sql statement without the *?
    Write them out once.
    Yes it's a pain in the bum, once, now, but it will save you hours of aggro later.
    If you have to, then build up a string dynamically of all field names dynamically in VBA and print it in the immediate window so you can paste it into your select statement.

    Do it once, do it right and you'll save yourself a lot of trouble in the long run

    Another repeated sentence.
    George
    Home | Blog

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Text book-wise you are correct Geo - I have no argument for your method. But needless to say I am a lazy Yank and I use the * when I am tired.

    Had to say it

    other than that - how did you like my code and the Izy twist (which is a bit cleaner I must admit)
    Dale Houston, TX

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think it's a nice idea, but I'm not sure I will be using it in practice.
    Partly because I try to avoid ambiguity between my control names and source fields and partly because I think explicit code would be more readable (but then again, 97 lines of code compared to one small loop does add up to a big difference).

    Basically, I'm torn. I like it in theory, but there's still a purist part of me telling me not to use it - but it's not able to find a good enough justification for this




    ...Just spotted another small mistake, line 14.
    George
    Home | Blog

  12. #12
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    The upside to this code is that as the developer adds fields or controls - they do not have to add a single line of code.

    Place this code in a function in a class module - pass in the variables for the form name tables/recordset and one can use it everywhere.

    Sometimes a budget (or closed ended project) dictates cutting corners - and this is a very acceptable and professional way to achieve the result needed.

    Plus at 46 it is such a relief to not always having to type
    me!control = myrecordset!field
    Dale Houston, TX

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I like the code and as George stated I am a little torn on it. It's a very nice method and is very re-usuable. Perhaps there is something about seeing the actual field names in the code. Either way, I'm keeping the code in my pocket of snippets to utilize as I can definately see a possible need.
    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
    good to hear pk - I bet Geo is a closet code snippett guy - and he in fact will use it someday
    Dale Houston, TX

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm actually terrible at snippets!
    I retain thetheory in my mindbox for as long as possible and search the forums for stuff I can't remember

    I did at one point have a database with a form with over 100 command buttons on it - each held a lovely little command behind it.
    That was my snippet library, and unfortunately I overwrote it by accident
    George
    Home | Blog

Posting Permissions

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