Using Option buttons to Select Days

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

Guest

Hi,
I was wondering if any one might know a quick solution to my problem:

I am trying to set a small system up that users can select any day(s) (i.e.
Mon, Tue, Wed etc) that they want a particular function to occur. Thier
choice can be 1 or all 7 days for selection.

(Basically this is an option group that allows more than one selection)

I have a table called Main which has a one-to-many relationship with a table
called days. So hopefully the main record can have up to 7 related records
in the days table.

My question is can anyone help with the logic to program the 7 option
buttons so that records are added to the days table when a user selects a
particular day? Also, if a user unchecks the option button then the record
will need to be deleted from the days table.

Is there a built in function in access that can do this?

Thanks,
GLT.
 
GLT said:
Hi,
I was wondering if any one might know a quick solution to my problem:

I am trying to set a small system up that users can select any day(s)
(i.e. Mon, Tue, Wed etc) that they want a particular function to
occur. Thier choice can be 1 or all 7 days for selection.

(Basically this is an option group that allows more than one
selection)

I have a table called Main which has a one-to-many relationship with
a table called days. So hopefully the main record can have up to 7
related records in the days table.

My question is can anyone help with the logic to program the 7 option
buttons so that records are added to the days table when a user
selects a particular day? Also, if a user unchecks the option button
then the record will need to be deleted from the days table.

Is there a built in function in access that can do this?

Thanks,
GLT.
Click to expand...

No, there's no built-in function for that. The option buttons can't
really be part of an option group, either, because an option group can
only have one value. So you're stuck loading and unloading records from
the table of days yourself. It's not unlike the code that is needed to
store multiple selected options from a multiselect list box.

You'll need code in the form's Current event to get the initial values
of all the "day" options, and code in the AfterUpdate event of each
option button. The following is just air code, but I'm thinking you
need something like this:

'----- start of example "air code" -----
Function UpdateDay()

' This is a helper function to add or remove a
' record from tblMainDays depending on the
' value and name of the option-button control
' that is passed as an argument. We require
' that the name of the control end with the
' day number, 1 to 7, for which the update
' is required.

Dim strSQL As String

With Me.ActiveControl

If .Value = True Then
strSQL = _
"INSERT INTO tblMainDays(MainID, DayNo) " & _
"VALUES(" & Me.MainID & ", " & _
Right(l.Name, 1) & ")"
Else
strSQL = _
"DELETE * FROM tblMainDays " & _
"WHERE MainID = " & Me.MainID & _
" AND DayNo = " & Right(.Name, 1)
End If

End With

CurrentDb.Execute strSQL, dbFailOnError

End Sub


Private Sub Form_Current()

Dim rs As DAO.Recordset
Dim intDay As Integer

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM tblMainDays " & _
"WHERE MainID=" & Me.MainID)

' Clear all day options.
For intDay = 1 to 7
Me.Controls("optDay" & intDay) = False
Next intDay

' Set those options for which there are records in rs.
With rs
Do Until .EOF
Me.Controls("optDay" & !DayNo) = True
.MoveNext
Loop
.Close
End With

Set rs = Nothing

End Sub
'----- end of example "air code" -----

Then you can set the AfterUpdate event property (on the Event tab of the
property sheet) of each of the 7 options controls named optDay1,
optDay2, ... optDay7 to this function expression:

=UpdateDay()

I think that should do it.
 
Thanks Dirk,

I have implemented what you said and I have it working.

There was a bug in one line (1st strSQL = ststement) is:

Right(l.Name, 1) & ")"

Should be just .Name:

Right(.Name, 1) & ")"

(just for anyone else out there who wants to use this.)

Thanks,
GLT
 
GLT said:
Thanks Dirk,

I have implemented what you said and I have it working.

There was a bug in one line (1st strSQL = ststement) is:

Right(l.Name, 1) & ")"

Should be just .Name:

Right(.Name, 1) & ")"

(just for anyone else out there who wants to use this.)
Click to expand...

Sorry about that. I had originally written it slightly differently,
using a Control object named "ctl", and then I changed my mind and
deleted (I thought) all the occurrences of "ctl". I'm glad you spotted
and fixed the problem.
 
Back
Top