[RESOLVED] Select case for checkButton to build a string

[nickthurg28]

Afternoon all

Slight problem, i've got several check buttons on a form so that the user can select more than one option. I thought that a case statement would be the best way to check which of the values are set to "True". If the value is True then a word would be passed to a string. Problem being that my string just isn't building up depending on what options have been selected.

Here take a look at this:-

Select Case Appointment
    Case chkDentist.Value = True
        Appointment = "Dentist"
    Case chkDoctor.Value = True
        Appointment = Appointment & "Doctor "
    Case chkHospital.Value = True
        Appointment = Appointment & ", " & "Hospital "
    Case chkOptician.Value = True
        Appointment = Appointment & ", " & "Optician "
    Case chkOther.Value = True
        Appointment = Appointment & ", " & txtOther.Text
End Select

I can't see why this isn't working, the variable Appointment is set as a string and should be creating a string of options

Your help on this would be great

Thanks

Nick

[GaryMazzone]

Where is Appointment being set to allow the select Case statement to run against? This might actually be better served in a series of If statements.

[nickthurg28]

Appointment is set as a public string variable in the module

yea i am thinking of just changing it to a series of IF's

[GaryMazzone]

In that case I don't think the Select case will work correctly. You are checking a string var against a True/False vale (the check). The If statements are the way I would go.

[nickthurg28]

I'll give that a go then let you know

i don't know eh, this is what you get when you think to much into things, thanks

[zeezee]

As I can see, the Select Case is wrong
And the If else would be the best soultion.
And make sure its not a If ... ElseIF ... End If.
You would have to check for each check box with If .. End If and append to Appointment . Other wise the If ... ElseIF ... EndIf would append only one Text box value.
Got it?

[nickthurg28]

Yes i have just realised that if ... else ... end if isn't the best way either, quite a messy way again!

If chkDentist.Value = True Then
        Appointment = "Dentist "
End If
If chkDoctor.Value = True Then
        Appointment = "Doctor "
        Else
        Appointment = Appointment & ", " & "Doctor "
End If
If chkHospital.Value = True & Appointment < "" Then
        Appointment = "Hospital "
        Else
        Appointment = Appointment & ", " & "Hospital "
End If
If chkOptician.Value = True & Appointment < "" Then
        Appointment = "Optician "
        Else
        Appointment = Appointment & ", " & "Optician "
End If
If chkOther.Value = True & Appointment < "" Then
        Appointment = txtOther.Text
        Else
        Appointment = Appointment & ", " & txtOther.Text
End If

As you can see i have changed it to add the ", " onto the end, however the if condition itself is wrong

[GaryMazzone]

I would try like this:

Dim Appointment As String
Appointment = vbNullString
If chkDentist.Value = True Then
   If Appointment <> vbNullString Then
       Appointment = Appointment & ", "
   End If
        Appointment = Appointment & "Dentist"
End If
If chkDoctor.Value = True Then
   If Appointment <> vbNullString Then
       Appointment = Appointment & ", "
   End If
    Appointment = Appointment &  "Doctor"
End If
If chkHospital.Value = True Then
   If Appointment <> vbNullString Then
       Appointment = Appointment & ", "
   End If
   Appointment = Appointment & "Hospital "
End If
If chkOptician.Value = True Then
   If Appointment <> vbNullString Then
       Appointment = Appointment & ", "
   End If
        Appointment = Appointment & "Optician "
End IF
If chkOther.Value = True Then
   If Appointment <> vbNullString Then
       Appointment = Appointment & ", "
   End If
   Appointment = Appointment  & txtOther.Text
End If

[zeezee]

I think its wrong again.
To solve this , either you have to create for all the possible combinations for the checks being true or false
Or
Check whether the Appointment is null or not.
eg:

If chkDentist.Value = True Then
        Appointment = iif(len(Appointment) > 0 , Appointment  & ", ","") &  "Dentist "
End If

EDIT: I was slow

[nickthurg28]

i was half way through writing something similar when your post came in. Hmmmmm vbNullString, never used that before, quite useful, one for the memory box

Thanks very much indeed

[GaryMazzone]

Your welcome. If this resolves the issue for you could you please use the Thread Tools above and mark the Thread as Resolved.

[Code Doc]

Consider building a check box control array. Add a command button and use code similar to this:

Dim Appoint() As String

Private Sub Command1_Click()
Dim Appointment As String
For I = 0 To 4
    If Check1(I).Value Then Appointment = Appointment + Appoint(I) + ", "
Next
If Len(Appointment) Then Appointment = Left$(Appointment, Len(Appointment) - 2)
MsgBox Appointment
End Sub

Private Sub Form_Load()
ReDim Appoint(4)
Appoint(0) = "Dentist"
Appoint(1) = "Doctor"
Appoint(2) = "Hospital"
Appoint(3) = "Optician"
Appoint(4) = "Other"
End Sub

[Static]

ooor.. if those are your only checkboxes...add the value to the tag property

Dim ctl as control
for each ctl in me.controls
if typeof(ctl) is Checkbox then
'add ctl.tag to your sql
end if
next

lol

[nickthurg28]

thank you all for your quick help, garymazzone's answer does the job perfectly, i'll resolve it now, thanks again