Recall previous values on a form

[Thermalmonster]

Hi



I have a form which I want to recall the value of the previous record when a new one is made.
the code im using is fine until access or the form is closed down.
on reopening it does not display the previous record
There are 30 fields to recall so i dont want to retype them
Any assistance apperciated

Cheers

Heres my code
Private Sub CD1_AfterUpdate()

If Not IsNull(Me.CD1.Value) Then
CD1.DefaultValue = Me.CD1.Value
End If
End Sub

[June7]

Code in the form Open or Load or Current event could retrieve the last record in a recordset object and loop through the recordset fields and set the DefaultValue property of each control.

[Missinglinq]

You have the right idea, but your syntax is a little off. It should be

Private Sub CD1_AfterUpdate()
   Me.CD1.DefaultValue = """" & Me.CD1.Value & """"
End Sub

This syntax is valid for Text, Number, DateTime and Boolean Datatypes.

Linq ;0)>

[Thermalmonster]

Many thanks I will try that Syntax

[Thermalmonster]

Missingling,

Your syntax still does not solve the problem of recalling the record once the form has been shut down?

[June7]

See post 2 for methodology. Do you know about recordsets in VBA?

[Thermalmonster]

See post 2 for methodology. Do you know about recordsets in VBA?

No Im only just beginning my Access & Code journey :-(

[June7]

Review http://allenbrowne.com/ser-29.html

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM MyTable WHERE ID IN (SELECT Max(ID) FROM MyTable);")
Me.CD1.DefaultValue = "'" & rst!CD1 & "'"
Me.AnotherControlName.DefaultValue = "'" & rst!AnotherFieldName & "'"

Continue the code for each control you want to set. Using ID in the query assumes latest record will have the maximum ID. Use whatever field from your table that is the equivalent to the unique ID.

[Missinglinq]

Sorry, missed the part about closing then re-opening the form! In this case June7's approach is the right one, assuming that you have an ID field that is always incremented with each new record. A date/time created field would be even better! ID fields are frequently made using Autonumbers, and these can get out of order, at times. Autonumbers should only be used as unique identifiers, and even this can be problematic, at times.

Linq ;0)>

[Thermalmonster]

Thanks Folks I'll Give that a whirl this morning

[Thermalmonster]

If My Table is called TUNNEL INFO 100

Should the code read

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT *TUNNEL INFO 100 (SELECT Max(ID) TUNNEL INFO 100);")
Me.CD1.DefaultValue = "'" & rst!CD1 & "'"
Me.CD2.DefaultValue = "'" & rst!CD2 & "'"


or was FROM part of the code?

Also does this code sit in the After event location ?

Many Thanks

[June7]

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [TUNNEL INFO 100] WHERE ID IN (SELECT Max(ID) AS MaxID FROM [TUNNEL INFO 100]);")

Names with spaces must be enclosed in []. Avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

What is the name of the unique identifier field in the table? I use ID as an example.

As noted in post 2, use form Open or Load or Current event

[Thermalmonster]

Worked a treat thank you