Reference Subform 2 levels deep

Question

I'm have a couple of problems.  The first is with referencing a Subform within another Subform.  The Main Form is called frmTimeCard, within it is the Subform named frmClockIn, and within it are two forms, frmClockOut and frmClockIn_Sub.  There are a few bits of code I'm having trouble with, but I think if someone can put me on the right path I can fix the rest.  Upon clicking the button to Clock out I want the frmClockIn_SUB to requery and refresh, but nothing happens.  Below are codes I've tried with no success.  First note the button is located on the frmClockIn_Sub. 

I've tried all of the following:

Forms("frmTimeCard").Controls("frmClockIn").Form.Controls("frmClockIn_SUB").Form.Requery

Forms("frmTimeCard").Controls("frmClockIn").Form.Controls("frmClockIn_SUB").Form.Refresh

Forms!frmTimeCard!frmClockIn.Form!frmClockIn_SUB.Form.Requery

Forms!frmTimeCard!frmClockIn.Form!frmClockIn_SUB.Form.Refresh

Forms!frmTimeCard.frmClockIn.Form.frmClockIn_SUB.Requery

Forms!frmTimeCard.frmClockIn.Form.frmClockIn_SUB.Form.Refresh

I can only get the form to update by exiting or by clicking the Refresh icon on the Home tab.

My second problem is somewhat similar and has to do with clicking the ClockIn button, which is located on the frmClockIn.  Below you will find the code I'm using.  Most of it runs fine except what's in bold.

Me.frmClockIn_SUB.SetFocus

Me.frmClockIn_SUB!TimeID.SetFocus

DoCmd.GoToRecord , , acNewRec

Me.frmClockIn_SUB!TimeIN = Now

Me.frmClockIn_SUB!DayName = Date

Me.frmClockIn_SUB!PayPeriodID = Forms!frmTimeCard!frmClockIn.Form.PayPeriodID

MsgBox "You have successfully clocked in at " & Now(), vbOKOnly

RunCommand acCmdSaveRecord

All seems to run smoothly, except it's not adding the PayPeriodID to the table.  Note: PayPeriodID is on the frmTimeCard and has an AutoNumber data type, which could be the issue, just not sure.  If so, not sure how to fix it.

Please help,

Mike

Answer 1

Your suggestion in the email was the best route to go by using an Insert Into statement.  Below is what I ended up doing:

Dim strSQL As String

Dim intPayPeriod As Integer

Dim datTimeIn As Date

Dim datDay As Date

intPayPeriod = Me.PayPeriodID

datTimeIn = Now

datDayName = Date

strSQL = "INSERT INTO tblEmployeeTime([PayPeriodID], [TimeIn], [DayName]) " & _

    "VALUES(" & intPayPeriod & ", #" & datTimeIn & "#,#" & datDayName & "#)"

CurrentDb.Execute strSQL, dbFailOnEror

MsgBox "You have successfully clocked in at " & Now(), vbOKOnly

RunCommand acCmdSaveRecord

Me.frmClockIn.Requery

Answer 2

That's strange.  The title bar says 2007-2010.  The link was from the website I got it from, so it's not actually my modified version; it was the original.  The actual website is http://smallbiztlc.com/ and the link to the application is at the bottom of the page.  In the mean time, I'll re-save as 2007 and send in an email.

Answer 3

I'm afraid I can't open your file.  You'll need to convert it to Access 2007 format for me to be able to do so.

Answer 4

I'm sorry, my initial post was completely accurate.  The ClockIn button is on the frmClockOut form.  Also, your suggestion of using: Me.frmClockIn_SUB.Form!PayPeriodID = Me.PayPeriodID is actually what I initially began with before trying all the other options I listed above.  When the ClockIn button is clicked the following code works fine

Me.frmClockIn_SUB!TimeIN = Now

Me.frmClockIn_SUB!DayName = Date

but Me.frmClockIn_SUB.Form!PayPeriodID = Me.PayPeriodID  does not.  When I look at the table that holds this data, the field for TimeIN and DayName have the data, but PayPeriodID has nothing.  Also, for some reason, which I didn't indicate earlier, after the button click, the frmTimeCard immediately goes to a new record.  There's some strange behavior happening here.

Note: this is an application that I downloaded from the web time\_clock\_task\_tracker.  I've made a few minor adjustments to fit my needs, like adding a table for pay periods and one more form for the pay periods. 

My structure is like this:  frmTimeCard record source is Teachers, frmClockIn record source is a query based on the tblPayPeriod and the two are linked by TeacherID..  frmClockIn is a subform of frmTimeCard.  The other two subforms (frmClockOut and frmClockIn_Sub) are subforms of are frmClockIn and are linked by PayPeriodID. 

Any thoughts on what might be happening or what I'm doing wrong?

Answer 5

If, as you say, the Clock Out button is located in frmClockIn_Sub, and it is frmClockIn_Sub which you wish to Requery, then you simply need Me.Requery.  Also there is no need to Refresh the form in addition to requerying it.

Similarly, with the Clock In button, which you say is located in frmClockIn you can reference the form (or more accurately the instance of the class in which the code is executing) with the Me key word:

Me.frmClockIn_SUB.Form!PayPeriodID = Me.PayPeriodID

Referencing the Form property of the subform control isn't essential, but I would normally include it for clarity.