Form or Table linked to Excel Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel spreadsheet template that I use to collect
RS-232 serial data. The sheet has conditional cells if
the data is outside of the tolerance (specified on the
worksheet). At this time we save the worksheet as the
toolid.xls for the toolid, for history's sake. I'm
trying to move this to an Access database. I have a
tblTool and a tblCalibration. I created a frmTool with
the readings as a subform from tblCalibrations. My
problem now is that I can't calculate the items as I did
in the excel sheet. Is there anyway I can "link" the
excel template to the Access form? I am not well versed
at all the VBA coding. Thanks if you are able to help.
If this is a bad idea, any suggestions would be greatly
appreciated.
 
Hi,

Populate your subform with a query based on tblCalibrations. Use
calculated fields in the query to do your calculations (or rather those
that depend on just the one tblCalibration record).

You can then use conditional formatting to flag off-tolerance records.
If some of the tests involve comparing calibration records for a
particular tool, you can do that with code running in the Current event
of the form (not the subform), using functions like DMax() and DSum() to
get the values you need from the individual calibrations.

Probably your tolerances should also be stored in a table, and pulled
from that as required either by joining this table with tblCalibrations
in the above query, or by using DLookup() in the calculated fields.
 
Thank you very much for your help. I really appreciate
it!
-----Original Message-----
Hi,

Populate your subform with a query based on tblCalibrations. Use
calculated fields in the query to do your calculations (or rather those
that depend on just the one tblCalibration record).

You can then use conditional formatting to flag off- tolerance records.
If some of the tests involve comparing calibration records for a
particular tool, you can do that with code running in the Current event
of the form (not the subform), using functions like DMax () and DSum() to
get the values you need from the individual calibrations.

Probably your tolerances should also be stored in a table, and pulled
from that as required either by joining this table with tblCalibrations
in the above query, or by using DLookup() in the calculated fields.



I have an excel spreadsheet template that I use to collect
RS-232 serial data. The sheet has conditional cells if
the data is outside of the tolerance (specified on the
worksheet). At this time we save the worksheet as the
toolid.xls for the toolid, for history's sake. I'm
trying to move this to an Access database. I have a
tblTool and a tblCalibration. I created a frmTool with
the readings as a subform from tblCalibrations. My
problem now is that I can't calculate the items as I did
in the excel sheet. Is there anyway I can "link" the
excel template to the Access form? I am not well versed
at all the VBA coding. Thanks if you are able to help.
If this is a bad idea, any suggestions would be greatly
appreciated.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top