Auto populating fields within tables

[tonycl69]

Not sure if this is for the forms thread or tables, think I am in the right place I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there. Does this make sense. Cheers

 

[Dmak]

How is the data in the tables related?

You shouldn't really be storing duplicate values across tables.

If you want the value to appear alongside data from another table I'd recommend creating a query that combines the data from both tables.

 

[tonycl69]

Yes I know you shouldnt have duplicate valuse, let me explain. It is a vehicle database, one table holds the vehicle info, another keeps the work completed on that vehicle and another keeps the test dates of servicing etc. Everything works brilliantly sao worries there, I am just being lazy I guess, it is a want not a need, if a vehicle has been serviced I put that date in the tests table, and in the work completed on the same date it could have had all sorts done to it, was just wondering if the date I type in the tests form can automatically be entered into the work completed so when I go back I dont have to type it in twice, does that make sense.

 

[Dmak]

Ahhh, kind of with you here.

Personally I set my date fields default value to =Now() so every time a new record is added it adds the date automatically. Does this help? There are other options if it's no use.

 

[tonycl69]

Can't do that becuase the jobdate is sometimes different from the service date, the jobs whether they be inspections or services or just brake pad change are taken from the work sheets so they are never today (now()) if you know what I mean.

 

[Dmak]

If the date entry for the Tests is via a textbox you could add something like this to the AfterUpdate event property

Private Sub tboTestDate_AfterUpdate()

TempVars.Add "TestDate", tboTestDate.Value

DoCmd.SetWarnings False
DoCmd.RunSql "INSERT INTO tblworkdone ( WorkDate ) VALUES ( " & [TempVars]![TestDate] & " )"
DoCmd.SetWarnings True

Me.sfmWorkDone.Requery
Me.sfmWorkDone.SetFocus

I'm assuming field and control names here, you would have to change.

Also, if you're entering via a table. There's probably something you can do but I don't know how at the moment.

Depending on how data is fed to your subform you might need to add;

Forms![Main]![SubFormName].Form.Recordset.MoveLast