Datasheet Row Access

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

I have a Access 2003 datasheet and I want to lock all fields in the first
few number of rows, but I do NOT wnat to lock all rows in the datasheet.
Can I do this and if so how does on enumerate datasheet rows?

Thank in advance

Charles
 
Hi Charles,
Using a datasheet you aren't going to be able to do this - and besides,
it'[s bad practice to give users direct access to tables. Better to set up a
continuous form (which looks like a datasheet to your users) so that you can
lock fields or records based on control contents.
 
Thank you Susan for your response,

However even with a continuous form the question remains... how do I
enumerate the rows and lock certain rows (fields on the row) and then move
on to the next.

How do I enumerate the rows

CL

SusanV said:
Hi Charles,
Using a datasheet you aren't going to be able to do this - and besides,
it'[s bad practice to give users direct access to tables. Better to set up
a continuous form (which looks like a datasheet to your users) so that you
can lock fields or records based on control contents.

--
hth,
SusanV


Charles said:
I have a Access 2003 datasheet and I want to lock all fields in the first
few number of rows, but I do NOT wnat to lock all rows in the datasheet.
Can I do this and if so how does on enumerate datasheet rows?

Thank in advance

Charles
Click to expand...
Click to expand...
 
Thank you Susan for your response,

However even with a continuous form the question remains... how do I
enumerate the rows and lock certain rows (fields on the row) and then move
on to the next.

How do I enumerate the rows
Click to expand...

A table HAS NO ORDER.

Talking about the first five rows in a table is like talking about the
first five potatoes in a wheelbarrow... <g>

You'll need *some* field in the table to indicate which rows are to be
protected; in the Form's Current event you can check the value of that
field and set the Form's AllowUpdates property appropriately.

John W. Vinson[MVP]
 
Gee John,

I am not talking about a table. I am talking about rows on a continuous
form. How do I advance from one row to the next row. I will test the
neccessary criteria as I need to but can I enumerate the continuous forms
rows.

CL
 
You could do it this way. Might not be the sleekest but it would work.
Add a yes/no field to the table. Loop through a recordset of the table,
using IF your condition is met, THEN set fldLock = -1, ELSE fldLock = 0.

On the form,
IF fldLock = -1, THEN
me.FirstName.Locked = true
me.LastName.locked = true
etc etc.
Else
me.FirstName.Locked = false
me.LastName.locked = false
etc etc.
Endif


--
hth,
SusanV
 
Thanks again Susan,

I understand your solution but do not want to add database fields whose
function is to help with input edits. I beleive I will look for a third
party datasheet solution

Thank you

Charles
 
Best of luck,

Susan

Charles said:
Thanks again Susan,

I understand your solution but do not want to add database fields whose
function is to help with input edits. I beleive I will look for a third
party datasheet solution

Thank you

Charles
Click to expand...
 
In looking more closely at the solution you suggested it appears to me that
when I set

me.FirstName.Locked = true

That it would set the colume to locked for every row of the form. Is there
an event that you had in mind in which to put this code and have it address
only a sinbgle row?

CL
 
Oh, jeez I forgot this was a continuous form - not sure how you could
accomplish this on a row by row basis.

May I ask what your purpose is with this? Wouldn't it be simpler to filter
your recordsource to 2 separate datasets - one which would be view-only and
one which would open in an editing form?

Susan
 
Actually it is a form processes subscriptions. Once a subscription is
entered and activated most (but not all) fields on that record are to
change. Most fields should be locked until the subscription lapses. Each
row has a start and end date and if the currecnt date is between those two
dates I want to lock those fields FOR THAT ROW ONLY.

I really can't create a view only form because even though some fields can
not be changed other fields can be cahnged and the form has a complete set
of right-click menus that allow other functions to the records. I just want
to limit which rows the user can mess with.

It is wierd that I can enumerate rows in a listbox but not in a datasheet or
continuous forms.

do I have ANY options
 
Hey Charles,

I really think this isn't as difficult as it seemed at first. Playing with a
continuous form I have, I found that the following works. The form has 4
fields, MCode, MFreq, MTitle,MEstHrs and LCauseCode. I put the following on
the Got Focus event of the field I want to be able to lock, based on whether
or not another field, LCauseCode, had been populated with data. On records
with NO LCauseCode data, I was unable to edit the MCode, but on the records
which have the LCauseCode entered the MCode can be changed. Enter or delete
the LCauseCode and then click back to the MCode field and poof, behavior is
as required.

''' code Start

Private Sub MCode_GotFocus()
If IsNull(Me.LCauseCode) Then
Me.MCode.Locked = True
Else: Me.MCode.Locked = False
End If

End Sub

''' code end


I hope I put all the data back to original LOL - shouldn't play with live
data <grin>

I'm sure you could use similar code on the GotFocus event of the fields you
would like to be able to lock programmatically.

Please let me know if this works for you,

Susan
 
YES !!!! Susan that will work for me. I am embarassed at how simple the
solution turns out to be. And I can use it with the original datasheet.
Thanks so much for your help

Man that is simple (blush blush)

CL
 
I know the feeling - here I was adding fields! LOL, this is what happens
when we start thinking before the coffee kicks in. <grin>

Glad I could help,

Susan
 
I certainly have a lot of continues forms where I set the controls 'enabled'
property on/off. While this actually makes all instances of the control go
disabled, I actually find this works quite well. I mean, when you move
to the next row, those columns that you enable, or disable can then
be set. So, you could set the controls visible property as you move
the cursor up/down through the

In fact, I actually PREFER the above behavior, as then during
data entry it is VERY easy to see that the column in question
is enabled.

In place of a VERY HARD TO READ checkerboard pattern of enabled, and
disabled boxes,
, you get a very nice enable/display view as I move the cursor up /down.

I have uploaded a gif animation of me navigating in a form, both of the two
screen shots will give you an idea of how this looks.

http://www.members.shaw.ca/AlbertKallal/HideColumn/index.htm
 
Each
row has a start and end date and if the currecnt date is between those two
dates I want to lock those fields FOR THAT ROW ONLY.
Click to expand...

In that case you can use the form's Current event; it fires whenever
the user moves to a different row. You can set the Form's AllowEdits
property, or the Enabled property of one or any number of controls, in
this event; when you move to a different row, you can then reset these
properties to the value appropriate for that row.

John W. Vinson[MVP]
 
Back
Top