A Lot of Code

[DS]

Is there another way to do this? it seems like a lot of Code!
Thanks
DS

Dim LSun As Date
LSun = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 1"), 0)
If LSun > 0 Then
Me.CheckSun = -1
ElseIf Me.CheckSun = 0 Then
End If

Dim LMon As Date
LMon = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 2"), 0)
If LMon > 0 Then
Me.CheckMon = -1
ElseIf Me.CheckMon = 0 Then
End If

Dim LTue As Date
LTue = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 3"), 0)
If LTue > 0 Then
Me.CheckTue = -1
ElseIf Me.CheckTue = 0 Then
End If

Dim LWed As Date
LWed = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 4"), 0)
If LWed > 0 Then
Me.CheckWed = -1
ElseIf Me.CheckWed = 0 Then
End If

Dim LThr As Date
LThr = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 5"), 0)
If LThr > 0 Then
Me.CheckThr = -1
ElseIf Me.CheckThr = 0 Then
End If

Dim LFri As Date
LFri = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 6"), 0)
If LFri > 0 Then
Me.CheckFri = -1
ElseIf Me.CheckFri = 0 Then
End If

Dim LSat As Date
LSat = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " &
Me.TxtMenuID & "And StartDay = 7"), 0)
If LSat > 0 Then
Me.CheckSat = -1
ElseIf Me.CheckSat = 0 Then
End If

 

[Rob Oldfield]

I'm not exactly sure what you're doing here - is StartDay a date or a day
number? - but you're right that there's an easier way.

Use the dlookup in the same way you already are to return a date or day
number (and you can get a day number from a date by using the weekday
function)

Instead of having seven completely separate check boxes, add an option group
containing seven check boxes (well, actually they should really be option
buttons as that fits standard Windows design better) and then set the value
of the option group to whatever figure is produced by the weekday bit above.

Incidentally, the lines like ElseIf Me.CheckSun = 0 Then will never do
anything and are completely superfluous.

 

[DS]

I'm not exactly sure what you're doing here - is StartDay a date or a day
number? - but you're right that there's an easier way.

Use the dlookup in the same way you already are to return a date or day
number (and you can get a day number from a date by using the weekday
function)

Instead of having seven completely separate check boxes, add an option group
containing seven check boxes (well, actually they should really be option
buttons as that fits standard Windows design better) and then set the value
of the option group to whatever figure is produced by the weekday bit above.

Incidentally, the lines like ElseIf Me.CheckSun = 0 Then will never do
anything and are completely superfluous.

StartDay is a Number Field. I can't use an option group because some
records may have 1 or even 7 of the checkboxes checked off.
Thanks
DS

 

[Rob Oldfield]

StartDay is a Number Field. I can't use an option group because some

records may have 1 or even 7 of the checkboxes checked off.
Thanks
DS

So you can have two records in MenuDetails with the same menu ID but two
distinct StartDay values? I think I see a way of handling that, but if you
could confirm that that's right first please.

It might also be worth you giving a quick description of what it is you're
trying to achieve - there might be a better way of handling the issue.

 

[DS]

So you can have two records in MenuDetails with the same menu ID but two
distinct StartDay values? I think I see a way of handling that, but if you
could confirm that that's right first please.

It might also be worth you giving a quick description of what it is you're
trying to achieve - there might be a better way of handling the issue.

Yes you can have 2 MenuIDs with the same StartDay but they would have a
different TerminalID. Basically when I click on a Menu I want to see
what days are assigned to it for a particular terminal. I'm using a
check box so thatI can update or change the days as necesarry.
Thanks
DS

 

[Rob Oldfield]

Yes you can have 2 MenuIDs with the same StartDay but they would have a
different TerminalID. Basically when I click on a Menu I want to see
what days are assigned to it for a particular terminal. I'm using a
check box so thatI can update or change the days as necesarry.
Thanks
DS

I'm confused again. Your original code doesn't use TerminalID, so it will
look at all TerminalIDs for that MenuID and hence 'add' the ticks for each
of those records. If you then update one of those ticks then how does the
app know which record to update?

 

[DS]

I'm confused again. Your original code doesn't use TerminalID, so it will
look at all TerminalIDs for that MenuID and hence 'add' the ticks for each
of those records. If you then update one of those ticks then how does the
app know which record to update?

The record is already filtered by TerminalID on the form.
So its only dealing with the records for that recoredset.
Thanks
DS
Thanks

 

[Rob Oldfield]

The record is already filtered by TerminalID on the form.
So its only dealing with the records for that recoredset.
Thanks
DS
Thanks

So more than one check box ticked implies two records with the same MenuID
and same TerminalID. Correct?

If you open the form and two boxes are checked, and you then check a third,
how do you want the data to be updated? With three records - one for each
of those checks?

 

[DS]

So more than one check box ticked implies two records with the same MenuID
and same TerminalID. Correct?

If you open the form and two boxes are checked, and you then check a third,
how do you want the data to be updated? With three records - one for eachhe sam
of those checks?

Right you can have the same MenuID and TerminalID but a Different day.
Yes everything would be updated.
Thanks
DS

 

[Rob Oldfield]

Right you can have the same MenuID and TerminalID but a Different day.
Yes everything would be updated.
Thanks
DS

I think I'm definitely missing something here (and you'll need to explain
what Menus and Terminals are, and more detail on what you're trying to set
up if that is the case). But why not just have seven boolean fields - one
for each day - with each one set as the data source of a check box?

 

[JohnGriffiths]

When you see a lot of code that is similar exect for names of objects,
treat it like you would repeating groups in a table.
Factor it out.
Move repeating fields to own table.
Move repeating code to own procedure/fuction/loop.

HTH John

Air code =================================>>

CheckMenu Me.TxtMenuID, 1, Me.CheckSun
CheckMenu Me.TxtMenuID, 2, Me.CheckMon
CheckMenu Me.TxtMenuID, 3, Me.CheckTue
CheckMenu Me.TxtMenuID, 4, Me.CheckWed
CheckMenu Me.TxtMenuID, 5, Me.CheckThu
CheckMenu Me.TxtMenuID, 6, Me.CheckFri
CheckMenu Me.TxtMenuID, 7, Me.CheckSat

------------------------------------------------------
'v1

Private Sub CheckMenu(MenuID As Long, StartDay As Integer, ByRef chkBox As
CheckBox)
Dim tmpDate As Date
tmpDate = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " & _
MenuID & "And StartDay = " & StartDay), 0)
chkBox = (tmpDate > 0)
End Sub

------------------------------------------------------
'v2

Private Sub CheckMenu(MenuID As Long, StartDay As Integer)
Dim tmpDate As Date
Dim DateName As String

tmpDate = Date() - Weekday(Date()) + StartDay - 1
DateName = Format(tmpDate, "Ddd")

tmpDate = Nz(DLookup("StartDay", "MenuDetails", "MenuID = " & _
MenuID & "And StartDay = " & StartDay), 0)

Me.Controls("Check" & DateName) = (tmpDate > 0)
End Sub

<<Air code =================================