Question Text box on form: how can I make textbox date default to previous record

[mahunter]

All,

I'm having trouble with a Form and getting a text box entry box (Date entry) default to the date entered on the last record, which is linked to a table.

The only way I can get it to work is to type the date into the Text Box's Default property, for example, #11/13/13".

Can anyone help me get the correct syntax or code into the Default property of the Text Box to make this work? I only want the entry to change versus the previous record/entry if the User changes / enters a new date.

Thanks and I'd appreciate any help/expertise here!

Mark

 

[CJ_London]

try in your date afterupdate event put (change myDate to the name of your date control name)

myDate.Default=myDate

This will only work providing you do not close the form between entering new records

 

[The_Doc_Man]

CJ has it right. I'll say it another way just so that you understand what you really asked.

This issue always comes up. You need to understand that technically, there is no previous record except when you have defined (explicitly or implicitly) a sort order. When you traverse a recordset, inherently there is STILL no next or previous record available for you to examine. Recordsets may have an order, but Access doesn't care.

However, you can simulate a "previous" record by the use of VBA code to capture the value last used chronologically in the text box (perhaps capturing it in the text box's LostFocus routine) and make a test for asserting that value in the same text box's GotFocus routine if the box is blank.


The first time you open the form, there is no "previous" record.

 

[mahunter]

Thanks for the knowledge sharing and expertise here.

Could I instead pull from the last value in the Table column/field that the form is linked to? How would I do that?

Really this date (which is the date of a piece of machinery installation that last 3 months or so will only change on the basis, i.e., every 3 months so it's a waste of time for the operators to keep reentering on a daily basis.

Also, the operators do often close the database and re-open as the Access database is on a computer used for other applications as well.

Thanks,
Mark

Thanks,
Mark

 

[CJ_London]

you can do using dlast, but you need to define last otherwise you can get a random value - see this link

http://office.microsoft.com/en-gb/access-help/dfirst-dlast-functions-HA001228823.aspx

To define last you need to refer to a value in a criteria which might be an autonumber field or a timestamp for example.

e.g.

SELECT mydate from mytable where id=(Select max(id) from mytable as tmp)

You also need to think about whether someone changing a prexisting record should be 'last'

 

[Pat Hartman]

mahunter,
You keep talking about "last" but as we are trying to tell you, that is not static. "Last" is a state of mind. Usually when people say "last" they mean highest value or most recent. If you have an autonumber primary key, you can find the most recent record by using DMax() to get the largest autonumber value from the table. You can then use DLookup() to find the date in that record. But DLast() will get you random results.

 

[David R]

A less complex solution is the Ctrl+' syntax, which will draw the value from the previous record according to your current sort order. It does require a bit of training for the user, though.

 

[Pat Hartman]

Ctrl+ works but only for the second and subsequent records since the form was opened. When you open the form, there is no previous record to copy from. So if you open and close during the day, the "first" record each time would have to be manually entered.

 

[David R]

Ah, I missed that 'date entry' was probably supposed to be 'data entry'.

mahunter: Why are you storing the same installation date on each record for three months, anyway?

 

[Dewi]

Create a Table .Call it TblDateStore. have 2 fields, a primary, auto number field [ID], and a field called [lastDate]

On the After Update event of the date field (I'll call it [date]) you are interested input..

Dim SQLNewItem As String
DoCmd.SetWarnings False
SQLNewItem = "insert into TblDateStore (LastDate) Values ('" & Forms!formname![subformname].Form.[date] "')"
DoCmd.RunSQL SQLNewItem
DoCmd.SetWarnings True.

Then have a control on your form whose control source uses a dlookup function to look for the last record in TblDateStore by looking for the biggest value of [ID]

[text1]=DlookUp("LastDate", "TblDatStore", "ID=" & Dmax("ID", "TblDateStore", "") )

you now have the last value on your form, so you can either default your date field to or use a button to assign it by the operator.

......something like that.
Cheers
D