Subforms

[jdickerson]

I am very new to access. I am using the database to track reject rates.
Right now I have 4 tables set up (4 different types of machinery). I would
like to set up a data entry form for the user that will capture all relevant
information and place it in the proper table. I have a form right now that
includes a drop down selection to choose the type of machine. I would like
to arrange it so that choice made determines what fields are provided to
record rejects.

For Example. IF machine1 is chosen from the list then only fields related
to machine1 appear. After completion of all required fields the information
is set to the correct table.

 

[Arvin Meyer [MVP]]

You must first fix your table design. All machines belong in 1 table. You
need another table for machine types, and possible tables for machine
attributes which are specific to a machine type and machine.

You will then enter a combo for MachineType, and the AfterUpdate event can
hide fields which aren't relevant. If there are lots of fields specific to a
machine type, 1 or more tables can be used to record the data specific to
that type, and the AfterUpdate event will show the proper subform.

 

[jdickerson]

First, I would like to thank you for your quick response. Now here is my
issue. I am having trouble altering the AfterUpdate event. I have created 1
table for machine types (primary keys), a second table for machines (using
foreign keys to designate the type). I have also created 1 table for each
type a machine (4 in total) listing the rejects attributed to that machine
type. I have succesfully created combo boxes that list the machines. I am
semi-familiar with creating forms and subforms. If you could give me a bit
more detail on AfterUpdate events I think my problem will be solved.

 

[Arvin Meyer [MVP]]

Let's say that your combobox is named cboWhatever. The code (untested) might
look something like this:

Private Sub cboWhatever_AfterUpdate()
Select Case Me.cboWhatever
Case = "Machine Type 1"
txtSomething1.Visible = False
txtSomething2.Visible = False
txtSomething3.Visible = False
txtSomething4.Visible = True
Case = "Machine Type 2"
txtSomething1.Visible = False
txtSomething2.Visible = True
txtSomething3.Visible = True
txtSomething4.Visible = False
Case = "Machine Type 3"
txtSomething1.Visible = True
txtSomething2.Visible = False
txtSomething3.Visible = True
txtSomething2.Visible = False
' etc.
End Select
End Sub

Now this code goes in the after update event which you get to by opening the
property sheet for cboWhatever, clicking on the Events tab, choosing the
After Update event, and choosing [Event Procedure] at the combo, then
clicking on the ellipses (...) to open a code window. Make sure you change
the names of everything to reflect your own control names.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[jdickerson]

Arvin,

Thank you again for your help. I could not get the code to work (after
trying several different versions). This may be a bit too advanced for me.
I have ordered Access 20003 Programming for Dummies. I appreciate your
efforts. If I have any other questions you will be the one I look for.

Let's say that your combobox is named cboWhatever. The code (untested) might
look something like this:

Private Sub cboWhatever_AfterUpdate()
Select Case Me.cboWhatever
Case = "Machine Type 1"
txtSomething1.Visible = False
txtSomething2.Visible = False
txtSomething3.Visible = False
txtSomething4.Visible = True
Case = "Machine Type 2"
txtSomething1.Visible = False
txtSomething2.Visible = True
txtSomething3.Visible = True
txtSomething4.Visible = False
Case = "Machine Type 3"
txtSomething1.Visible = True
txtSomething2.Visible = False
txtSomething3.Visible = True
txtSomething2.Visible = False
' etc.
End Select
End Sub

Now this code goes in the after update event which you get to by opening the
property sheet for cboWhatever, clicking on the Events tab, choosing the
After Update event, and choosing [Event Procedure] at the combo, then
clicking on the ellipses (...) to open a code window. Make sure you change
the names of everything to reflect your own control names.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

First, I would like to thank you for your quick response. Now here is my
issue. I am having trouble altering the AfterUpdate event. I have
created 1
table for machine types (primary keys), a second table for machines (using
foreign keys to designate the type). I have also created 1 table for each
type a machine (4 in total) listing the rejects attributed to that machine
type. I have succesfully created combo boxes that list the machines. I
am
semi-familiar with creating forms and subforms. If you could give me a
bit
more detail on AfterUpdate events I think my problem will be solved.