Fill a Calendar Grid 1

[terre]

I have a calendar grid for a school term which has 11 Rows of 5 Textboxes.
11 weeks M,T,W,Th,F
I have named these textboxes 1,2,3,4,........55 (For originality!!)

I then have a query AbsQuilt which returns DateAbsent, and Reason for a student.

DateAbsent Reason
1/2/2011 F
3/3/2011 U
.
.
.
4/4/2011 F


Any suggestions as to how I can the Reason in the correct Textbox, and fill the grid with reasons absent?

Terre

 

[MajP]

dim txtBoxName as string
dim ctrl as access.textbox
dim startDate as date
dim recordDate as date
dim rs as dao.recordset

'Somewhere you have to pull the start of the semester
'I assume that is stored somewhere

startDate = ....

set rs = currentdb.openrecordset("absQuilt")

'You could set the default value of the textbox to "Present"
'Then only overwrite those with absences

do while not rs.eof
recordDate = rs!dateAbsent
txtBoxName = cstr(int(recordDate) - int(starDate) + 1)
ctrl = forms("yourFrmName").controls(txtBoxName)
ctrl = rs!absenceReason
loop

 

[dhookom]

You don't state anything about a student field or if there is a record for each date.

I would consider a continuous form based on the crosstab query:

TRANSFORM First(AbsQuilt.Reason) AS FirstOfReason
SELECT DateAdd("d",-Weekday([DateAbsent])+1,[DateAbsent]) AS WeekOf
FROM AbsQuilt
GROUP BY DateAdd("d",-Weekday([DateAbsent])+1,[DateAbsent])
PIVOT Choose(Weekday([DateAbsent]),"Su","Mo","Tu","We","Th","Fr","Sa") In ("Mo","Tu","We","Th","Fr");

results:
[tt]
WeekOf Mo Tu We Th Fr
1/2/2011 U F
1/9/2011 F U F F
1/16/2011 U F
1/23/2011 F U
1/30/2011 U U
2/6/2011 U F F
2/13/2011 U
2/20/2011 F U
2/27/2011 U U U
3/6/2011 U
3/13/2011 F U U F
[/tt]
If you don't have at least one record per week, you may need to create a table of dates.

Duane
Hook'D on Access
MS Access MVP

 

[terre]

Thanks Aceman, and Duane
Aceman.....something not quite right......ctrl always appears to be Nothing
ctrl = Nothing even though all the others seem right

Any suggestions?

Terre

dhookum
I will need to ponder the setting up of a table of dates, as I wont always have an entry every week.

Thanks

Terre

 

[dhookom]

If you have holes in your dates, create a WeekOf table with values like in the query. Then use a LEFT or RIGHT JOIN to include all of the records from the WeekOf table.

I typically prefer a non-code method rather than maintaining code.

Duane
Hook'D on Access
MS Access MVP

 

[terre]

MajP....where did that Aceman come from?
Actually its the forms("yourFrmName").controls(txtBoxName)
that is Null, therefore (I think) ctrl is Nothing

Terre

 

[MajP]

Can you post your actual code with your real form, control, and field names? You can add some debug statements.



do while not rs.eof
recordDate = rs!dateAbsent
debug.print "Date is " & recorddate
txtBoxName = cstr(int(recordDate) - int(starDate) + 1)
debug.print "shoud return a number " & textboxName
ctrl = forms("yourFrmName").controls(txtBoxName)
debug.print "ctrl name " & ctrl.name
ctrl = rs!absenceReason
debug.print "reason " & rs!absenceReason
loop

 

[terre]

The Query AbsenceQuilt has the fields [Date Absent], PartofDayAbsent, LengthAbs, absenceReason


My code is

Dim rs As DAO.Recordset
Dim ctl As Access.TextBox
Dim startDate As Date
Dim recordDate As Date
Dim txtBoxName As String

startDate = T1
'StartDate stored in Forms!Term1Abs!T1

Set rs = CurrentDb.OpenRecordset("absenceQuilt")

Do While Not rs.EOF
recordDate = rs![Date Absent]
txtBoxName = CStr(Int(recordDate) - Int(startDate) + 1)
ctl = Forms("Term1Abs").Controls(txtBoxName)
ctl = rs!absenceReason
Loop


Then when it produces error

startDate = 31/01/2011
recordDate = 7/02/2011
txtBoxName = "8"
Forms("Term1Abs").Controls(txtBoxName) = Null
ctl = Nothing
absenceReason = "L"

Thanks for looking at this!

Terre

 

[MajP]

I see one error I made. I forgot a set. Should read

set ctl = Forms("Term1Abs").Controls(txtBoxName)

I put the ctl local variable just to make it more readable, but you could simplify to
...
txtBoxName = CStr(Int(recordDate) - Int(startDate) + 1)
Forms("Term1Abs").Controls(txtBoxName) = rs!absenceReason
...

if you are real daring with your debug skills you could do it in a single line.

Do While Not rs.EOF
   Forms("Term1Abs").Controls(CStr(Int(rs![Date Absent]) - Int(startDate) + 1)) = rs!absenceReason
Loop

 

[terre]

MajP
Had tried to add the Set

Still a problem

What seems to happen is...well nothing actually
The database seems to freeze and then I get a message saying its not responding.
Always happens

I might just try waiting it out while I have some lunch.

Terre

 

[MajP]

If the code does not break then it sounds like a different issue. However, this is how I would debug. I can then read the output and determine if there is a logic error or a runtime error. So run it an post the relevant immediate window results

Dim rs As DAO.Recordset
Dim ctl As Access.TextBox
Dim startDate As Date
Dim recordDate As Date
Dim txtBoxName As String

startDate = T1
'StartDate stored in Forms!Term1Abs!T1
debug.print "start date = " & startDate
Set rs = CurrentDb.OpenRecordset("absenceQuilt")
debug.print "rs count: " & rs.count
Do While Not rs.EOF
  debug.print "looping recordset "
  recordDate = rs![Date Absent]
  debug.print "record date " & recorddate
  txtBoxName = CStr(Int(recordDate) - Int(startDate) + 1)
  debug.print "text box name : " & txtBoxName
  set ctl = Forms("Term1Abs").Controls(txtBoxName)
  debug.print "control is set: " & ctl.name
  debug.print "reason " & rs!absenceReason
  ctl = rs!absenceReason
Loop

You also may want to look at the other recommended solution of using a crosstab query.

 

[terre]

Thanks MajP

I think there is something else going on here
Your code looks and feels right.....but the dabtabase just "jams up"

Thanks for trying though, and if you think of something else sometime, let me know

Terre

PS the CrossTab method works well