Help with Combo & Subform 1

[JaimeBlue]

Hi. I really hope somebody here can help me...

I'm doing up a form that has a subform. This is for a mailing list, and I want to be able to call up people or groups of people by the organization they belong to. The main form has a combo box taken from a table I created with the sole purpose of listing the organizations. The subform is, of course, the information for the person (people).

It works *to a degree* as I used the Wizard to link the Subform to the Main form via the Organization Combo when I created the Subform. However, here are my big problems:

* Whenever I choose an option from the Combo, it adds it as a new entry on the table from which it gets its data (I only want one listing per organization -- the table is one column only listing all the organizations, created specifically for the Combo)
* If I try to lock the Combo, I can't choose an option
* If I change the table from which the Combo gets its info so that it doesn't accept duplicate entries, then there are problems whenever I try to access organizations with several people listed

When I worked on this at my old job, I'd managed to do something with the AfterUpdate command -- but without the template I had, the coding is Greek to me.

I appreciate any help given. Thank you kindly.

Jaime

 

[JerryDennison]

If I understand your problem correctly, what you'd like is to select an organization and have only those people who are members of this organization listed in the subform. Is this what you're after?

 

[MangroBongacello]

If I got it correctly, just unbound the combo box.

Mangro

 

[JerryDennison]

I do not understand the question, and evidently you didn't understand mine. What about the combobox is not doing what you want? Where is the combobox that is causing the problem, in the main form or sub form?

 

[JaimeBlue]

Let' see if I can explain this better

The original problem: The combo box originally worked all right *except* for the fact that whenever I chose an item from the list, it wrote that item to the source table, thus creating 2 entries rather than the original 1. For example, if I were to choose DFAIT from the combo box, it would bring up the person(s) belonging to DFAIT in the subform (what I wanted) -- but then it would add a 2nd DFAIT on the originating table (and thus the Combo box as well would contain 2 listings for DFAIT).

If I make the column a key or make it so that it doesn't accept duplicates, then it causes problems whenever I select an organization that has more than 1 member.

So in essence, what I want is for the Combox to choose the target item without writing to the table and allowing for more than 1 listing in the subform.

Thanks!

Jaime

 

[JerryDennison]

Ok, now I understand. Delete the combobox you have and create a new one, only this time when the wizard asks what you want the field to do check the radio button to Find Record based on selection. This will create an unbound combobox and add code to the afterupdate event to find the record the main form is based on. This should solve your problem.

 

[JaimeBlue]

Thanks so much!

Just one little thing though... As I may have mentioned, the codes are Greek to me because Access doesn't make it easy to write your own code (I'm not computer illiterate, I'm just a secretary who doesn't have time to learn a 4th language Do you know of any sites that may have lists of such codes with descriptions of what they do? I worked with a similar one before, but that one I was able to cut and paste from a professionally designed DB and merely inserted the appropriate terms -- but that was months ago.

Thanks again!

Jaime

 

[JerryDennison]

The Combobox wizard will create the code for you automatically.

 

[juttuniemi]

Jerry,

Thanks so much for your (last) reply of 14 Dec!!! As an "advanced beginner", I've been struggling to find a solution to essentially the same problem, and not finding it anywhere.

Thanks again!!!

Richard

 

[juttuniemi]

Correction to my previous post:
I meant to refer to Jerry's "Delete the combobox you have..." reply, not to the last one of the 14th.

Richard

 

[JerryDennison]

You're welcome.

 

[juttuniemi]

Jerry,

As I mentioned earlier your "Delete the combobox you have and create a new one" suggestion was very helpful, but I'm still stuck.

I'm trying to use a list box in the main form, but I need to keep the field bound to the subform. (I'm using the subform
to add a new record to the table the subform is/was bound to.)

I've tried every which way to keep the list box and create new records via the subform,
but it seems that having it unbound prevents me from adding to the table. (I'm sure there must be a way to do this, but don't
have enough Access experience to know how to proceed.)

Any help would be much appreciated...

Richard Richard

 

[JerryDennison]

You cannot have a field on your main form bound to a field on the subform. You can display a field value from the current record of a subform, but it isn't bound to the underlying recordset and thus is not editable. Why do you want to do this? What purpose will it serve?

 

[juttuniemi]

Jerry,

Here's what I'm trying to do:

I'm designing a database that records hours worked by employees. There are 3 tables:
- employees
- projects
- hours_worked

On the main form, I want a list box for employee ID. In the hours subform (or main form), I want a list box for project ID.

Then, in the hours subform, I want to input hours worked for that employee and project and create a new record for hours, empl id and proj id in the hours_worked table.

So far, I can either use the employee record navigation button at the bottom of the main form (and leave the form bound to hours_worked, or I can use an unbound list box which (being unbound) won't create a new record.

How can I have both a listbox and be able to update hours_worked from the subform?

My first formal class in Access is next week. Up to now, I've learned (limited) Access with a few books and a lot of experimentation, neither of which is getting me over critical hurdles.
Richard

 

[JerryDennison]

What you have is a many to many relationship between employees and projects (this of course assumes that an employee can work on more than one project and more than one employee can work on the same project). To facilitate the capture of the hours a given employee works on a given project you need 4 tables.

Employee table
Project table
EmployeeProject table
ProjectHours table

The EmployeeProject table should have at a minimum a foreign key link field to the employee table's primary key and a foreign key link field to the project table primary key. This table should also have it's own primary key. This primary key will then be used (via foreign key link) in the ProjectHours table to link the hours worked to the EmployeeProject record. This will then give you automatic link to the employee that did the work and the project the work was done on.

That said, how do you accomplish this efficiently? There are a couple of ways to do this. Probably the easiest would be as a subform of a subform. Using your project as the mainform record, create a subform using the EmployeeProject table to capture the employees that work on the project then create a subform off this using the ProjectHours table to capture the hours for each employee for each project.