Form wizard needs more magic dust :)

[sorebrain]

I am quite new to Access 2000 and have some questions on a general level. I have 3 tables in my 1st database attempt and am trying to use the form wizard to allow fields from all 3 tables to be on 1 form. I add the fields I choose from the 3 different tables and prefer to set it as single form and justified to put all the fields on one main form. But when I test it, I get some fields that cannot connect to the recordset. Does the wizard have enough magic dust to make this work or how do I make it work this way? I am thinking it can be done but maybe I am thinking wrong and its not the best way to do it? If I try other wizard options I always get 2 or 3 navigation controls which I don't want (1 per table). Do I need to go to design my own using subforms or tabs?
Thanks,
sorebrain

 

[sorebrain]

Maybe I need more magic dust...

Ok, I went back and selected all of the fields from all of the 3 tables and it looks like I am down to just one field that I need to work on. Its a foreign key number ID field and I think I need to work on it for awhile...I am getting closer. Can a foreign key be a text value from a lookup list?
Thanks,
sorebrain

 

[yeatmanj]

I have been working with Access for 4 years now. I know it may be time consuming, but I highly recommend picking up a step-by-step manual so that you can learn how to use access more efficiently. It will help with terminology and many other aspects.

Make sure that if you buy a manual it is written for the version of Access you are currently using. My first dive into the pond was a Microsoft publication called MS-Access Step-By-Step. It was written for access 97 and helped greatly. Not only does it teach about the use of Access, but it also teaches many database fundamentals (not saying you don't know them already, but in case you don't). Naming conventions are very critical, especially if you expect other people to be able to pick up where you left off. That is just one way a step-by-step manual can help.

I would like to help you with your current problem, but I do not understand exactly what it is you need. I never use the wizards and if I have multiple table, either pull together using a query or SQL before trying to put all the info on one form. Either that or map each table to its own subform.

 

[sorebrain]

More info on my question

Hi, and thanks for your response. I live just west of you down I-10 in Crestview. Anyhow, I just ordered a book for Access 2000 and the other books I have read are for Access 97. I have also been reading Visual Basic Database programming but I believe that was the backwards approach. So to put it plainly, I have exactly 7 days experience in Access but have Visual Basic experience. To clear up my question, what fields are NEVER to be put on a form? Using the wizard, I put ALL of the fields from ALL of the tables to experiment. I can view the form and enter data in most of the fields. I took notes on the ones I could not. These are the related autonumber fields in most cases. My question is this: If I take the autonumber fields off of the form, how do they receive their click event? I guess I am just asking the basics of what needs to be on the form and what doesn't? Does that make any sense?
Thanks,
sorebrain

I bought this:Access 2000: The Complete Reference [Paperback] by Andersen, Virginia

 

[yeatmanj]

First thing, In A2K never use autonumber fields in a table that you will be deleting records from.

Reason: When you compact and repair the database A2k will reset the autonumber to the earliest empty slot in the sequence of numbers.

Example: Records 1-10 are created, you delete record 2 then compact and repair the database (because it is access and will swell in size if you don't). At this point A2k will reset the autonumber to 2, so the next record you enter will now be record 2.

Problem: The next autonumber, once 2 is created, will be 3 which is already being used. Since it is an autonumber there can be no duplicates, hense the problem.

Solution: Create the field in the table, but just set it to number. Put the field on the form and use the expression "DMax("[FieldName]", "TableName") + 1" in the Default Value Property or in the VBA code behind the form to set the value for the field when there is a new record. Syntax may change depending on whether it is VBA or Default Value Property.

2. You can put anything you want to on a form, but the general rule of thumb is; if you don't want users to be able to see it or change it make it invisible (Set the visible property of the control to NO) if it has to be on the form.


All that aside, if you are going to use autonumbers they will generate automatically when a record is started regardless of whether they are on the form or not. Play with entering data in the table with the autonumbers. You should find that when you start a new record the autonumber fills in (1). If you cancel the entry or delete the record and start again, the autonumber should skip to the next number (2, 3, 4, etc).

I also took a couple of classes at Software Solutions Now (www.ssnow.com) that helped a lot. One thing the instructor stated firmly was that she never used autonumbers. She said she would always use the DMax function to set up sequential numbering. That way the next record is always the max number in the list plus 1.

I hope this helps. Also, if you want to email me I would be happy to see if I can dig up the manuals I have and pass them on to you. (jyeatman@ene.com)

 

[sorebrain]

Interesting...

Thats good to know about the autonumber. I didn't know that when you compact and repair the database that it would do that. I thought it would still start at the next highest number and leave the deleted records as unused record numbers like:
1
2
5
6
7
start here at number 8

But I haven't the experience except to delete all my records and compact and repair just to start over at record number 1 while testing....I am going to try your method and see how it works as it sounds logical.

I noticed the autonumber fields automatically filled in but when I used more than one table with more than one autonumber field I ran into some missing join key errors. The more I worked with it I was able to make progress.

Thanks for the offer on the manuals, I have sent you an email. The ssnow link doesn't seem to be working but I can google for that...
Thanks,
sorebrain

 

[yeatmanj]

Create one field that will be the same in all the tables. This field should contain a number (or other unique identifier) Use the relationship manager to create a relationship between the "sub"tables and the "master" (my terminology isn't perfect either ). Set the relationship from master to sub to "Enforce Referential Integrity" and "Cascade Update and Delete". This way when you create a record in the master it will automatically create and delete records in the subs.

 

[sorebrain]

A little history

Just so you know where I am at.

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

That might help a little and I also emailed you privately,
Thanks,
sorebrain