Help opening a related form (1 Viewer)

[yahoo11]

Hi guys i am new to the forum and am hoping for some help because this has been doing my head in for a few days now.
I am a noob at access and i hope you will be patient with me

I am using Access 2007. I have a form called Student that has student details and a seperate form called Parent 1 which has parent details. What I want is a button on the Student form that will open the Parent 1 form with the detials related to the student i was just viewing.
The student and parent are linked by the same ID (The Student ID is the Primary key)

I have managed to create a button using the wizard that opens the Parent 1 form however it doesn't link to the related parent.
In the wizard i chose Open the form and find specific data to display and linked the ID fields.

Do i need to create a query? If so what is the criteria?

Or do i need to use a Macro or VB Code? (i am really new at these and would need some guidance if i need to use this)

Any help would be fantastic as for the first time google has been of little help to me!

Cheers
Miranda

PS if needed i can attach the database

 

[John Big Booty]

In your buttons On Click event put the following;

    Dim stDocName As String
    Dim stLinkCriteria As String

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

 

[John Big Booty]

The code above will open your form filtered to show only records related to your StudentID on the referring form, so your form can be bound to a table.

 

[John Big Booty]

Should also mention that you can achieve this with the Command button wizard, if you are not keen on fooling around with VBA just follow the bouncing ball and when you get to the step that asks "Do you want the button to find specific information to display in the form?" Select the radio button that has the caption "Open the form and find specific data to display". The next screen will allow you to specify which fields to link on.

 

[yahoo11]

In your buttons On Click event put the following;

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FormToBeOpenedName"
    stLinkCriteria = "[StudenID]=" & Me![StudenID]
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks so much for your quick replies.
Where abouts do i put this code? In the where condition?

Should also mention that you can achieve this with the Command button wizard, if you are not keen on fooling around with VBA just follow the bouncing ball and when you get to the step that asks "Do you want the button to find specific information to display in the form?" Select the radio button that has the caption "Open the form and find specific data to display". The next screen will allow you to specify which fields to link on.

I tried this but it doesn't open the specific data. It just goes to the first record.

 

[John Big Booty]

The stLinkCriteria is the where condition.

 

[yahoo11]

I'm sorry i am a little lost
Can you dumb it down a little for me please.

I have changed

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormToBeOpenedName"
    stLinkCriteria = "[StudenID]=" & Me![StudenID]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

to

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Parent 1"
    stLinkCriteria = "[StudentID]=" & Me![StudentID]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Is there anything else i need to change in that to match my database? Does DocName need to be changed to the name of my database?

And once i click the On Click event where abouts do i enter this code?

Sorry again for my noobiness

 

[John Big Booty]

stDocName and stLinkCriteria are variables and do not need to be changed. What needs to be changed is the information being fed into them which it appears you have done.

Open your form in design view, now click on the command button that you want to put the code into. Now in the properties window click on the Events tab, now click in the On Click row, from the drop down list select event procedure, if this is not already present in that line. Now click the ellipsis button (that's the little button with three full stops on it). The code window will open. The first line that you are interested in will look something like;

[COLOR="Blue"]Private Sub[/COLOR] Command4_Click()

Paste your code in below this line. If there is already code below the above line and the bottom line, erase this first, the last line should be like;

[COLOR="Blue"]End Sub[/COLOR]

If you have code that looks like;

Exit_Command4_Click:
    [COLOR="Blue"]Exit Sub[/COLOR]

Err_Command4_Click:
    MsgBox Err.Description
    [COLOR="Blue"]Resume[/COLOR] Exit_Command4_Click
    
[COLOR="Blue"]End Sub[/COLOR]

You can leave that where it is.

 

[yahoo11]

Thanks heaps!

It pops up with a dialogue box asking me to enter parameter value and when i do this it pops up with the related Parent info form! So i am already light years further than i was without your help. Thank you.

My next question is, is there any way to get rid of this step so that it just goes to the form without having the dialogue box pop up.




If not i can deal with the pop up box coz at least it does take me where i want to go!

 

[John Big Booty]

The dialogue box should not pop up. The fact that it is would suggest that one of the linking references is not quite correct.

If you're able to post a copy of the DB (pre '07 format, please) I'll have a look and see if I can find the error.

 

[yahoo11]

Thanks

View attachment test.mdb

 

[John Big Booty]

OK; your ID is a text field rather than a numeric field so your link criteria needs to look like;

stLinkCriteria = "[ID]= " & [COLOR="Red"]"'" &[/COLOR] Me![ID] [COLOR="Red"]& "'"[/COLOR]

Note that Me![ID] is surrounded by single quote enclosed between double quotes.

If you make that change to your button's On Click event the Parent 1 form will open with the related record.

Just as an aside can I suggest that you consider implementing a naming protocol for your DB objects for example FRM_FormName, QRY_QueryName, TBL_TableName etc. In this way it is readily apparent what type of object you are referring to when you write code. Also avoid using spaces and other special charters as these will greatly complicate code writing further down the track. Limit yourself to alpha numeric characters and the underscore (_)

 

[yahoo11]

Thank you!!
You are an absolute legend I greatly appreciate your help!
I will do as you suggest with the naming

Cheers
Miranda

 

[John Big Booty]

Have you considered showing Parent details in a subform embedded in the student form?

 

[yahoo11]

Thought about it... but i dont like how messy it looks

 

[John Big Booty]

Thought about it... but i dont like how messy it looks

Fair enough, just a thought

 

[yahoo11]

Thanks again for your wonderful help