Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Address fields in selected area for fill/copy down (97-2002)

    Most valuable members of the board,

    I'm trying to improve a friend's function which add's Excell's "Copy/fill-down" functionality to Access (a <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> great idea by the way). When you have an area selected, it copies the values in the first row to all selected cells below. It should work both in tables/queries and datasheet forms.

    Now I need to know how to get the name of the ...th field in a datasheet, both in a table and a form. How do I achieve this?

    I can retrieve the columns involved using the .SelLeft and .SelWidth properties. But I miss the link to the corresponding fields (and their names and values).

    Thanks for any suggestions,

    Hasse, Belgium

    p.s. If there is some interest and I get it working, I can probably leave the result on this forum too.
    p.s. If you think right now 'yet one other guy reinventing the wheel, please feel free to post a link to a freeware code/add-in which adds this functionality to Access!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Address fields in selected area for fill/copy down (97-2002)

    You can use DAO for this. Both a TableDef and a Recordset object have a Fields collection. It is zero-based, so the first field is Fields(0). Example:

    Dim strTableName As String
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim i As Long

    strTableName = "tblSomething"
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)

    For i = 0 To tdf.Fields.Count - 1
    Debug.Print "Field #" & i & ": " & tdf.Fields(i).Name
    Next i

    Set tdf = Nothing
    Set dbs = Nothing

    For a form, you can do something similar with a recordset:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Long

    Set dbs = CurrentDb
    Set rst = Me.RecordsetClone ' Me = form that runs the code

    For i = 0 To rst.Fields.Count - 1
    ...

    In Access 97, a reference to the Microsoft DAO 3.5? Library is set automatically; in Access 2000 and 2002 you will have to make sure that there is a reference to the Microsoft DAO 3.6 Object Library.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Address fields in selected area for fill/copy down (97-2002)

    <hr>p.s. If you think right now 'yet one other guy reinventing the wheel, please feel free to post a link to a freeware code/add-in which adds this functionality to Access! <hr>
    What you are ignoring is the fact that Access is not a spreadsheet, and there is no real purpose in this kind of functionality in Access. Access records are not related to one another the way that rows in a spreadsheet are. If you explain *why* you're trying to do this, someone may very well be able to point you to a method that approximates that.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Address fields in selected area for fill/copy down (97-2002)

    Hans,
    I've considered your suggestion too. BUT the problem is that users/designers tend to switch columns once in a while. Therefore, the order in the fields collection isn't always equal to the order which is visually present in the datasheet.
    Determining the fields involved by comparing the values in the datasheet-columns with the fields' values is a bad workaround, as I assume that different fields might contain the same value (e.g. yes/no-fields).
    So here I get stuck...

    Charlotte,
    I understand what you mean, but nevertheless data entry in Access should certainly be helped with such feature. I've often been in a situation where I had to enter the same value in a field for a number of existing records. Updatequeries aren't always appropriate then, and certainly not easy-going for many users.

    Looking forward to your replies,
    Hasse

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Address fields in selected area for fill/copy down (97-2002)

    Allowing users to enter data in a datasheet rather than using a form is not a good idea. Datasheet view is sometimes useful for presenting data, but it lacks the control features of a regular form view. In Access, a user is only working with a single record at a time, no matter how many he sees. And the order of the columns is irrelevant to the database. That was my point. Access hangs together differently than Excel or Lotus 123. The columns are related to the record and the record doesn't care what order it's in. There are shortcut keys for copying from one field to another or one record to another, but if you are repeating a bunch of information in every record, then the table isn't properly designed in the first place.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Address fields in selected area for fill/copy down (97-2002)

    Charlotte,

    please don't understand me wrong: I'm glad with your reply and seriously respect it. But isn't this a rather theoretical point of view when smaller, non-professional databases are concerned? Of course Access and Excel datasheets are structurally totally different, but is this relevant here and a reason why not to try and work around it to ease data entry? There are plenty of databases quick and dirty built from scratch, to be managed by the 'author' him/herself to store data quickly, with no time (yet) to implement all good design rules, nor application objects like update queries and forms for controlling all the user's behaviour, default values, validation and all those other data entry & management issues. And in those cases, where data often is entered immediately in datasheet view, I remain at my opinion that the fill-down feature remains useful. It might be true that those databases aren't properly designed, lacking a proper user interface and controll,... But sometimes reality forces you to work with what you've got, not?

    Maybe, I can once deliver some examples for further discussion.

    Hasse

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Address fields in selected area for fill/copy down (97-2002)

    I totally agree with Charlotte. For one thing you don't have a set of existing blank rows in an Access database, so you can't fill down multiple rows like you do in Excel. You can only add one record at a time in a database! (Of course an append query can add multiple records, but they are each added one at a time.) In addition, you can do things like cloning an existing record to set default values in a form with very little code, and there have been discussions about that frequently in the lounge. Yes, there are the skunk works databases that people build and use, but trying to make them work like Excel, or Word or PowerPoint or Outlook, or, or, ... is an exercise in futility. Not only that, but it gives Access (or whatever) a bad name when someone is working in the datasheet view and accidentally deletes a bunch of rows, and then wants to do an Undo. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Address fields in selected area for fill/copy down (97-2002)

    OK, Charlotte, Wendell,
    <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> <img src=/S/sad.gif border=0 alt=sad width=15 height=15> <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>
    in case my previous post didn't convince you... I got your point, I really do. If anyone wants to, I'll honestly declare to extend my functionality, if it's ever going to work, with a message box notification about how bad the concerned database's design probably is. Mea maxima culpa.

    Still, I think I have a point <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 'cause - I'm sorry if I wasn't enough clear about this - I'm NOT talking about adding data to new, empty records. It's about the ability to assign a value to a field in a number of records which, sorted in a recordset, follow up eachother because they have some correlation.

    In case this was clear and still, you consider this an exercise in futility, I appreciate your trust in my creative abilities. But unfortunately I just don't always have the time to thinker and plan about how I can solve this otherwise by changing the data structure and (re)building all proper user interface, not even mentioning me actually doing it. Then, what should I do? Just forget it and continue? Actually, I start to get a bit annoyed and to believe in a misunderstanding partly due to the fact that I haven't got a set of template forms, controls, code and add-ons readily available to implement in whatever new database project I start, as some long-time and full-time professionals probably do. (...) Please understand that under these circumstances, it's a bit frustrating to receive no answer to my question at all (besides HansV's suggestion - thank you!), and apparently to have to wait for an answer untill someone else with more valid, accepted reasons and circumstances under which he needs to get the names of the fields involved in a selected area ask the same question.

    You know, I'll discuss my problem with someone in private, in order not to unnecessarily bother you anymore, and come back with a clear example, or, a humble apology. And in the meanwhile, thank you for your time. Whatever the case, it think it was an interesting discussion. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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