maintenance scheduler

[lemon_balm]

Morning guys and gals

I am trying to create a sceduling calendar for my department which displays given events day by day.
I have created this using subforms for each day and a combo box to select the month.
For display purposes it works fine but I am having a little difficulty with the functionality.
I want to be able to click into any of the subforms, and depending on whether there is data within, open a specific document.
1)If there is an item in the schedule the I want to open a report filtered for that date.
2)If the day is blank then I want to open a form to input an item. (This works but I cannot get the date to pre-fill)
I have created a seperate piece of code to do this but it doesn't want to work. It may be because my knowledge of VB isn't what it was or it may be that I am completely barking up the wrong tree.
Hopefully someone can have a look and let me know where I am going wrong.

Code

Private Sub OpenCalRep(date1 As Date)
If date1 = Null Then
DoCmd.OpenForm "frmmaintenance", , , , acFormAdd
Forms!frmmaintenance.txtDate = Forms!frmcalsite.txtDate
Else
DoCmd.OpenReport "rptmaintenance", acViewPreview, , "Tables!tblmaintenance.txtdate" = "forms!frmcalmain.sf1.form!txtdate"
End If

End Sub

I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.

This code is the prefilled from the subform by using:

Private Sub SF1_Enter()
OpenCalRep (Forms!frmcalmain.SF1.Form!txtDate)
End Sub

I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.

Regards

Jason

 

[Oldsoftboss]

Try
If IsNull(date1) Then

 

[lemon_balm]

I think there is a problem with the referencing of the date field in my subform. (SF1) The form itself is named "frmcalsite" but is referenced as SF1 as it is being used as a subform. At present SF1 is blank so it should open my maintenance input form (frmmaintenance) with the date box filled in (txtdate) Instead the error "You entered an expression that has no value" appears. (runtime error 2427).
If I run this for a choice which has input data then I get the report up but it is blank.

 

[lemon_balm]

Issue still the same

I have tried the IsNull(date1) piece of code but I seem to recieve the same result.
Any more ideas?
This has really got me stumped.
Between this and trying to get my listboxes running properly I am running out of ideas

Regards

Jason

 

[lemon_balm]

Ok I've amended the code. I just can't figure out why I am getting a boolean result returning.

Here it is:

Private Sub OpenCalRep(date1 As Date, subformname As String)

If IsNull(date1) Then
DoCmd.OpenForm "maintenance1", , , , acFormAdd
Else
DoCmd.OpenReport "calendarreport", acViewPreview, , ("maintenance!datedue = forms!frmcalmain." & subformname & ".form!date")
End If

End Sub

The function is called from:

Private Sub SF4_Enter()
Dim SFdate As Date
Dim SFname As String
OpenCalRep "SFdate" = Forms!frmcalmain.SF4.Form!Date, "SFname" = Forms!frmcalmain.SF4.Tag
End Sub

Instead of subformname returning SF4 to make the SQL Where condition "maintenance!datedue = forms!frmcalmain.SF4.form!date" I am getting it return "False" which makes the condition "Forms!frmcalmain.False.form!date"
I am baffled as to why this is happening as I have not set any boolean commands at any point.
Any ideas?

 

[Oldsoftboss]

OpenCalRep Forms!frmcalmain.Form.SF4.Form!Date, Forms!frmcalmain.Form.SF4.Tag

I dont thing you need to reference the date and string names to reference them.

Try
Msgbox Forms!frmcalmain.Form.SF4.Form!Date
and see what you get. Once you get the corret thing in the Msgbox, all shouls work.

Dave

 

[lemon_balm]

Spot On

You were right OldSoftBoss. I didn't need to reference the variables which was why a boolean result was returned.
I also used the txtbox name as a choice instead of the tag line. This is because I am still having some issues as you will see below.

The report link now works fantastically well but I cannot get the Null reference to open my other form.

I think I need to generate some data on the tag line on the fly so that a null reference can be created. At present, if I select a subform which is blank then I get an error. This is because the data to fill that subform doesn't actually exist and hence there is nothing for the If statement to pick up on. If I could find a way of making all blank fields convert to a tag line of FALSE on opening then I could use ValueIsNull in my If statement to open the form.

What do you think and any ideas on where I should put the code?

Jason

 

[lemon_balm]

scheduling issues

Ok, I've had a play with the coding and it now kindo of works. Attatched is a screen shot of the scheduler itself so that you guys can further understand what I am trying to do.

I want to open an input form if a blank subform is entered.
To do this I have added an extra if statement at the On enter stage and a boolean variable to the OpenCalRep function. The code now looks like this:

Private Sub SF5_Enter()
Dim SFdate As Boolean
If IsNull(Forms!frmcalmain.SF5.Form!Date) Then
SFdate = True
OpenCalRep SFdate, Forms!frmcalmain.SF5.Form!Date, Forms!frmcalmain.SF5.name
Else
SFdate = False
OpenCalRep SFdate, Forms!frmcalmain.SF5.Form!Date, Forms!frmcalmain.SF5.name
End If
End Sub

Private Sub OpenCalRep(SFNull As Boolean, date1 As Date, subformname As String)
If SFNull = True Then
DoCmd.OpenForm "maintenance1", , , , acFormAdd
Else
DoCmd.OpenReport "calendarreport", acViewPreview, , ("forms!frmcalmain." & subformname & ".form!date = maintenance.datedue")
End If

End Sub

Unfortunately this still returns an error of "You entered an expression that has no value"

Can anyone help?

Jason

 

[KenHigg]

Sorry to butt-in, where the heck did a user name like lemon_balm come from? (If the answer is x-rated then feel free to pass on the answer )

 

[lemon_balm]

Just an old handle. Non offensive yet different enough to make people like yourself ask questions.

 

[lemon_balm]

Has anyone got any ideas on how to assign a value (even a null value) to a subform when there is no data to prefill it. i.e. if subform is blank then...
I need to do this so that I can open an input form if the blank subform is entered.

Regards

Jason

 

[Oldsoftboss]

Try:
If IsNull(Forms!frmcalmain.SF5.Form!Date) or Forms!frmcalmain.SF5.Form!Date = "" Then

If the date field has had some information in it, it is no longer Null, just empty.

Someone explained it this way once:

You have a sealed bucket with a perfect vaccum - Null
You break the seal - looks the same inside, but is empty, not null
Fill it with water - now contains data.
Empty it out, empty, not Null

Dave

 

[lemon_balm]

requerying

Very eloquently put I must say.

However, due to the fact that each month is selected from a combo box at the top of the screen (cbo_month) the whole form is requeried on selection. This means that only those subforms that have data to be imported are tampered with in any way leaving the rest of the subforms completely sealed. The code I am using for the calendar is:

Private Sub cbo_month_change()
'Month has changed so we have to recalculate the form
Dim cnt As Long
'First step is to find out what the month is composed of

Dim DaysInMonth As Long
DaysInMonth = DateDiff("d", cbo_month, DateAdd("m", 1, cbo_month))
Dim FirstDay As Long
FirstDay = WeekDay(cbo_month, vbMonday)

'Hide the first few forms that dont count
For cnt = 1 To 6
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay > 1 Then
For cnt = 1 To FirstDay - 1
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If

'Hide the last few forms that dont count
For cnt = 28 To 37
Me.Controls("SF" & CStr(cnt)).Visible = True
Next
If FirstDay < 7 Or DaysInMonth < 31 Then
For cnt = FirstDay + DaysInMonth To 37
Me.Controls("SF" & CStr(cnt)).Visible = False
Next
End If
Dim frm As Form
Dim DayOfMonth As Long
'Now we need to set the record source for each day.....
For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
DayOfMonth = (cnt - FirstDay) + 1
Set frm = Forms!frmcalmain.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT Datedue,Site,scheduledmain FROM qrycalendarinfo WHERE Datedue = #" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_month, "mmm/yyyy") & "#"
frm.lbl_date.Caption = CStr(DayOfMonth)
frm.Controls("Date").DefaultValue = "#" & CStr((DayOfMonth)) & "/" & Format(Me.cbo_month, "mmm/yyyy") & "#"
Me.Controls("SF" & CStr(cnt)).Requery
Next

End Sub

This piece of code has to be credited originally to John Hawkins.

As you can see, a subform is only acessed if there is some data to place in it and then everything is requeried.

Any more ideas because I am completely baffled.

Jason

 

[lemon_balm]

Right,

I have completely redone the code.

The On_enter event now solely passes the subform name to the function:

OpenCalRep(SF1)

The Function now reads:

Private Sub OpenCalRep(subformname As String)

If IsNull("Forms!frmcalmain." & subformname & ".Form!ScheduledMain") Then
DoCmd.OpenReport "calendarreport", acViewPreview, , ("forms!frmcalmain." & subformname & ".form!date = maintenance.datedue")
Else
DoCmd.OpenForm "maintenance1", , , , acFormAdd

End If
End Sub

The theory is that ScheduledMain is the field that is filled in when creating a schedule item so if it is blank then there can't be a field there.
Depending on which way round I have the 2 terms of the If statement (Openreport and openform) now gives me one or the other.
If I select a blank subform with the code as it is above, the input form opens FANTASTIC., but if I select a subform with data in then...the input form opens DAMN AND BLAST.

With the code reversed I get the Report in both instances.

Can anyone tell me what I am doing wrong?
It is as if the code cannot read the "ScheduledMain" data

Regards

Jason

 

[lemon_balm]

Aaaaaaaaarrrrrrrrrrgggggggggggghhhhhhhhh!!!!

Ok

I've reset my If statement to produce a listbox instead of go to either the report or form for testing purposes

Here is the code

If IsNull("forms!frmcalmain." & subformname & ".form!scheduledmain") Then
SFNull = True
Else
SFNull = False
If SFNull = False Then
MsgBox ("false")
Else
MsgBox ("true")
End If
End If
End Sub

This is called from each of the subforms where their name is placed in the variable "subformname".

No matter whci subform I select, whether it be populated or not, I get the False message box returning.

Does anyone have any ideas?

I just cannot figure this piece of code out

Jason

 

[Oldsoftboss]

I think you need to count the records in the subform. If the record count is 0, then there are no records, so open one form, otherwise open the second form.