Using a Subform a Listview. 1

[bartus991]

In stead of a listview I have created a form with different subform which have the function of a listview. Now I want to create a listview as subform which should look like following:

Name		Monday Tuesday Wednesday Thursday Friday Saturday Sunday
Weekend                                                 X       X
Weekdays      X       X       X         X       X
Daily         X       X       X         X       X       X       X

My table structure is as follows:

Table 1 - DaySelection
DaySelectionId
Name
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Child Table – DaySelectionSub
DaySelectionFK
DaySelectionSubId
DayNumber (1 for Sunday, 2 for Monday, 3 for Tuesday etc.)

I was experimenting with the following code:

Private Sub Form_Load()
  Dim Monday As Boolean
  Dim rs As dao.Recordset
  
  Set rs = CurrentDb.OpenRecordset("DaySelection", dbOpenTable)
  Do While Not rs.EOF
     rs.Edit
     If IsSelectedMonday(rs!DaySelectionId) Then
        rs!Monday = True
     Else
        rs!Monday = False
     End If
     rs.Update
     rs.MoveNext
    Loop
End Sub

Public Function IsSelectedMonday (ID As Long) As Boolean
  
  If Not Nz(DLookup("DayNumber", "DaySelectionSub", "DaySelectionId = " & ID _
     & " AND Dag = " & 1), 0) = 0 Then
     IsSelected = True
  End If
End Function

But somehow it will not check the appropriate Boolean fields in the table DaySelection.

 

[misscrf]

I am a little confused about the set up you have. In table one it looks like you are saying these are the field names:

Name
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday


Is that right? If so, this is not normalized as name is a reserved value and shouldn't be used for a field name. Also, instead of each day of the week as separate columns, I would make 1 field to be FKDOW (day of week) and then have a lookup to a foreign table with a PK and a colmn for the txtDOW with each nice name. The FKDOW in the main table should just have the PK of the DOW stored.


I can't tell if that is what you actually are saying is your setup. If it is, then on your form, you just want to see checkboxes for each day?

I would add them to the form as unbound and oncurrent of your form (bound to the main table), each check would get the following kind of statmennt:


If [dayselectionid].value = 1 then me.chkSunday = 1
end if

If [dayselectionid].value = 2 then me.chkMonday = 1
end if

If [dayselectionid].value = 3 then me.chkTuesday = 1
end if


If you have navigation buttons for next and previous, you will need to put them there as well. Also if you leave the form, ie a search form, and come back to a new result, you will also want the code there as well.

hth


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Why not make the "day selection form" an unbound form? I think you are just using it to populate values in another table.

Also this does not look correct based on what you showed:
Nz(DLookup("DayNumber", "DaySelectionSub", "DaySelectionId = " & ID _
& " AND Dag = " & 1), 0) = 0

what is Dag? And why concatenate 1?

 

[bartus991]

I would like to create a kind of schedule to make a quick selection on which days something is valid.

In the above mentioned Output the X is representing a checkbox an must always be in the same order. I will use a continues subform that can be readed horizontally (on which day is the DaySelection valid, and vertically on which is which DaySelection valid.

I will created multiple form with this, one to add or delete the subrecords of the DaySelection. If Monday is checked, then in the DaySelectionsub table a record with Monday is added.

The other form is use to look up the values and with a Checkbox at the begin of each row you will be able to add or delete the DaySelection as a sub of the Main table.

@MajP
Dag should be Day and It should concatenate 2 for Monday.

The checkboxes won't have an event, only after clicking the update button, Visual Basic looks up which boxes have been checked and not been added a child record (with the correct parent record) it will add the record, if unchecked it will be deleted.

I can use the If [dayselectionid].value = 1 then me.chkSunday = 1
end if
in my form open code, but each checkbox represents a child record and has to be shown with the correct parent record.

 

[MajP]

Take a look at this demo. I changed some of the fields to demo the concept. See if this gives you some ideas.

http://www.4shared.com/file/253843223/20c6f07a/UseSubFormAsAcontrol.html

 

[MajP]

Use this demo

http://www.4shared.com/file/254519062/ee4d93c2/UseSubFormAsAcontrol_v11.html

 

[bartus991]

Thanks this demo is very helpfull, except, the form isn't shown as a continous form.

On your form you use Weekend, Weekday and Daily but these are the names which I use as a record. For example:

Id  Name              ChildId  Day
 1  Weekend               1    Saturday
                          2    Sunday
 2  Daily                 3    Monday
                          4    Tuesday
                          5    Wednesday
                          6    Thursday
                          7    Friday
                          8    Saturday
                          9    Sunday
 3  Weekdays             10    Monday
                         11    Tuesday
                         12    Wednesday
                         13    Thursday
                         14    Friday

In my case employees will be the name that is stored in the main table (Weekdays, Weekend, Daily etc.) Only one row with checkboxes will be show named Monday, Tuesday etc...

Depending of the childrecords the checkboxes have to checked. But I'll show this on a continues form.

The code in the demo is working but not on a continues form...

 

[MajP]

I am suggesting that there is absolutely no need for a continous form. All I have to do is click on some control and save the childID value. If I save the value 10 in the data table I know that the Weekday monday is saved. Why would I need a continous form?

 

[bartus991]

This continues form is used as a list view which allows you to add records to the selected table. The Days as above are only for fast reference and a wuick overview which DaySelections a approriate.

Please not I know only mentioned Weekend, Daily and Weekday, but there are a lot more and not al simple to understand like M&B.

That's why I need the checkboxes from Monday till Sunday as a continues form.

 

[bartus991]

Please find here a image of what I mean:

http://www.4shared.com/file/257370082/6537fde6/Form_Tab_Day_and_Time.html

The first column states Active, this allows you to add or deleted records to a specified table.

The second column states Titel, this is the name of the specified DaySelection (table DaySelection)

The other columns are stating the days, which may only be checked is there is a Child record in de childtable DaySelectionSub.

 

[MajP]

See demo using continous form.

http://www.4shared.com/file/257527613/44115e5f/UseSubFormAsAcontrol_v13.html