Discussion Locked

HELP!!! aka Access 97

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Before I get into this, I should state what will become obvious. I am a total novice to Access 97 and databases in general. So with that, here we go!

First, does anyone have any suggestions on how to train (self-paced)on Access? Maybe some good references, in progression, that would take me from a total weenie to an Access pro (yeah, right!)? The company I work for only has Access 97 and is not in a hurry to upgrade, so please keep it in the 97 context.

Second, I'm trying to put together a database that should be simple but I'm having trouble with it. I only have three tables I'll call A, B, and C. Table A has a one-to-many relationship with table B. Table B has a one to many relationship with Table C. I established my join lines with join type option 2 with referential integrity. I created an input form which is set up on table A with a subform for table B. The table B subform has a subform of table C. I've been trying to add a combo box to the table B subform so I can pick from existing records instead of retyping existing info once I add a record in table A. (Shoot, I'm getting confused trying to explain this!!!). Anyway, I haven't been able to get it to work. Is it possible that in Access 97 this is not possible? Any words of wisdom?

I'd appreciate any and all reponses.

Thanks,

AVB

8 replies

by gpeters

I started using Access 97 about 2 years ago. Like you, I had never used the software before and I had to create a fairly complex database. I read a book called "Access 97 Bible" which was very useful for learning the basics of database design. However, to learn how to program visual basic into Access I took a class from AppDev called "Programming in Access 97". I doubt that the class is still offered since Accee 2000 is out, but you could probably still buy a copy of the video. Both the book and the class helped me out in a huge way. Hope this helps you.

As for your database question...You should be able to get your idea to work. I also use sub-forms in my database. Even layered sub-forms like you are trying. The only thing I don't useis table relationships. Those always confused me. Try un-doing the relationships and build the forms using queries that you can then use to tie the tables together. Sorry if this doesn't make much sense. Table relationships are not my strong suit.

by scott

Que makes a good book. It is worth $50, less if you get a used one from Amazon. If you aren't good at learning by reading they tend to have decent examples of how to do things. and samples. Also you can use the northwind database that comes with Access and disect it to solve problems, also search the web and their are tons of free databases made in Access that might be able to give you the basic structure you need to get you project done, you can disect it as you go. In Access their are tons ofway to do any one thing so you just have to get in and get dirty. Good luck!

by outthink

Marden's has received a ton of computer parts and current books. If you are near one (I am guessing you are with the "maine" in your email) you can pick up a Que, IDG, or Dummies book from Marden's for less than $20

When you create your sub-formsmake sure you are using the Wizard as it will walk you through it a step at a time.

Outthink@aol.com

by outthink

Ignore the email address in my previous reply (REnsor@PerformanceKnowledge.com). I am an Access instructor and two weeks ago I was downsized. I just realised I had not changed my email address with Tech Republic.

Roberta

by beaverdam sam

Thanks All. But I guess I didn't explain myself very well. Guess that happens after 11 hours staring at the screen! LOL...

It seems I was in a haze and for some reason was doing something (!!) wrong. I just tried again and it's doing what I thought it shoud, populating my form and table.

As for the "learning part". Is there any particular books that are better than others? Should I start with a "Dummies" book and progress from there? Or are the Que books the best place to start? I'm already aware that the Microsoft Press books are not for the faint of heart.

Roberta (Outthink@AOL.com), yes I am in Maine and have heard of Mardens. Which store are you refering to? When I do go, it's usually to the Waterville store. Also, where are you instructing? I live in Farmington and work in the Rumford area. Do I have any chance of getting a "flexible" class or training? I'll take any help I can get.

Again, thanks to all that replied.

Andy

by shaknbak

What you might want to try is to go to the code view of the form that you have the Combobox on and add something like the what appears below. But fist, in the design view of the form, select the combobox you are working with. Right click and go to properties. In the properties window, click on the "Event" tab. Look for the "OnChange" event. Click on the little box that appears to the right of the text field. A window will propably come up asking if you want "Expression", "Macro" or "Code". Select "Code". In the resulting window, add something like this:

Sub Combo1_Click()

Combo1.RowSource=("SELECT [whatever your field name is] FROM [TABLE_NAME] WHERE [whatever your field name is] LIKE '" & Combo1.Value & "'"

End Sub

Depending on how big the database is, this should work just fine with fast results. This was a nice little feature that Access had bundled with its controls. All you should have to do is to change the Table Name, Field Name and ComboBox Name. Note: You will need to have the single qoutes around the field if it's a text field. If it's a date field, replace the single quotes with # and if its a number field, you don't need anything around the field. Hope this helps.

by shaknbak

Instead of "Sub Combo1_Click()" It should be like below.

Sub Combo1_Change()

Combo1.RowSource=("SELECT [whatever your field name is] FROM [TABLE_NAME] WHERE [whatever your field name is] LIKE '" & Combo1.Value & "'"

End Sub

by davegoodness

Look at the example 'Northwind' database that MS provides. Also, when you try to set up the combo box on a form, did you define the field in the table as Combo????

This discussion is closed to new replies.