Data Entry Issue

[jenvandiver]

Hi, thanks for any help in advance!

What I need to do (and I'm sure this is an extremely simple thing for most of you) is to create a form that will collect information from the user, then open another form with a button which will confirm the user's choices. I would like to have all the information saved in one table, however there are "categories" of information that I need to collect. What I would like is to start with Form(1) to collect general information, then have a button on that form which will open another to collect audience information, then another to collect system information, etc. Then at the end, I would like a button to open up a form that displays all their choices so they can confirm/edit. I've tried to do this myself but I keep getting code errors. Is there a simple way to lead them through the data collection process without getting overly complicated on the programming?

I appreciate the help!
Thanks again,

 

[AlanS]

You may want to re-think your approach before getting started. Data confirmation and validation are generally done on the same form as where the data is entered - using multiple forms is likely to produce an application that is less reliable, more clunky and much more difficult to create, debug and maintain. Simple validations involving a single field (e.g., the value must be at least 1 and no greater than 20) can be handled with the ValidationRule and ValidationText properties of the table field to which each form control is bound. More complex validations, and those involving multiple fields, can be handled in VBA code in the AfterUpdate event procedure(s) for the relevant control(s). User confirmation before the record is saved can be handled in VBA code in the form's BeforeUpdate event procedure.

Whether you have separate forms for the different categories of information (general, audience, system, etc.) depends primarily on whether or not they're stored in separate tables, and how the data is broken down into tables depends in turn upon the relationships between those categories. If you have one-to-many relationships, you'll probably want separate tables, and either separate forms or a single main form with appropriate subforms. If the relationships are all one-to-one, normalization rules dictate that all the fields be put into a single table, unless there is a clear reason to do otherwise (spaces savings, security considerations, more fields than Access will allow in a single table, etc.). As indicated above, you'll probably want only a single form for each table.

 

[jenvandiver]

Thanks for your response. The main reason I need a confirmation form to post the data entered by the user is because I need to create a word .doc from their choices. I've tried linking the bookmarks from the word .doc to "live" controls and it doesn't seem to work. However, if I have a control that is = to the data chosen in a combo box, and link my word .doc to the second control, it seems to work fine.

I think I understand what you're saying about separate tables. I've divided my information up a little more and it seems to be going a little smoother (at least for the moment!). Obviously, I would prefer a streamlined database without a lot of noise and clutter, however I'm not too versed on the intricacies of high-level programming - i.e, I don't know why I'm doing this for a living!!!

Thanks for your help and advice. This website is invaluable to so many people - thanks to senior members like yourself...

 

[AlanS]

Thanks for your kind words. If the main reason for separate forms is to export the record to Word, you can still do that without a separate form. Once the record has been saved, you can use a query to make it directly available to Word. The query would return all fields from the table where the record was saved, with a criteria that limits it to only the record currently displayed on the form (e.g., in the Criteria box for the field MyKey, you would enter a criteria such as Forms!MyForm!txtKeyField).

In Word, you can use the query as a data source for a mail merge.

Good luck!

 

[jenvandiver]

Thanks again for your help. Up to now, I have been putting controls for data collection onto one form, however I've run out of room. If I'm forced to use at least two forms to collect information for one table, how can I ensure the second form will open and continue collecting information for the same record?

 

[AlanS]

It's possible to coordinate two forms in the way you describe, but it's pretty cumbersome. Better to simply make one form accommodate all the fields in the table.

One way to do that is to make the form longer, and make sure that its ScrollBars property is set to either Both or Vertical Only. That way the user can scroll up and down, and thereby use a form which is bigger than the screen.

Another way is to use a tab control with multiple pages. Controls which logically go together can be grouped together in a single page, and you can add as many pages to the tab control as needed to accommodate all your fields. Important fields like CustomerName, AccountNumber, ID (or whatever your key field is) can be put directly on the form rather than on one of the tab pages, so that they remain visible regardless of which page is selected.

 

[jenvandiver]

Thanks again. I'd actually done one form with the tabs like you suggested, but since the data from the form/table will end up in a word .doc, some of the entries will be paragraphs/memo controls. This is why I'm having trouble fitting it all onto one form and also why I chose to try a different route than the tabs. I'd have had a horizontal row of tabs that visually weren't too appealing. I guess I've been thinking of data entry pages like those you see on the web, where you complete part of the information, then click to another page to continue, etc. I guess Access isn't necessarily like web design...

I appreciate the insight, however. At least now I know what my options are, what will work and what won't. Thanks for taking the time to answer my questions today!