Running an Entire Process Under one Module

[Kevin McBrearty]

I am trying to consolidate and simplify everything to make it easier to
update or make changes to the database. Everything I currently have works
but it uses a one Module to start the process and bounces from Form to Form
until it is done. Each Form controls how to procede next. Here is a summary
of what I am trying to do:

1) Determine What type of Record the User would like to add.

This is pretty much taken care of. I have three types of records that
could be added, which includes Store, Manufacturer, and Distributor. Each
has its own function that can be called.

2) Assign the table that is going to be used, some of the input masks, etc

This is handled in the individual functions.

3) Use the same forms to to add all three types of records

********************************************************
Here is where I am running into problems
********************************************************
Each function is calling Sub Add_Record(). From Here it opens the first
form, [Add Record - Record Id] that prompts the user to enter the key field
and then verifies that the value is not currently being used.

What I want to be able to do is:
1. Open the Form so that the code stops running (acDialog?)
2. Format the Form for the specific record type, and or pass on any
variables to the form. (Problem)
3. Allow the user to Enter the Information
4. Close the form and continue with the code (Me.Visible = False?)
5. Have the code store the value that was entered in the form in a variable
to use on the following forms.
6. Repeat the process for the next form.

Any help would be greatly appreciated.
Thank you.

 

[Brian]

If the user has only to enter one or two entries, you might doing something
like this that uses append queries instead of forms:

1. Create an append query that uses the same data source and filter that the
form would use. Use DoCmd.OpenQuery "<QueryName>" instead of opening the form.
2. In the append query, for the Field name of the field where the user will
enter data, enter something like this: Key:[Please enter the key: ]

This will pause the query, display the prompt "Please enter the key: " and
wait for the user's input, then assign the input to the Key field before the
append. If it must be a unique key value, control that by making it
Indexed/No Duplicates in the table, and then capturing the 3022 duplicate key
error and forcing re-entry of the key if it is a duplicate.