Data from previous record (1 Viewer)

mtagliaferri · Dec 17, 2006

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 · Dec 17, 2006

See this link on Microsoft:

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

mtagliaferri · Dec 18, 2006

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

Rickster57 · Dec 18, 2006

error

the code should be tbl1 not frm1

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

mtagliaferri · Dec 18, 2006

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 · Dec 18, 2006

slight correction:

Code:

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

mtagliaferri · Dec 23, 2006

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

boblarson · Dec 23, 2006

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:

Code:

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


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

jdncoke99 · Nov 5, 2007

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 · Nov 7, 2007

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 · Nov 7, 2007

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

jdncoke99 · Nov 7, 2007

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 · Nov 7, 2007

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 · Nov 7, 2007

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 · Nov 7, 2007

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 · Jan 21, 2010

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 · Feb 17, 2011

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 · Jan 27, 2012

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

Data from previous record (1 Viewer)

Registered User.

Attachments

Software Developer

Registered User.

Attachments

Registered User.

Registered User.

Smeghead

Registered User.

Smeghead

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

AWF VIP

Registered User.

New member

Registered User.

New member

Similar threads

Users who are viewing this thread