Access Database Frustration

markrichardson

markrichardson

Judge Judy
Suspended / Banned
Messages
1,852
Name
Mark
Edit My Images
Yes
Entirely non-photography, but plenty of very clever people here!


I am creating an Access database which has 4 related tables and having difficulties getting a form to correctly operate.

The 4 tables are:

A - A table containing a list of committees
B - A table containing a list of committee meeting dates, linked to Table A via the numeric index key
C – A table containing a list of business items
D – A table which lists the discussion of the business item at a particular meeting – it is linked to tables B and C via numeric index keys.

I am creating a form which contains details of the business item and a subform which lists each committee and meeting date that item of business is scheduled for. I can display test data without problems.

I would however like to have in that subform the Committee and Meeting Date as combo boxes to allow changes. The meeting date combo box should only display meeting dates that are available for the selected committee. Changing the committee must remove the meeting date as it will no longer be valid.

I just can’t get this to work – can someone reassure me that the table structure is a sensible one (and if possible suggest where I may be going wrong with the combo boxes!)
 
My employer (a large organisation at that!) is not quite with the times and my immediate colleagues are pretty computer illiterate. When I started 3.5 years ago they were still using index cards (3,500 of them!) to file information. They are currently wildly misusing Excel to try to manage information.

I'm running Access 2007 on an XP machine and my only alternative is to learn MySQL and PHP sufficiently to be able to build a site to do all I want and make it sufficiently easy to use that it's foolproof.
 
The fool proof bit will be the challenge...

You could probably build something to do that with drupal. You can make custom content types so I built module and course descriptions. I don't think it would be impossible to build something which had committee meetings and what happened in them.

It's basically only an agenda with a link to the minutes and links to each item within those minutes.

There's also GDB. It's a flat file database. Mysql also has a flat file option now too.

Also had a quick google and found this: https://www.mycommittee.com/Default.aspx

There are probably opensource web apps that would do what you want. I know there are conference management tools out there so it seems likely that there are meeting management ones around.

Few more ideas here: http://www.cbsnews.com/8301-505143_162-57402953/3-tools-for-managing-meeting-minutes/
Cloud based solution: http://gminutes.com/

I think there are shed loads of options that are already out there :)
 
Last edited:
srichards said:
I think there are shed loads of options that are already out there :)
Click to expand...

There certainly are - but I can't install any new software (and indeed have no server access). There are a number of major IT projects so I'm limited to a localised solution for the time being.

Access should be fine for the job - just having issues with the interdependent combo boxes on a continuous form. From reading online seems like a broader problem!
 
markrichardson said:
I would however like to have in that subform the Committee and Meeting Date as combo boxes to allow changes. The meeting date combo box should only display meeting dates that are available for the selected committee. Changing the committee must remove the meeting date as it will no longer be valid.

I just can’t get this to work – can someone reassure me that the table structure is a sensible one (and if possible suggest where I may be going wrong with the combo boxes!)
Click to expand...

Just to clarify, you want a combo box that allows you to select a committee, which will then update the second combo box with the results of a query for available dates where the committee is the first combo box. That's possible. You'd probably need some VBA (or you might get away with a macro to run on the onChange event), though I can't say for certain without attempting something similar myself.


markrichardson said:
I'm running Access 2007 on an XP machine and my only alternative is to learn MySQL and PHP sufficiently to be able to build a site to do all I want and make it sufficiently easy to use that it's foolproof.
Click to expand...

If you don't need it as a web based form, I'd avoid it. It would be far more complex than Access.
 
What a pain.

There will be ones which are hosted solutions so potentially do-able even with IT restrictions.

Good luck with having any hair left after dealing with access and vba :)
 
Well baldness is in the genes so it's just a matter of time anyway!
 
I just can’t get this to work – can someone reassure me that the table structure is a sensible one (and if possible suggest where I may be going wrong with the combo boxes!)

On the 1st combo box, on the OnExit property you will need to run a function which re-queries the datasource of the 2nd combo box. Better to have it that way rather than have the datasource re-queried on the OnEnter or GotFocus property of the 2nd combo.

The datasources of both combo boxes are best as queries rather than tables.

You will have issues trying to link field of tables B,C & D to the index field of table A.

If you want to give fuller details re your table structure / field types it would be possible to advise further.
 
It's been a couple of years since I've done any Access, but prior to that 8 years worth so I reckon I should still remember a thing or two!

PM me if you want me to have a look at what you've done so far. It sounds like a bit of VBA behind the Committee combo to refresh the meeting times combo (if it's not doing it already and also clearing anything that has been selected i the meeting times combo)
 
Thanks for the guys who've posted advice - which was helpful though not the root of the problem.

It seems that on continuous forms each control (in this case combo box) only exists once, even if it is displayed multiple times. The issue that I was hitting was that updating the combo box changed that combo box for the records in the continuous forms.

The best solution I could come up with was to use unbound combo boxes hidden behind bound text fields with some VBA to update the field based on the combo box. A ridiculous workaround but it seems to be the result of the core Access structure rather than my configuration.
 
You must log in or register to reply here.
Back
Top