Populating and protecting fields on a form.

[ryetee]

I am displaying records from a table on a form line after line like a spreadsheet/data sheet.

I want to do 2 things.
When the user clicks anywhere on a new row to add data I want to populate that row with some data from the row above.
I also want to protect certain fields on each line (but not all lines!)

So basically if the user is presented with

old model number.....new model number..Added by.....date


123456...................77777777.................Fred..........15/10/2014 10:11:12
77777777..................8888.....................John..........16/10/2014 12:12:12
8888..........................6767.....................Jack..........16/10/2014 14:11:12
6767..........................4321......................Joe..........17/10/2014 10:11:12

I want to protect all of the 1st 3 rows and the old model number in the last row.

When they tab or click into the new row I want the old model number populated with the new model number.

Any suggestions

 

[Mihail]

It is hard to explain. There are a lot of things here.
The solution seems to be:
1) the form should Allow Addition but don't Allow Edits (or Delete)
2) You must set as "Default" for all fields (in fact, controls) the value that you find in the "last row".

I quoted the "last row" because here is (again) a long discussion about what means "the last row".
This "last row" always depend from the sort order.
As example, from A-Z sort order "Z" is in the "last row" but from the Z-A sort order "A" will be in the "last row".

 

[ryetee]

It is hard to explain. There are a lot of things here.
The solution seems to be:
1) the form should Allow Addition but don't Allow Edits (or Delete)
2) You must set as "Default" for all fields (in fact, controls) the value that you find in the "last row".

I quoted the "last row" because here is (again) a long discussion about what means "the last row".
This "last row" always depend from the sort order.
As example, from A-Z sort order "Z" is in the "last row" but from the Z-A sort order "A" will be in the "last row".

Last row for me would be last record added (I sort by the ID )
I'm more bothered about getting new row to pick up values from last row.

So how do I do your 2) above?

 

[JHB]

You can use the form's on current event, (something like below), you can also use the same event to lock and unlock controls/fields:

Private Sub Form_Current()
  If Me.NewRecord Then
    Set rst = Me.RecordsetClone
    If Not rst.EOF Then
      rst.MoveLast
      Me.[new model number].DefaultValue = "'" & rst![old model number] & "'"
      Me.[new model number].Requery
    End If
  Else
   Me.[new model number].DefaultValue = ""
  End If
End Sub

 

[ryetee]

You can use the form's on current event, (something like below), you can also use the same event to lock and unlock controls/fields:

Private Sub Form_Current()
  If Me.NewRecord Then
    Set rst = Me.RecordsetClone
    If Not rst.EOF Then
      rst.MoveLast
      Me.[new model number].DefaultValue = "'" & rst![old model number] & "'"
      Me.[new model number].Requery
    End If
  Else
   Me.[new model number].DefaultValue = ""
  End If
End Sub

JHB - you're a star. Going to test this to destruction now!!

I need either to be able to lock all rows except for the current one or I need this to work if I go back and change the field in an existing row - it needs to update the following row! If the latter is not possible then how do I lock down the entire (non current) record, bearing in mind when I go into the form there may be a number of rows already filled in.

Thanks

 

[JHB]

Again in the on current event:

  If Me.NewRecord Then
    Me.AllowEdits = True
  Else
    Me.AllowEdits = False
  End If

 

[ryetee]

Again in the on current event:

  If Me.NewRecord Then
    Me.AllowEdits = True
  Else
    Me.AllowEdits = False
  End If

Getting there slowly but a wee problem with the above. When I go into the form everything is fine. If I fill in the form and then proceed to the new line again everything is fine.
Problem arises in 2 areas
1. If the user tries to edit other than the current line then he can't - perfect but can we make this a bit more clever (see below for what I mean). However if he then goes back to what was the current line (ie the new unfilled record) it seems to assume that it is no longer new (presumably because we've copied some stuff to other fields. I've tried to do a Me.undo but doesn't seem to work/
2. The other problem is that if he doesn't fill in the model number I put out a message to say he hasn't done so and go and fill it in, but because it's not the new record he can't.


So what about the clever bit - is there anyway we can do the same for each row that we're doing for the new record?
At the moment if we have 4 rows when we type in the 5th it populates it from the 4th. Can we have it that if he updates row 2 it is reflected in the (already filled in) row 3?

 

[JHB]

Oh yes I think so, if you are able to set up some rules, then it is possible, when no clear rules, then it isn't possible.
Look at how I've done it until now for getting some ideas.

 

[Minty]

I would use some additional unbound fields (flags) to keep track of what you want to be able to edit, and if it has been updated, or needs updating or saving. You'll need to work out the logic you need to apply.

 

[ryetee]

Oh yes I think so, if you are able to set up some rules, then it is possible, when no clear rules, then it isn't possible.
Look at how I've done it until now for getting some ideas.

Rules are
1. If new record adopt previous row's values for certain fields
2. If last row updated do nothing
3. If any other row updated then update row +1 with details of row that is updated.

I'll have a go myself though! This would be easy in COBOL!!

 

[ryetee]

Right this seems to work. This stops them editing previous rows but have cleared other problems mentioned above by putting in a DoCmd.RunCommand acCmdUndo instead of a Me.undo. This clears the last record if the user has jumped to a new line without filling anything in. Still be intersted to see how I can edit row (0) and change fields in row (1) (so long as row(0) isn't the last row!)


Code is now
If Me.NewRecord Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

If Me.NewRecord Then
Set rst = Me.RecordsetClone
If Not rst.EOF Then
rst.MoveLast
If IsNull(rst![ModelNumber]) Then
MsgBox ("the mdodel number in the row above is missing - please enter missing data")
DoCmd.RunCommand acCmdUndo
Exit Sub
End If

If IsNull(rst![ModelSerialNumber]) Then
MsgBox ("you neeed to fll in the model serial number in the row above - please enter missing data")
DoCmd.RunCommand acCmdUndo
Exit Sub
End If

Me.txtOldModelSerialNumber.DefaultValue = "'" & rst![ModelSerialNumber] & "'"
Me.txtOldModelNumber.DefaultValue = rst![ModelNumber]
' Me.txtModelSerialNumber.Requery
Me.txtOldModelSerialNumber.Requery
Me.txtOldModelNumber.Requery
End If
End If

 

[gemma-the-husky]

A different point of view....

Really, the whole premise is non relational. The idea of getting values from previous rows is generally not the right way to try to use a database. You would probably do better reconsidering how you use the data you do have.

For instance, why store the old value. Add a flag to the record to indicate the current record, then you can automatically lock the old records. You need a record key, then just display the records by record key, in date order.

Things like that.

 

[Galaxiom]

Again in the on current event:

  If Me.NewRecord Then
    Me.AllowEdits = True
  Else
    Me.AllowEdits = False
  End If

Far more concisely expressed as:

Me.AllowEdits = Me.NewRecord

 

[ryetee]

A different point of view....

Really, the whole premise is non relational. The idea of getting values from previous rows is generally not the right way to try to use a database. You would probably do better reconsidering how you use the data you do have.

I know it's not relational and have found out the hard way why it should be! I've racked my brains how to fit this in and couldn't think of a way to do it.

For instance, why store the old value. Add a flag to the record to indicate the current record, then you can automatically lock the old records. You need a record key, then just display the records by record key, in date order.

Things like that.

If I had the knowledge of what I'd have to do before starting out I would probably have done things a lot different. Having the old and new on the same record (and therefore duplicating data) will make reporting a lot easier. I hope!!

 

[Minty]

I think you would be better with a field in the parts used table to indicate if the part was fitted (default) or not and then ideally a separate table with the reason for removal, removal date and who by etc. that was only used if the part was removed.
This way you have a a list of part ID's that were removed and can easily report on them.

 

[Mihail]

Just now I am wonder why you are need this ?
What you are trying to do ?
Can you explain in sample words what you have and what you wish to obtain ?
Better is to post your DB.

 

[ryetee]

Just now I am wonder why you are need this ?
What you are trying to do ?
Can you explain in sample words what you have and what you wish to obtain ?
Better is to post your DB.

CAn't post DB - against rules.
I'm trying to keep a track of maintenance parts that have to be replaced.

 

[Minty]

I think, as a couple of people have stated, you will get very confused trying to tie this up in reports. Take a step back and look at the suggestions we have made.

An extra days sorting out now, will mean a whole heap of time saved later when you suddenly need to adjust something and the "difficult" method no longer works.

You have a usage table - just flag if the part was removed or replaced. Record the ID and details in another table to show replaced / removed parts (Use a status flag 1 = removed, 2 replaced, 3 repaired and refitted, etc etc. This way if you have a new status you aren't restricted to a fixed number of types of operation.

 

[Mihail]

CAn't post DB - against rules.

Do you think that we are not able to design a DB like yours ?
I don't ask about data. I ask about DB. Is not the same thing. The DB can be empty but is better to fill it with some false data in order to test the functionality.