Relationships, back end

comments inline.

"Len B" wrote in message
...
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.
"tina" wrote in message
...
| comments inline.
|
| "Len B" wrote in message
| ...
| 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.
| "tina" wrote in message
| ...
| | 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
| |
| |
| | "Len B" wrote in message
| | ...
| | 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.
| | "tina" wrote in message
| | ...
| | | comments inline.
| | |
| | | "Len B" wrote in message
| | | ...
| | | 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.
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|

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.

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.
"tina" wrote in message
...
| comments inline.
|
| "Len B" wrote in message
| ...
| 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.
| "tina" wrote in message
| ...
| | comments inline.
| |
| | "Len B" wrote in message
| | ...
| | 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.
| | "tina" wrote in message
| | ...
| | | 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
| | |
| | |
| | | "Len B" wrote in message
| | | ...
| | | 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.
| | | "tina" wrote in message
| | | ...
| | | | comments inline.
| | | |
| | | | "Len B" wrote in message
| | | | ...
| | | | 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.
| | | |
| | | |
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|

comments inline.

"Len B" wrote in message
...
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.
"tina" wrote in message
...
| comments inline.
|
| "Len B" wrote in message
| ...
| 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.
| "tina" wrote in message
| ...
| | comments inline.
| |
| | "Len B" wrote in message
| | ...
| | 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.
| | "tina" wrote in message
| | ...
| | | 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
| | |
| | |
| | | "Len B" wrote in message
| | | ...
| | | 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.
| | | "tina" wrote in message
| | | ...
| | | | comments inline.
| | | |
| | | | "Len B" wrote in message
| | | | ...
| | | | 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.
| | | |
| | | |
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|

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.
"tina" wrote in message
...
| comments inline.
|
| "Len B" wrote in message
| ...
| 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.
| "tina" wrote in message
| ...
| | comments inline.
| |
| | "Len B" wrote in message
| | ...
| | 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.
| | "tina" wrote in message
| | ...
| | | comments inline.
| | |
| | | "Len B" wrote in message
| | | ...
| | | 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.
| | | "tina" wrote in message
| | |
...
| | | | 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
| | | |
| | | |
| | | | "Len B" wrote in message
| | | | ...
| | | | 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.
| | | | "tina" wrote in message
| | | |
...
| | | | | comments inline.
| | | | |
| | | | | "Len B" wrote in message
| | | | | ...
| | | | | 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.
| | | | |
| | | | |
| | | | |
| | | | |
| | | |
| | | |
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|

you're welcome, Len.

btw, keep in mind that whatever update you're doing to tblEquipment (from a
separate recordset running within the loop on the first recordset,
perhaps?), you might be able to do "all at once" in an Update query on that
table. it's unusual to *have to* use recordsets and loops to dump multiple
records of raw data into a table, and update multiple records in
consequence. unless you're doing some pretty complex conditional
appends/updates and/or tricky data clean-up, Append and Update queries are
usually preferable over looping through recordsets.

hth


"Len B" wrote in message
...
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.
"tina" wrote in message
...
| comments inline.
|
| "Len B" wrote in message
| ...
| 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.
| "tina" wrote in message
| ...
| | comments inline.
| |
| | "Len B" wrote in message
| | ...
| | 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.
| | "tina" wrote in message
| | ...
| | | comments inline.
| | |
| | | "Len B" wrote in message
| | | ...
| | | 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.
| | | "tina" wrote in message
| | |
...
| | | | 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
| | | |
| | | |
| | | | "Len B" wrote in message
| | | | ...
| | | | 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.
| | | | "tina" wrote in message
| | | |
...
| | | | | comments inline.
| | | | |
| | | | | "Len B" wrote in message
| | | | | ...
| | | | | 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.
| | | | |
| | | | |
| | | | |
| | | | |
| | | |
| | | |
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|