Report Group Level On Open - Receiving Error

L

lbernarde

I'm trying to set my group level using code I've found on the msg board. I'm
getting the following error:
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. (Error
3197)

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![frm_report_status]![cmbx_sort_order]
Case "Due_Date" 'Due Date
Me.GroupLevel(0).ControlSource = "Due_Date"
Me.GroupLevel(1).ControlSource = "GroupName"
Me.GroupLevel(2).ControlSource = "Report_Type"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select
sql_source = "SELECT tbl_GroupPlan_Reports.*" & _
" FROM tbl_GroupPlan_Reports" & _
" WHERE
(((tbl_GroupPlan_Reports.Report_type)=nz([Forms]![frm_report_status]![txt_reporttype],[tbl_GroupPlan_Reports]![Report_type]))
AND
((tbl_GroupPlan_Reports.GroupID)=nz([Forms]![frm_report_status]![cmbx_group_name],[groupID]))
AND
((tbl_GroupPlan_Reports.Due_date)>=nz([Forms]![frm_report_status]![txt_daterangestart],[due_date])
And
(tbl_GroupPlan_Reports.Due_date)<=nz([Forms]![frm_report_status]![txt_daterangeend],[due_date])));"
Me.RecordSource = sql_source
End Sub
 
A

Allen Browne

Nothing in your code is altering data, so the message must be due to
something else. It may be to do with data types.

Your code does a Select Case based on the value of the combo box on the
form. Your first Case statement then compares the combo's value to the
*text* "Due_Date". Your second Case compares the combo's value to the
numeric value 2. What is the data type of the combo's bound column? Is it
text? Or is is a number? You need to design your Case statements so they use
the matching data type (whatever that is for the combo.)

There could also be an issue with the data type of the groupings. If
Due_Date is a Date/Time field, but Company is a Number field, then changing
the ControlSource of the GroupLevel could have other implications. This will
depend on what other settings you have on the group level. For example, if
you saved the report to group on the first prefix character of the field,
that's not going to work well when you switch the GroupLevel to a date.

A third possibility is that the data type of the fields for the report is
being messed up. Your code assigns a SQL statement to the report's
RecordSource for some reason. The SQL statement uses Nz() around the
expressions. JET cannot determine the data type of a value returned from
Nz() because it returns a variant. A variant doesn't have any consistent
data type, so such a function could return a different data type each time
it's called. But JET needs a consistent data type for a column, so it makes
the only safe choice it can: it treats the value as Text. If you then try to
treat the result as a different data type, it's not going to work. More on
this issue:
http://allenbrowne.com/QueryPerfIssue.html#Nz

While those 3 factors regarding data types are real issues, there's also a
strong possibility that the message is the result of something else going on
in the database, such as unsaved records in your form(s), or issues with
recordsets that were not closed properly, or transactions that were not
committed/rolled back, or ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

lbernarde said:
I'm trying to set my group level using code I've found on the msg board.
I'm
getting the following error:
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time.
(Error
3197)

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![frm_report_status]![cmbx_sort_order]
Case "Due_Date" 'Due Date
Me.GroupLevel(0).ControlSource = "Due_Date"
Me.GroupLevel(1).ControlSource = "GroupName"
Me.GroupLevel(2).ControlSource = "Report_Type"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select
sql_source = "SELECT tbl_GroupPlan_Reports.*" & _
" FROM tbl_GroupPlan_Reports" & _
" WHERE
(((tbl_GroupPlan_Reports.Report_type)=nz([Forms]![frm_report_status]![txt_reporttype],[tbl_GroupPlan_Reports]![Report_type]))
AND
((tbl_GroupPlan_Reports.GroupID)=nz([Forms]![frm_report_status]![cmbx_group_name],[groupID]))
AND
((tbl_GroupPlan_Reports.Due_date)>=nz([Forms]![frm_report_status]![txt_daterangestart],[due_date])
And
(tbl_GroupPlan_Reports.Due_date)<=nz([Forms]![frm_report_status]![txt_daterangeend],[due_date])));"
Me.RecordSource = sql_source
End Sub
Click to expand...
 
L

lbernarde

I believe that your suggestion of :"such as unsaved records in your form(s),
or issues with recordsets that were not closed properly, or transactions that
were not
committed/rolled back, or ..." is the problem. I tried running a make table
query to see what happened and I get the same error. Can you give me some
ideas of where to begin checking for the error? I have 2 databases, one with
the tables and one with the forms/code etc.
Thanks

Allen Browne said:
Nothing in your code is altering data, so the message must be due to
something else. It may be to do with data types.

Your code does a Select Case based on the value of the combo box on the
form. Your first Case statement then compares the combo's value to the
*text* "Due_Date". Your second Case compares the combo's value to the
numeric value 2. What is the data type of the combo's bound column? Is it
text? Or is is a number? You need to design your Case statements so they use
the matching data type (whatever that is for the combo.)

There could also be an issue with the data type of the groupings. If
Due_Date is a Date/Time field, but Company is a Number field, then changing
the ControlSource of the GroupLevel could have other implications. This will
depend on what other settings you have on the group level. For example, if
you saved the report to group on the first prefix character of the field,
that's not going to work well when you switch the GroupLevel to a date.

A third possibility is that the data type of the fields for the report is
being messed up. Your code assigns a SQL statement to the report's
RecordSource for some reason. The SQL statement uses Nz() around the
expressions. JET cannot determine the data type of a value returned from
Nz() because it returns a variant. A variant doesn't have any consistent
data type, so such a function could return a different data type each time
it's called. But JET needs a consistent data type for a column, so it makes
the only safe choice it can: it treats the value as Text. If you then try to
treat the result as a different data type, it's not going to work. More on
this issue:
http://allenbrowne.com/QueryPerfIssue.html#Nz

While those 3 factors regarding data types are real issues, there's also a
strong possibility that the message is the result of something else going on
in the database, such as unsaved records in your form(s), or issues with
recordsets that were not closed properly, or transactions that were not
committed/rolled back, or ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

lbernarde said:
I'm trying to set my group level using code I've found on the msg board.
I'm
getting the following error:
The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time.
(Error
3197)

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![frm_report_status]![cmbx_sort_order]
Case "Due_Date" 'Due Date
Me.GroupLevel(0).ControlSource = "Due_Date"
Me.GroupLevel(1).ControlSource = "GroupName"
Me.GroupLevel(2).ControlSource = "Report_Type"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select
sql_source = "SELECT tbl_GroupPlan_Reports.*" & _
" FROM tbl_GroupPlan_Reports" & _
" WHERE
(((tbl_GroupPlan_Reports.Report_type)=nz([Forms]![frm_report_status]![txt_reporttype],[tbl_GroupPlan_Reports]![Report_type]))
AND
((tbl_GroupPlan_Reports.GroupID)=nz([Forms]![frm_report_status]![cmbx_group_name],[groupID]))
AND
((tbl_GroupPlan_Reports.Due_date)>=nz([Forms]![frm_report_status]![txt_daterangestart],[due_date])
And
(tbl_GroupPlan_Reports.Due_date)<=nz([Forms]![frm_report_status]![txt_daterangeend],[due_date])));"
Me.RecordSource = sql_source
End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

To save the record in your form first, use:
If Me.Dirty Then Me.Dirty = False

If you open recordsets in your code, you will need to find the routines
where you did that and make sure the recordset variable is closed (if you
opened it) and set to Nothing.

Likewise if you are using BeginTrans, you must ensure you have a matching
CommitTrans or RollBack (even after an error.)

We can't locate those for you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping and Sorting a Report On Open Event 5
Data type mismatch when passing String sort field to report 1

Top