Referencing a form on a Navigation form

[CanWest]

I am creating a database in Access 2013
I have created a Navigation form using the Navigation form tool and called it frmMain
I have added two forms to the tabs on the navigation form called frmClients and frmEmployers

On the frmEmployers form there is a command button (cmdNewRecord) that opens a modal popup form frmAddEmployer

This form allows the user to enter data for a new employer. On this form there is a save button which saves the record and closes frmAddEmployer

What I need to do now is go to the record of the employer I just created. On the save button of the frmAddEmployer form I have tried referencing the frmEmployers form so that I can use the DoCmd.GotoRecord option or the DoCmd.SearchForRecord option to accomplish this

Every one of the lines below generates an error that says the form is not open even though it is.

Is there something special I have to do with these new Navigation Forms. Any help will be greatly appreciated.

DoCmd.SearchForRecord acForm, "frmEmployers", "EmployerID=" & EmployerID
DoCmd.SearchForRecord acDataForm, "frmEmployers", "[EmployerID]=" & Me!EmployerID
DoCmd.GoToRecord acDataForm, "[Forms]![frmMain].[frmEmployers]", acGoTo, EmployerID
DoCmd.GoToRecord acDataForm, [Forms]![frmMain]![NavigationSubform], acGoTo, EmployerID
DoCmd.GoToRecord acDataForm, "Forms![frmMain].Form![NavigationSubform].Form[frmEmployers]", acGoTo, EmployerID

 

[arnelgp]

Forms![Navigation Form]![frmMain].SetFocus
Docmd.GotoRecord, , acLast

 

[CanWest]

Forms![Navigation Form]![frmMain].SetFocus
Docmd.GotoRecord, , acLast

So I tried this and no go

I tried a couple of variations of it

This one did not work at all because there is no form called Navigation Form

Forms![Navigation Form]![frmMain].SetFocus
Docmd.GotoRecord, , acLast

I found an article that suggests that the container on a Navigation form are reference with [NavigationSubform] So I tried this

Forms![frmMain]![NavigationSubform].SetFocus
Docmd.GotoRecord, , acLast

And finally I tried one more variation in desparation

Forms![frmMain]![NavigationSubform].[frmEmployers]SetFocus
Docmd.GotoRecord, , acLast

No luck at all

 

[arnelgp]

Forms![frmMain]![frmEmployers].SetFocus
Docmd.GotoRecord, , acLast

 

[CanWest]

Forms![frmMain]![frmEmployers].SetFocus
Docmd.GotoRecord, , acLast

That generates the error 'can't find the field 'frmEmployers' referred to in your expression'

 

[CanWest]

As you may remember I needed a way for the underlying for frmEmployers to go to the specific record after the new record was created. The form frmEmployers is not sorted by EmployerID but rather by Company name for ease of scrolling through the companies. Because of this acLast would not work but my thought was that was the easiest way to see if the code worked and if it did change it to acGoTo later.

Nothing seemed to work. I then remembered I had done something in another database a few years back. Found the code and modified it to fit this db.

This is the code

    Set rst = Forms!frmMain!frmEmployers.Form.RecordsetClone
    rst.FindFirst "EmployerID = " & Me.EmployerID
        If rst.NoMatch Then
            MsgBox "No entry found.", vbInformation
        Else
            Forms!frmMain!frmEmployers.Form.Bookmark = rst.Bookmark
        End If
    Set rst = Nothing

That generated the same error. It was if the db was treating Forms!frmMain!frmEmployers as a field rather than a form. The one thing to note is that frmMain is a Navigation Form created with the tool in Access with the same name.

So I tweeked the code a bit
See Below

    Set rst = Forms!frmMain!NavigationSubform.Form.RecordsetClone
    rst.FindFirst "EmployerID = " & Me.EmployerID
        If rst.NoMatch Then
            MsgBox "No entry found.", vbInformation
        Else
            Forms!frmMain!NavigationSubform.Form.Bookmark = rst.Bookmark
        End If
    Set rst = Nothing
            
    DoCmd.Close acForm, "pfrmAddEmployer", acSaveNo

I used the reference Forms!frmMain!NavigationSubform. It is interesting to note that there is no form called NavigationSubform. This is the name of the Container that my subforms reside in frmMain

I hope this helps anyone else that is trying to figure this out

 

[arnelgp]

ok its hard to tell what is the parent form's name at this time, so can you go to design view of your form and on the On Load event of your subform, try this code, for temporary purpose just to know which parent form should are we dealing with:

Private Sub Form_Load()
MsgBox "Correct Reference to Subform: Forms![" & Me.Parent.Name & "]![" & Me.Name & "]"
End Sub

save and run the form.

 

[CanWest]

ok its hard to tell what is the parent form's name at this time, so can you go to design view of your form and on the On Load event of your subform, try this code, for temporary purpose just to know which parent form should are we dealing with:

Private Sub Form_Load()
MsgBox "Correct Reference to Subform: Forms![" & Me.Parent.Name & "]![" & Me.Name & "]"
End Sub

save and run the form.

And it reports that Forms![frmMain]![frmEmployers] is the correct reference.
BUT it does not work on any of the places I have tried.The only thing that does work is Forms![frmMain]![NavigationSubform]

Go figure!!!!

 

[arnelgp]

ok, well do with your later approach.
upon hitting the "Save" button of your frmAddEmployer add this to your code:

TempVars("ID") = Me.yourEmployerIDFieldHere

now, on the code after calling your frmAddEmployer add this code:

if not IsNull(TempVars("ID")) then

' pull new data from our recordsource (new or deleted)
Me.Requery

Dim rst as dao.recordset
set rst=me.recorsetclone

with rst
.findfirst "[yourIDField] = " & TempVars("ID")
If Not .NoMatch Then Me.BookMark = rst.BookMark
End with

set rst=nothing

End If

TempVars.Remove "ID"

 

[RCostolo2]

Alright I read through this because I feel like i've had to do something similar. It looks like you may have solved it but I thought i'd throw my 2 cents in.

When I need to reference the last record I've added to a table, especially if I don't need it for more than just the immediate task at hand I tend to lean towards functions to get the unique ID of that record. I've pasted some code below that, depending on the situation, I've used/tweaked in the past to pull the last record of a table.

Public Function LastValueAdded() As Integer

Dim db As DAO.Database
Dim SourceTable As DAO.Recordset
Set db = CurrentDb()
Set SourceTable = db.OpenRecordset("Your Table Here")
SourceTable.MoveLast
LastValueAdded = SourceTable("Field Name")
End Function

I'm not saying this is the prettiest way to go about it, and I certainly don't expect to impress anyone... but it's a pretty simple solution in regards to getting, and using, the last record you added to a table without having to mess with referencing forms or controls on forms... because I know quite well how frustrating that can be.

A couple things I've learned the hard way when it comes to referencing forms, subforms, and ESPECIALLY controls on subforms:

This website is the ultimate secret weapon for this kind of situation... especially if you have the memory of a squirrel like I do:
access.mvps.org/access/forms/frm0031.htm (I can't post links yet lol)

Also ... there are some instances where I've had to set focus multiple times in order to do what I was trying to do... like so:

Private Sub cmdLvCharged_Click()
Me!sfrm_Logg988_Ctrl_Panel.SourceObject = "sfrm_Leave_Charged"
Me!sfrm_Logg988_Ctrl_Panel.SetFocus
Me!sfrm_Logg988_Ctrl_Panel.Form.txtChargedLvNum.SetFocus
End Sub

So there you have it folks. The coding and musings of a simple man!

 

[spikepl]

I am sorry but


Code:

Public Function LastValueAdded() As Integer

Dim db As DAO.Database
Dim SourceTable As DAO.Recordset
Set db = CurrentDb()
Set SourceTable = db.OpenRecordset("Your Table Here")
SourceTable.MoveLast
LastValueAdded = SourceTable("Field Name")
End Function

can give you AT TIMES the last record added, but on many occasions it will NOT!

Tables and recordsets have no inherent order. If you do not specify a query with an ORDER BY then you can never ever be sure in what order you get the data.

So therefore the above method cannot be recommended.