SET TEXTBOX VALUE TO LAST SAVED VALUE

[Shafique]

Hello Everyone
i want to show in textbox its previous saved record's value
e.g......
ID txtdate
1 19-07-22
2 ID 1 = txtdate (19-07-22)

 

[Micron]

Not really possible to solve with only one piece of sample data. According to your post, if you have

ID	DateVal
1​	1/01/22​
2​	1/02/22​
3​	1/03/22​
4​	1/04/22​
5​	1/05/22​

the previous to the last "saved" record value is likely 01/04/2022 - sometimes. However, if I edit the records thus:

ID	DateVal
1​	1/01/22​
2​	1/02/22​
3​	1/15/22​
4​	1/04/22​
5​	1/05/22​

then the last saved record is ID 3 so the previous value is 01/02/2022.

Regardless, you will probably have to create a sub query. How that would look depends on what it is you exactly want.

 

[CraigYellick]

The Access event model for bound controls is pretty slick. You could capture the previous value in the BeforeUpdate event, like so:

Dim previousValue

Private Sub txtExample_BeforeUpdate(Cancel As Integer)
  previousValue = txtExample.OldValue
End Sub

 

[Shafique]

Not really possible to solve with only one piece of sample data. According to your post, if you have

ID	DateVal
1​	1/01/22​
2​	1/02/22​
3​	1/03/22​
4​	1/04/22​
5​	1/05/22​

the previous to the last "saved" record value is likely 01/04/2022 - sometimes. However, if I edit the records thus:

ID	DateVal
1​	1/01/22​
2​	1/02/22​
3​	1/15/22​
4​	1/04/22​
5​	1/05/22​

then the last saved record is ID 3 so the previous value is 01/02/2022.

Regardless, you will probably have to create a sub query. How that would look depends on what it is you exactly want.

YES.
you are Right. then what to do in backend code that when i start posting in ID 6 row and in txtdate textbox get automatically ID 5's txtdate value (1/05/22)

 

[Micron]

If
- primary key field of form record is named ID
- form control that gets the value you want is named txtDate
- table date field is named DateVal

then try using the form AfterUpdate event as per code below. This would only work where a new record is being created, which is what you seem to be asking for:

If Me.NewRecord Then
     txtDate = DLookup("[DateVal]", "[Table Name Here]", "ID = " & Me.ID - 1)
End If

 

[Shafique]

If
- primary key field of form record is named ID
- form control that gets the value you want is named txtDate
- table date field is named DateVal

then try using the form AfterUpdate event as per code below. This would only work where a new record is being created, which is what you seem to be asking for:

If Me.NewRecord Then
     txtDate = DLookup("[DateVal]", "[Table Name Here]", "ID = " & Me.ID - 1)
End If

thanks for your effort
I tried this code same as you typed but it comes with this error massage
syntax error (missing operator) in query expression 'ID='.

So i am sending you my table design view where i am get stuck. the DateVal Field is in my table named with PRDATE

 

[Micron]

I just remembered there is no guarantee that the prior ID value is one less than the record being added. This should be safer to use:
txtDate = DMax("[DateVal]", "[Table Name Here]", "ID < " & Me.ID)

What you really want is the Max of the value that is less than the value just added.

Edit - Saw that you had posted just after I hit save. Will look at what you posted now.

 

[Micron]

Many others have written that they tried what was posted, only to find out that is not really the case. Post your code attempt please.
and ignore last posted suggestion. It will be wrong too. Working on it.

 

[Shafique]

I just remembered there is no guarantee that the prior ID value is one less than the record being added. This should be safer to use:
txtDate = DMax("[DateVal]", "[Table Name Here]", "ID < " & Me.ID)

What you really want is the Max of the value that is less than the value just added.

Edit - Saw that you had posted just after I hit save. Will look at what you posted now.

SAME error
syntax error (missing operator) in query expression 'ID<'.

 

[Micron]

txtDate = DLookup("PRDDATE", "MDFPRD", "ID = " & DMax("ID", "MDFPRD", "ID < " & Me.ID))

Our posts are crossing. Note that the red/blue parts are associated with each other.

Firstly, I after looking at your table pic I realized that you need a date based based on the ID that is the highest value but is less than the current record ID value.
So post your actual code or see what happens if you put in any valid ID number instead, such as

txtDate = DLookup("PRDDATE", "MDFPRD", "ID = 5"). Also, form must have the ID field on it, otherwise Me.ID will raise an error. If not there, try adding it (you don't have to make it visible). The PK field should always be on a form/report in case it becomes necessary - such as in this case.
EDIT - company coming; not sure how much time I will have for this today.