Open new form based on a value entered in current form

[cinders]

I have a situation where I have a table containing prebooked appointments. Each appointment is booked with a specific appointment type. I have a main form that you can input the appointment number, the company and some other details.

When you input the appointment type I need a form that is built for that appointment type to open up and populate with the information that has been input on the main form.

The appointment types varies and each type of appointment has its own set of fields.

Example 1:

Input on Main Form....
Appointment: 12345
Company: TEAM Trucking
Appointment Type: 1 - Receive Exports
[Press TAB] or if easier [press ENTER]

Now I need the Receive Exports form to open and populate with the appointment number and company name

Example 2:
Input on Main Form....
Appointment: 99998
Company: Reimer Trucking
Appointment Type: 2 - Receive Empty Container
[Press TAB]or if easier [press ENTER]

Now I need the Receive Empty Container form to open and populate with this information

Any suggestions on how to do this???

Thanks

Cindy

 

[RichO]

To begin with it sounds as if you are going to have to create separate tables for each appointment type seeing that they have their own set of fields.

You will want to use the appointment number as a key to link the appointment type tables to the existing table. That is, assuming the appointment number is a unique value - no duplicates in the main table.

After you have your tables created, then you will have to create a query for each appointment type to bring the records from the 2 tables into the form, and then move on to the individual forms.

There is more to do after that but that would be step 1.

 

[cinders]

To begin with it sounds as if you are going to have to create separate tables for each appointment type seeing that they have their own set of fields.

You will want to use the appointment number as a key to link the appointment type tables to the existing table. That is, assuming the appointment number is a unique value - no duplicates in the main table.

After you have your tables created, then you will have to create a query for each appointment type to bring the records from the 2 tables into the form, and then move on to the individual forms.

There is more to do after that but that would be step 1.

I've managed to get that far. What I am looking for is the coding that would say on Form A, in Field X if the value is 1 then open and populate FORM B upon pressing the TAB key, if the value is 2 open and populate FORM C upon pressing the TAB key, if the value is 3 then open and populate FORM D upon pressing the TAB key etc.....

Any suggestions for this???

Thanks

 

[RichO]

We'll refer to the "Field X" text box as "txtAppType"
In the On Lost Focus event on the txtAppType text box try this code:

Dim stDocName As String
stDocName = "Form" & Chr$(Me.txtAppType + 64)
DoCmd.OpenForm stDocName

If you name your forms "FormA", "FormB", "FormC"... etc, this code converts the number entered to its corresponding letter of the alphabet. You will probably want to insert a piece of code into the Before Update event to prevent an invalid entry. Otherwise Access will attempt to open a form that doesn't exist if an illegal entry is made.