Need help with Access

[Heretic Apostate]

I'm designing a database for the local theater company. I'm not an expert designer, but considering what I'm charging them (nothing), they're getting a good bargain.

So far, I've managed to lock up my first attempt with a poorly-designed macro. I managed to retrieve all the tables, queries, and forms I designed by importing the info. Yay me!

Now I've got another problem. I'm designing switchboards so the users can just click on the various links to get to the form or report they need. The trouble is, when I use the switchboard, the forms come up blank. But when I open the forms directly, the test data I'm using shows up.

I don't know why this is so, and I can really use some help.

I also need help with the data entry screens. I'm going on the assumption that anyone who is going to use the application will be totally clueless when it comes to computers and will at some point accidentally screw up the application. So I'm trying to make the application as simple as possible, while also preventing the user from screwing something up.

I have a data entry screen that lets the user pull up a particular patron, then in a subform enter the current transaction. I'd like to have a button that pops up a search box, so the user can enter the first few lettes of a patron's last name, and it'll pop to the first record that has that info. Given that the theater sells 500 tickets per performance and about 3 performances per week, there's going to be a heck of a lot of patrons, so I don't want to do a pull-down. (I've got a similar screen for pulling up a particular performance and enter the current transaction.)

The above forms, I'd like to have the option of limiting the data to the last week's transactions, if that's possible, by simply pushing a button.

I'd also like to have buttons that jump to the "Add or Edit Patrons" and "Add or Edit Shows."

Access isn't my strong point. I'm much better in Excel... Can anyone help out a moderate Access user? C'mon... It's for the Arts!

http://www.lincolntheater.org/

 

[Muad'dib Pendragon]

Take a look at www.tek-tips.com, specifically their Access forums. There are form, report, query, macro, and VBA forums filled with Access questions and answers. You have multiple issues that may stem all the way back to basic table design and relationships. Access is a tough nut to crack without actually seeing the design. But if anyone can help, it would be someone over there.

Good luck!

 

[Andre]

So far, I've managed to lock up my first attempt with a poorly-designed macro. I managed to retrieve all the tables, queries, and forms I designed by importing the info. Yay me!

Now I've got another problem. I'm designing switchboards so the users can just click on the various links to get to the form or report they need. The trouble is, when I use the switchboard, the forms come up blank. But when I open the forms directly, the test data I'm using shows up.

Did you, by chance, import the switchboard and its underlying table from the earlier file? If so, I would suggest recreating it from scratch. Switchboard forms can be a bit flaky and importing one into a different file might cause problems like this. Alternately, check to see if the forms are opening to a new record - if so, you can change this in the switchboard properties. Another possibility is a problem with the record source of the form. Open it both ways and check the record source to verify that it's the same.

BTW - When you open a form with the switchboard, try going to design view, then back to form view. If your data suddenly appears, then it's probably a problem in the switchboard properties.

I also need help with the data entry screens. I'm going on the assumption that anyone who is going to use the application will be totally clueless when it comes to computers and will at some point accidentally screw up the application. So I'm trying to make the application as simple as possible, while also preventing the user from screwing something up.

Consider hiding the database window on startup, but only if you leave a method for bringing it back (so you can make changes). IIRC, <F6> will bring it up. Alternately, I tie a small bit of code to a logo or other object, so if I double-click it, the database window appears.

Later you'll want to create custom menu and toolbars for the application. Absolutely necessary if you want to "idiot-proof" the database. Just always save a copy of the file before making significant changes - helps you avoid problems such as the macro you mentioned above.

I have a data entry screen that lets the user pull up a particular patron, then in a subform enter the current transaction. I'd like to have a button that pops up a search box, so the user can enter the first few lettes of a patron's last name, and it'll pop to the first record that has that info. Given that the theater sells 500 tickets per performance and about 3 performances per week, there's going to be a heck of a lot of patrons, so I don't want to do a pull-down. (I've got a similar screen for pulling up a particular performance and enter the current transaction.)

I understand where you're going with this, but it will require some vba programming to pull it off. A simpler technique is to tie the main form to a combo box. The user can start typing in the combo box, then hit [Enter] when the correct name is displayed. Not much extra effort for the user, but much easier for you if you're not comfortable with vba programming in Access. Note: you'll need to add some error-trapping at some point, as I've seen people start typing, then erase what they've entered, then try to click something else on the form. This prompts an error message, as the combo box doesn't like the null entry. Irritating...

I'm not a big fan of form/subform for entry, as I don't think it's always intuitive for the casual user. If you're willing to dig into the code, I have a form I modified from someone else that allows me to link contacts with groups (think distribution groups in Outlook). You could easily adapt the concept to Patrons and Performances. Basically, the form would display two list boxes, one for patrons, one for performances. The user highlights one or more names, clicks the add button, and the selections are added. An almost identical form would allow the user to select multiple performances and add those to one patron. Very user-friendly (with proper error-trapping, of course ).

The above forms, I'd like to have the option of limiting the data to the last week's transactions, if that's possible, by simply pushing a button.

A control button, with the Apply Filter action tied to it. Create a query for the parameters you want, then apply that query as the filter. I used this idea for my 3.x database of monsters, spells, items, and so forth.

I'd also like to have buttons that jump to the "Add or Edit Patrons" and "Add or Edit Shows."

Same as above, except the button has an Open Form event tied to it. It's usually good design to minimize the current form before opening the new one, then restore the old form when the new one closes, but that's not strictly necessary.

If you want to zip the file and send it (w/o any sensitive data), I'll be happy to take a look and make more detailed suggestions. I'm at sirwhiskers@att.net.