Setting Form Focus Properly

[Stang70Fastback]

Here is my current setup: I have a Main Form with a tab control. On each of the six tabs is a SubForm that takes up the entire tab. So basically I have a tabbed interface for my forms, just with a bit more control over the layout than if I just opened the forms separately in the Access window.

Anyway, on one of those subforms is a button. When you click it, it opens a separate form in a pop-up window, which is used to find a specific record. Once that record is found, you can click another button, and the idea is that this pop-up window closes, and you return to the underlying form, which jumps to the record you selected.

So far so good. Now, if I am working on my program in Access, and I open that subform's source form separately in its own tab, click the icon to open the record finding form, find a record, and then click the button to load that record, the following code runs, and it runs flawlessly - closing the pop-up window, and passing the information back to the main form which displays the proper record:

Private Sub ViewRecord_Click()

Dim RecordID As Integer
RecordID = Me.ID
DoCmd.OpenForm "CDLExam", , , "ID = " & RecordID
DoCmd.Close acForm, "CDLExamCONT", acSaveNo

End Sub

Where my program falls apart is that, in actual use, this form is not open on its own. It's open as a subform on one tab of a tab control on a Main Form. So the third line of code falls apart. Access thinks I want to open CDLExam separately, but it can't because it's already open in the subform, so instead I just end up back at the main window like I want, but the form fails to move to the proper record. Basically, line 3 just doesn't do anything.

How can I make this work? I tried replacing "CDLExam" with the name of the main window, but then it tries to move to the record in the main window, which throws an error as the main window doesn't even have a record source attached to it.

I hope I was clear in what I'm trying to do. In a nutshell, I am wondering how to refer to the SUBFORM which has CDLExam open, and tell THAT to move to the proper record.

 

[burrina]

I would think that maybe something along this line might work for you. Assuming of course that both ID's are named RecordID.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CDLExam"

stLinkCriteria = "[RecordID]=" & Me![RecordID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

HTH

 

[Stang70Fastback]

So that does the same thing my code does.

As an update, it turns out that my third line is actually doing SOMETHING. I couldn't tell because I hide the main Access window, but it DOES open the correct form in its own tab, to the correct record, in the main Access window. So, basically, everything works fine, just instead of returning focus to the window behind it, which already has that form open as a subform, it's opening the form all over again in the background.

For what it's worth, I was able to properly set focus back to my subform by adding two lines of code. The focus returns properly, so maybe all I need now is regular code you'd use to display a specific record. I've tried the bookmark function that's floating around the internet, but that doesn't seem to work right for some reason.

Private Sub ViewRecord_Click()

Dim RecordID As Integer
RecordID = Me.ID

DoCmd.Close acForm, "CDLExamCONT", acSaveNo
Forms!MAINWINDOW!CDLExamSUB.SetFocus
'DoCmd.OpenForm "CDLExam", , , "ID = " & RecordID (Still doesn't work)

End Sub

 

[vbaInet]

The main point I got out from your post is this (and your code):

Anyway, on one of those subforms is a button. When you click it, it opens a separate form in a pop-up window, which is used to find a specific record. Once that record is found, you can click another button, and the idea is that this pop-up window closes, and you return to the underlying form, which jumps to the record you selected.

Short and sweet is best

Your method won't work because the form is already opened. First we need to know if the subform you're trying to filter is linked to the parent form?

 

[Stang70Fastback]

Yeah, I kind of blabbered on. Just wanted to make sure I was giving you guys as much as I could. The subform isn't linked in any way to the parent. The parent form's sole purpose is to be a physical GUI to manage the subforms.

To be clear, I don't want to FILTER the subform down to the one record, because I still need all the records to be available. I just want to tell it to move to XX record based on the ID field. I imagine it can't be difficult. I can set the focus back to the subform... surely there is a simple way of telling it which record to display.

 

[vbaInet]

To be clear, I don't want to FILTER the subform down to the one record, because I still need all the records to be available. I just want to tell it to move to XX record based on the ID field.

Ok, good you made this clear too.

Do you know how to use recordsets? The code goes something like this (aircode):

dim rst as dao.recordset
dim frm as form

set frm = Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]SubformControlName[/COLOR].Form
set rst = frm.recordsetclone

with rst
    .findfirst "[COLOR="blue"]ID [/COLOR]= " & frm.[COLOR="Blue"]RecordID[/COLOR]

    if not nomatch then
        frm.bookmark = .bookmark
    end if
end with

set frm = nothing
set rst = nothing

All it does is it gets a clone (or copy) of the subform's dataset, it finds the record, and if a record is found it sets the subform's bookmark to that of the cloned bookmark (a bit syncing it).

Edit the bits in blue to suit.

 

[vbaInet]

Or use DoCmd.GoToRecord

 

[Stang70Fastback]

Would that looks something like this?

Dim RecordID As Integer
RecordID = Me.ID

DoCmd.Close acForm, "CDLExamCONT", acSaveNo
Forms!MAINWINDOW!CDLExamSUB.SetFocus

DoCmd.GoToRecord acDataForm, Forms!MAINWINDOW!CDLExamSUB.Form, acGoTo, RecordID

 

[vbaInet]

The same way you pass criteria in the OpenForm method is the same way you'll pass criteria here.

 

[Stang70Fastback]

So when I try this method it says that GoToRecord isn't available. I have the focus set to the subform, so I don't know why it wouldn't work...

 

[vbaInet]

Sorry wrong method, I meant DoCmd.FindRecord.

In any case, use the first method I showed you.

 

[Stang70Fastback]

Ok. I'm trying and still having issues. I apologize for being so stupid.

Here is what I came up with:

Private Sub ViewRecord_Click()

Dim RecordID As Integer
RecordID = Me.ID

DoCmd.Close acForm, "CDLExamCONT", acSaveNo
Forms!MAINWINDOW!CDLExamSUB.SetFocus

Dim rst As dao.Recordset
Dim frm As Form

Set frm = Forms!MAINWINDOW!CDLExamSUB.Form
Set rst = frm.RecordsetClone

With rst
.FindFirst "ID = " & frm.RecordID

If Not NoMatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing
Set rst = Nothing

End Sub

I get an application/object defined error on the highlighted line.

 

[vbaInet]

Did you Debug > Compile in the VBA window? Why is dao still lowercase.

 

[Stang70Fastback]

I did, without issue. It didn't change a thing!

 

[vbaInet]

What is the record source of the subform? A table or query?

 

[Stang70Fastback]

The record source is a table. I wonder if the fact that the Table (CDLExam) has the same name as the form (CDLExam) might be an issue...?

 

[vbaInet]

It needs to be changed to a query.

 

[Stang70Fastback]

I can make its source a query, but won't that mean I can no longer add or edit records?

 

[vbaInet]

I can make its source a query, but won't that mean I can no longer add or edit records?

You could have simply tested this yourself.

 

[Stang70Fastback]

I did. That works, but I'm still getting the same object defined error... I appreciate your help, but I'll just see if I can do things another way. I'm not sure why this is so difficult, but I've been trying to get this working for the better part of the day and I'm running out of patience.