Stumped for now - could it be Access 2007? References?

[Fred Boer]

Hello:

I have a small program that I use to track student attendance and lessons
for my classes. I have a problem with the program that I haven't been able
to debug.

My personal workstation computer has Access 2003 and 2007. Other
workstations on the network have only Access 2003. All workstations use
Windows XP. If I use the program on my own computer it works fine. If I try
to use it on other network workstations, it fails. I'm continuing to try to
debug this, within the limits of my debugging skills <g>, but for now I am
stumped. I am wondering: could there be something about a combination of
A2003/A2007 on one machine that might cause an A2003 application to fail on
a machine with only A2003? What if I had opened the application with A2007?

I have double-checked that I have full privileges and that the LBD file can
be created and deleted. I have tried this both split BE/FE and as a single
file. The checked references are : Visual Basic for Applications; Microsoft
Access 11.0 Object Library; Microsoft DAO 3.6 Object Library.

As to exactly how it fails, (if you are interested):

The user selects a class name from a combobox and uses a calendar to pick a
date. With these selections completed, a form opens filtered by the
class/date criteria. It displays lesson notes and, in a subform, attendance
information. When the application fails, the form, although given correct
criteria, opens blank failing to display any data. Attempting any other
function generates an error message about inadmissable duplicate entries,
although I don't believe any attempt to add or edit records occurs. The
subform, at this point, of course, may not be correctly linked to the main
form which is missing the linking data. I've checked that the criteria would
return data.

Thanks!

 

[SteveM]

Are you using any ActiveX controls?

Steve

 

[Fred Boer]

I'm using Stephan Lebans MonthCalendar control, which, I believe is not an
ActiveX control..

Thanks!

 

[SteveM]

How are you opening the form? What code?

I asume you have tried it in break mode since you stated that the form is
getting the correct criteria. Can you open the form directly from the
database window on the other workstations?

Steve

 

[Fred Boer]

Hi! Thanks for trying to help...

Code from frmSelection:

Private Sub cmdOK_Click()
On Error GoTo ErrorPoint

Dim strLinkCriteria As String

If Len(Nz(Me!cboClassNames, "")) = 0 Then
' Class Combo is empty
MsgBox "Please select a Class from the list before continuing.", _
vbInformation, "Enter Class"
Me.cboClassNames.SetFocus
Else
If Len(Nz(Me!txtDate, "")) = 0 Then
' Date is empty
MsgBox "Please enter a date before continuing.", _
vbInformation, "Enter Date"
Me.txtDate.SetFocus
Else
' All clear
' Build a string criteria to open the form to a specific lesson
date and class
strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" & "
And " & "[ClassID] =" & Me.cboClassNames
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate
' Hide this form now
Me.Form.Visible = False
Forms!frmNewDayBook.SetFocus
End If
End If

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Hm... opening form directly. I *think* I tried that with the same results,
but I will have to confirm that. If I can reproduce the issue at home, I
will, otherwise I'll have to wait until Monday at school.

Thanks!

 

[Fred Boer]

....and just to add some detail:

I set a break point and stepped through the code with F8. No errors as I
stepped through the code and opened the form. I checked the criteria in the
Immediate window, and it was correct and should have provided data for the
form to display. The form opened without throwing an error, but without data
as well - which was.. well, wrong!

The error messages popped up whenever I did anything that caused code to
run - click a button to print a form, enter the subform, etc.

Cheers!

 

[SteveM]

Nothing in your code jumps out at me.
I would do sometthing slightly different:
If Nz(Me!Combo,"") = "" Then 'will achieve the same

Small difference but less code to write...

I would be curious to know if this form opens directly from the database
window on the other machines.

What controls do you have on that form?
Is there anything special happening in an event on that form?
Try commenting out the OnCurrent event and see what happens...

Steve

Hi! Thanks for trying to help...

Code from frmSelection:

Private Sub cmdOK_Click()
On Error GoTo ErrorPoint

Dim strLinkCriteria As String

If Len(Nz(Me!cboClassNames, "")) = 0 Then
' Class Combo is empty
MsgBox "Please select a Class from the list before continuing.", _
vbInformation, "Enter Class"
Me.cboClassNames.SetFocus
Else
If Len(Nz(Me!txtDate, "")) = 0 Then
' Date is empty
MsgBox "Please enter a date before continuing.", _
vbInformation, "Enter Date"
Me.txtDate.SetFocus
Else
' All clear
' Build a string criteria to open the form to a specific lesson
date and class
strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" & "
And " & "[ClassID] =" & Me.cboClassNames
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate
' Hide this form now
Me.Form.Visible = False
Forms!frmNewDayBook.SetFocus
End If
End If

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Hm... opening form directly. I *think* I tried that with the same results,
but I will have to confirm that. If I can reproduce the issue at home, I
will, otherwise I'll have to wait until Monday at school.

Thanks!
--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

How are you opening the form? What code?

I asume you have tried it in break mode since you stated that the form is
getting the correct criteria. Can you open the form directly from the
database window on the other workstations?

Steve

 

[Fred Boer]

Hi Steve:

The form has a tab control with 2 pages:

Page 1 has 5 or 6 textboxes and some command buttons to print reports/close
the form/quit the app/reset the criteria. Not too much code so here it all
is:

Option Compare Database
Option Explicit

Private Sub cmdChange_Click()
On Error GoTo ErrorPoint

' If Form is dirty, save the record
If Me.Dirty = False Then
' Form is dirty set focus to a control on main form
Me.txtLessonTopic.SetFocus
' Save the record
Me.Dirty = True
End If
' Hide this form now
Me.Form.Visible = False
' Make the selection screen visible again
Forms!frmSelection.Visible = True

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub cmdClose_Click()
On Error GoTo ErrorPoint

' If the Selection form is still loaded then close it
If CurrentProject.AllForms("frmSelection").IsLoaded Then
DoCmd.Close acForm, "frmSelection"
End If

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorPoint

' Save the record first
DoCmd.RunCommand acCmdSaveRecord
' Open the report
DoCmd.OpenReport "rptLessonPlan", acPreview
'DoCmd.OpenReport "rptLessonPlan"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub cmdPrintAll_Click()
On Error GoTo ErrorPoint

' Save the record first
DoCmd.RunCommand acCmdSaveRecord
' Open the report
DoCmd.OpenReport "rptAllLessonPlans", acPreview
'DoCmd.OpenReport "rptLessonPlan"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub cmdPrintToday_Click()
On Error GoTo ErrorPoint

' Save the record first
DoCmd.RunCommand acCmdSaveRecord
' Open the report
DoCmd.OpenReport "rptTodaysLessonPlans", acPreview
'DoCmd.OpenReport "rptLessonPlan"

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub

Private Sub cmdQuit_Click()
On Error GoTo ErrorPoint

DoCmd.Quit

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint


End Sub



Private Sub Form_Activate()
DoCmd.Maximize

End Sub

Page 2 is a subform which is a continuous form displaying students and
attendance codes. Not too much code either...

Private Sub cmdBuildAttendanceForm_Click()
On Error GoTo ErrorPoint

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim lngRecordsAffected As Long
Dim fInTransaction As Boolean

Set ws = Workspaces(0)
Set db = CurrentDb
Set qdf = db.QueryDefs("qryBuildAttendanceSheet")

For Each prm In qdf.Parameters
With prm
.Value = Eval(.name)
End With
Next prm

ws.BeginTrans
fInTransaction = True

qdf.Execute dbFailOnError

lngRecordsAffected = qdf.RecordsAffected

ws.CommitTrans
fInTransaction = False
Me.Form.Requery
Me.optAttendance.SetFocus

ExitPoint:
On Error Resume Next
If fInTransaction Then
ws.Rollback
End If
Set qdf = Nothing
Set db = Nothing
Set ws = Nothing
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint

If Me.RecordsetClone.RecordCount = 0 Then
' No records so enable the command button
Me.cmdBuildAttendanceForm.Enabled = True
Else
Me.cmdBuildAttendanceForm.Enabled = False
End If

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

Thanks!

 

[SteveM]

Should the criteria supplied have opened the form with records?

You should test that the criteria produces records and if not, either open
the form in Add mode or terminate the process. You could test the criteria in
a DCount() for example...you will certainly get problems if you try to open a
form with criteria that doesn't produce records.

Is there some event that runs when the form opens?
Do you hae code in the OnCurrent event?
It would be good to see the code there too.

Steve

 

[Fred Boer]

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the correct
results.

But this is the crux of my debugging issues: I can show that the code runs
without error when it opens the form by stepping through the code. I can
show that the criteria is correct and should fill the form with data. But
the darn thing opens blank - and I don't know why. After it opens, any
attempt to run code throws an error.

My debugging skills fail me at the point at which I want to determine "why
the form is opening blank"...

BTW, the only event that runs when the form opens (actually when it
activates) is Docmd.Maximize...

I appreciate your efforts!

 

[Ken Snell \(MVP\)]

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the correct
results.

But this is the crux of my debugging issues: I can show that the code runs
without error when it opens the form by stepping through the code. I can
show that the criteria is correct and should fill the form with data. But
the darn thing opens blank - and I don't know why. After it opens, any
attempt to run code throws an error.

This result suggests a timing issue, Fred. Stepping through the code takes
longer and probably is allowing some process to "complete" before the next
step is executed.

I have looked through the code you posted a few levels ago, but I don't see
anything specific in those code that suggest where timing might be an issue
(but I profess that I'm not fully understanding which code goes with which
form, so I probably am looking at the wrong things) -- other than you appear
to be using the Form_Open event to disable/enable a command button, and you
would be better off using the Form_Load event to do that -- the recordset's
records will be loaded into the form by the Load event, but not necessarily
in the Open event.

I do note this code step that you posted:

strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


It's always a good idea to formally format a date value into the mm/dd/yyyy
syntax when putting it into a WHERE clause to be sure that you get the US
date format for the query:

strLinkCriteria = "[LessonDate]=" & "#" & Format(Me![txtDate],
"mm\/dd\/yyyy") & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


Another code structure that I use is to pass values in the OpenArgs argument
that I want to write into a form's controls when the form loads (do this by
making concatenating the values into a delimited string), and then use that
form's Load event to parse the OpenArgs string back into an array and then
assign those values to the appropriate controls -- it avoids the possibility
of your code trying to write values to a form that is still opening. This is
the code excerpt to which I am referring:

' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate

I would do this:
Dim strOpenArgs As String
strOpenArgs = Me.cboClassNames.Column(0) & "|" & _
Me.cboClassNames.Column(1) & "|" & Me.txtDate
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria, ,
strOpenArgs
' Pass in the user's selections onto the form

(in the just-opened form, this is the code for its Load event:
Private Sub Form_Load()
Dim varOpenArgs As Variant
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me.txtClassID = varOpenArgs(0)
Me.txtClassName = varOpenArgs(1)
Me.txtLessonDate = varOpenArgs(2)
End If
End Sub
)

 

[Pieter Wijnen]

FYI
Military date format (yyyy-mm-dd) also works & is universal

Pieter

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the correct
results.

But this is the crux of my debugging issues: I can show that the code
runs without error when it opens the form by stepping through the code. I
can show that the criteria is correct and should fill the form with data.
But the darn thing opens blank - and I don't know why. After it opens,
any attempt to run code throws an error.

This result suggests a timing issue, Fred. Stepping through the code takes
longer and probably is allowing some process to "complete" before the next
step is executed.

I have looked through the code you posted a few levels ago, but I don't
see anything specific in those code that suggest where timing might be an
issue (but I profess that I'm not fully understanding which code goes with
which form, so I probably am looking at the wrong things) -- other than
you appear to be using the Form_Open event to disable/enable a command
button, and you would be better off using the Form_Load event to do
that -- the recordset's records will be loaded into the form by the Load
event, but not necessarily in the Open event.

I do note this code step that you posted:

strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" &
"
And " & "[ClassID] =" & Me.cboClassNames


It's always a good idea to formally format a date value into the
mm/dd/yyyy syntax when putting it into a WHERE clause to be sure that you
get the US date format for the query:

strLinkCriteria = "[LessonDate]=" & "#" & Format(Me![txtDate],
"mm\/dd\/yyyy") & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


Another code structure that I use is to pass values in the OpenArgs
argument that I want to write into a form's controls when the form loads
(do this by making concatenating the values into a delimited string), and
then use that form's Load event to parse the OpenArgs string back into an
array and then assign those values to the appropriate controls -- it
avoids the possibility of your code trying to write values to a form that
is still opening. This is the code excerpt to which I am referring:

' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate

I would do this:
Dim strOpenArgs As String
strOpenArgs = Me.cboClassNames.Column(0) & "|" & _
Me.cboClassNames.Column(1) & "|" & Me.txtDate
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria, ,
strOpenArgs
' Pass in the user's selections onto the form

(in the just-opened form, this is the code for its Load event:
Private Sub Form_Load()
Dim varOpenArgs As Variant
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me.txtClassID = varOpenArgs(0)
Me.txtClassName = varOpenArgs(1)
Me.txtLessonDate = varOpenArgs(2)
End If
End Sub
)

 

[Fred Boer]

Hi Ken! Thanks for jumping in!

Hmm. A timing issue? Ok, I will try your suggestions on Monday. I'm not sure
why a timing issue would come into it, since I have exactly the same
computer as a network machine, and both machines would access the back end
over the same network, but I'll definitely try everything you've suggested.
BTW, I did check that maybe the regional settings were messing with the date
criteria, but I'll also do the date "thing"!

Many thanks!

--
Fred Boer - Amateur Access Enthusiast
Interests: Library software / Z39.50 / Web Services
Freeware Small Library Application available here:
http://www3.sympatico.ca/lornarourke/

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the correct
results.

But this is the crux of my debugging issues: I can show that the code
runs without error when it opens the form by stepping through the code. I
can show that the criteria is correct and should fill the form with data.
But the darn thing opens blank - and I don't know why. After it opens,
any attempt to run code throws an error.

This result suggests a timing issue, Fred. Stepping through the code takes
longer and probably is allowing some process to "complete" before the next
step is executed.

I have looked through the code you posted a few levels ago, but I don't
see anything specific in those code that suggest where timing might be an
issue (but I profess that I'm not fully understanding which code goes with
which form, so I probably am looking at the wrong things) -- other than
you appear to be using the Form_Open event to disable/enable a command
button, and you would be better off using the Form_Load event to do
that -- the recordset's records will be loaded into the form by the Load
event, but not necessarily in the Open event.

I do note this code step that you posted:

strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" &
"
And " & "[ClassID] =" & Me.cboClassNames


It's always a good idea to formally format a date value into the
mm/dd/yyyy syntax when putting it into a WHERE clause to be sure that you
get the US date format for the query:

strLinkCriteria = "[LessonDate]=" & "#" & Format(Me![txtDate],
"mm\/dd\/yyyy") & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


Another code structure that I use is to pass values in the OpenArgs
argument that I want to write into a form's controls when the form loads
(do this by making concatenating the values into a delimited string), and
then use that form's Load event to parse the OpenArgs string back into an
array and then assign those values to the appropriate controls -- it
avoids the possibility of your code trying to write values to a form that
is still opening. This is the code excerpt to which I am referring:

' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate

I would do this:
Dim strOpenArgs As String
strOpenArgs = Me.cboClassNames.Column(0) & "|" & _
Me.cboClassNames.Column(1) & "|" & Me.txtDate
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria, ,
strOpenArgs
' Pass in the user's selections onto the form

(in the just-opened form, this is the code for its Load event:
Private Sub Form_Load()
Dim varOpenArgs As Variant
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me.txtClassID = varOpenArgs(0)
Me.txtClassName = varOpenArgs(1)
Me.txtLessonDate = varOpenArgs(2)
End If
End Sub
)

 

[Fred Boer]

Thanks!

Fred

"Pieter Wijnen"

FYI
Military date format (yyyy-mm-dd) also works & is universal

Pieter

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the
correct results.

But this is the crux of my debugging issues: I can show that the code
runs without error when it opens the form by stepping through the code.
I can show that the criteria is correct and should fill the form with
data. But the darn thing opens blank - and I don't know why. After it
opens, any attempt to run code throws an error.

This result suggests a timing issue, Fred. Stepping through the code
takes longer and probably is allowing some process to "complete" before
the next step is executed.

I have looked through the code you posted a few levels ago, but I don't
see anything specific in those code that suggest where timing might be an
issue (but I profess that I'm not fully understanding which code goes
with which form, so I probably am looking at the wrong things) -- other
than you appear to be using the Form_Open event to disable/enable a
command button, and you would be better off using the Form_Load event to
do that -- the recordset's records will be loaded into the form by the
Load event, but not necessarily in the Open event.

I do note this code step that you posted:

strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" &
"
And " & "[ClassID] =" & Me.cboClassNames


It's always a good idea to formally format a date value into the
mm/dd/yyyy syntax when putting it into a WHERE clause to be sure that you
get the US date format for the query:

strLinkCriteria = "[LessonDate]=" & "#" & Format(Me![txtDate],
"mm\/dd\/yyyy") & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


Another code structure that I use is to pass values in the OpenArgs
argument that I want to write into a form's controls when the form loads
(do this by making concatenating the values into a delimited string), and
then use that form's Load event to parse the OpenArgs string back into an
array and then assign those values to the appropriate controls -- it
avoids the possibility of your code trying to write values to a form that
is still opening. This is the code excerpt to which I am referring:

' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate

I would do this:
Dim strOpenArgs As String
strOpenArgs = Me.cboClassNames.Column(0) & "|" & _
Me.cboClassNames.Column(1) & "|" & Me.txtDate
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria, ,
strOpenArgs
' Pass in the user's selections onto the form

(in the just-opened form, this is the code for its Load event:
Private Sub Form_Load()
Dim varOpenArgs As Variant
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me.txtClassID = varOpenArgs(0)
Me.txtClassName = varOpenArgs(1)
Me.txtLessonDate = varOpenArgs(2)
End If
End Sub
)

 

[Fred Boer]

Ken, I've been thinking, (although I am not going to think anymore after
this post <g> - at least not tonight). If we assume the the "error" is that
the form is opening without data - then timing isn't an issue, since it will
open blank whether I step through the code or not. Does that make sense?
Error messages do occur if I try to do anything once the form is open, but
it opens without error messages (though the blankness is itself an "error").

I'm still going to follow your suggestions, as I mentioned! But for now, I'm
off to bed!

Cheers!
Fred



Actually, Ken, I'm not sure about this. If we take the fact that the form
opens without data as the "error", this happens whether or not I step
through the code.

Hi Steve:

Yes, the criteria should have provided data.I tested the criteria by
creating a query with the identical criteria, and it produced the correct
results.

But this is the crux of my debugging issues: I can show that the code
runs without error when it opens the form by stepping through the code. I
can show that the criteria is correct and should fill the form with data.
But the darn thing opens blank - and I don't know why. After it opens,
any attempt to run code throws an error.

This result suggests a timing issue, Fred. Stepping through the code takes
longer and probably is allowing some process to "complete" before the next
step is executed.

I have looked through the code you posted a few levels ago, but I don't
see anything specific in those code that suggest where timing might be an
issue (but I profess that I'm not fully understanding which code goes with
which form, so I probably am looking at the wrong things) -- other than
you appear to be using the Form_Open event to disable/enable a command
button, and you would be better off using the Form_Load event to do
that -- the recordset's records will be loaded into the form by the Load
event, but not necessarily in the Open event.

I do note this code step that you posted:

strLinkCriteria = "[LessonDate]=" & "#" & Me![txtDate] & "#" &
"
And " & "[ClassID] =" & Me.cboClassNames


It's always a good idea to formally format a date value into the
mm/dd/yyyy syntax when putting it into a WHERE clause to be sure that you
get the US date format for the query:

strLinkCriteria = "[LessonDate]=" & "#" & Format(Me![txtDate],
"mm\/dd\/yyyy") & "#" & "
And " & "[ClassID] =" & Me.cboClassNames


Another code structure that I use is to pass values in the OpenArgs
argument that I want to write into a form's controls when the form loads
(do this by making concatenating the values into a delimited string), and
then use that form's Load event to parse the OpenArgs string back into an
array and then assign those values to the appropriate controls -- it
avoids the possibility of your code trying to write values to a form that
is still opening. This is the code excerpt to which I am referring:

' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria
' Pass in the user's selections onto the form
Forms!frmNewDayBook!txtClassID = Me.cboClassNames.Column(0)
Forms!frmNewDayBook!txtClassName = Me.cboClassNames.Column(1)
Forms!frmNewDayBook!txtLessonDate = Me.txtDate

I would do this:
Dim strOpenArgs As String
strOpenArgs = Me.cboClassNames.Column(0) & "|" & _
Me.cboClassNames.Column(1) & "|" & Me.txtDate
' Open the form now
DoCmd.OpenForm "frmNewDaybook", , , strLinkCriteria, ,
strOpenArgs
' Pass in the user's selections onto the form

(in the just-opened form, this is the code for its Load event:
Private Sub Form_Load()
Dim varOpenArgs As Variant
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs, "|")
Me.txtClassID = varOpenArgs(0)
Me.txtClassName = varOpenArgs(1)
Me.txtLessonDate = varOpenArgs(2)
End If
End Sub
)

 

[Ken Snell \(MVP\)]

Ken, I've been thinking, (although I am not going to think anymore after
this post <g> - at least not tonight). If we assume the the "error" is
that the form is opening without data - then timing isn't an issue, since
it will open blank whether I step through the code or not. Does that make
sense? Error messages do occur if I try to do anything once the form is
open, but it opens without error messages (though the blankness is itself
an "error").

I misunderstood your info then -- I thought I'd read that the problem didn't
occur when you stepped through the code, but did occur when your code ran on
its own.

If the form is opening without records, then likely the query is not reading
the parameter or WHERE filter correctly. Can you post the SQL statement of
the form's Recordsource query?

 

[Sascha Trowitzsch]

Hi Fred,

Ken, I've been thinking, (although I am not going to think anymore after this
post <g> - at least not tonight). If we assume the the "error" is that the
form is opening without data - then timing isn't an issue, since it will open
blank whether I step through the code or not. Does that make sense? Error
messages do occur if I try to do anything once the form is open, but it opens
without error messages (though the blankness is itself an "error").

I'd agree with the assumption you made in your first posting.
I don't think this to be a VBA or SQL problem. It really may be that
reprocessing your database under Access 2007 has modified it in a way that leeds
to incompatibilities under A2003 - it's likely somehow corrupted. To test if the
problems are caused by corruption:
- Start Access 2003 on your machine
- Decompile your database (command line; e.g. "c:\programs\microsoft
office\office11\msaccess.exe" "c:\your.mdb" /DECOMPILE). Press SHIFT during
decompilation if it contains a start form or an autoexec macro.
- Close it, open once more, compact&repair. Then compile the vba project. Run
all forms and reports or other functions one time to see if everything works
correctly.
- Try this mdb version on the other machines.

It is generally not a good idea to develop, even partly, a mdb in a higher
version of Access than it will later run.

Ciao, Sascha

 

[Fred Boer]

Hi Ken:

Sorry if I wasn't clear! The form is called "frmNewDaybook" is actually
based on a table called "tblLessons", with the fields:

LessonID (Autonumber PK)
ClassID
LessonDate
LessonTopic
LessonNotes

frmNewDaybook is based on a filtered version of the table, based on choices
of class and date made by made in frmSelection, which is the first form
shown, and which actually opens frmNewDaybook.

The attendance subform is based on the following query:

SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;

The application has the following tables:

tblAttendance (AttendanceID, StudentClassID, AttendanceCode, ClassDate)
tblClasses (ClassID, ClassName)
tblLessons (LessonID, ClassID, Lesson Date, LessonTopic, LessonNotes)
tblStudentClasses (StudentClassID, StudentID, ClassID)
tblStudents (StudentID, StudentLastname...)

Cheers!
Fred

 

[Fred Boer]

Dear Sascha:

Will also try this on Monday. I don't think I modified the application in
A2007, simply ran it, *but* I could be wrong and have made some change...

Cheers!
Fred

 

[Ken Snell \(MVP\)]

Fred -

In cases like these, I explicting declare the parameters in the query to
avoid data mismatching that won't give an error but don't produce any
records either (e.g., a date/time value that is interpreted as something
other than a date/time value). Try this for the subform's RecordSource query
(assuming that ClassID is a Long Integer data type):


PARAMETERS [Forms]![frmNewDayBook]![txtLessonDate] DateTime,
[Forms]![frmNewDayBook]![txtClassID] Long;
SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;


--

Ken Snell
<MS ACCESS MVP>

Hi Ken:

Sorry if I wasn't clear! The form is called "frmNewDaybook" is actually
based on a table called "tblLessons", with the fields:

LessonID (Autonumber PK)
ClassID
LessonDate
LessonTopic
LessonNotes

frmNewDaybook is based on a filtered version of the table, based on
choices of class and date made by made in frmSelection, which is the first
form shown, and which actually opens frmNewDaybook.

The attendance subform is based on the following query:

SELECT tblAttendance.AttendanceID, tblAttendance.StudentClassID,
tblAttendance.AttendanceCode, tblAttendance.ClassDate,
tblStudentClasses.ClassID, tblstudents.StudentLastname & ", " &
tblStudents.StudentFirstName & " " & tblstudents.StudentMidName AS
FullName
FROM (tblStudents RIGHT JOIN tblStudentClasses ON tblStudents.StudentID =
tblStudentClasses.StudentID) LEFT JOIN tblAttendance ON
tblStudentClasses.StudentClassID = tblAttendance.StudentClassID
WHERE (((tblAttendance.ClassDate)=[Forms]![frmNewDayBook]![txtLessonDate])
AND ((tblStudentClasses.ClassID)=[Forms]![frmNewDayBook]![txtClassID]))
ORDER BY tblStudents.StudentLastName, tblStudents.StudentFirstName;

The application has the following tables:

tblAttendance (AttendanceID, StudentClassID, AttendanceCode, ClassDate)
tblClasses (ClassID, ClassName)
tblLessons (LessonID, ClassID, Lesson Date, LessonTopic, LessonNotes)
tblStudentClasses (StudentClassID, StudentID, ClassID)
tblStudents (StudentID, StudentLastname...)

Cheers!
Fred

I misunderstood your info then -- I thought I'd read that the problem
didn't occur when you stepped through the code, but did occur when your
code ran on its own.

If the form is opening without records, then likely the query is not
reading the parameter or WHERE filter correctly. Can you post the SQL
statement of the form's Recordsource query?