show fields depending on another

[strawberry]

Hi, i am creating a database, an enquiry sheet

One of the questions is:-
Are there any solids in the fluid and the answer is either y or n

If they answer y then they need to fill in the next 3 fields
what size are they
are they hard or soft
What concentration are they

when i create my form, is there any way that i can only display the 3 fields if the user answers y to the question

or if not possible, prompt them to fill in the 3 fields and then if they answer n, take them to the next question

Many thanks
Hope you can help

Jane

 

[georgedwilkinson]

You can set the visible property of the fields in question on the After Update event of the check box. You may also have to do the same thing during the On Current event of the form.

 

[strawberry]

Many thanks George, i will have a go

 

[georgedwilkinson]

Give us a yell if you need more help.

 

[strawberry]

Hi George, thank you, can i take you up on that

I have so far created a form with the fields i have created so far.

As i said in my initial question, if they answer 'y' to question - 'are there solids present' then i want them to answer 3 more questions relating to the solids, if they answer 'n' then these 3 questions are n/a

I have clicked on the properties for field 'are there any solids present' and chose in the properties 'after update' then chose event procedure.

i now have showing
'Private Sub Solids_Particles_Present_AfterUpdate()

End Sub

Please can you help me with the code i need to enter.

The other 3 fields which are relevant if answer is 'y' are called

UnitsOfSolids
SizeOfSolids
ConcentrationOfSolids

Hope you can help

Many thanks
Jane

 

[MSAccessRookie]

Hi George, thank you, can i take you up on that

I have so far created a form with the fields i have created so far.

As i said in my initial question, if they answer 'y' to question - 'are there solids present' then i want them to answer 3 more questions relating to the solids, if they answer 'n' then these 3 questions are n/a

I have clicked on the properties for field 'are there any solids present' and chose in the properties 'after update' then chose event procedure.

i now have showing
'Private Sub Solids_Particles_Present_AfterUpdate()

End Sub

Please can you help me with the code i need to enter.

The other 3 fields which are relevant if answer is 'y' are called

UnitsOfSolids
SizeOfSolids
ConcentrationOfSolids

Hope you can help

Many thanks
Jane

I am not George, but I believe this does what he is suggesting

Note: This code assumes that the names of the four Control Fields on the Form are:

• [Solids Particles Present]
• UnitsOfSolids
• SizeOfSolids
• ConcentrationOfSolids

'Private Sub Solids_Particles_Present_AfterUpdate()
 
    If (([Solids Particles Present] = 'Y') Or ([Solids Particles Present] = 'y'))  Then
        Me.UnitsOfSolids.Visible = True
        Me.SizeOfSolids.Visible = True
        Me.ConcentrationOfSolids.Visible = True
    Else
        Me.UnitsOfSolids.Visible = False
        Me.SizeOfSolids.Visible = False
        Me.ConcentrationOfSolids.Visible = False
    End If
 
End Sub

 

[strawberry]

Hi many thanks for help with this. Please can you help further. i am now getting compile error, syntax error.

my code now looks like this in the 'afterupdate property'


Private Sub Solids_ParticlesPresent_AfterUpdate()

If (([Solids_ParticlesPresent] = 'Y') Or ([Solids_ParticlesPresent] = 'y')) Then
Me.UnitsOfSolids.Visible = True
Me.SizeOfSolids.Visible = True
Me.ConcentrationOfSolids.Visible = True
Else
Me.UnitsOfSolids.Visible = False
Me.SizeOfSolids.Visible = False
Me.ConcentrationOfSolids.Visible = False
End If

End Sub

I then went into my form and entered in a record to test but as soon as i ticked the box for 'are articles present' it came up with compile error, syntax error

Many thanks

Jane

 

[MSAccessRookie]

If you run the procedure (click the check box), you should get an error message box that has the options Continue, End, Debug, and Help (Continue may be greyed out). If you select Debug, then the VB Editor Window will appear and one line will be Highlighted. Which Line is highlighted?

 

[strawberry]

this line is highlighted

If (([Solids_ParticlesPresent] = 'Y') Or ([Solids_ParticlesPresent] = 'y')) Then

when i created the table, i chose the y/n box, when i create the form, it just comes up as tick box. maybe this is causing the problem

Many thanks

Jane

 

[georgedwilkinson]

Try this:

If [Solids_ParticlesPresent] = True Then

or

If Me.[Solids_ParticlesPresent] = True Then

 

[georgedwilkinson]

Or you can just do this:

Private Sub Solids_ParticlesPresent_AfterUpdate()
Me.UnitsOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.SizeOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.ConcentrationOfSolids.Visible = Me.[Solids_ParticlesPresent]
End Sub

 

[strawberry]

Brilliant, your a star. i used the top line of the choices you gave me and it works
If [Solids_ParticlesPresent] = True Then

Can i ask another question. When i create the form, i obviously have shown all the questions, i notice that when i go into the form to add a record, the box is automatically ticked but when i untick it, the next 3 questions disappear from the screen. is this how this should work. You wouldnt have it unticked initially then when the user ticks the box, the 3 questions appear

 

[georgedwilkinson]

Try this:

Private Sub Form_Current()
Me.UnitsOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.SizeOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.ConcentrationOfSolids.Visible = Me.[Solids_ParticlesPresent]
End Sub

Or use the other code if it makes you more comfortable in the On Current event of the form.

 

[strawberry]

sorry George, as you can see i am a newbie to this, where do i put this code. is this additional code to what we already have. What will new code do.

Many thanks
Jane

 

[georgedwilkinson]

Just put it underneath the code you already have. It will do essentially the same thing as what you already have whenever the form gets a new record set (for your purposes, anyway).

 

[strawberry]

thanks George. one more question, last one promise!

I still have fields to add to my enquiry database and again some of the questions will only be relevant/visible if the answer to a previous one is yes. do i just paste both lots of code into after update property (obviously changing the field names)

eg

Private Sub Solids_ParticlesPresent_AfterUpdate()

If [Solids_ParticlesPresent] = True Then
Me.SizeOfSolids.Visible = True
Me.UnitOfSolids.Visible = True
Me.ConcentrationOfSolids.Visible = True

Else
Me.SizeOfSolids.Visible = False
Me.UnitOfSolids.Visible = False
Me.ConcentrationOfSolids.Visible = False
End If

End Sub
Private Sub Form_Current()
Me.SizeOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.UnitOfSolids.Visible = Me.[Solids_ParticlesPresent]
Me.ConcentrationOfSolids.Visible = Me.[Solids_ParticlesPresent]
End Sub


Many thanks
Jane

 

[georgedwilkinson]

You can do it that way.

Or you could write a separate subroutine for each set of controls you want to change and call that subroutine from Form_Current() and the "controlling" control's AfterUpdate() event.

Or, you could drive this via your database, having all the conditions in a table and use less code, but the system would be more complex, from your perspective.

Unless you have a ton of these or are a pretty formidable programmer, I recommend you go with the first option.