Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2005

Tip: Looking for answers? Try searching our database.

Using Option buttons to Select Days

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
GLT - 22 Jan 2005 19:03 GMT
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.
Dirk Goldgar - 22 Jan 2005 22:40 GMT
> Hi,
> I was wondering if any one might know a quick solution to my problem:
[quoted text clipped - 19 lines]
> Thanks,
> GLT.

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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

GLT - 23 Jan 2005 15:45 GMT
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

> > Hi,
> > I was wondering if any one might know a quick solution to my problem:
[quoted text clipped - 99 lines]
>
> I think that should do it.
Dirk Goldgar - 23 Jan 2005 16:23 GMT
> Thanks Dirk,
>
[quoted text clipped - 9 lines]
>
> (just for anyone else out there who wants to use this.)

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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.