Thanks for the education.
I wasn't even aware you could Get External Data from other than
access files.
It looks like I'll have to use a temp tbl after all. Also I'll probably
have to 'loop through' rather than append qry because a field in
tblEquipment has to be updated as a result of the movement.
Thanks for all your help.
--
Len
______________________________________________________
remove nothing for valid email address.
| comments inline.
|
| | > I'm not sure I trust other users to be creating and executing append
| > queries.
| > I'm much happier having them click a button once the file has been
| imported.
| > Or did you mean - set up a permanent append query and have a button run
it
| > and
| > then delete the temp table.
|
| yes, that's exactly what i meant. though i probably would set up an Import
| Specification on the text file and *link* it to the FE db. then write the
| query the same, using the linked file directly rather than a temp table.
run
| the Append query from VBA code in a form, so the user sees only the
| interface rather than the nuts 'n bolts.
|
| if you find that you must use a temp table for some reason, rather than
the
| linked text file directly, i'd favor setting up a separate db on the hard
| drive or the server, to hold the (empty) temp table. you can write code to
| replace the temp *db* each time you use it, so there's no bloat issue
either
| in the temp db or in the working FE db.
|
| >
| > BTW, before your previous reply I did change the Master/Child links for
| the
| > movements subform to MovementNum and now the movements subform now
| displays
| > only the appropriate movement records plus an 'add new record' (*) row
| with
| > default values. OTOH The Maintenance subform just displays appropriate
| > maintenance records without the * row. I expect this will remain the
case
| > when
| > I change the links to MovementID. I am curious as to the differing
| > behaviours.
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| > | > | comments inline.
| > |
| > | | > | > Good Tina.
| > | > I understand what you've said.
| > | >
| > | > I'm sorry to confuse you. My comment about the MoveTo field as the
| > | > link was an afterthought that I put under the wrong heading.
| > | > (My 'see below' comment should have made me realize! D'oh.)
| > | > I was referring to your concept of making the Movements table a join
| > | > table between quniLocation and tblEquipment. What I meant was -
| > | > Which should I link the LocationID field of the select query to,
| > | > either the Movements.MoveTo or Movements.MoveFrom fields.
| > | > Since I now realize that the query isn't a native table and only one
| of
| > | its
| > | > component tables is native, that it is fruitless to 'draw the line'
| > | anyway.
| > | > Right?
| > | >
| > | > As for the import process, I didn't intend to import the text file
| > | directly
| > | > into tblMovements but into a temp table, then use VBA to create
| records
| > in
| > | > tblMovement by looping through the tblTmpImport, then del
| tblTmpImport.
| > | > I don't fully understand your use of the query which effectively
adds
| > the
| > | > EquipmentID field the the temp table. You said "use that query to
| create
| > | > the records in tblMovements". Did you mean 'use VBA to create the
| > records
| > | > from the data in the query' or have I missed the point? If I have
| > | correctly
| > | > understood, how is creating an 'actual' query object
better/different
| > from
| > | > creating a 'virtual' recordset to loop through?
| > |
| > | okay, looks like you understand creating the SELECT query "which
| > effectively
| > | adds the EquipmentID field the the temp table". you're correct, that's
| > | exactly what you're doing - writing a query that will return a dataset
| > which
| > | includes the fields you'll need in order to add the new records to
| > | tblMovements. once you've done that, open the query in Design view. on
| the
| > | menu bar, select Query | Append, and in the dialog, choose
tblMovements
| > from
| > | the droplist of tables. if you're not familiar with setting up Append
| > | queries, read up on it in Help, it's not hard to do in the Design
grid.
| if
| > | you have difficulties, post back for specific help.
| > |
| > | no point opening opening and looping through a recordset when an
Append
| > | query will do all the work for you.
| > |
| > | hth
| > |
| > | >
| > | > I am grateful for your patience, time and expertise.
| > |
| > | no problem. a newsgroup is not the best venue to teach techniques and
| > | troubleshoot problems, but with patience and persistence, we can
usually
| > end
| > | up communicating well enough to get it done.
| > |
| > | > --
| > | > Len
| > | > ______________________________________________________
| > | > remove nothing for valid email address.
| > | > | > | > | comments inline.
| > | > |
| > | > | | > | > | > Thanks tina,
| > | > | > FYI
| > | > | > ---
| > | > | > This app tracks loans of medical equipment to disabled kids.
| > | > | >
| > | > | > I'm not sure exactly why I used the LastMoveID field in
| > tblEquipment.
| > | > | > I guess I'll find out once I remove it ;-)
| > | > | >
| > | > | > The EquipmentNum is a barcode sticker (6 numerals) applied at
| > | purchase.
| > | > | > Unfortunately a small possibility exists that it may wear off or
| > fall
| > | > off
| > | > | > and a new sticker issued. Also the numbers are not issued
| > sequentially
| > | > but
| > | > | > from a number of different rolls of pre-printed barcode
stickers.
| > | > | >
| > | > | > There's already a MoveDate field in tblMovements.
| > | > | > (Will appear in subform and be used for ORDER BY DESC clause.)
| > | > | >
| > | > | > Location/Movements Relationship
| > | > | > -------------------------------
| > | > | > There's already a Location table (tblChild) but it's in an
| entirely
| > | > | > different db created for unrelated purposes but is linked only
in
| > this
| > | > FE,
| > | > | > but not linked in this BE; why would you.
| > | > | > (Similarly other tables are linked from the Child db eg Regions,
| > | Staff.)
| > | > | > FE Relationship not made yet so does this prompt any warnings
from
| > you
| > | > | > for making the relationship in this FE?
| > | > |
| > | > | you can't enforce referential integrity in relationships between
| > linked
| > | > | tables, so it's a waste of time to "draw the lines" in the
| > Relationships
| > | > | window in a FE db. you can only truly relate data between two
| *native*
| > | > | tables that are in the same database. since you're working with
| linked
| > | > | tables that are native to multiple backend dbs, you'll have to
rely
| on
| > | > | yourself to "enforce" referential integrity in the user interface.
| > it's
| > | > | harder to do, because the system won't prevent you from entering
| > | "orphan"
| > | > | data - child data that has no valid parent data.
| > | > |
| > | > | > I assume you would link to the
| > | > | > 'MoveTo' field rather than 'From' field - see below.
| > | > |
| > | > | i'm not sure what you're referring to here. link to the "MoveTo"
| field
| > | > | where? if you were using native tables, you'd link tblLocations to
| > both
| > | > the
| > | > | From and To fields in tblMovements. but as i said above, there's
no
| > | point
| > | > | setting that relationship in the FE db, and since tblMovements and
| > | > | tblLocations are in different BE dbs, you can't set relationships
| > | between
| > | > | the two tables at all.
| > | > |
| > | > | >
| > | > | > Equipment/Movements Relationship
| > | > | > --------------------------------
| > | > | > The movement info is created using a portable barcode scanner so
| the
| > | > | > EquipmentID isn't known then but the barcode is. The text file
| from
| > | the
| > | > | > scanner (Barcode, MovedBy, When, From, To) is then imported and
| the
| > | > | > movement records are created. That's why I wanted to use
| > EquipmentNum
| > | > | > (rather than ID) as the basis for this relationship. Will using
| > | > | > EquipmentNum rather than EquipmentID mean more work or will it
| mean
| > | > there
| > | > | > will be things to be keep in mind later? (Recording both From
and
| To
| > | > helps
| > | > | > to pick up unrecorded movements.)
| > | > |
| > | > | don't use EquipmentNum, use the primary key field EquipmentID as
the
| > | > foreign
| > | > | key in tblMovements, as i said before. when you import your text
| file,
| > | > | import it to a temporary table. then write a query that matches
the
| > | > | EquipmentNum in the text file with the EquipmentNum in
tblEquipment,
| > and
| > | > | include the EquipmentID in the query's output. use that query to
| > create
| > | > the
| > | > | records in tblMovements, rather than dumping the text file
directly
| > into
| > | > the
| > | > | table. that's how you get the necessary foreign key EquipmentID
| value
| > | into
| > | > | each record in tblMovements, so there's a solid link between that
| > table
| > | > and
| > | > | tblEquipment.
| > | > |
| > | > | hth
| > | > |
| > | > | >
| > | > | > It sure would be simpler if that small possibility didn't exist
| and
| > I
| > | > | > could make barcode the pk and get rid of the ID field.
| > | > | >
| > | > | > Your help is very much appreciated. Thanks again.
| > | > | >
| > | > | > --
| > | > | > Len
| > | > | > ______________________________________________________
| > | > | > remove nothing for valid email address.
| > | > | >
| > | > | > | suggest the following changes to tables A and C, as
| > | > | > |
| > | > | > | tblEquipment
| > | > | > | EquipmentID (pk)
| > | > | > | EquipmentNum
| > | > | > | (get rid of the LastMoveID field in this table)
| > | > | > | (and btw, a primary key field can be text. if the equipment
| number
| > | > | > assigned
| > | > | > | to a given item will never change, and is absolutely unique -
if
| a
| > | > piece
| > | > | > of
| > | > | > | equipment breaks down and is replaced, the new piece will get
a
| > | *new*
| > | > | > | equipment number - then you should be able to use it as the pk
| for
| > | > this
| > | > | > | table, if you want. but you can certainly use a separate field
| for
| > | pk,
| > | > | as
| > | > | > | you're now doing.)
| > | > | > |
| > | > | > | tblMovements
| > | > | > | MoveID
| > | > | > | EquipmentID (fk from tblEquipment)
| > | > | > | MoveTo
| > | > | > |
| > | > | > | relationship would be
| > | > | > | tblEquipment.EquipmentID 1:n tblMovements.EquipmentID
| > | > | > |
| > | > | > | and btw, i'm guessing that you're tracking the movement of
| > equipment
| > | > | from
| > | > | > | location to location, correct? if so, i might have a table
| listing
| > | all
| > | > | > | locations, with as much detail describing locations as you
need;
| > | then
| > | > | > | tblMovements would actually be a join table between
tblEquipment
| > and
| > | > | > | tblLocations, as
| > | > | > |
| > | > | > | tblMovements
| > | > | > | MoveID (pk)
| > | > | > | EquipmentID (fk from tblEquipment)
| > | > | > | LocationID (fk from tblLocations)
| > | > | > | MoveDate
| > | > | > | (if you include a move date, you can always find where a piece
| of
| > | > | > equipment
| > | > | > | is currently located - it will be the record with the newest
| date
| > | for
| > | > | that
| > | > | > | piece of equipment, in tblMovements.)
| > | > | > |
| > | > | > | hth
| > | > | > |
| > | > | > |
| > | > | > | | > | > | > | > Thanks tina
| > | > | > | > The FE/BE split is exactly as you said. The link fields
looked
| > ok
| > | > but
| > | > | > I'll
| > | > | > | > look at them again in the light of your definitions. The
fact
| > that
| > | > | they
| > | > | > | > seemed ok led me to look at the relationships as the
problem.
| > Yes
| > | I
| > | > | > meant
| > | > | > | > creating the 'link lines' and enforcing referential
integrity
| > when
| > | I
| > | > | > said
| > | > | > | > 'define relationships'.
| > | > | > | >
| > | > | > | > Looking at the relationships prompts this question -
| > | > | > | > The three tables (and fields) concerned are
| > | > | > | >
| > | > | > | > A B C
| > | > | > | > (tbl)Equipment (tbl)Maintenance (tbl)Movements
| > | > | > | > EquipmentID (PK) MaintenanceID (PK) MoveID (PK)
| > | > | > | > EquipmentNum (RU) EquipmentID (FK,A) EquipmentNum
| > | > ??(FK,A)??
| > | > | > | > LastMoveID (FK,C) MaintenanceCost MoveTo
| > | > | > | > PK=primary
| > | > | > | > FK=foreign
| > | > | > | > RU=Reqd+Unique (effectively another PK but data type is txt)
| > | > | > | >
| > | > | > | > Subform on B works. Relationship is A(1)-B(many)on
| EquipmentID
| > | > | > | >
| > | > | > | > There is a relationship between C(1)-A(many) on
| > MoveID/LastMoveID.
| > | > | > | > I think I also need one A-C on EquipmentNum but when I try
to
| > | create
| > | > | > | > one, access complains that there is already a relationship
| > defined
| > | > and
| > | > | > | > offers to delete it. Do I really need the existing
| relationship
| > or
| > | > can
| > | > | > | > I delete it to create the new one?
| > | > | > | >
| > | > | > | > --
| > | > | > | > Len
| > | > | > | > ______________________________________________________
| > | > | > | > remove nothing for valid email address.
| > | > | > | >
| > | > | > | > | comments inline.
| > | > | > | > |
| > | > | > | > | | > | > | > | > | > Hope this is the appropriate group to ask.
| > | > | > | > | >
| > | > | > | > | > I have two general questions and one a bit more
specific.
| > | > | > | > | > (a) Are relationships defined in the back end effective
in
| > the
| > | > | front
| > | > | > | > end?
| > | > | > | > |
| > | > | > | > | yes.
| > | > | > | > |
| > | > | > | > | > (b) Is it preferable to define relationships in FE or
BE?
| > | > | > | > |
| > | > | > | > | if your BE db is where you store the tables, and the FE db
| has
| > | > links
| > | > | > to
| > | > | > | > | those tables (that's the normal BE/FE setup), then we're
on
| > the
| > | > same
| > | > | > | page.
| > | > | > | > | you can "draw the lines" between linked tables, in the FE
| > | > | > Relationships
| > | > | > | > | window, but you can't enforce referential integrity on
table
| > | > links.
| > | > | so
| > | > | > | if,
| > | > | > | > | when you say "define", you mean set the parent/child links
| AND
| > | > | enforce
| > | > | > | > | referential integrity, then you must do that to native
| > tables -
| > | in
| > | > | > other
| > | > | > | > | words, in the BE db.
| > | > | > | > |
| > | > | > | > | > (c1) Is it necessary to define relationships for
subforms
| to
| > | > work
| > | > | or
| > | > | > | > |
| > | > | > | > | if you mean "define relationships in the Relationships
| > window",
| > | no
| > | > | > it's
| > | > | > | > not
| > | > | > | > | "necessary". but you should, because defining
relationships
| > and
| > | > | > | enforcing
| > | > | > | > | referential integrity is about ensuring the validity of
the
| > | data.
| > | > | the
| > | > | > | fact
| > | > | > | > | that it's easier to work with mainform/subform setups when
| > those
| > | > two
| > | > | > | > things
| > | > | > | > | are done, is a great by-product, but not the reason for
| doing
| > | it.
| > | > | > | > |
| > | > | > | > | > (c2) How do relationships affect the working of
subforms?
| > | > | > | > |
| > | > | > | > | strictly speaking, they don't, in themselves. there are
| > numerous
| > | > | > | > | non-traditional uses of subforms that don't involve table
| > | > | > relationships
| > | > | > | at
| > | > | > | > | all, or stand the usual setup on its' head. but once you
| > define
| > | a
| > | > | > | > | parent/child relationship at the table level, and enforce
| > | > | referential
| > | > | > | > | integrity, and then base a mainform/subform on those
| > | parent/child
| > | > | > | tables,
| > | > | > | > | Access will pretty much demand that you set up it up
right,
| or
| > | it
| > | > | > won't
| > | > | > | > | work.
| > | > | > | > |
| > | > | > | > | >
| > | > | > | > | > I have a form with two subforms. Each subform also has a
| > | > subform.
| > | > | > | (Each
| > | > | > | > | > combination appears on a separate tab of a tab control.)
| One
| > | > | works,
| > | > | > | one
| > | > | > | > | > doesn't and I cannot find why. The bad one shows all the
| > | detail
| > | > | > | records.
| > | > | > | > |
| > | > | > | > | if you have a subform that shows all the records in the
| child
| > | > table,
| > | > | > | > rather
| > | > | > | > | than only the records related to the parent record
displayed
| > in
| > | > the
| > | > | > | > | mainform, then it sounds like you don't have the
| > | mainform/subform
| > | > | > | properly
| > | > | > | > | linked. open the mainform in Design view. click ONCE on
the
| > | > subform,
| > | > | > | > within
| > | > | > | > | the mainform, to select it. in the Properties box, look at
| the
| > | > | > | > | LinkChildFields and LinkMasterFields properties. the first
| > | > property
| > | > | > | should
| > | > | > | > | be set to the name of the foreign key field in the child
| table
| > | > (and
| > | > | > make
| > | > | > | > | sure that field is included in the subform's
RecordSource),
| > and
| > | > the
| > | > | > | second
| > | > | > | > | property should be set to the name of the primary key
field
| in
| > | the
| > | > | > | parent
| > | > | > | > | table (again, make sure the primary key field is included
in
| > the
| > | > | > | > mainform's
| > | > | > | > | RecordSource.
| > | > | > | > |
| > | > | > | > | hth
| > | > | > | > |
| > | > | > | > | > All
| > | > | > | > | > the properties seem to be set in a similar manner so I
am
| > now
| > | > | > looking
| > | > | > | > more
| > | > | > | > | > widely. Any tips on what else might be worth looking at
| > would
| > | be
| > | > | > | > | appreciated
| > | > | > | > | > also.
| > | > | > | > | >
| > | > | > | > | > TIA
| > | > | > | > | > --
| > | > | > | > | > Len
| > | > | > | > | > ______________________________________________________
| > | > | > | > | > remove nothing for valid email address.
| > | > | > | > | >
| > | > | > | > | >
| > | > | > | > |
| > | > | > | > |
| > | > | > | >
| > | > | > | >
| > | > | > |
| > | > | > |
| > | > | >
| > | > | >
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
