Calculate Query and Checkboxes

[Guest]

I have to set up checkboxes on a form and am having difficulties due to a
query. I have a query that “catches†a max value on any duplicate records.
Although they are not actually duplicates, the do have a few duplicate values
that are Primary Keys in another table/query. So I need to use the most
recent of any ‘duplicated’ records. This feeds a list to another (parameter)
query. The checkboxes won’t function because of the ‘calculated’ query
(recordset not updateable). Is there a way to redo or rework this and get the
same effect?

‘Calculated’ Query:

SELECT Max([Main data table].[Inspection #]) AS [MaxOfInspection #], [Main
data table].[Work Order:]
FROM [Main data table]
GROUP BY [Main data table].[Work Order:];

Parameter Query:

SELECT tblSpecialsByJobNmr.JOB_NBR, tblSpecialsByJobNoPrintList.print_check,
tblSpecialsByJobNoPrintList.job_line_no, tblSpecialsByJobNmr.WORK_ORDER,
tblSpecialsByJobNmr.ITEM_NBR, tblSpecialsByJobNmr.STATUS,
tblSpecialsByJobNmr.DT_ACT_COMPL, tblSpecialsByJobNmr.SCHED_COMP_QTY,
tblSpecialsByJobNmr.SCRAP_QTY, [Main data table].[Date of inspect], [Main
data table].[Time Inspect], [Main data table].[Disposition:], [Main data
table].[Rework Sent To]
FROM ((tblSpecialsByJobNoPrintList RIGHT JOIN tblSpecialsByJobNmr ON
tblSpecialsByJobNoPrintList.job_line_no = tblSpecialsByJobNmr.JOB_LINE_NBR)
LEFT JOIN qryQaTableParseMax ON tblSpecialsByJobNmr.WORK_ORDER =
qryQaTableParseMax.[Work Order:]) LEFT JOIN [Main data table] ON
qryQaTableParseMax.[MaxOfInspection #] = [Main data table].[Inspection #]
WHERE (((tblSpecialsByJobNmr.JOB_NBR)=[Enter a Job Number]));

Any help on this is greatly appreciated. You don’t know how long I spent of
deducing the problem.
Jim

 

[[MVP] S.Clark]

I've got about 5 ideas for you, but I need to understand the use of the
checkboxes before offering anything.

Please elaborate.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Guest]

Howdy,

the checkboxes are to 'tag' items to be printed. a list is displayed as a
tabular form. You should be able to place a check in the box and it would
send a check to the underlying table. I have a button on the form header that
starts a Report and prints only those with a check. I will be adding a button
to clear all checks (once i can get this functional). it worked as a
portotype, but when i added a few more items into the queries, i lost the
ability to change the check value from the form or query. Please note i
updated the 'Calculated' query as 'qryQaTableParseMax'. i noticed it might
help in the last query.

Thanks Loads for any help or direction. I just wish i had the ability to code.
Jackle

I've got about 5 ideas for you, but I need to understand the use of the
checkboxes before offering anything.

Please elaborate.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

I have to set up checkboxes on a form and am having difficulties due to a
query. I have a query that "catches" a max value on any duplicate records.
Although they are not actually duplicates, the do have a few duplicate
values
that are Primary Keys in another table/query. So I need to use the most
recent of any 'duplicated' records. This feeds a list to another
(parameter)
query. The checkboxes won't function because of the 'calculated' query
(recordset not updateable). Is there a way to redo or rework this and get
the
same effect?

'Calculated' Query (shown below as qryQaTableParseMax):

SELECT Max([Main data table].[Inspection #]) AS [MaxOfInspection #], [Main
data table].[Work Order:]
FROM [Main data table]
GROUP BY [Main data table].[Work Order:];

Parameter Query:

SELECT tblSpecialsByJobNmr.JOB_NBR,
tblSpecialsByJobNoPrintList.print_check,
tblSpecialsByJobNoPrintList.job_line_no, tblSpecialsByJobNmr.WORK_ORDER,
tblSpecialsByJobNmr.ITEM_NBR, tblSpecialsByJobNmr.STATUS,
tblSpecialsByJobNmr.DT_ACT_COMPL, tblSpecialsByJobNmr.SCHED_COMP_QTY,
tblSpecialsByJobNmr.SCRAP_QTY, [Main data table].[Date of inspect], [Main
data table].[Time Inspect], [Main data table].[Disposition:], [Main data
table].[Rework Sent To]
FROM ((tblSpecialsByJobNoPrintList RIGHT JOIN tblSpecialsByJobNmr ON
tblSpecialsByJobNoPrintList.job_line_no =
tblSpecialsByJobNmr.JOB_LINE_NBR)
LEFT JOIN qryQaTableParseMax ON tblSpecialsByJobNmr.WORK_ORDER =
qryQaTableParseMax.[Work Order:]) LEFT JOIN [Main data table] ON
qryQaTableParseMax.[MaxOfInspection #] = [Main data table].[Inspection #]
WHERE (((tblSpecialsByJobNmr.JOB_NBR)=[Enter a Job Number]));

Any help on this is greatly appreciated. You don't know how long I spent
of
deducing the problem.
Jim

 

[[MVP] S.Clark]

To "send a check to the underlying table", you could use an Update Query for
the desired RecordID.

e.g.
Update Tablename Set Fieldname = True WHERE RecordID =
[Forms]![FormName]![ControlNameFromForm]

To clear them all use:

Update Tablename Set Fieldname = False

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Howdy,

the checkboxes are to 'tag' items to be printed. a list is displayed as a
tabular form. You should be able to place a check in the box and it would
send a check to the underlying table. I have a button on the form header
that
starts a Report and prints only those with a check. I will be adding a
button
to clear all checks (once i can get this functional). it worked as a
portotype, but when i added a few more items into the queries, i lost the
ability to change the check value from the form or query. Please note i
updated the 'Calculated' query as 'qryQaTableParseMax'. i noticed it might
help in the last query.

Thanks Loads for any help or direction. I just wish i had the ability to
code.
Jackle

I've got about 5 ideas for you, but I need to understand the use of the
checkboxes before offering anything.

Please elaborate.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

I have to set up checkboxes on a form and am having difficulties due to
a
query. I have a query that "catches" a max value on any duplicate
records.
Although they are not actually duplicates, the do have a few duplicate
values
that are Primary Keys in another table/query. So I need to use the most
recent of any 'duplicated' records. This feeds a list to another
(parameter)
query. The checkboxes won't function because of the 'calculated' query
(recordset not updateable). Is there a way to redo or rework this and
get
the
same effect?

'Calculated' Query (shown below as qryQaTableParseMax):

SELECT Max([Main data table].[Inspection #]) AS [MaxOfInspection #],
[Main
data table].[Work Order:]
FROM [Main data table]
GROUP BY [Main data table].[Work Order:];

Parameter Query:

SELECT tblSpecialsByJobNmr.JOB_NBR,
tblSpecialsByJobNoPrintList.print_check,
tblSpecialsByJobNoPrintList.job_line_no,
tblSpecialsByJobNmr.WORK_ORDER,
tblSpecialsByJobNmr.ITEM_NBR, tblSpecialsByJobNmr.STATUS,
tblSpecialsByJobNmr.DT_ACT_COMPL, tblSpecialsByJobNmr.SCHED_COMP_QTY,
tblSpecialsByJobNmr.SCRAP_QTY, [Main data table].[Date of inspect],
[Main
data table].[Time Inspect], [Main data table].[Disposition:], [Main
data
table].[Rework Sent To]
FROM ((tblSpecialsByJobNoPrintList RIGHT JOIN tblSpecialsByJobNmr ON
tblSpecialsByJobNoPrintList.job_line_no =
tblSpecialsByJobNmr.JOB_LINE_NBR)
LEFT JOIN qryQaTableParseMax ON tblSpecialsByJobNmr.WORK_ORDER =
qryQaTableParseMax.[Work Order:]) LEFT JOIN [Main data table] ON
qryQaTableParseMax.[MaxOfInspection #] = [Main data table].[Inspection
#]
WHERE (((tblSpecialsByJobNmr.JOB_NBR)=[Enter a Job Number]));

Any help on this is greatly appreciated. You don't know how long I
spent
of
deducing the problem.
Jim

 

[Guest]

i did some looking up in Help and on=line and experimented. I tried the
recordset type=Dynaset (Inconsistent Updates). it seems to work since i am
only changing values for one field. do you know of any snags or problems with
this?
Thanks,
Jim

To "send a check to the underlying table", you could use an Update Query for
the desired RecordID.

e.g.
Update Tablename Set Fieldname = True WHERE RecordID =
[Forms]![FormName]![ControlNameFromForm]

To clear them all use:

Update Tablename Set Fieldname = False

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Howdy,

the checkboxes are to 'tag' items to be printed. a list is displayed as a
tabular form. You should be able to place a check in the box and it would
send a check to the underlying table. I have a button on the form header
that
starts a Report and prints only those with a check. I will be adding a
button
to clear all checks (once i can get this functional). it worked as a
portotype, but when i added a few more items into the queries, i lost the
ability to change the check value from the form or query. Please note i
updated the 'Calculated' query as 'qryQaTableParseMax'. i noticed it might
help in the last query.

Thanks Loads for any help or direction. I just wish i had the ability to
code.
Jackle

I've got about 5 ideas for you, but I need to understand the use of the
checkboxes before offering anything.

Please elaborate.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

I have to set up checkboxes on a form and am having difficulties due to
a
query. I have a query that "catches" a max value on any duplicate
records.
Although they are not actually duplicates, the do have a few duplicate
values
that are Primary Keys in another table/query. So I need to use the most
recent of any 'duplicated' records. This feeds a list to another
(parameter)
query. The checkboxes won't function because of the 'calculated' query
(recordset not updateable). Is there a way to redo or rework this and
get
the
same effect?

'Calculated' Query (shown below as qryQaTableParseMax):

SELECT Max([Main data table].[Inspection #]) AS [MaxOfInspection #],
[Main
data table].[Work Order:]
FROM [Main data table]
GROUP BY [Main data table].[Work Order:];

Parameter Query:

SELECT tblSpecialsByJobNmr.JOB_NBR,
tblSpecialsByJobNoPrintList.print_check,
tblSpecialsByJobNoPrintList.job_line_no,
tblSpecialsByJobNmr.WORK_ORDER,
tblSpecialsByJobNmr.ITEM_NBR, tblSpecialsByJobNmr.STATUS,
tblSpecialsByJobNmr.DT_ACT_COMPL, tblSpecialsByJobNmr.SCHED_COMP_QTY,
tblSpecialsByJobNmr.SCRAP_QTY, [Main data table].[Date of inspect],
[Main
data table].[Time Inspect], [Main data table].[Disposition:], [Main
data
table].[Rework Sent To]
FROM ((tblSpecialsByJobNoPrintList RIGHT JOIN tblSpecialsByJobNmr ON
tblSpecialsByJobNoPrintList.job_line_no =
tblSpecialsByJobNmr.JOB_LINE_NBR)
LEFT JOIN qryQaTableParseMax ON tblSpecialsByJobNmr.WORK_ORDER =
qryQaTableParseMax.[Work Order:]) LEFT JOIN [Main data table] ON
qryQaTableParseMax.[MaxOfInspection #] = [Main data table].[Inspection
#]
WHERE (((tblSpecialsByJobNmr.JOB_NBR)=[Enter a Job Number]));

Any help on this is greatly appreciated. You don't know how long I
spent
of
deducing the problem.
Jim