Default values from table to subform 1

[HRick]

I am trying to build a database to keep track of workers hours and prepare multi-year billings to client and am having problem with getting a default value into a subform.

Have 2 tables
tblLaborClass with
ClassNo pk
ClassName

tblLaborRate with
RateID pk
ClassNo
RateStart (date)
RateEnd (date)
Rate
Have a form
LaborClass with
ClassNo pk
ClassName
With a subform
tblLaborRate Subform with
RateStart (date)
RateEnd (date)
Rate

When calling up a LaborClass on the form, would like the tblLaborRate Subform to default to the latest (largest) RateStart date value from the tblLaborRate fields for that LaborClass.

When adding a new record to the tblLaborRate Subform, would like the default values for RateStart and RateEnd to be the latest (largest) date value from the tblLaborRate fields.

Hope this makes sense. Any help would be appreciated.

HRick

 

[Aerin]

write the public function(s) for defining the largest date in tables (DMax or create the recordset order by date DESC and take the very first one. DMax works slow, recordset could be not good solution for big tables and network databases)and put it to the default value property of the target fields in subform.
=FuncName()

 

[wemeier]

HRick,

Your first request is easy. When you change records on the master form you can add code behind that form to change the current record that displays in the subform.

To default to the record with the latest RateStart date, and save that date and its associated RateEnd date for new records, you could do the following:

First, add two hidden, unbound fields to the master form (we'll call them dtmRateStart and dtmRateEnd). You can put them anywhere you want.

Add an Event Procedure to the OnCurrent event of your master form and add the following coding to subroutine:

Private Sub OnCurrent()
    Dim rst as Recordset

    Me.dtmRateStart = DMax("RateStart","tblLaborRate","ClassNo = " & Me.ClassNo) [COLOR=green]' find the latest RateStart within tblLaborRate for all records with a ClassNo equal to the ClassNo on the current master form[/green]
    set rst = Me.sfrRates.RecordsetClone [COLOR=green]' create a recordset of the subform's recordset clone[/color]
    If Not rst.EOF Then [COLOR=green]' if there are records in the subform...[/color]
        rst.FindFirst "RateStart = #" & Me.dtmRateStart & "#" [COLOR=green]' within the recordset clone find the record that contains the latest RateStart that you found[/color]
        Me.sfrRates.Bookmark = rst.Bookmark [COLOR=green]' set the subform's bookmark to the recordset clone's bookmark, making it the subform's current record[/color]
        Me.dtmRateEnd = rst!RateEnd [COLOR=green]' capture the RateEnd from that record as well[/color]
    End If
    rst.Close [COLOR=green]' close the recordset[/color]
End Sub

I'm stil working on setting the default value in the subform, but I'm having trouble figuring out where to put the code.

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[wemeier]

HRick,

I have a solution for providing the default values to your new records. It's not particularly elegant or efficient (because the code is invoked EVERY time you switch records in the subform), but it works:

Private Sub Form_Current()
    If Me.NewRecord And Not Me.Dirty Then [COLOR=green]' if you are currently on a new record and you haven't entered anything in the record yet[/color]
        Me.RateStart = Me.Parent.dtmRateStart [COLOR=green]' set the new record's RateStart to the saved RateStart hidden on the master form[/color]
        Me.RateEnd   = me.Parent.dtmRateEnd [COLOR=green]' and set the new record's RateEnd to the saved RateEnd hidden on the master form[/color]
    End If
End Sub

Again, this code will execute EVERY time you switch records on the subform, but will only set the dates if the record is a new record that has not been modified by the user yet.

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[HRick]

wemeier:

We are getting close. I pasted the first code for "recordset", but it appears to be missing something. It hangs up at "set rst = Me.sfrRates.RecordClone". It says there is a Compile Error - Method or Data Member not Found and the "sfrRates" is highlighted.

Any ideas
Rick

 

[Aerin]

Try Me.sfrRates.Form.RecordsetClone

 

[wemeier]

Rick,

Replace sfrRates with the name of the object on your main form that contains the subform.

Wally

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]