Access 2007 – Can't figure out the relationships or something

Topic

New Reply

I feel like an idiot. I should know this, but I’ve done something wrong and I can’t figure it out.

Attached is my database without data so you can see what I’m doing.

I have companies that get together for meetings. At these meetings, we have actions items assigned to people. Sometimes more than one person can have an action item, and sometimes more than one action is assigned to a person.

I want to create one form (subforms, pages, whatever – one place for data entry) that will allow the user to enter the meeting date, attendees, action items and people responsible for those action items.

I got the meetings/attendees to work fine as a form/subform. But danged if I can get the action items/responsible person part to work right. It looks right, but when I try to enter a responsible person’s name, I get the error “record cannot be updated.” And yet, it appears to update. I’ve been struggling with this for the better part of 4 days now.

Help, please!!

Reply | Quote

Replies

Most Loungers (including me) won’t be able to open a database in the new Access 2007 .accdb format.

See [post=”364203″]this post[/post] for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

Reply | Quote

I don’t know whether it is any help to say this, but this all works perfectly for me!

I can assign people to action items without problem, as in the screenshot below.
Am I trying the right thing?

Reply | Quote

Well good grief. Yes, it does work, THIS MORNING! I swear, for days it has been giving me errors. But, maybe just compacting/repairing it to prep it for sending to you fixed that. So ok. Gulp.

One more question though. When I create a report to list each person’s action items, the responsible person field only shows their ID number, not their name. When I run the query that the report is based on, I see their names. What am I missing (besides, apparently, my brain.. lol)?

Reply | Quote

If you look at the design of the relevant table (and probably also of the form based on it), you’ll probably see that the person id is a lookup field with a combo box with 2 columns of which the first (the person id) is hidden, so that the second column (the name) is displayed.
You should use a similar combo box on the report.

If that doesn’t help, John Hutchison or someone else can probably fill you in on the details (I can’t open your database).

Reply | Quote

You’re right, as usual, Hans. But, how do I change the report? You said use a similar combo box, but I’ve never used a combo box on a report before. I tried changing the field to a combo box, but it still displayed just the numbers. So, I tried deleting it and re-creating a combo box, but that didn’t work either – it says it’s unbound. Sorry, but I don’t get it.

Reply | Quote

Can you go back to the original report? If so, change the text box to a combo box, then modify the properties as follows:
– Set Column Count to 2
– Set Column Widths to 0″;1″
– Set Row Source to the same table name or SQL statement as used in the table or form (this is where I can’t provide detailed info)

Reply | Quote

I saved the db as Access 2003, Hans. Would you please take a look? I tried following your instructions, but I still am missing something because it didn’t work. Thank you sooo much.

Reply | Quote

In the attached versions, I have applied the suggestions from my previous reply.

Reply | Quote

Thank you, Hans!! As you figured out, I was picking the wrong table for the row source. Dang. Thank you so much!

Reply | Quote