Please help: Access Subform and combobox nightmare (1 Viewer)

SomeoneHadBlundered

New member
Local time
Today, 07:43
Joined
Aug 7, 2012
Messages
4
Hello y'all.

First post. W00t.

I have spent the last few days (approximately 22 hours) attempting to get combo-boxes to work on my form.

Yes - it seems almost impossible. Am I stupid? I have to ask myself. Or perhaps I'm caught up in some disturbing dream which involves a permanent cycle of catch-try statements that really needs a break. Sorry... this is VBA, isn't it?

Anyhoo: my database is looking quite nice. Quite a few many-to-many relationships though. I hadn't quite realised how much of a disaster many-to-many relationships would prove when making the frontend (i.e. the forms).

But sorry, I digress.

The combo-box situation has driven me near crazy. I have consulted many pages, even asked the advice of the best Access minds on StackOverflow, and nothing has surfaced that has so far worked. Have a go if you think you're hard enough, but you've been warned! :D

But let's chase back along the winding path whence this tale began. The dream was the creation of a scheduling system through Access 2010. You know, quite a straightforward thing. Quite intuitive.

Scheduling system? Well you'll need to make meetings. And who will you have meetings with? Has to have a table for contacts. Who are these contacts... this is a scheduling system for a business.. so it stands to reason that the contacts must be connected with businesses as well. And what about if the meeting is not with one person, but a whole department? That has to be accommodated. Then, you have to know who it is will be chairing this meeting (it takes two to tango!). Wouldn't it be nice if our company members could share this scheduling system on... Sharepoint! Then we could see who will be taking what meetings, and at what times... OH MY GOD! All our employees use Outlook... so you could integrate the meetings with the Outlook calendar. The possibilities are endless! Microsoft has provided the ambrosia of organisation!

Forms

Forms

Forms

Forms

Forms

:banghead:

There is no point having a form for meetings if you cannot select the contact that will be met! It is unreasonable to suppose that whoever is using the form will somehow remember the autogenerated ID integer of the business, or the client, or will be prepared to write up some half baked information in the meeting form about the client which is to be completed in the client form. Whilst filling in details in one table from a form designed for a different table may have some merits, the primary means by which the appointments - the entire crux upon which this database is based - must operate is by being able to select items from other tables through the use of combo boxes.

Oh god the combo boxes. They seem to mock me. They have even, on occasion, actually displayed the relevant information in the drop down menu (generally they refuse to do anything on the grounds that the "Control can't be edited; it's bound to the AutoNumber field ID" - well DUH, the control source is a key! God-damn.)

I've even followed the advice given here; (can't post links) to the letter, but nope; Access is having none of that nonsense! :mad:

I've started pulling the database apart; changing keys from Autogenerated numbers to String based names; dumbing down the architecture and reducing the relationships. But still - still - the meeting form refuses even the vestiges of common sense or usability. My next option will be to remove many-to-many relationships altogether. I'm pretty sure that that will work, although it will make the Scheduling system only a fine line above useless.

Any attempts to defeat the combo box problem will be rewarded in heaven (good deeds, don't ya know?)

http
://w
ww.
ucd.ie/fencing/Images/shed_databa
se.P
NG (how hard is it to disguise LINKS!!?!?!?!)

For the record I've used pretty much every permutation I can think of to make the combobox work... it would take a bit of time to list everything I've tried (looking at the navigation menu I see that I've got 14 Meeting forms still in operation... after all the previous ones that have now been deleted)...
 

boblarson

Smeghead
Local time
Yesterday, 23:43
Joined
Jan 12, 2001
Messages
32,059
Okay, first off, just looking at your relationships makes me seasick. Here's a good rule of thumb to work from. If your relationship lines essentially make a circle, it is a pretty safe bet that the table structure is not correct and needs to be fixed. In most (99% I'm guessing) situations, you should NOT have relationships which connect in such a way that you end up with a circular type of route.

So the structure needs to be fixed BEFORE going any further.

Have you followed these instructions to be able to post a copy of your database here? (even without 10 posts):

http://www.access-programmers.co.uk/forums/showthread.php?t=212125

Make sure you have run COMPACT AND REPAIR first and then ZIP the file by right clicking on the database file and selecting SEND TO > COMPRESSED FOLDER.
 

SomeoneHadBlundered

New member
Local time
Today, 07:43
Joined
Aug 7, 2012
Messages
4
At this point I'm prepared to liberally delete tables - hell I'm almost prepared to abandon all relationships altogether and just have everything on the one table - as long I can get it working! :p

If you can help, that would be great. I'm afraid I can't promise much in return! :eek:

Note: a lot of the extant forms are totally out of date on the grounds that keys and fields that they were SQL'd to have since been modified.
 

Attachments

  • QuickBase02.zip
    203.9 KB · Views: 172

boblarson

Smeghead
Local time
Yesterday, 23:43
Joined
Jan 12, 2001
Messages
32,059
Sorry, I have not been able to get to the forum that much in the past couple of days. (I undeleted this post as it really shouldn't be deleted if someone has responded at all, which I did.) I will try to take a look soon. Unfortunately I don't know how quickly I can but I will try.
 

boblarson

Smeghead
Local time
Yesterday, 23:43
Joined
Jan 12, 2001
Messages
32,059
Okay, I made some modifications. Also deleted a lot of useless forms. I didn't rebuild them all but hopefully enough so you can see what to do.

I changed a few of your field names (so they are more easily known what they are - mostly around the ID fields). It is better to give them a slightly longer but more meaningful name as it will help later when someone else has to look at this. And, any place where you had TEXT as the PK, I modified it so it would be using a Long Integer instead. Much more efficient.

Also, I removed the contact details (email, phones, etc.) to another junction table and I show you how you can provide only what's necessary for each contact as I built the contacts page. I used a tabbed control to give you more in one page too.

Hopefully you can look at the relationships and also look at the form and see what I did. I left it but I am thinking the manager and contingent may have been done in a better way but I'm not sure exactly what you needed them like this for, so I just left them as is.


See attached revised file.
 

Attachments

  • QuickBase02_RevBL.zip
    90.9 KB · Views: 175

bob fitz

AWF VIP
Local time
Today, 06:43
Joined
May 23, 2011
Messages
4,717
boblarson
Would it be possible to post a copy of the db in A2003 mdb format.
 

SomeoneHadBlundered

New member
Local time
Today, 07:43
Joined
Aug 7, 2012
Messages
4
Just saw this now.

Amazing! I'd actually lost all hope and was starting to move onto Google spreadsheet~form as a non-rdms solution! :p

No idea how you sorted it out; you were probably right to mutually exclude businesses from the other processes; did you use any VBA or was it primarily a Control/Source issue?

Looking forward to working with it - hope that Sharepoint is kind to it!

Thanks! :D
 

boblarson

Smeghead
Local time
Yesterday, 23:43
Joined
Jan 12, 2001
Messages
32,059
No VBA was used in the sample I returned.
 

boblarson

Smeghead
Local time
Yesterday, 23:43
Joined
Jan 12, 2001
Messages
32,059
boblarson
Would it be possible to post a copy of the db in A2003 mdb format.

Bob-
I'll see what I can do. I will have to wait until I get home in about 12 hours as I don't have 2010 with me at work. I'm on the bus heading to work (using Tapatalk).
 

bob fitz

AWF VIP
Local time
Today, 06:43
Joined
May 23, 2011
Messages
4,717
Bob Larson
Thank you. That would be very much appreciated.

Bob
 

Users who are viewing this thread

Top Bottom