Data from previous record

[mtagliaferri]

I have still not solved the problem wich is doing my head in!!!
In the following DB I have the "frm1" wich the value of the first record for "Value1" is 10 when I pass to the second record Iwould like to show automatically the value 10 in the field "Value1" this for all the records untill I change toa new value so from then on it will show the new value.
I believe is the Dlookup function, but I have not find the way to make it work.
I must be thick but I need your help!!!!!
Thanks

 

[jwhite]

See this link on Microsoft:

ACC2000: Referring to a Field in the Previous Record or Next Record

 

[mtagliaferri]

I have followed your link, but have a error!!

 

[Rickster57]

error

the code should be tbl1 not frm1

=DLookUp("[Value1]","tbl1","[IDData]=Forms![frm1]![IDData]-1")

 

[mtagliaferri]

I stillhave a error, the rror indicated is with the comma that are not accepted, it will only accept semi colon.
Do I have to set some properties in the form..... I really do not understand what is wrong!

 

[boblarson]

slight correction:

=DLookUp("[Value1]","tbl1","[IDData]=" & Forms![frm1]![IDData]-1)

 

[mtagliaferri]

I don't know for what reason it will not accept the "," in the formula how can that be?

 

[boblarson]

The DLookup is working the way it should, if put in exactly like I had it. The only problem is that DLookup is not going to give you what you want. But, the good news is that I did figure out how to give you what you want. Plus, since the controlsource was the dlookup, it wasn't saving to the table.

I'm posting your database (revised by me) so you can see how to do it. I added an unbound text box to your form (which I called txtStatic and which you will set it's visible property to NO, but it is visible only now so you can see how it works) and when the form is updated, then it changes the static to the current value in the value1 text box. It will then change the static to match. Then, I set the default for the Value1 text box to =[txtStatic] and it will fill in that amount on a new record but ignore it if scrolling through old records.

After adding the static text box and binding txtValue1 to the Value1 field, I only had to add two lines of code to make it work:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.txtStatic = Me.txtValue1
End Sub


Private Sub txtStatic_AfterUpdate()
    Me.txtValue1 = Me.txtStatic
End Sub

 

[jdncoke99]

Hi Bob
This, sounds to be exactly what I need for MY Form frmResults. I have no Primary Key in tblResults, will this matter?. I have;

tbx.Date
Format: Short Date
Input Mask: 00/00/0000;0;_

tbx.LocalTime
Format: Short Time
Input Mask: 00:00;0;_

cbo.Day
RowSource Type: Value List

I would like them all to maintain their last entry, when the form moves to the next record. Would the code work in these three instances? Does it matter about the Tab Order for the txtStatic boxes?
Your help would be appreciated

 

[jdncoke99]

Hi
I tried the example above but it doesn't work, should it? I could try again but I am confident I put the right things in the right places. Further help on this problem would be much apprecieted

 

[jdncoke99]

Is there perhaps code that would set the text to be the default value, in the After Update Event?

 

[jdncoke99]

Thanks Missinglinq, found my (your) answer

For Text fields

Code:
Private Sub YourTextControlName_AfterUpdate()
If Not IsNull(Me.YourTextControlName.Value) Then
YourTextControlName.DefaultValue = """" & Me.YourTextControlName.Value & """"
End If
End Sub

For Numeric fields

Code:
Private Sub YourNumericControlName_AfterUpdate()
If Not IsNull(Me.YourNumericControlName.Value) Then
YourNumericControlName.DefaultValue = Me.YourNumericControlName.Value
End If
End Sub

For Date fields

Code:
Private Sub YourDateControlName_AfterUpdate()
If Not IsNull(Me.YourDateControlName.Value) Then
YourDateControlName.DefaultValue ="#" & Me.YourDateControlName & "#"
End If
End Sub

 

[missinglinq]

Glad you found the code and that it helped! I cannot, in my wildest dreams, imagine why the Micro$oft "gurus" would think their convoluted scheme would be the way to accomplish this relatively simple task!

Linq

P.S. Bob, congratulations on the BoSox' sweep!

 

[Oldsoftboss]

You need to check for new record, because if you are scrolling through existing records that are correct, they may update.

Making the next field in records default to the last record. Using the UserName field as an example....


In the After Update event of the UserName field put code like this:
Me![UserName].Tag = Me![UserName].Value

In the On Enter Event of the same field put code like this:
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![UserName].Tag) Or Me![UserName].Tag = "") Then
Me![UserName].Value = Me![UserName].Tag
End If

The UserName code will not carry over into your next session with the form but will work as long as the form is open....

Dave

 

[jdncoke99]

Thanks for that Dave (Oldsoftboss) but it appears not to alter the existing records, when I scroll through them, which I have many times after adding new fields.

 

[bikeshox13]

Thanks Oldsoftboss for the code, it works great. How would I modify the code to handle blank fields, and carry those over to the next form?

 

[Jason1]

Alright guys,

I found this post through a seach for this situation. I have followed these steps and code to the letter. It still does not work for me. I am running Access 2007. I am very new to VB code. Can someone walk me through this step by step.

Thanks

 

[jen18126]

I have tried multiple solutions for this and can get some of my fields to populate and some not..

I used this below and it works, however the value does not show in the field until you actually put your cursor in the field.. is there anyway to change that?

Me![UserName].Tag = Me![UserName].Value

In the On Enter Event of the same field put code like this:
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![UserName].Tag) Or Me![UserName].Tag = "") Then
Me![UserName].Value = Me![UserName].Tag
End If


I also have tried using the above code and receive an error (Object Doesnt Support this Property or Method).. The field is a combo box, but another combo box works with the above code..

any help would be great.. Thanks