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

Topic

New Reply

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 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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

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!

---

[/indent] 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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

OK, Charlotte, Wendell,

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 ’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.

Reply | Quote

OK, Charlotte, Wendell,

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 ’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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote