Auto filling fields from previous record into next (1 Viewer)

[joe90]

Hi, Thankyou in advance for any help you can give me on this, Much appreciated.

I am trying to establish how I can autofill 1 of the next records fields with data from 1 of the last records fields.

ie.

End figure needs to be the start figure of the next record.

I am trying to log machine output across shifts, there are 3 shifts, so ideally rather than the next shift inputting the last shifts "end figures" as their "start figures", I would like it to autofill for them, Then I would like to display the results, ie total on the form, so that the shift can see there total across all the machines. This part I'm ok with, its just the autofill I'm having trouble with.

Hope someone can help and also hope it makes sense.

Regards

Joe

 

[cath_hopes]

I've used the defaultvalue property to achieve the same thing when moving to a new (not next) record from a current record.
Assign the current value to the default value property within Form_Current and within After_Update of the fields you want auto-filled. Then within Form_Close re-set all these defaultvalues to blank/ zero or whatever is relevant for your system.

 

[joe90]

Thankyou

Thankyou very much for your help, i will have a play with it and let you know how I get on.

Very kind of you to take the time to respond.

Joe

 

[missinglinq]

Here's code to carry out cath_hopes suggestion:

If field is Text

Private Sub EndFigure_AfterUpdate()
If Not IsNull(Me.EndFigure.Value) Then
 StartFigure.DefaultValue = """" & Me.EndFigure.Value & """"
End If
End Sub

If field is Numeric

Private Sub EndFigure_AfterUpdate()
If Not IsNull(Me.EndFigure.Value) Then
 StartFigure.DefaultValue = Me.EndFigure.Value
End If
End Sub

Note that the form has to stay open between entries; when the form closes, any default values set in code vanish. There is no need to set the valuse to zero or null when the form closes.

 

[joe90]

Thankyou again, but.

Thankyou for this, think I'm getting somewhere now, with your help.
I have entered the code and it works a treat, but I would like to be able to open the form and the StartFigure already displaying the old endfigure.
In my table now it displays all the data I require but obviously when I close the form and go back into it I have values of 0 entered into the StartFigure for the new record, when I would like it to be already filled with the last record.(last end figure entered)

Hope this makes sense, and thankyou again for you help.

Joe

 

[missinglinq]

Okay, we'll start over again. You'll need to have a Date/Time field in your table that holds the date and the time.. If you already have one, skip instructions 1 - 4 below and go the the code and plug its name in instead of Me.DT. If you don't already have a field that holds the date and time:

1. Add a new field to your table
2. Name it DT
3. Datatype: Date/Time
4. After you've added it to the table, open the table, and in the last existing record, fill in this field with a date/time appropriate for the last shift's record, in this format: 4/19/2008 9:06:12 PM

Private Sub Form_Current()
Dim LastShift As Date
  
 If Me.NewRecord Then
    Me.DT = Now
   If RecordsetClone.RecordCount = 0 Then
    Me.StartFigure = 0
   Else
     LastShift = DMax("[DT]", "Table1")
     Me.StartFigure = DLookup("[EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
   End If
 End If
End Sub

Now, go into the Design View for your form, and add the new DT field to the form. If you don't want it to show, set its Visible Property to No. Now you should be ready to go! What this code does, when you add a new record, is to look for the record with the lastest date and time, retrieve the EndFigure from it, and assign that value to the new record's StartFigure.

The lines

If RecordsetClone.RecordCount = 0 Then
Me.StartFigure = 0

are there in case you start from scratch and the table has no records in it. This assigns the value of zero to StartFigure and prevents the other code from running, which would throw an error against an empty table.

You'll have to remember to remove the other code you were given before in Post #4.

 

[joe90]

Done Exactly as you said and it only ****king works. Thankyou so much.

I really don't know how to thank you enough, I can now get on with this little task I set myself, one I thought would be relatively easy. lol. You've obviously given it some thought and helped me for nothing other than helping which is very nice. Thankyou

TOP BANANNA, as we/I say here in england.

I'll let you know how it goes once complete......?lol

Thankyou

Joe

 

[missinglinq]

Always glad to help someone across the great pond!

Good Luck!

Linq

 

[joe90]

Hi Again

You probably guessed it wouldn't be long before I ask the next question.

What you gave me worked well but after having a play with it all I soon realised that it works fine, but I have more than one machine. And from there on I've been scratching my head again.

So whereas my first machine is XeroxStartFigure, which you know as simply StartFigure!. My second machine is Oce5160StartFigure and Oce5160EndFigure. In all I have 5 machines.
I tried changing the same bits in the code I changed for the first machine(which worked lovely), but now I get an error regarding the Form_Current property. I am again very grateful for your help and any more help you can give.

Joe

 

[missinglinq]

Where M1, M2, M3, etc. are the various machines:

Private Sub Form_Current()
Dim LastShift As Date
  
 If Me.NewRecord Then
    Me.DT = Now
   If RecordsetClone.RecordCount = 0 Then
    Me.M1StartFigure = 0
    Me.M2StartFigure = 0
    Me.M3StartFigure = 0
    Me.M4StartFigure = 0
    Me.M5StartFigure = 0

   Else
     LastShift = DMax("[DT]", "Table1")
     Me.M1StartFigure = DLookup("[M1EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
     Me.M2StartFigure = DLookup("[M2EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
     Me.M3StartFigure = DLookup("[M3EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
     Me.M4StartFigure = DLookup("[M4EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
     Me.M5StartFigure = DLookup("[M5EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
   End If
 End If
End Sub

 

[joe90]

Top Banana, works a treat

should of tried that really before I asked for your help again. I tried putting the whole code in for each machine rather than list it like above. Thankyou so much for all your help, I would not of been able to do it without you.

Joe

 

[joe90]

Hi Again,

Well on the way now to finishing, But again I need your expertise if you don't mind? Firstly I would like for each shift to have there own form, rather than having to manually select 'Shift1,Shift2 or Shift3 from a drop down on a single form. Basically so that when I open for instance shift1_form It enters the value of 'Shift1' into the field 'Shift' in my main table.

Thankyou again

Joe

 

[joe90]

Ive sorted the shift bit now. But on to the next problem you may be able to point me in the right direction with. I would like a total count for each machine to be displayed in the form and also update the table for each machine. So basically after I put in a value into lets say "M1EndFigure", I would like a total to be displayed next to it in "M1Total" but make the calculation as soon as I move onto the next field to input, ie(M2EndFigure). Then also have the calculated value go into "M1Total" in my table(Table1).

Thankyou again for all your help, or if anyone else reading and could give me some ideas it's much appreciated.

Kindest regards

joe

 

[joe90]

Hi again
I am having some trouble now which I hope you can help me with. For some reason the function has stopped
working, This is the only code I have.
Private Sub Form_Current()
Dim LastShift As Date

If Me.NewRecord Then
Me.DT = Now
If RecordsetClone.RecordCount = 0 Then
Me.M1StartFigure = 0
Me.M2StartFigure = 0
Me.M3StartFigure = 0
Me.M4StartFigure = 0
Me.M5StartFigure = 0
Else
LastShift = DMax("[DT]", "Table1")
Me.M1StartFigure = DLookup("[M1EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
Me.M2StartFigure = DLookup("[M2EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
Me.M3StartFigure = DLookup("[M3EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
Me.M4StartFigure = DLookup("[M4EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
Me.M5StartFigure = DLookup("[M5EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
End If
End If
End Sub
Private Sub Command223_Click()
On Error GoTo Err_Command223_Click

DoCmd.GoToRecord , , acNewRec
Exit_Command223_Click:
Exit Sub
Err_Command223_Click:
MsgBox Err.Description
Resume Exit_Command223_Click

End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

It was working fine but now doesn't autofill the StartFigures. This has been absolutely sending me crazy over the last week as it
was fine.
Hope you can help.
J

 

[zoober1]

Okay, we'll start over again. You'll need to have a Date/Time field in your table that holds the date and the time.. If you already have one, skip instructions 1 - 4 below and go the the code and plug its name in instead of Me.DT. If you don't already have a field that holds the date and time:

1. Add a new field to your table
2. Name it DT
3. Datatype: Date/Time
4. After you've added it to the table, open the table, and in the last existing record, fill in this field with a date/time appropriate for the last shift's record, in this format: 4/19/2008 9:06:12 PM

Private Sub Form_Current()
Dim LastShift As Date
  
 If Me.NewRecord Then
    Me.DT = Now
   If RecordsetClone.RecordCount = 0 Then
    Me.StartFigure = 0
   Else
     LastShift = DMax("[DT]", "Table1")
     Me.StartFigure = DLookup("[EndFigure]", "Table1", "[DT] = #" & LastShift & "#")
   End If
 End If
End Sub

Now, go into the Design View for your form, and add the new DT field to the form. If you don't want it to show, set its Visible Property to No. Now you should be ready to go! What this code does, when you add a new record, is to look for the record with the lastest date and time, retrieve the EndFigure from it, and assign that value to the new record's StartFigure.

The lines

If RecordsetClone.RecordCount = 0 Then
Me.StartFigure = 0

are there in case you start from scratch and the table has no records in it. This assigns the value of zero to StartFigure and prevents the other code from running, which would throw an error against an empty table.

You'll have to remember to remove the other code you were given before in Post #4.

I am trying to make this code work for my database. Instead of using date, could I use an auto number PK field (Transaction_ID)? I'm pretty much trying to do the exact same thing as OP, but instead of Start Figure/End Figure, im trying to keep track of OldBank/NewBank for daily transactions. After reading this code, I am confused on what the "Dim LastShift" is. Is that a field in a table? Please help! Thanks