Access - Tabbed Form - Form to Select Record for Another Tab

Posted on 2012-09-09
Last Modified: 2012-09-10
Hello All
Continuing the rewrite corrections of moving my separate forms to a Tabbed Form.

On my Main Tab where the clerk will select the reporting type, when they click a Control Button it Inserts a new record in a particular table [ShiftRptgLVLCtl].   That record now needs to be the one selected on the Page2 tab of my tabbed forms.

My tabbed form name is [frm_DataReporting] with a TabControlName of TabCtl_DataReporting.

The user clicks a Control button on the form on tab Name [Page1] page Index 0
I have code that on that click it inserts a new record in the [ShiftRptgLVLCtl] table

I thought... the last line of code would SELECT that new record on the Page2 Tab Index 1
but it errors and tells me:
Runtime error 2580
The record Source SELECT.... , specified on this form or report doesn't exist

Private Sub NewLVLControl()
Dim lngRptgShiftID As Long, lngShiftSeqID As Long, lngShiftRptgLVLCtlID As Long

lngRptgShiftID = GetMyShiftID()

    CurrentDb.Execute "INSERT INTO ShiftReportingLVLCtl (LocationID, LVLRptgTypeID) VALUES (" & Me.txtLocationNbr & "," & Val(Me.cboSelectedLVLRptgType) & ")", dbFailOnError
lngShiftRptgLVLCtlID = DMax("ShiftRptgLVLCtlID", "ShiftReportingLVLCtl", "ShiftRptgLVLCtlID")
Me.txtNewShiftReportingLVLCtl = lngShiftRptgLVLCtlID
lngShiftSeqID = Forms![frm_DataReporting]![WVLVLControlTotals].Form![ShiftRptgLVLCtlID]
Me.txtNewSeqID = lngShiftSeqID
Forms![frm_DataReporting]![WVLVLControlTotals].Form![txtNewShiftPrtgLVLCtlID] = lngShiftRptgLVLCtlID
Me.Parent!WVLVLControlTotals.Form.RecordSource = "SELECT ShiftRptgLVLCtlID FROM ShiftRptgLVLCtl WHERE ShiftRptgLVLCtlID =" & lngShiftRptgLVLCtlID

End Sub

Open in new window

Question by:wlwebb

    Author Comment

    I also tried this as the last line of code
     Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form![ShiftRptgLVLCtlID].RowSource = "SELECT ShiftRptgID, ShiftRptgLVLCtlID, RptgSeqID, LVLPositionNbr, AmtIn, AmtOut, NetAmt, AmtVal, MachinePulled, MachineClearChipped FROM ShiftReportingLVL WHERE ShiftRptgLVLCtlID =" & Forms![frm_DataReporting]![ ].Form![txtNewShiftReportingLVLCtl]

    Open in new window


    Author Comment

    Ok, I'm getting closer.... well at least I don't get an error now...  Replaced that last line with these two lines...
    DoCmd.FindRecord "Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form![ShiftRptgLVLCtlID].txtNewShiftReportingLVLCtl =" & Me.txtNewShiftReportingLVLCtl

    Open in new window

    By the way
    Parent.Page4. is Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form!
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    "By the way
    Parent.Page4. is Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form! "

    No, that is not correct.  

    Parent.Page4 is a tab on the tab control, which is displaying a form in a subform control.  

    It looks like you have the syntax wrong for your subform.  The general syntax for referencing subforms is:


    So I have used this syntax below.  If that form has a consistent RecordSource, which appears to have, then you will need to:

    1.  requery the form after doing the insert of a record.   Changing the forms recordsource would also do this, but is not necessary.  So you could use:


    2.  Then you need to filter the form, to focus on the newly added record.  You do this by creating a filter criteria

    strCriteria = "[ShiftRptgLVLCtlID] = " & lngShiftRptgLVLCtlID
    Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form.filter = strCriteria
    Forms![frm_DataReporting]![frm_ShiftReportingLVL].Form.filteron = true


    Author Comment


    Thanks for responding.  I think I understand that.  

    First, for the strCriteria, I need to Dim that as String, correct?

    Then on the requery,,,,, I put that code after these last two lines
    (these two appear to work because it makes that the visible form and the field AmtOut is the second field and it is the one selected, I used the second field just so I could tell that it was setting the focus instead of defaulting to the first field....

    but I'm getting an error:
    Run-time error '2465'
    Microsoft Office Access can't find the field 'frm_shiftReportingLVL' referred to in you expression.

    That seems odd...I checked the spelling of both [] form names and they're correct...

    Just as an FYI, originally, that subform had the Name frm_ShiftReportingLVL and Source Object frm_ShiftReportingLVL... when I couldn't get it to work (select the 5 records I wanted) I changed the NAME but not the Object to ShiftReportingLVL..  I would think that's correct but letting you know just in case.......
    LVL 47

    Accepted Solution

    "First, for the strCriteria, I need to Dim that as String, correct?"


    You need to use the name of the subform control, not the name of the actual subform that is displayed in the control.  I use a naming convention for all of my controls, for subforms, I give the control a name that looks like:


    That way, when I refer to the control, I know that I am referring to the actual control, not the form.  So, what is the new name of the subform?  That is what you need to put in here:


    2.  Then you need to filter the form, to focus on the newly added record.  You do this by creating a filter criteria

    strCriteria = "[ShiftRptgLVLCtlID] = " & lngShiftRptgLVLCtlID
    Forms![frm_DataReporting]![Subform_CONTROL_Name].Form.filter = strCriteria
    Forms![frm_DataReporting]![Subform_CONTROL_Name].Form.filteron = true

    Author Closing Comment

    "I SEE" said the blind man!!!!!!!!!!!!  WORKED instantly!!!!!!!!!

    Thank you!!!!!!  struggled with all kinds of combos because I was grasping at straws
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Glad I could help.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now