How to duplicate a record and auto-adjust only 1 field from another table

[markzaal]

Hi,

I am trying to create a form with about 20 fields, with only 1 date field.
Each time a user fills in the form, most values (but not all) are the same as the day before, so I have it duplicate on open. I would like the date value to be automatically adjusted, keeping all other fields untouched. The date that should be filled in is always the last value from a different table.
Does anyone know how to do this?
I use Access 2010...
Many thanks!

 

[JHB]

Place the code in the form open, I don't know if you think of the newest date or last record in a table, therefor I've included code for both, remove the one you don't need.

Private Sub Form_Open(Cancel As Integer)
  Dim dbs As Database, rst As Recordset
  
  Set dbs = CurrentDb
  'If the date you want [FONT=&quot]should [/FONT]be the newest date
  Set rst = dbs.OpenRecordset("SELECT Max(YourDateFieldName) AS MaxDate FROM YourDateTableName")
  Me.YourControlName = rst![MaxDate]
    
  'If the date you want [FONT=&quot]should[/FONT] be the last record in a table
  Set rst = dbs.OpenRecordset("YourDateTableName")
  rst.MoveLast
  Me.YourControlName = rst![YourDateFieldName]
End Sub

 

[markzaal]

Hi JHB,

Thanks for the response!

The code does however give me a Compile error: Type mismatch, at "Set dbs"...
Any thoughts?

 

[JHB]

Maybe set it to a DAO database!

Dim dbs As dao.Database, rst As dao.Recordset

 

[markzaal]

Now it says I can't assign a value to this object...

 

[JHB]

Ahh - never heard about that before at that stage and I can't get the same error pop up here no matter what I do.
I'll send you a sample database - open the only form in that and see if you get any errors.

 

[markzaal]

I get no errors.
In your DB the MaxDateField is an unbound textbox. In mine it has a control source (same table as where the rest of the data from the form go into). If I remove the control source it does fill in the correct value in the text box. But then it does not get filled into the table that I am trying to fill with my form...

 

[spikepl]

Just to set one thing straight:

  'If the date you want [FONT=&quot]should[/FONT] be the last record in a table   
Set rst = dbs.OpenRecordset("YourDateTableName")   
rst.MoveLast

This is not correct. .MoveLast will indeed take you to the last record of the RECORDSET. But, there is not guarantee that that will correspond to the last record of the table, as you perceive "last record". Because tables have no inherent record order. If you wish to obtain a "last" record you have to have the records sorted in some specific order, so as to have a consistent "last" record.

 

[markzaal]

I do want it to go to the last record I have added, not specifically the last date or other value.
I also found a workaround for the last problem. I added an unbound control that accepts the last date value and after update have this filled into the date field I wanted updated.
Thanks for your help!!

Code:

Private Sub Form_Open(Cancel As Integer)

DoCmd.GoToRecord , , acLast

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ProjectInfoT", dbOpenDynaset)
rs.MoveLast
Me.DistrDPRDate = rs![DPRDate]

End Sub

 

[spikepl]

You have ignored the point about tables not being ordered, if ProjectInfoT is a table, and not a query. Is it because you do not understand my advice? Or is it because it seems to work OK as is, so you assume that that will always be the case?

 

[markzaal]

What I did was add an autonumber ID field to the table. Each time a new record is created it creates a number higher than the last one right? I have the table ordered on this number and then have it select the last one. Would this be good solution?

 

[spikepl]

Each time a new record is created it creates a number higher than the last one right?

No. The autonumber is quaranteed unique, but not always incrementing or even positive.

I have the table ordered on this number

No you don't. A table has NO INHERENT ORDER!. That the display of it is ordered in some fashion, according to what you specified in the design view, has no bearing on in which sequence the rows of the tables are extracted. For display of the table, the rows are ordered because the system runs a select query with order as specified by yoo. But if you open the table in code, the sequence of rows is not quaranteed the same each time, unless you use a SELECT query to get the rows, with an ORDER BY clause.

For sequence use your own sequence number, or date/time.