Link (synchronize) a combobox in a Main form to a subform

(This is my first time to this forum, hope I can post about help for VBA code here - sorry if I'm breaking etiquette; don't hate me.) :p

Hi,

I've got a database in Access that's been giving me grief for awhile. There is a combo box in my main form. I only have 5 records in my main form; one for each of the years five main goals. I want this main form locked at those five records so that the main form can never be manipulated (how?).
Also, I want it setup so that the user can click a combo box with all five goals listed, pick one, and the subform refreshes with that goal's subInfo based on the goal picked. I've managed to make some bookmark code work (I think). Now I just need to figure out how to synchronize it with the subform - So the user can click between the goals in the combobox freely and have the subform update.
I need it so that the user pops up the form, has 5 goals to chose from, picks one, and the subform is switched to the appropriate spot. I've got a one-to-many relationship setup between the main form and subform because each goal may have about 20 subgoals within it.
I've got an After_Update setup on the goal Combobox, and I've got an On_Current in the subform that I'm trying to link with the main form so they synchronize, but I'm having some problems.

The code I've put in the main form is this:

Private Sub Goalbox_AfterUpdate()

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "Goals = '" & Me.Goalbox.Value & "'"

If rst.NoMatch Then
Beep
Else
Me.Bookmark = rst.Bookmark
End If

rst.Close

Set rst = Nothing
End Sub

................. This appears to be working allright and gives me a bookmark. However; I dont know what to do with this bookmark when synchronizing with my subform. I think I need to put some code in the On_Current section of my subform.
Any ideas or anyone have some code handy that I could edit that might work; thanks for your help!

-Josh

Try this:

Private Sub Goalbox_AfterUpdate()

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "Goals = '" & Me.Goalbox.Value & "'"

If rst.NoMatch Then
Beep
Else
Me.Bookmark = rst.Bookmark
ChildForm1.combo1.Text = rst.Bookmark
End If

rst.Close

Set rst = Nothing
End Sub

Neo (gigsvoo),
Okay ... I will try that; however, I think I should make it clear (don't think I did before) that I have never tried this sort of coding before, so I have on clue how to setup Child and Master fields.
When I use your code, I should obviously edit the bottom part that says:

ChildForm1.combo1.Text = rst.Bookmark

I'm assuming that I place the subform name in the Childform1 place. But the Combo1.text - no clue what to place here. I tried this...

frmSubActnPlan.LinkNumber = rst.bookmark

It gave me an error: Method or Datamember not found. I'm lost..
I'm a low to medium level programmer - learning everyday. I'm not sure what type of field I am supposed to have in the subform and I link it with the main form. I've setup links before by having an autonumber as the primary key in the mainform's table and creating a LongInteger field in the subform's table that a one-to-many relationship is setup to, this works sort of the same way. Just this time I don't want an infinite number of mainform records and I want the forms to stick together (with the neat combo box selector).
I'm going to keep plugging at it .... let me know if any of you can sport some advice. Thanks a ton for your reply!

-Josh

Do you really need a subform ??

Can you not use a listbox (perhaps) to return the information, filtered and refreshed by your combo box.

Then you need :
1 combo - lists years choices
1 Listbox - lists info - filtered by forms!frmname!comboname
1 event on click/after update to refresh the list box


Let me know if you want a rough version, if I get the time I'll make one (Access 2000).

Regards

Vince

Vince,

Here's a post that I put on another forum to sort of get the idea out. I think that I do need a subform for what I want to accomplish. But if there is another way, I'm open to any possibility as long as it gets done. Thanks for the reply, here's the post.

Okay, this is a Project Database that I'm designing. There are five set MAIN GOALS that we have for the year 2002. Each of these main goals has an unset number of Projects. I have a form that lets us add Goals, and give each goal a list of projects and their progress (respectively).
Here's the scoop. I've got a form right now linked to a main table. Within that form, I have a subform, linked to a subtable. The Primary key in the main form's table is setup as an AutoNumber (ID). There is a field in the subtable that is setup as a number field (IDNumber). I have a relationship setup between the two tables with ID to IDNumber as a One-to-Many relationship.
This makes it so that one Set of Records in the Main Form can link to an unlimited (well, as many as I'll ever need) number of record sets in the subform. So I stay on the first record of the main form, and I can add - let's say - 20 records in the subform that link only to the first record in the main form. Then when I go to recordset number 2 in the main form, the subform is now set back to 1 record (instead of 20 from recordset 1), and I can now add a bunch of recordsets in the subform that will only be viewable from recordset number 2 of the main form. That make sense?
Now here's what I'm trying to do. I want to set it up so that instead of a one-to-many relationship involving numbers between the two forms, I want a set FIVE records in the main form (one for each of the 5 main goals) to link to a potentially limitless number of recordsets in the subform (for each recordset of the main form).
I would like for the user to be able to load up the form and have it default to Goal 1 of the main form. The box that says Goal 1 will be a combo box. If they want to view the recordsets (in the subform) of Goal 2, all they have to do is click the combo box and chose Goal 2 - and badabing, they are at the Goal 2 Recordset and can edit away at the projects in the subform.
What this will usually do is just edit the main table's Goals field containing 'Goal 1' and change it's value to 'Goal 2'. Then I'll have 2 Goal 2's. Bye bye Goal 1.
So I'm stumped. Is what I want to do possible with some coding? Or maybe I'm missing a simple relationship feature. Can anyone help? Did I make any sense at all? Please Help if you can. Thanks a ton.

-Josh

Sounds reasonable :

Main Table
ID < Primary Key
Other fields...

Sub Table
MainID < primary Key
ProjectID < Primary key
Other fields... (info)

Select the main table on the first part, listbox/subform displays the list of projects and their info...


Is that what it is ?

Vince

Vince,

Here's a post that I put on another forum to sort of get the idea out. I think that I do need a subform for what I want to accomplish. But if there is another way, I'm open to any possibility as long as it gets done. Thanks for the reply, here's the post.

Okay, this is a Project Database that I'm designing. There are five set MAIN GOALS that we have for the year 2002. Each of these main goals has an unset number of Projects. I have a form that lets us add Goals, and give each goal a list of projects and their progress (respectively).
Here's the scoop. I've got a form right now linked to a main table. Within that form, I have a subform, linked to a subtable. The Primary key in the main form's table is setup as an AutoNumber (ID). There is a field in the subtable that is setup as a number field (IDNumber). I have a relationship setup between the two tables with ID to IDNumber as a One-to-Many relationship.
This makes it so that one Set of Records in the Main Form can link to an unlimited (well, as many as I'll ever need) number of record sets in the subform. So I stay on the first record of the main form, and I can add - let's say - 20 records in the subform that link only to the first record in the main form. Then when I go to recordset number 2 in the main form, the subform is now set back to 1 record (instead of 20 from recordset 1), and I can now add a bunch of recordsets in the subform that will only be viewable from recordset number 2 of the main form. That make sense?
Now here's what I'm trying to do. I want to set it up so that instead of a one-to-many relationship involving numbers between the two forms, I want a set FIVE records in the main form (one for each of the 5 main goals) to link to a potentially limitless number of recordsets in the subform (for each recordset of the main form).
I would like for the user to be able to load up the form and have it default to Goal 1 of the main form. The box that says Goal 1 will be a combo box. If they want to view the recordsets (in the subform) of Goal 2, all they have to do is click the combo box and chose Goal 2 - and badabing, they are at the Goal 2 Recordset and can edit away at the projects in the subform.
What this will usually do is just edit the main table's Goals field containing 'Goal 1' and change it's value to 'Goal 2'. Then I'll have 2 Goal 2's. Bye bye Goal 1.
So I'm stumped. Is what I want to do possible with some coding? Or maybe I'm missing a simple relationship feature. Can anyone help? Did I make any sense at all? Please Help if you can. Thanks a ton.

-Josh

Wierd, sorry about the double post, that's kinda freaky. Wonder what I did.
Okay! Yes you've got the general Idea, Vince. There's tons of subgoals within each main goal that I need to keep track of... but only 5 main goals. I'd like the user to be able to simply click on a combo box (or the type that you can use the up and down arrows) and then have it change what's in the subform based on what is selected in the main forms combobox. The user needs to be able to switch back and forth between MainGoals in the main form. No new records will ever be added in the main form - the five goals are set in stone, the subgoals are checked on and updated constantly.

That paint a clearer picture? Thanks for the replies!

-Josh

Ok, assuming the table set up is as above.....

Then what you need to do is decide 'how' your data is to be displayed and whether the users have to go to another form to enter/change or use the subform.


Now, defaulting to goal 1 - if the ID =1 then set the combo box value to 1, it'll match. Since you will have coding to refresh the subform/listbox under the combo box, call this as well to refresh it.

If you use a list box, you can pull the details from the list box into fields on the form, (unbound) and the user can edit, get it validated and update your records. Once updated successfully, refresh the list box. You depend on the Goal as to what is updated so the correct records are amended and you don't lose goals.

Sound good ?

The nasty bit is making your mind up, cause then its settled, you then do the programming as neccessary. If you didn't use a sub form, you could have the whole lot on one screen. If you use a sub form, you need to pull the selected Goal from the main form or hold the selected goal ID on the sub form for use so you don't over write it. Plus, don't let the users overwrite the goal headers, otherwise you will get two (or more) Goals that have the same name but aren't...

Vince

Vince,

(sorry it took me so long to reply - had a meeting then lunch)

I notice that you have two primary keys in the subtable. Now I'm assuming that you mean the subtable's MainID is setup with a relationship to the ID (primary key) table in the main table, and ProjectID is the primary key of the subtable. If this is so, then yes. I do have it setup this way.
Now, I'd actually like to set it up so that it is a list box. The project descriptions will end up being long, right now they are just numbers until I get everything setup. If this is going to be a long term problem, perhaps I could have 2 fields side by side, one with project number, one with project description, and have them able to see both in the combo or list box (easy enough), and link by the number instead of a huge description - I think I'll do this. I'm pretty sure how to.
The listbox (well, it's a combo box right now) is unbound right now, but I have it linking to another table to get it's records. But on the form it is unbound.
I with your final paragraph. The main table needs to be uneditable. They need to only see the five choices, and be able to chose them. I don't want them to be able to mess with anything else. And on the subform, I'm hiding the linking box so that they coulnd't mess with it if they wanted to. it's just there so I can put it in the code as a bookmark link. -> which I don't knwo how to do yet. This is my delima. Once I can figure out how to have the two tables synchronize, I'm set. The database's main code is written and I can finish up with the aesthetics of the forms and reports.
Any clue how to program this, is my last coding even remotely close? Should i post all the coding that I have? Or should I just post the database? Whatever can help you guys help me. Thanks for the reply.

-Josh

Ok, check this out.

See what you think and whether you can use it - it is the basic idea.

Vince
PS: lstProjects.column(1) - on the onClick event to pull info into a textbox underneath - you try it ;) also you can use the hidden columns they are still there.