Problem with VBA Code behind form

[Tom]

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card printed.
They are selected using a check box hidden behind a button. When the print
button is pressed, it should select all of the users with status 'N' (new),
output their information to a form to print the barcoded cards, then open
the underlying data table in which it will change the cards' status from
'N'(ew) to 'P'(rinted), and load the PC name, the Administrator's ID and the
Date/Time into their appropriate fields.

You can see there are two major parts to this code: print the cards, and
update the table.

I have tried running the report followed by updating the table, and vice
versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and the table
updates OK, but on leaving the code and returning to the underlying form,
the form's display is corrupt (like it could not get the lines out in time).
Even exiting the form to its parent form and coming back in again results in
a corruption of the form.

If I update the table then run the report, the update goes OK but the report
shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table update
and the record select on the report (using the 'In()' command). Both
MsgBoxes suggest there is no problem at all. This is most weird when the
report is being run after the table update as it clearly tells me the report
is being passed the correct data (eg "REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious code and
it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the update and
move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying query, then
end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

 

[JohnFol]

"on leaving the code and returning to the underlying form, the form's
display is corrupt (like it could not get the lines out in time)."

What if you do a requery / refresh of the form?

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card printed.
They are selected using a check box hidden behind a button. When the print
button is pressed, it should select all of the users with status 'N'
(new),
output their information to a form to print the barcoded cards, then open
the underlying data table in which it will change the cards' status from
'N'(ew) to 'P'(rinted), and load the PC name, the Administrator's ID and
the
Date/Time into their appropriate fields.

You can see there are two major parts to this code: print the cards, and
update the table.

I have tried running the report followed by updating the table, and vice
versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and the
table
updates OK, but on leaving the code and returning to the underlying form,
the form's display is corrupt (like it could not get the lines out in
time).
Even exiting the form to its parent form and coming back in again results
in
a corruption of the form.

If I update the table then run the report, the update goes OK but the
report
shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table update
and the record select on the report (using the 'In()' command). Both
MsgBoxes suggest there is no problem at all. This is most weird when the
report is being run after the table update as it clearly tells me the
report
is being passed the correct data (eg "REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious code and
it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the update and
move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying query,
then
end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

 

[Tom]

Hi John

I have tried this.

I have embedded 'requery' at the end of each part, to no avail.

Tom

"on leaving the code and returning to the underlying form, the form's
display is corrupt (like it could not get the lines out in time)."

What if you do a requery / refresh of the form?

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card printed.
They are selected using a check box hidden behind a button. When the print
button is pressed, it should select all of the users with status 'N'
(new),
output their information to a form to print the barcoded cards, then open
the underlying data table in which it will change the cards' status from
'N'(ew) to 'P'(rinted), and load the PC name, the Administrator's ID and
the
Date/Time into their appropriate fields.

You can see there are two major parts to this code: print the cards, and
update the table.

I have tried running the report followed by updating the table, and vice
versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and the
table
updates OK, but on leaving the code and returning to the underlying form,
the form's display is corrupt (like it could not get the lines out in
time).
Even exiting the form to its parent form and coming back in again results
in
a corruption of the form.

If I update the table then run the report, the update goes OK but the
report
shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table update
and the record select on the report (using the 'In()' command). Both
MsgBoxes suggest there is no problem at all. This is most weird when the
report is being run after the table update as it clearly tells me the
report
is being passed the correct data (eg "REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious code and
it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the update and
move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying query,
then
end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

 

[Dirk Goldgar]

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card
printed. They are selected using a check box hidden behind a button.
When the print button is pressed, it should select all of the users
with status 'N' (new), output their information to a form to print
the barcoded cards, then open the underlying data table in which it
will change the cards' status from 'N'(ew) to 'P'(rinted), and load
the PC name, the Administrator's ID and the Date/Time into their
appropriate fields.

You can see there are two major parts to this code: print the cards,
and update the table.

I have tried running the report followed by updating the table, and
vice versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and
the table updates OK, but on leaving the code and returning to the
underlying form, the form's display is corrupt (like it could not get
the lines out in time). Even exiting the form to its parent form and
coming back in again results in a corruption of the form.

If I update the table then run the report, the update goes OK but the
report shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table
update and the record select on the report (using the 'In()'
command). Both MsgBoxes suggest there is no problem at all. This is
most weird when the report is being run after the table update as it
clearly tells me the report is being passed the correct data (eg
"REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious
code and it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the
update and move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying
query, then end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, ,
strWhere DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

Questions for you, Tom:

1. When you talk about the form's display being "corrupt", what do you
mean? What are you actually seeing?

2. What is the recordsource of the report? If you're getting no data on
the report when you update the table first, it sounds as though there
must be criteria in the recordsource that exclude the records you've
just updated.

3. What is "MySelected"? I *guess* it's a function that returns the
comma-delimited list of record IDs, but it would be nice to see what's
in there.

4. Is there code in the report that also updates the OPERATOR_CARDS
table?

 

[Tom]

Hi Dirk

In answer to your questions

1. When you talk about the form's display being "corrupt", what do you
mean? What are you actually seeing?

A bit like parralel vertical bars or slats of wood, in that it looks like
some lines have moved up half a line leaving a gap underneath through which
you can see the report. I have never seen anything like it before.

2. What is the recordsource of the report? If you're getting no data on
the report when you update the table first, it sounds as though there
must be criteria in the recordsource that exclude the records you've
just updated.

The recordsource for both the form and the report are a query, the basis of
which is the same table that is updated by the other part of the code.
Basically, the query calls from the table details of all existing operator
cards with a status of 'N' for new. Only new cards can be printed. Each
record is displayed as part of the continuous form and has a checkbox under
a non-visible command button. Clicking on the button sets the check box,
thereby selecting that record for further processing. When the user clicks
on the print button, the report is run using the same query as used to load
the form, but the selection is limited further by only choosing the records
checked on the form. I guess I can try using the table as the source for the
report to see what that does as the form is feeding through the selection
criteria. The update element just selects all records from the underlying
table where the record number (field REC_ID, an autonumber field) is checked
on the form.

I can see how conflicts are occurring because everything sources from, or is
written back to, the same table. I suppose I could use temporary tables but
I am reluctant to do that as this is going to be rolled out over a dozen
PCs, after converting the back end to server-based MSDE tables, rather than
Access.

3. What is "MySelected"? I *guess* it's a function that returns the
comma-delimited list of record IDs, but it would be nice to see what's
in there.

Good deduction. The code is below.

====================

Private Function MySelected() As String

Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <> "" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i

End Function

=============================

4. Is there code in the report that also updates the OPERATOR_CARDS
table?

No. I didn't think of trying that one.


I'm sorry about the length of the reply. I just this helps you help me.

Regards

Tom

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card
printed. They are selected using a check box hidden behind a button.
When the print button is pressed, it should select all of the users
with status 'N' (new), output their information to a form to print
the barcoded cards, then open the underlying data table in which it
will change the cards' status from 'N'(ew) to 'P'(rinted), and load
the PC name, the Administrator's ID and the Date/Time into their
appropriate fields.

You can see there are two major parts to this code: print the cards,
and update the table.

I have tried running the report followed by updating the table, and
vice versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and
the table updates OK, but on leaving the code and returning to the
underlying form, the form's display is corrupt (like it could not get
the lines out in time). Even exiting the form to its parent form and
coming back in again results in a corruption of the form.

If I update the table then run the report, the update goes OK but the
report shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table
update and the record select on the report (using the 'In()'
command). Both MsgBoxes suggest there is no problem at all. This is
most weird when the report is being run after the table update as it
clearly tells me the report is being passed the correct data (eg
"REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious
code and it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the
update and move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying
query, then end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, ,
strWhere DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

Questions for you, Tom:

1. When you talk about the form's display being "corrupt", what do you
mean? What are you actually seeing?

2. What is the recordsource of the report? If you're getting no data on
the report when you update the table first, it sounds as though there
must be criteria in the recordsource that exclude the records you've
just updated.

3. What is "MySelected"? I *guess* it's a function that returns the
comma-delimited list of record IDs, but it would be nice to see what's
in there.

4. Is there code in the report that also updates the OPERATOR_CARDS
table?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tom]

I just tried what I said in my last reply, that is changing the source of
the report from the query to the table itself, and it now seems to work OK
running the table update followed by the report.

Not sure why, though.

I may try running the report followed by the table update to see if that
works as well.

Thanks for your help.

Regards

Tom

Hi Dirk

In answer to your questions

1. When you talk about the form's display being "corrupt", what do you
mean? What are you actually seeing?

A bit like parralel vertical bars or slats of wood, in that it looks like
some lines have moved up half a line leaving a gap underneath through
which you can see the report. I have never seen anything like it before.

2. What is the recordsource of the report? If you're getting no data on
the report when you update the table first, it sounds as though there
must be criteria in the recordsource that exclude the records you've
just updated.

The recordsource for both the form and the report are a query, the basis
of which is the same table that is updated by the other part of the code.
Basically, the query calls from the table details of all existing operator
cards with a status of 'N' for new. Only new cards can be printed. Each
record is displayed as part of the continuous form and has a checkbox
under a non-visible command button. Clicking on the button sets the check
box, thereby selecting that record for further processing. When the user
clicks on the print button, the report is run using the same query as used
to load the form, but the selection is limited further by only choosing
the records checked on the form. I guess I can try using the table as the
source for the report to see what that does as the form is feeding through
the selection criteria. The update element just selects all records from
the underlying table where the record number (field REC_ID, an autonumber
field) is checked on the form.

I can see how conflicts are occurring because everything sources from, or
is written back to, the same table. I suppose I could use temporary tables
but I am reluctant to do that as this is going to be rolled out over a
dozen PCs, after converting the back end to server-based MSDE tables,
rather than Access.

3. What is "MySelected"? I *guess* it's a function that returns the
comma-delimited list of record IDs, but it would be nice to see what's
in there.

Good deduction. The code is below.

====================

Private Function MySelected() As String

Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <> "" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i

End Function

=============================

4. Is there code in the report that also updates the OPERATOR_CARDS
table?

No. I didn't think of trying that one.


I'm sorry about the length of the reply. I just this helps you help me.

Regards

Tom

Hi

I am still a newbie to VBA, and have a problem with the code below.

The theory is as follows.

This code is called when a button is clicked on a continuous form. The
detail area of the form shows user names that can have an ID card
printed. They are selected using a check box hidden behind a button.
When the print button is pressed, it should select all of the users
with status 'N' (new), output their information to a form to print
the barcoded cards, then open the underlying data table in which it
will change the cards' status from 'N'(ew) to 'P'(rinted), and load
the PC name, the Administrator's ID and the Date/Time into their
appropriate fields.

You can see there are two major parts to this code: print the cards,
and update the table.

I have tried running the report followed by updating the table, and
vice versa, and both methods give problems.

If I run the report then update the table, the report runs OK, and
the table updates OK, but on leaving the code and returning to the
underlying form, the form's display is corrupt (like it could not get
the lines out in time). Even exiting the form to its parent form and
coming back in again results in a corruption of the form.

If I update the table then run the report, the update goes OK but the
report shows no data.

In order to try and debug it, I have embedded MsgBoxes to display the
selection data being passed on both the record select for the table
update and the record select on the report (using the 'In()'
command). Both MsgBoxes suggest there is no problem at all. This is
most weird when the report is being run after the table update as it
clearly tells me the report is being passed the correct data (eg
"REC_ID in(101,102,103)).

Can anyone help me with this? This is my first attempt at serious
code and it is slow going ;-)

Regards

Tom

==================================================




Private Sub butPrintCard_Click()

''' Firstly, set up variables as required
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wrtSTATUS As String
Dim wrtPRINT_PC_NAME As String
Dim wrtPRINT_CARD_DATE As Date
Dim wrtPRINT_ADMIN_ID As String
Dim i As Integer
Dim strWhere As String


''' Part 1: Identify the check boxes that are checked

strWhere = MySelected
If strWhere <> "" Then
strWhere = "REC_ID in (" & strWhere & ")"
End If
'''Temporary MsgBox to prove content
MsgBox ("Check Boxes " & strWhere)

''' Open the database and table and find the records
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM OPERATOR_CARDS WHERE " &
strWhere & "ORDER BY [REC_ID]")

''' Populate variables to be written to the table
wrtSTATUS = "P"
wrtPRINT_PC_NAME = Me.PC_NAME
wrtPRINT_CARD_DATE = Now()
wrtPRINT_ADMIN_ID = Me.ADMINISTRATOR_ID

''' Move to the start of the table
rs.MoveLast
rs.MoveFirst

With rs
Do While Not .EOF
.Edit
''' Set KEY_ID to required value, carry out the
update and move to next record
.Fields("STATUS").Value = wrtSTATUS
.Fields("PRINT_PC_NAME").Value = wrtPRINT_PC_NAME
.Fields("PRINT_CARD_DATE").Value = wrtPRINT_CARD_DATE
.Fields("PRINT_ADMIN_ID").Value = wrtPRINT_ADMIN_ID
.Update
.MoveNext
Loop
End With

''' clear down db and rs, and set form to requery its underlying
query, then end the sub
Set db = Nothing
Set rs = Nothing

''' Part 2: Print the cards
'''Temporary MsgBox to prove content
MsgBox ("Print Record IDs " & strWhere)

DoCmd.OpenReport "rptPRINT_EMPLOYEE_CARDS", acViewPreview, ,
strWhere DoCmd.RunCommand acCmdZoom100

Me.Requery

End Sub

===========================================================

Questions for you, Tom:

1. When you talk about the form's display being "corrupt", what do you
mean? What are you actually seeing?

2. What is the recordsource of the report? If you're getting no data on
the report when you update the table first, it sounds as though there
must be criteria in the recordsource that exclude the records you've
just updated.

3. What is "MySelected"? I *guess* it's a function that returns the
comma-delimited list of record IDs, but it would be nice to see what's
in there.

4. Is there code in the report that also updates the OPERATOR_CARDS
table?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In answer to your questions


A bit like parralel vertical bars or slats of wood, in that it looks
like some lines have moved up half a line leaving a gap underneath
through which you can see the report. I have never seen anything like
it before.

Huh. I've never seen anything like that before. I'd be more inclined
to suspect a problem with your video driver than anything else. What
version and service-level of Access are you using, and what operating
system?

The recordsource for both the form and the report are a query, the
basis of which is the same table that is updated by the other part of
the code. Basically, the query calls from the table details of all
existing operator cards with a status of 'N' for new. Only new cards
can be printed. Each record is displayed as part of the continuous
form and has a checkbox under a non-visible command button. Clicking
on the button sets the check box, thereby selecting that record for
further processing.

I'm curious. Why use a transparent button to check a check box? Why
not just let the user check the check box itself? Is the check box not
bound to a field in the form's recordsource?

When the user clicks on the print button, the
report is run using the same query as used to load the form, but the
selection is limited further by only choosing the records checked on
the form. I guess I can try using the table as the source for the
report to see what that does as the form is feeding through the
selection criteria. The update element just selects all records from
the underlying table where the record number (field REC_ID, an
autonumber field) is checked on the form.

If your code behind the "Print" button first updates the table and sets
the selected records' Status to 'P', then those records won't show up on
the report, will they?

I can see how conflicts are occurring because everything sources
from, or is written back to, the same table. I suppose I could use
temporary tables but I am reluctant to do that as this is going to be
rolled out over a dozen PCs, after converting the back end to
server-based MSDE tables, rather than Access.

I don't think see that there's any problem having everything bound to
the same table, so long as you get the sequence of events right. In a
multiuser, server-based environment, you could have editing conflicts if
two users are working on the same set of records, but there are ways to
deal with those, and I don't think you've even gotten to that point yet.

Good deduction. The code is below.

====================

Private Function MySelected() As String

Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <> "" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i

End Function

=============================

Okay, so now I have to ask what "colCheckBox" is. Have you done
something very clever to avoid binding the check boxes on your
continuous form to a field in the form's recordsource?

No. I didn't think of trying that one.

I'm sorry about the length of the reply. I just this helps you help
me.

It does indeed, but there are still some hazy areas.

Here is a how I think I would do something like this. If only one user
(at a time) will be authorized to print these records, I'd probably
define an additional value for the Status field, meaning "to be
printed". The act of selecting a record on the form -- whether by a
button on each record or by some other means -- would set the Status to
that value.

I'd change the report's recordsource to a query that returns only the
records with a Status of "to be printed". That way, the report would
need no special criteria at run time, and if it doesn't get printed the
first time for some reason, the records will still be marked for a
reprint. I'd have code in the Report's Close event that displays a
MsgBox dialog to ask the user if all the records printed successfully.
If the user answers yes, I'd execute an update query to change Status to
some other value for all those records where Status was "to be printed",
and then I'd requery the form (if it's open).

On the other hand, if multiple users are going to be marking records and
printing, and each user must print only her own marked set of records,
then I'd take a different approach. I'd probably use a local table in
the front-end to store the IDs of the records to be printed -- selecting
a record for printing would result in its ID being stored in the local
table. Then the report would just be based on a query that inner joins
the main table with the "IDsToPrint" table on the ID field, so only
those records would get printed. If the report prints successfully, I'd
first execute an update query to change the status of the records in the
main table, and then execute a delete query to empty the IDsToPrint
table.

 

[Dirk Goldgar]

I just tried what I said in my last reply, that is changing the
source of the report from the query to the table itself, and it now
seems to work OK running the table update followed by the report.

Not sure why, though.

Your report's recordsource is now no longer filtering out the records
you want to print.

 

[Tom]

I understand what you are saying but I cannot see why that is the case. The
report's recordsource is the query on which the form is based, ie the form
that supplies the data to the report. This is proven by the test message box
that confirms the correct records are being passed forward to the report.
Also, if I put in a 'go to' command in the VBA code to bypass the update of
the table, it works OK.

Confused from Devon.

Regards

Tom

 

[Tom]

Huh. I've never seen anything like that before. I'd be more inclined
to suspect a problem with your video driver than anything else. What
version and service-level of Access are you using, and what operating
system?

Access 2000 with all updates installed. Tried on two PCs: (a) Pentium 330
with 584Mb RAM running Windows 2000 and onboard graphics, and (b) Sony Vaio
laptop with Centrino 1.7M CPU and 512Mb Ram, running XP Pro with Radeon 64Mb
graphics card. Same on both.

I'm curious. Why use a transparent button to check a check box? Why
not just let the user check the check box itself? Is the check box not
bound to a field in the form's recordsource?

I tried this originally but because it is a bound continuous form if you
check one box it checks it for every record. Found this fix in this
newsgroup and it seems to work.

If your code behind the "Print" button first updates the table and sets
the selected records' Status to 'P', then those records won't show up on
the report, will they?

Yes, because the report uses the field REC_ID on the form where checkboxes
are set to get the record numbers to update. The status is only checked when
the form is loaded or opened to confirm which
records are to be displayed for selection.

I don't think see that there's any problem having everything bound to
the same table, so long as you get the sequence of events right. In a
multiuser, server-based environment, you could have editing conflicts if
two users are working on the same set of records, but there are ways to
deal with those, and I don't think you've even gotten to that point yet.


Okay, so now I have to ask what "colCheckBox" is. Have you done
something very clever to avoid binding the check boxes on your
continuous form to a field in the form's recordsource?

I have reproduced somebody else's 'very clever' (courtesy of Albert D
Kallal - http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html ). I
had to tweak it a bit, but it works.

It does indeed, but there are still some hazy areas.

Here is a how I think I would do something like this. If only one user
(at a time) will be authorized to print these records, I'd probably
define an additional value for the Status field, meaning "to be
printed". The act of selecting a record on the form -- whether by a
button on each record or by some other means -- would set the Status to
that value.

I'd change the report's recordsource to a query that returns only the
records with a Status of "to be printed". That way, the report would
need no special criteria at run time, and if it doesn't get printed the
first time for some reason, the records will still be marked for a
reprint. I'd have code in the Report's Close event that displays a
MsgBox dialog to ask the user if all the records printed successfully.
If the user answers yes, I'd execute an update query to change Status to
some other value for all those records where Status was "to be printed",
and then I'd requery the form (if it's open).

On the other hand, if multiple users are going to be marking records and
printing, and each user must print only her own marked set of records,
then I'd take a different approach. I'd probably use a local table in
the front-end to store the IDs of the records to be printed -- selecting
a record for printing would result in its ID being stored in the local
table. Then the report would just be based on a query that inner joins
the main table with the "IDsToPrint" table on the ID field, so only
those records would get printed. If the report prints successfully, I'd
first execute an update query to change the status of the records in the
main table, and then execute a delete query to empty the IDsToPrint
table.

At this time, we believe there will only ever be one card administrator, and
only about 100 or so active cards. With this low number of records in mind,
I thought it would be simplest to work the following
status sequence, thereby allowing a query just to look for a specific status
when an action is required.

1). An operator can only have one active card at a time.
2). When a user is first allocated a card, it has status (N)ew.
3). The sequence under discussion here takes the (N)ew card, prints it,
changes it status to (P)rinted and stamps the record's PRINT_DATE field.
4). The new card now exists (barcode format) but has to be (A)ctivated as it
may not be convenient to hand over the card at the time it is printed. When
they want to (A)ctivate the card, it (S)uspends any
active card. The newly activated card goes to status (A)ctivated and has
current date loaded into the ACTIVATED_DATE field. The old card has its
status set to (S)uspended and has the current date
loaded into its SUSPENDED_DATE field. In this way, we keep the transactions
both separated, and provide our own audit trail. Each action also logs the
administrator's ID and the PC network name.

For your interest, this is the administration part of a barcoded data
collection system for reporting scrap during the manufacturing process: ie,
operators scan in a job number, an operation number,
the amount of items they received to work on, the number of goods ones
produced, the number of scrap items, and a reason code for any scrap
produced (all known as Work in Progress). The WiP side
is the easy bit and I have already done that.

Regards

Tom

 

[Dirk Goldgar]

I understand what you are saying but I cannot see why that is the
case. The report's recordsource is the query on which the form is
based, ie the form that supplies the data to the report. This is
proven by the test message box that confirms the correct records are
being passed forward to the report. Also, if I put in a 'go to'
command in the VBA code to bypass the update of the table, it works
OK.

Confused from Devon.

<g>

Think it through. You stated, "The recordsource for both the form and
the report are a query, the basis of which is the same table that is
updated by the other part of the code. Basically, the query calls from
the table details of all existing operator cards with a status of 'N'
for new." So both the form and the report will show only records with
Status = "N".

Now, your code for butPrintCard_Click() updates the table to set all the
selected records to Status = "P", and *then* opens the report.
Therefore, at the time the report's recordsource query is run, none of
the selected records could have Status = "N". Hence the report shows no
records. There's no other possible result, unless you either change the
report's recordsource query or delay the table update until after the
report has been run and printed.

 

[Dirk Goldgar]

Access 2000 with all updates installed. Tried on two PCs: (a) Pentium
330 with 584Mb RAM running Windows 2000 and onboard graphics, and (b)
Sony Vaio laptop with Centrino 1.7M CPU and 512Mb Ram, running XP Pro
with Radeon 64Mb graphics card. Same on both.

Hmm. That doesn't sound like it could be a video driver or hardware
problem, so I'm at a loss. Let's try to solve the data problem, and see
if that makes the display problem go away.

Yes, because the report uses the field REC_ID on the form where
checkboxes are set to get the record numbers to update. The status is
only checked when the form is loaded or opened to confirm which
records are to be displayed for selection.

But that's not what you said earlier. See my reply to your other recent
message in this thread.

I have reproduced somebody else's 'very clever' (courtesy of Albert D
Kallal -
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html ). I
had to tweak it a bit, but it works.

I thought it must be something like that. I don't see anything wrong
with that, though I probably wouldn't do it that way myself unless I had
to. Still, we must consider the possibility that your tweaking
introduced some sort of error.

At this time, we believe there will only ever be one card
administrator, and only about 100 or so active cards. With this low
number of records in mind, I thought it would be simplest to work the
following
status sequence, thereby allowing a query just to look for a specific
status when an action is required.

1). An operator can only have one active card at a time.
2). When a user is first allocated a card, it has status (N)ew.
3). The sequence under discussion here takes the (N)ew card, prints
it, changes it status to (P)rinted and stamps the record's PRINT_DATE
field. 4). The new card now exists (barcode format) but has to be
(A)ctivated as it may not be convenient to hand over the card at the
time it is printed. When they want to (A)ctivate the card, it
(S)uspends any
active card. The newly activated card goes to status (A)ctivated and
has current date loaded into the ACTIVATED_DATE field. The old card
has its status set to (S)uspended and has the current date
loaded into its SUSPENDED_DATE field. In this way, we keep the
transactions both separated, and provide our own audit trail. Each
action also logs the administrator's ID and the PC network name.

For your interest, this is the administration part of a barcoded data
collection system for reporting scrap during the manufacturing
process: ie, operators scan in a job number, an operation number,
the amount of items they received to work on, the number of goods ones
produced, the number of scrap items, and a reason code for any scrap
produced (all known as Work in Progress). The WiP side
is the easy bit and I have already done that.

This all sounds pretty good, so it's just a matter of getting the
sequence of event to come out right. I still believe that you really
have an additional Status for a card, that you haven't properly
accounted for -- "W" : Waiting to Print.

If I were doing this, I would drop all that extra code, have a button on
the continuous form that changes the status of a record from "N" to "W",
then have the print button save the current record (if it's dirty) and
just open the report with no WhereCondition. The report, though, would
be based on a query that selects only the records with Status = "W", and
thus would print only those that are waiting to print. I would set the
query's RecordLocks property to All Records, so that no one can update
the table while the report is printing, and I would have code in the
report's close event ask if all cards printed properly. If the user
says they did, the code would then run an update query to update the
table and set all records with Status = "W" to Status = "P".