form help

[JEM]

I am designing a database, after taken advice previously I have read up and
managed to normalise the fields in my tables. I was however, wondering if
there is a way when an outcome is not achieved it automatically opens up
another form to record the variance, action taken and outcome.

The achieved field was a yes/no data type however, again after reading
others experiences I was thinking either to make the field a number or text
type with a list box with values of yes, no or missing.

Can anyone please advice? Thanks in advance.

 

[Steve]

Open your form in design view and selecthe checkbox. Go to Properties - Data
and set Triple State to Yes. (Look up Triple State in the Help file). You
can now use the checkbox to indicate yes, no or missing. To take appropriate
action, add code to the AfterUpdate event of the checkbox.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[JEM]

I am fairly new to access and the use of codes,do you know the code which
will let me do this or were I'm likely to find one.

Thanks

 

[Steve]

Add the following code to the AftrUpdate event of your checkbox:
Select Case Me!NameOfYourCheckbox.Value
Case True
<<Code to do something>>
Case False
<<Code to do something>>
Case Else
<<Code to do something>>
End Select

The code under Case True will run when you check the checkbox. The code
under Case False will run when you uncheck the checkbox. The code under Case
Else will run when you first click on the checkbox when it is not checked.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[JEM]

Sorry to appear total thick but what is <<code to do something>> is this
where I make up the code to goto the other form?

Thanks for your patience

 

[Steve]

For Case True, you might just have code that raises a message saying the
outcome was achieved. For Case False, you would have code that automatically
opens up another form to record the variance, action taken and outcome. For
Case Else, you might just have code that raises a message saying it is not
known whether the outcome has been achieved.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Joan Wild]

You would change the data type of achieved to number. You can then use an option group bound to this field. In the afterupdate of the option group, assuming 1 is yes, 2 is no, 3 is missing...
Select Case Achieved
Case 1
'do nothing it seems
Case 2
DoCmd.OpenForm "name of form you want to open"
Case 3
'I don't know what you want to do here'
End Select

Steve's solution will not work for you, since a checkbox bound to a yes/no field will always store true or false. It doesn't matter if its triple state is set to true; a yes/no field cannot store a null.

 

[JEM]

I put in the following:

Select Case Me!Achieved.Value
Case True
MsgBox "Outcome Achieved"
Case False
Openmyfrm ("Ongoing Assessment Variance")

before I put this code in I put a module code like this

Public Function Openmyfrm(myfrmname As String)
DoCmd.OpenForm myfrmname
End Function

I actually forgot to to put in anything for the Case Else. I'll try it when
I get home.

Joan would I have to put in the module code in using your method?

Thanks for the advice in advance

 

[Joan Wild]

Your original post said that you wanted a field that held three values True, False, Missing. You can't use a yes/no field for this. change the Achieved field from Yes/No to Number - integer size. On your form I would use an option group with three options
1 - Yes
2 - No
3 - unknown or missing

Assuming the option group is named Achieved and is bound to the Achieved field
Select Case Me!Achieved
Case 1
MsgBox "Outcome Achieved"
Case 2
DoCmd.OpenForm "Ongoing Assessment Variance"
Case 3
' what do you want to do if it's unknown/missing?
End Select

I don't see any value in having a separate function to open a form, although there's no harm.

 

[JEM]

Can you tell me how to set up an option group with three options please.

 

[Joan Wild]

Open your form in design view. Ensure the Field list is showing (View, Field List if it's not).
With the toolbox showing (View, Toolbox if it's not), and the control wizard button depressed (the button with the wand and stars)...

Click on the Option Group button ( note you want the option group button, not the option button - the tooltip will tell you what you are pointing at)
Next drag the Achieved field from the field list to your form. The wizard will walk you through the steps to create the option group, which will be bound to the Achieved field.
Step 1 - enter the labels - These are the labels that will appear beside each option - Yes, No, and Missing
The remaining steps are fairly clear - just answer each one. Post back if you are having problems.

 

[JEM]

Hi Joan

Thanks for all your help it is much appreciated. I am working on the forms
at the moment and have managed to work out the group options. I am however
now having problems when applying the code to other fields I wish to to do
the same to. I know it is because the field contain 2 words for example say
field is First Name (Option group is bound to this field) I have entered the
following:

Select Case Me!First Name
Followed by the rest

It keeps saying that it can't find the field in the expression. I have
tried putting _ in the spaces and closing the spaces up but nothing seems to
work. Could you please tell me were I am going wrong. Do I have to put a
..Value next to it?

Many Thanks

 

[Joan Wild]

Because it has a space in the name of the field you need to enclose it in square brackets:
Me![First Name]

However you need to think carefully about this. The option group will store the values 1, 2, 3, etc., not someone's first name. Furthermore, you'll likely have two people with the same first name.

 

[John W. Vinson]

Select Case Me!First Name

Blanks are meaningful. Access is looking for something named Me!First, and
doesn't know what to do with the completely separate and unrelated word
"Name".

If you (unwisely, IMO) put blanks or special characters in field or control
names, you must wrap them in square brackets so Access will see them as "one
thing" rather than two separate words:

Select Case Me![First Name]

Consider using "camel case" and taking out the blanks; you won't have this
problem with fieldnames such as FirstName, LastName, etc.

John W. Vinson [MVP]

 

[JEM]

Hi Joan

Thanks very much for your help and taking the time to reply. I am fairly
new to Access, this site has been a godsend. I used first name as an example
the other fields I will be using this for are all Yes/No/Missing.

Because it has a space in the name of the field you need to enclose it in square brackets:
Me![First Name]

However you need to think carefully about this. The option group will store the values 1, 2, 3, etc., not someone's first name. Furthermore, you'll likely have two people with the same first name.

--
Joan Wild
Microsoft Access MVP

Hi Joan

Thanks for all your help it is much appreciated. I am working on the forms
at the moment and have managed to work out the group options. I am however
now having problems when applying the code to other fields I wish to to do
the same to. I know it is because the field contain 2 words for example say
field is First Name (Option group is bound to this field) I have entered the
following:

Select Case Me!First Name
Followed by the rest

It keeps saying that it can't find the field in the expression. I have
tried putting _ in the spaces and closing the spaces up but nothing seems to
work. Could you please tell me were I am going wrong. Do I have to put a
.Value next to it?

Many Thanks

 

[Joan Wild]

You're quite welcome.