how do I set up a lookup table to open multiple forms in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in a contacts database for type of work called "work". I have
a form for each work type which uses this table and the main Contacts table
to display all those included in each work type group.

I want to make a form with a combo box that offers the values held in the
"work type" table and when you select one it takes you to the corresponding
Form for that worktype.

Any ideas?
 
Hi, Andy.

Create your combo box with the wizard. Toggle the wizard on my depressing
the wand & stars button on the Toolbox toolbar (View, Toolbox).

Then create an AfterUpdate event to open the proper form.

Dim stDocName As String
Select Case [cboWorkType] ' Change to the name of your combo box
' Each Case is for each possible value of the worktype
' This is the Bound column value, likely the primary key, not necessarily
' what is *displayed*
Case WorkType1
stDocName = "WorkType1Form"
Case WorkType1
stDocName = "WorkType1Form"
End Select
DoCmd.OpenForm stDocName

Alternatively, you could store the form name in a text field in your
Worktype table, and include it as a 0" width column in your combo box, so
that it does not display. This would be my preference, since it makes it
much easier to edit, and simplifies the code. Assuming this would be the
third column:

Dim stDocName As String
stDocName = YourComboBox.Column(2)
DoCmd.OpenForm stDocName

Hope that helps.
Sprinks
 
On your first form, place a combo box with its Row Source property set to a
Query based on the 'work type' table. Then, in the AfterUpdate event of the
combo box, use the DoCmd.OpenForm command to open the appropriate form based
on the selection in the combo box.

HTH,

Carl Rapson
 
Thanks Sprinks,

I went with the third column idea and its working great.
Just in time for the weekend too.......what a lifesaver!

Thanks Again,
Andy_2005


Sprinks said:
Hi, Andy.

Create your combo box with the wizard. Toggle the wizard on my depressing
the wand & stars button on the Toolbox toolbar (View, Toolbox).

Then create an AfterUpdate event to open the proper form.

Dim stDocName As String
Select Case [cboWorkType] ' Change to the name of your combo box
' Each Case is for each possible value of the worktype
' This is the Bound column value, likely the primary key, not necessarily
' what is *displayed*
Case WorkType1
stDocName = "WorkType1Form"
Case WorkType1
stDocName = "WorkType1Form"
End Select
DoCmd.OpenForm stDocName

Alternatively, you could store the form name in a text field in your
Worktype table, and include it as a 0" width column in your combo box, so
that it does not display. This would be my preference, since it makes it
much easier to edit, and simplifies the code. Assuming this would be the
third column:

Dim stDocName As String
stDocName = YourComboBox.Column(2)
DoCmd.OpenForm stDocName

Hope that helps.
Sprinks

andy_2005 said:
I have a table in a contacts database for type of work called "work". I have
a form for each work type which uses this table and the main Contacts table
to display all those included in each work type group.

I want to make a form with a combo box that offers the values held in the
"work type" table and when you select one it takes you to the corresponding
Form for that worktype.

Any ideas?
Click to expand...
Click to expand...
 
Thanks Carl,
I used Sprinks method this time, but its good to know different ways of
doing things - I'm writing that one down for later.

Cheers,
Andy_2005
 
Back
Top