Refer to records in datasheet view

[Karen]

I have a main form with a subform. The subform is set as datasheet view. How can I differentiate one record from another in the subform? I mean, if I wanted to set focus to a control in the second record displayed, how can I point to that line in the datasheet record?

Access 2003

 

[JohnWL]

I'm not sure if you want a way for the user to move the cursor to a specific field in a specific row of data or have the program do that behind the scenes. (The datasheet view displays records and their fields of data; a 'control' is a textbox, checkbox, etc in a form.) For the first, simply point at the field and row with the mouse and click. If you mean the second choice, I don't have the answer right off.

One problem, often noted in these newsgroups, is that datasheet view doesn't allow you to do things you can do in form view which is based on a single record at a time. Whatever you have in mind to do with an ability to set focus on a field and row may not be doable in the first place.

--
John Loewen
Loewen Computing Services


I have a main form with a subform. The subform is set as datasheet view. How can I differentiate one record from another in the subform? I mean, if I wanted to set focus to a control in the second record displayed, how can I point to that line in the datasheet record?

Access 2003

 

[Karen]

Thanks John,

What I want to do is more in line with the latter choice. If I could note the record I was on then I can set focus, programmatically, to the first control in that record BUT it's not just set focus. I do want to reference and change values on the controls programmatically and, sure enough, I can't get it to work unless I use 'single form'. I can't say I'm pleased that MS hasn't come up with some way to reference the individual rows in the datasheet view.

Karen
I'm not sure if you want a way for the user to move the cursor to a specific field in a specific row of data or have the program do that behind the scenes. (The datasheet view displays records and their fields of data; a 'control' is a textbox, checkbox, etc in a form.) For the first, simply point at the field and row with the mouse and click. If you mean the second choice, I don't have the answer right off.

One problem, often noted in these newsgroups, is that datasheet view doesn't allow you to do things you can do in form view which is based on a single record at a time. Whatever you have in mind to do with an ability to set focus on a field and row may not be doable in the first place.

--
John Loewen
Loewen Computing Services


I have a main form with a subform. The subform is set as datasheet view. How can I differentiate one record from another in the subform? I mean, if I wanted to set focus to a control in the second record displayed, how can I point to that line in the datasheet record?

Access 2003

 

[John Vinson]

I can't say I'm pleased that MS hasn't come up with some way to reference the individual rows in the datasheet view.

Well... you can, actually.

Dim rs AS DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst <criterion>
- or -
rs.Move <to some record number>
Me.Bookmark = rs.Bookmark

 

[Karen]

Hi John,

Although this references the individual row's data, it can't help reference
the controls in the row, correct?

Karen

the individual rows in the datasheet view.

 

[John Vinson]

Although this references the individual row's data, it can't help reference
the controls in the row, correct?

Once you've navigated to the row, you can reference a control on the
subform - and it will reference that row's instance of the control.
Even on a datasheet there is a "textbox" named for each field in the
form's recordsource.

 

[Karen]

John,

Ok, I'm pretty confused now. When I have a subform as 'Continuous' or
'Single' views, I can assign values to controls based on some action. For
instance, if I type 'Something' in the control, txtSomething, on a record; I
can put some VBA on the 'afterupdate' of the textbox to put 'SomethingElse'
in another textbox in that same record. If I try this in datasheet view, it
does not work. I get numerous errors considering the numerous ways I've
tried to get this to work but they all boil down to 'you cannot assign
values to these controls'. So, if I've navigated to a row in a datasheet
and I want to put some value in a textbox on that record only, how do I
refer to the control?

When the subform, or form, is in Continuous or Single view it seems to
understand where I am in the form; when I'm on a form in datasheet view it
gets lost.

Karen

 

[John Vinson]

When the subform, or form, is in Continuous or Single view it seems to
understand where I am in the form; when I'm on a form in datasheet view it
gets lost.

I'll have to do some experimenting, but IME you can refer to
Me![fieldname] - no aliases, using the name of the field rather than a
separate name of a control - to update a selected field in a table.

But I don't use datasheets very much so I may be mistaken here!

One suggestion: *why* a datasheet? You can make a Continuous Form look
exactly like a datasheet if you like the appearance; about the only
thing you lose is the ability of the user to resize columns.

 

[Karen]

Thanks John,

I'll experiment with that.

As far as datasheets, I'm constrained by the client's preference. I too
prefer other views.

Karen

When the subform, or form, is in Continuous or Single view it seems to
understand where I am in the form; when I'm on a form in datasheet view it
gets lost.

I'll have to do some experimenting, but IME you can refer to
Me![fieldname] - no aliases, using the name of the field rather than a
separate name of a control - to update a selected field in a table.

But I don't use datasheets very much so I may be mistaken here!

One suggestion: *why* a datasheet? You can make a Continuous Form look
exactly like a datasheet if you like the appearance; about the only
thing you lose is the ability of the user to resize columns.

 

[John Vinson]

As far as datasheets, I'm constrained by the client's preference. I too
prefer other views.

I'd be inclined to create a Continuous form, with the textboxes
aligned touching and at the top of the screen, Form Header formatted
to look like a datasheet header... and just not tell them that it's
not a datasheet! <g>

Clients. Can't live without them, can't live with them!