VBA for subforms - problems!!

[adamburton]

This is probably a really simple and easy question, but Im going to ask it anyway!

I have a continuous form which displays a list of records. On each record there is a button which when clicked takes you to the main form for that record. Within the main form is a sub-form which tends to have more than one entry (displayed as a single form). When I select a record from the continuous form I am taken to the form for that record, but rather than jumping to the appropriate record in the sub-form, it always shows the first record in the sub-form.

Is there a way that when I select a record I can be taken to the form, but also showing the relevant sub-form entry to the option Ive selected.

God, that was a shocking description. Let me put it another way: I have a form for my family name, and embedded within that form is a sub form showing the members of the family. If I select from my continous form (basically my option list) to open up the record for my younger brother, the main family form opens, but the subform always opens up to the first record in the list, rather than jumping to my brothers entry. Can I choose to go straight to my brothers record?

I hope I havent confused too many people. It cannot be a hard thing to do, just maybe a hard thing to explain.
Thanks,
Adam.

 

[Carmen]

Adam,
If I'm reading you right, using your Family example, you have a continuous form that has a list of people, ex. John Smith, Mary Smith, Adam Smith, Next Family's Records, etc.... and each with a command button. If you click on the button on any Smith record, a main Smith form opens with a subform that shows John's info, Mary's, Adam's and this subform is a single form so it always opens to John's record no matter which Smith you choose.

If that's all correct, I assume you have a FamilyID unique identifier for the family name and a PersonID unique identifier for each person in the family and that your person table has FamilyID for the foreign key and that's how the two are linked.

Make sure PersonID is in the field list of your continuous form (it doesn't have to be displayed on the form) and your subform and add this criteria to PersonID in the record source of your subform: =Forms!YourContinuousFormName!PersonID. Then when you click a command button, the FamilyID link opens the main family form and the subform should jump to the correct person via the PersonID link.

Sorry if I totally confused you!!!!!! But I hope this helps.
Carmen

 

[adamburton]

Thank you Carmen

Carmen,

Thank you so much for your reply. I was getting a bit worried that nobody was going to reply and I would be left stuck!

I've actually only just picked your message up now after leaving work, so wont get chance to try until Monday. But I wanted to say that you completely understood my problem and so Im very confident that you have given me the answer I need.

Access is really weird like this. You develop your skills on all sorts of diverse and advanced things, and then all of a sudden come up against the most simple, stupid little thing and are stumped!! So, I just wanted to say a big huge THANK YOU in advance!

Have a nice weekend,
Adam.

 

[Carmen]

Going Home! It's not even Noon yet!
Hope you had a good weekend and You're Very Welcome, but maybe don't thank me yet til you see if my advice works! Please post back and let me know.........

Carmen

 

[casey]

look into DAO.recordsets...You can set a recordset to your subform's recordset and then use rs.FindFirst method will take you to the actual record in that subform.

i.e. rs.FindFirst "[MemberID]=" & Me.MemberID


Remember to set the rs=nothing when finished!

 

[adamburton]

It worked!!! Thank you

Carmen,

Ive just got into work (Im in England!) and followed your advice - and it worked! Thank you very much for your help. Now I can press ahead with my database, until I come across the next thing that stumps me. When that happens I'll be back in touch with you ;-)

Thank you too Casey, I didnt need to try your solution in the end but thanks for offering it anyway.

Have a good day,
Adam.

 

[Carmen]

Anytime Adam! Glad I could help........

Carmen

 

[adamburton]

Ahhh no!! more problems

Carmen,

More problems. Your solution was spot on, but Ive just realised its caused me another problem.

Keeping on the family members example, when I select a family member and are taken to their record (the family form, and person sub-form) everything works well. But, I wanted the facility to then be able to click the navigation buttons and cycle through the other members of that family (cycle through the sub-form while still on the main form) - unfortunately I cannot do this now. I understand why - its because Ive told the record to only display a recordset that matches the personID selected from my main continuous form. So, for all intents and purposes there are no other records to display.

Do you know of an easy way around this? I thought maybe instead of having the sub-form nav.buttons I could put in an unbound combo box to use as a record selector. However, from experience I cant set up this type of combo box on a sub-form - it doesnt seem to work.

At the end of the day, I could always advise the user that in order to browse through other members of that family they will have to navigate back to the continous form, but I had hoped I wouldnt need to do this, as it seems a bit long winded (as does this message probably ;-)

If you know of anything that could get round this I would be soooo grateful. Thanks again Carmen.

Adam.

 

[Carmen]

Hi Adam,
I see your problem. This might not be a great way to solve it but I think this should work.........
Let's say your subform's RecordSource is a table (or query) called "Person" (sticking with our example). Make a new query using all the fields from this recordsource and put the filter criteria we used earlier in this query: for PersonID use =Forms!YourContinuousFormName!PersonID and save this query and name it something--I'll use "SubformFilter". Now set your subform's recordsource to this new query. If you check it out, it should work like before where you can't navigate through the records. Add a Command Button to your subform and DON'T use the wizard. In the On Click event put this code:

Private Sub CommandButton_Click()

If Me.RecordSource = "SubformFilter" Then
Me.RecordSource = "Person" 'Your Original Recordsource without the filter
Else
Me.RecordSource = "SubformFilter"
End if

End Sub

What this does is, when you click John's Button on the continuous form, the Main Form opens and the subform opens to John's record but you can't scroll thru the records. When you click the command button, it changes the recordsource to the unfiltered set and now you can see the rest of the Smith family records.

Does this make any sense???????

PS: If anyone else out there has a better solution, go for it! Like I said, this may not be the best way to go about it.........

 

[adamburton]

Thank you..........again!!

Carmen,

You, my dear are a superstar!!!! Once again youve helped me out of a sticky situation and I thank you for it!

I couldnt quite get your code to work (probably me being dumb) but I understood what your were saying, so I played around a little and - low and behold - it worked!!

You have saved me hours of endless tearing my hair out!!!

Thanks again,
Adam.

 

[adamburton]

new entry record

I probably should start a new thread for this, but seeing as weve been chatting here a bit, I thought I would post this question here.

On my subform, I can now cycle through the entries using nav.command buttons. However, all I want to show is the records that exist, and not the 'create new record' screen (you know - the new entry screen). Is there a way I can stop this screen being displayed - so the user can only cycle through records that have been set up already with data.

If I wanted to create a new entry I have a command button which I press which takes me to this new entry record. Ideally I want this to be the only time that the new entry record is shown. I know that by adjusting the 'Allow Additions' subform property I can stop the new entry record being displayed, but then this also stops my command button working when I want to set up a record.

I hope this makes sense, and you can help.
Thank you very much,
Adam.

 

[adamburton]

Ive got it

Ive actually been playing around myself, and have figured it out, so thanks anyway.

adam.