How to open a record inside a horizontal tab with a subform in it.

[ziad]

Dears
I am trying to reach the student ID within a form called studentsFrm embedded in a horizontal tab of the main form called Home.

So from outside the form, I need to call for a student number x and then the studentsfrm will go to this record.
Home form is open but the search for the student ID ( number ) is from outside the form , from another form.
thanks in advance.

so long story short, I want the subform to go to a record, but this subform is inside the main "home" form of a horizontal tab, there is no child or parent or anything, I am confused....

NB: I was trying to work with browseto I couldn't manage to find the right syntax, stuck in an run time error 6054, a valid path argument is of the form : Mainform1.subform1>form1.subform1
thanks a lot in advance

all the best
Zee

 

[sneuberg]

Forms inside a Navigation form are inside a subform. The default name for this is NavigationSubform and hardly anyone ever changes that name. So the reference your are looking for might be

forms![Home]![NavigationSubform].[Form]![student ID]

unless there's an additional subform in there. Note that studendsfrm isn't in this reference as is represented by forms![Home]![NavigationSubform].[Form]

One way to build these reference is to open all of the forms involved and go into the query builder. There you can form a reference in the criteria and the IntelliSense will help you, i.e, after you type Forms! you get a drop down with the possible forms.

Also http://access.mvps.org/access/forms/frm0031.htm and http://ss64.com/access/syntax-references.html might be helpful

 

[ziad]

dear Snauberg
thanks for the reply, I tried but it did not work.


I have checked as you suggested what inside the forms! within the query builder, please check attached picture
what I wanted to do is to simply open the "home" form where the studentsfrm is pointing to a certain student ID let say me.studentID from outside it.
for instance, if I want to use the open form command:

DoCmd.OpenForm ??? , , "StudentID =" & StudentID


all the best

 

[sneuberg]

Well my suggestion was a shot in the dark as I don't know how you have this set up. I think I need to see this to figure this out. Could you zip your database and upload it?

 

[ziad]

dear Sneuberg
attached is the file, the main form will open ( home) then, you should see a search command, it should take you to the list of all the students.
now, if you chose one of them ( by simply clicking on the student ID), the chosen student should be displayed within the home form.

all the best
zee

Well my suggestion was a shot in the dark as I don't know how you have this set up. I think I need to see this to figure this out. Could you zip your database and upload it?

 

[sneuberg]

I'll try to answer your question but since that's an usually way of doing that it will take some thought. For your amusement in the meantime please look at the way I did something similar in the attached database. I believe this is the more typical way of doing this.

I put a combo box in the header of the Studentsfrm. The combo box has the StudentTb as its Row Source with the ID being the bound column. I just used the wizard to set this up. In the afterupdate of the combo box you will find the following code.

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me.cboSearch
If rst.NoMatch Then
    MsgBox "Record not found"
Else
    Me.Bookmark = rst.Bookmark
End If
rst.Close

This code is from https://msdn.microsoft.com/en-us/library/office/ff835062.aspx. I just modified it slightly. This code moves the form to the record selected in the combo box. So I believe this provides about the same functionality you were trying to obtain.

But your way presents an interest challenge so I will figure it out and get back with you as soon as I can.

 

[sneuberg]

You were trying to use the BrowseTo command so maybe that will do what you want. This site discusses a problem similar to yours and it seems you need to close the seach form before using DoCmd.BrowseTo. Of course you need to get the ID before the close so you have something for the where condition. So the code I ended up with in the StudentID_Click is:

Private Sub StudentID_Click()

Dim lngID As Long
lngID = Me.ID
DoCmd.Close
DoCmd.BrowseTo acBrowseToForm, "Studentsfrm", "Home.NavigationSubform", "[ID] = " & lngID

End Sub

I've upload the database with this code. I think this does what you were asking for.

 

[ziad]

THANK YOU,
it's working fine; I was stuck using the browseto command, specially on the web they advised to use something like this: main.form.navigationsubform>form.navigationsubform which added complication more to my work,
but your solution has done the trick, thank you again

all the best