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

Posted on 2012-09-09
Medium Priority
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
  • 4
  • 3

Author Comment

ID: 38381278
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

ID: 38381304
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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 38382581
"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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Author Comment

ID: 38382920

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 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38382945
"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

ID: 38382981
"I SEE" said the blind man!!!!!!!!!!!!  WORKED instantly!!!!!!!!!

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

Expert Comment

by:Dale Fye
ID: 38383287
Glad I could help.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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