Logicals - Efficient design or not

[Charles D Clayton Jr]

A2K
I have an unbound form that has 9 unbound textboxes. In the first one
a person enters a week ending date, the second a person picks from a
list of options and 3-9 the person enters a number. 3-9 represent days
of the week and not every day will have a number recorded. This is the
code that I am using (it works) and my question is, is this the most
efficient way to do this task. I take each day of the week and
determine if it has any data and if it does, then add that information
to the recordset, otherwise I do nothing. So I have 7 If-Then
statements. Everything works fine but I wanted to ask the newsgroup if
I should be doing this task differently.

'Add new records
With rst
If Me.txtSun <> "" Then 'only do if it has data
..AddNew
..Fields("id_CostCodes") = Me.cboCostCode
..Fields("hrsspent") = Me.txtSun
..Fields("dt") = Me.txtSun_6
..Update
End If
If Me.txtMon <> "" Then 'only do if it has data
..AddNew
..Fields("id_CostCodes") = Me.cboCostCode
..Fields("hrsspent") = Me.txtMon
..Fields("dt") = Me.txtMon_5
..Update
End If

Thanks,

Charles D Clayton Jr

 

[Graham Mandeno]

Hi Charles

You could do this with a loop. Given a counter from 1 to 7, the date of
each day in the week can be calculated by subtracting 7 from the week ending
date and adding the counter.

To work out which textbox to use, you could use Format(dateval,"ddd") to
convert the date to "Sun", Mon", etc, or you could simply rename txtSun as
txtDay1, txtMon as txtDay2, etc.

Then your code would look like this:

Dim dtDay as date, txtHrs as textbox, iDay as integer
With rst
For iDay = 1 to 7
dtDay = CDate(txtEndOfWeek) - 7 + iDay
Set txt = Me("txtDay" & iDay)
If IsNumeric( txt.Value ) Then
.Fields("id_CostCodes") = Me.cboCostCode
.Fields("hrsspent") = txt.Value
.Fields("dt") = dtDay
.Update
End If
Next iDay
End With

 

[Charles D Clayton Jr]

Thanks so much for the help. I had to make a couple of changes (but
it helped me to understand some more things). You accidentially typed
txtHrs as textbox but then only used txt in the rest of the code and I
noticed that you left off the .addnew command. When I made those
changes it worked great.

I appreciate your skill and your willingness to share with me.

Blessings,

Charles D Clayton Jr

Hi Charles

You could do this with a loop. Given a counter from 1 to 7, the date of
each day in the week can be calculated by subtracting 7 from the week ending
date and adding the counter.

To work out which textbox to use, you could use Format(dateval,"ddd") to
convert the date to "Sun", Mon", etc, or you could simply rename txtSun as
txtDay1, txtMon as txtDay2, etc.

Then your code would look like this:

Dim dtDay as date, txtHrs as textbox, iDay as integer
With rst
For iDay = 1 to 7
dtDay = CDate(txtEndOfWeek) - 7 + iDay
Set txt = Me("txtDay" & iDay)
If IsNumeric( txt.Value ) Then
.Fields("id_CostCodes") = Me.cboCostCode
.Fields("hrsspent") = txt.Value
.Fields("dt") = dtDay
.Update
End If
Next iDay
End With
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

A2K
I have an unbound form that has 9 unbound textboxes. In the first one
a person enters a week ending date, the second a person picks from a
list of options and 3-9 the person enters a number. 3-9 represent days
of the week and not every day will have a number recorded. This is the
code that I am using (it works) and my question is, is this the most
efficient way to do this task. I take each day of the week and
determine if it has any data and if it does, then add that information
to the recordset, otherwise I do nothing. So I have 7 If-Then
statements. Everything works fine but I wanted to ask the newsgroup if
I should be doing this task differently.

'Add new records
With rst
If Me.txtSun <> "" Then 'only do if it has data
.AddNew
.Fields("id_CostCodes") = Me.cboCostCode
.Fields("hrsspent") = Me.txtSun
.Fields("dt") = Me.txtSun_6
.Update
End If
If Me.txtMon <> "" Then 'only do if it has data
.AddNew
.Fields("id_CostCodes") = Me.cboCostCode
.Fields("hrsspent") = Me.txtMon
.Fields("dt") = Me.txtMon_5
.Update
End If

Thanks,

Charles D Clayton Jr