Goto record when click in subform

[Stingone]

Hi all,

i have a form and subform. The subform is connected to the form and display all BandID's associated with that record. What i want is the ability to click on a record in the subform so that it automaticly set's that record in the form for editing.

 

[vbaInet]

The natural order of things is to invoke any actions from top down, i.e. from the parent to the child. Your subform and parent form should also naturally be editable so I'm not sure what you're trying to do.

 

[Stingone]

The natural order of things is to invoke any actions from top down, i.e. from the parent to the child. Your subform and parent form should also naturally be editable so I'm not sure what you're trying to do.

When i click on a record in my subform is does not go to that record in my main form. I have a big query in my main form with comboboxes. So what i simply want when clicking on a record in the subform it sets that record in the main form for editing.

 

[vbaInet]

I don't think you fully understand how a subform works. You said:

The subform is connected to the form and display all BandID's associated with that record.

If the subform is linked to the parent form it will only show records that relate to the parent form.

 

[vbaInet]

Here's a link that would help you understand the relationship better:
http://office.microsoft.com/en-gb/access-help/about-subforms-HP005188395.aspx

 

[Stingone]

I don't think you fully understand how a subform works. You said:
If the subform is linked to the parent form it will only show records that relate to the parent form.

I understand but. I have on my mainform BandID/Podia/Day/Time.

As soon i have filled in all those options it shows on my subform for that BandID the podia/day/time. So when im at the 6e record i directly can see what podia/day/time have already been used.

So on my main form i use the record selector for all events. The subform only shows the records associated with a bandid. But when im at record 250 and i see a misstake on the subform i want to click in the subform so that i goes to that ID on the mainform and i dont have to go to all 250 record. hope this explains what i want to do.

 

[vbaInet]

I understand but. I have on my mainform BandID/Podia/Day/Time.

As soon i have filled in all those options it shows on my subform for that BandID the podia/day/time. So when im at the 6e record i directly can see what podia/day/time have already been used.

I still don't follow. Here are some questions for you:

1. What is the Record Source of main form?
2. What is the Record Source of the subform?
3. Which field links the main form to the subform?
4. How are they linked?

 

[Stingone]

I still don't follow. Here are some questions for you:

1. What is the Record Source of main form?
2. What is the Record Source of the subform?
3. Which field links the main form to the subform?
4. How are they linked?

1. Evenement
2. SELECT Bands.[Band], Bands.BandID, Evenement.Dag, Evenement.Tijd, Locatie.Locatie, Locatie.Podium FROM Locatie INNER JOIN (Bands INNER JOIN Evenement ON Bands.[BandID] = Even
3. BandID

I found some example of what i try to do. but doesnt work this is where they use employeeID as the primary key for the main and subform. where i use BandID.

'----- start of example code -----
> Private Sub Form_Click()
>
> If IsNull(Me.EmployeeID) Then Exit Sub
>
> If Me.Dirty Then Me.Dirty = False
>
> Me.Parent.Recordset.FindFirst "EmployeeID=" & Me.EmployeeID
>
> End Sub
> '----- end of example code -----
>

 

[vbaInet]

I found some example of what i try to do. but doesnt work this is where they use employeeID as the primary key for the main and subform. where i use BandID.

You don't need to show us code you found somewhere that you "think" does what you want. If you found code somewhere, feel free to try it out for yourself.

2. SELECT Bands.[Band], Bands.BandID, Evenement.Dag, Evenement.Tijd, Locatie.Locatie, Locatie.Podium FROM Locatie INNER JOIN (Bands INNER JOIN Evenement ON Bands.[BandID] = Even

Wrong use of a subform. So like I said you're misunderstanding how a subform is properly used. You would essentially use a subform for linking a one-to-many relationship. What you've done isn't the right way to do things.

By the way, you've not answered question No. 4?

 

[Stingone]

You don't need to show us code you found somewhere that you "think" does what you want. If you found code somewhere, feel free to try it out for yourself.

Wrong use of a subform. So like I said you're misunderstanding how a subform is properly used. You would essentially use a subform for linking a one-to-many relationship. What you've done isn't the right way to do things.

By the way, you've not answered question No. 4?

Sorry, I just try to find a awnser on this forum. As i'm just a starter in Access i don't have all the knowledge as many of the people on this forum have.

I created the subform through the wizard as i thought i should be worked. I created the subform just for the user so that i can see directly all associated bands with a record on the mainform.

No.4 i dont know how to check as i created it through the wizard.

 

[vbaInet]

You don't need to apologise. What I was saying is that it's best you ask the questions and we'll look for the best solution for you.

Ok the wizard is fine. The way it works is that the main form should be just Evenement and the subform should only be Locatie (not both). Which means main form always controls subform but the subform cannot and should not control the main form. Do you see what I mean?

Maybe what you need is a Split Form or a Search Form.

 

[Stingone]

You don't need to apologise. What I was saying is that it's best you ask the questions and we'll look for the best solution for you.

Ok the wizard is fine. The way it works is that the main form should be just Evenement and the subform should only be Locatie (not both). Which means main form always controls subform but the subform cannot and should not control the main form. Do you see what I mean?

Maybe what you need is a Split Form or a Search Form.

I understand what you mean. Although i have the feeling that the subform works as it should be. See attached picture below. Currently the subform cannot control the main form.

as you can see there are 2 records in the form associated with the BandID on the mainform. My goal was to click on one of the subform records so that i automaticaly goes to the right record on the main form. but i understand from your point of view that thats not possible.

 

[vbaInet]

That's not my viewpoint. It is possible but there are several parameters to deal with that's why Microsoft created what you call a Split Form (which I mentioned above) for you to look into. It lays out a form just as you have it but the form itself has its own limitations too. It looks like you have a version that should have the Split Form so give it a try.

If you think about it, if FormA is linked to FormB, but from FormB you want to be sync it with FormA, what do you think will happen? It ends up in an endless loop. FormB will sync FormA, however, because FormA is linked to FormB it too would attempt to sync with FormB... and so on. Does that make sense?

 

[Stingone]

That's not my viewpoint. It is possible but there are several parameters to deal with that's why Microsoft created what you call a Split Form (which I mentioned above) for you to look into. It lays out a form just as you have it but the form itself has its own limitations too. It looks like you have a version that should have the Split Form so give it a try.

If you think about it, if FormA is linked to FormB, but from FormB you want to be sync it with FormA, what do you think will happen? It ends up in an endless loop. FormB will sync FormA, however, because FormA is linked to FormB it too would attempt to sync with FormB... and so on. Does that make sense?

I now understand i just tried the split screen but with the split screen you dont get the same overview as with the subform. but i see if i can try some different setup's

 

[vbaInet]

Yeah and with the Split Form there are some things you can't do in code.

I wrote some code for someone on here to do what you're trying to do. I'll see if I can find it.

 

[Stingone]

Yeah and with the Split Form there are some things you can't do in code.

I wrote some code for someone on here to do what you're trying to do. I'll see if I can find it.

Many thanks hopefully you can find it

 

[Stingone]

Yeah and with the Split Form there are some things you can't do in code.

I wrote some code for someone on here to do what you're trying to do. I'll see if I can find it.

I tried the code below in the subform click event.

Private Sub Form_Click()
DoCmd.GoToRecord acDataForm, "Evenementen", acGoTo, Me.Form.CurrentRecord
End Sub

So when i now click on a record in the subform it goes to the associate record on the mainform for editing.

However as soon i trie to startup with the navigation form i get a error and it does not "always" work. sometimes it jumps to the incorrect record. Just tried some options. Prob what happens is that when i click the third record in the subform it goes to the third record in the main form what not always is the correct record.

 

[vbaInet]

The problem of synchronising is beyond just the user interface. You have to consider the dataset as well because both forms are bound to the same dataset. Bear in mind that you are using two forms so they would want to perform individual actions.

For example, if you're creating a new record on form A how do you get the pointer on Form B to move to the new record? Well you can use some methods to do that but what really happens? Both forms are attempting to create a record at the same time. That's just one challenge.

Anyway, I can't find my example but I'm doing one quick one now.

 

[vbaInet]

So here's a quick example. Just using a basic module, nothing advanced.

However, one way I can see this working properly (without APIs) is if you bind the parent form to a recordset (instead of a query or table) and bind the other to a query (as usual) then event synch both. But because VBA is single threaded you won't really achieve "true synching".