subform data

[cporter]

I have a table for equipment and another for parts from an old existing
database. The equipment is under an alpha numeric primary key similar
to ACS-215. The parts have a column for equipment used on that
references the alpha characters of the equipment ID. How can I get the
parts data to show in a subform under the equipment? I was thinking of
using a query using mid(machid,1,3) but placing that in the "EQPUSEDON"
field generates a compile error. Some sample data is listed below,
unformatted.



MACHID DESCRIP MFG MODEL SERNO
ACS-215 SPIN RESIST COATER KARL SUSS "ACS-200""SUSS RC8""" 0215
AIG-001 AIR IONIZING GRIDS. 2 FT. X 4 FT.
AMS-013 AMS AMS-200 8619-013
AMS-152 AIRIAL IMAGE MEASUREING SYSTEM ZEISS MSM100 741152
AMT-001 MASK TITLER #001 A#420413 APT 230XY 001


PARTNO PRIORITY DESCRIP LOCATION ONHAND MIN UNIT UNITQTY LASTPRICE LASTORDER ONORDERQTY ONORDERPO EQPUSEDON VENDORID COST AVAIL DEV
VALVE-ADJ 1 VALVE ADJ. FOR
HMC 06-D07-D 4 1 Each 0 $0.00 0 HMC $0.00 0
AU-45 1 CIRCUIT BOARD 00-000-0 1 1 Each 0 $0.00 0 SRC $0.00 0
38-07-02-30101 1 READING HEAD FOR MICROMANIPULATOR
SCOPE 04-D04-A 1 1 Each 0 $0.00 0 MIC $0.00 -1
W244 1 "4"" LABLINE CAP" 06-D03-B 1 1 Each 0 PLUMBING $0.00 0
236-25 1 COUPLING, HUCO DISKS, FOR
BRUSH 16-D06-K 4 1 Each 0 $0.00 0 HMC $0.00 0

 

[Larry Linson]

You do realize, I suppose, that the "unformatted" nature of the data you
show makes it only guesswork for us to relate the name to the data? What
seems obvious to you may not be so obvious to someone else. It is made
particularly difficult when your data appears to be separated by blanks, but
at least one field of may also contain blanks.

In the query for the main Form, create a caclulated field (and I am guessing
from context that "machid" is the alphanumeric primary key for the
equipment -- it also helps if you are consistent in what you use for names
for things) like

PREFIX: Left$(machid, 3)

Use that as the LinkMasterField and (again, guessing from context) EQPUSEDON
as the LinkChildFields of the Subform Control. That's the way to synchronize
a main Form and the data displayed in a Subform Control.

Larry Linson
Microsoft Access MVP

 

[cporter]

Yes I realize the unformatted data makes it a guess but it's a better
guess than with just a description.

Using the function that you suggested first gave me a compile error
like the mid function I was using. I apparently made some changes to
the wrong things in the VBA module windows. The subform is working now,
thanks.