Select the same record on 2nd subform

H

hgoslin

Hi
I am a newbie at MS Access programming. I have 5 subforms on
different tabs all based on a single table. When a record is edited,
selected (or added) on subform1 - frmSubTaskOrder, I would like the
same record to be selected when I move to subforms2-5 eg
frmSubContractor. On frmSubTaskOrder, the control TaskOrderNumber
would be the unique field (although not a primary key), to be able to
find the matching record on frmSubContractor. I have created a text
box called txtTONum that simply references the control eg
=[TaskOrderNumber] so that the user can see that they are accessing
the correct record.

These 5 subforms are on a main form frmAdministrators and I use a
combobox with the link child, link parent to synchronise all the
subforms. I have attempted much of the code found in this group but
as yet have been unsuccessful. Any help would be appreciated.
Thanks
Heather
 
J

Jeff Boyce

Heather

First, if all the forms are based on a single table, why do you have
subforms? Generally, the main-form/subform construction is used when you
have one-to-many table relationships.

Next, if you have some fields from a table on one tab, and other fields on
the second tab, and so on, unless something else is going on, all the
fields/controls point to a single record. Again, there would be no reason
to be using separate forms/subforms.

If you'll provide a bit more description of your underlying data, the folks
in the newsgroup may be able to offer other suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
H

hgoslin

Heather

First, if all the forms are based on a single table, why do you have
subforms? Generally, the main-form/subform construction is used when you
have one-to-many table relationships.

Next, if you have some fields from a table on one tab, and other fields on
the second tab, and so on, unless something else is going on, all the
fields/controls point to a single record. Again, there would be no reason
to be using separate forms/subforms.

If you'll provide a bit more description of your underlying data, the folks
in the newsgroup may be able to offer other suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP




Hi
I am a newbie at MS Access programming. I have 5 subforms on
different tabs all based on a single table. When a record is edited,
selected (or added) on subform1 - frmSubTaskOrder, I would like the
same record to be selected when I move to subforms2-5 eg
frmSubContractor. On frmSubTaskOrder, the control TaskOrderNumber
would be the unique field (although not a primary key), to be able to
find the matching record on frmSubContractor. I have created a text
box called txtTONum that simply references the control eg
=[TaskOrderNumber] so that the user can see that they are accessing
the correct record.
Click to expand...
These 5 subforms are on a main form frmAdministrators and I use a
combobox with the link child, link parent to synchronise all the
subforms. I have attempted much of the code found in this group but
as yet have been unsuccessful. Any help would be appreciated.
Thanks
Heather- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi Jeff
The subforrms are to separate the data into sections according to the
paper based format being used for current data capture. There are
also +30 fields in the table, so space was also an issue. I ran into
other problems when I split the table, and used a query to display all
the necessary fields from multiple tables, thus I went back to a
single table. If the user selects a TaskOrderNumber on the
frmTaskOrder eg 4929, when they select the tab which contains the
frmSubContractor, we need the record selector to be on the correct
record, not the first record as is currently happening. I am also not
getting the subsequent subforms to update when a new record is added
on the first subform. Each subform is in datasheet view, and there is
a one-to-many relationship between the data displayed in the combobox
on the main form frmAdministrators and the subforms.
Thanks for your interest - I have spent hours scouring the net for
help already.
Heather
 
J

Jeff Boyce

Heather

I suspect what you are seeing happening is because you are "trying to drive
nails with a chainsaw". You might be able to do what you are trying to do,
the way you are trying to do it (theoretically), but both you and Access
will have to work overtime to make it happen.

Access is a relational database... if your underlying data has not been
well-normalized, you won't get the best use of the relationally-oriented
features and functions Access offers.

In Access, everything starts with the data. Again, please post a
description of your data (an example of what you have in a couple records
would help) -- we aren't there, so you'll need to give us some context to
work around.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
H

hgoslin

Heather

I suspect what you are seeing happening is because you are "trying to drive
nails with a chainsaw". You might be able to do what you are trying to do,
the way you are trying to do it (theoretically), but both you and Access
will have to work overtime to make it happen.

Access is a relational database... if your underlying data has not been
well-normalized, you won't get the best use of the relationally-oriented
features and functions Access offers.

In Access, everything starts with the data. Again, please post a
description of your data (an example of what you have in a couple records
would help) -- we aren't there, so you'll need to give us some context to
work around.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Hi Jeff

Thanks for your patience.
The database stores information about building maintenance for +25
buildings. Each time a plumber, electrician, painter etc is called to
do maintenance, a Task Order is raised. The task order Maintenance
table (the many side) of the relationship then contains TO Date, TO
Number which Building (the one side of the relationship), who appoved
the work, a description, service provider, category, cost, invoice
details and payment details as seen below:

MaintenanceID 1 3 17 26
Building Name Elangeni Elangeni Elangeni Douglas Rooms
Task Order Date 01/07/2007 01/07/2007 01/07/2007
Task Order Number J4329/0 J4330/0 J4331/0 J4138/0
UnitNumber 333 203 132 Common Area
Unit Category Residential Residential Residential
Cost Responsibility Tenant Recovery Tenant Recovery Tenant Recovery
Contractor Nyelisani Nyelisani
Nyelisani SA Maintenance
Other Contractor
Approved Contractor TRUE TRUE TRUE TRUE
Description of Work painted in full only kitchen painted painted in
full
Maintenance Category Painting Painting Painting Plumbing
TaskProgress Complete Complete Complete Complete
Date Task Complete 01-Jul-07 01-Jul-07 01-Jul-07 02-Jul-07
Guarentee 0 0 0 0
InvoiceDate 01/07/2007 01/07/2007 01/07/2007
Invoice Number 133 134 132
Amount (excl VAT) R 614.04 R 219.30 R 614.04 R 0.00
VAT R 85.96 R 30.70 R 85.96 R 0.00
Cost Verified Blue Book Blue Book Blue Book
PO Payment Authorisation
PM Payment Authorisation
HSV Vincent Msomi Vincent Msomi Vincent Msomi
Date Received by Creditor
Date of Payment
Expense Code
Payment Reference
Recovery Reference

The subforms are divided as follows:
frmSubTaskOrder
TO Date TO Number Unit Number Common Area Unit Category Cost
Responsibility HSV

frmSubContractor
Contractor Other Contractor Approved

frmSubMaintenance
Description Maint. Category Task Progres Date Complete
Guarentee

frmSubInvoice
Inv Date Inv Number Amount (excl VAT) VAT Cost Verified

frmSubFinance
Date rcvd Creditor Date Paid Exp Code Pay Ref Recovery
Ref


My Main form has a combo box, to select the building, and I use the
link child to synchronise all the subforms to display only the
relevant maintenance for the current building.

HTH
Heather
 
J

Jeff Boyce

Heather

Please take another look at my earlier responses ... you really don't want
to be using subforms to break up data from your main table. You can simply
put some controls on one tab, and other controls on another tab, and so on.

But from what I've seen, your database has a much larger issue. The table I
believe you are describing sounds more like a spreadsheet than a relational
database. It appears to have repeating fields (multiple "painting" fields,
multiple date fields, ...), which may be the way to handle the data in
Excel, but not in Access.

I'll suggestion that you step away from your keyboard and grab paper and
pencil for this next exercise.

A relational database needs "things about which you will keep data"
("entities"), and a description of how those things related to each other
("relationships").

First, try to isolate the various "things" you have. For instance, it
appears (to this outsider) that you have Buildings, Task Orders, (possible
Task Order Details), Contractors (plumbers, electricians, ...), Invoices,
Payments, and undoubtedly more!

Draw a box for each "entity". Now, what facts about each entity do you want
to store? Put those with their respective entities.

Now, how, if at all, are all these related? For instance, I'll hazard a
guess that Invoice and Payment are related (or need to be).

If this seems like too much work, and if you are able to use a
spreadsheet-like data structure to satisfy your business needs, may I
suggest that you reconsider using a spreadsheet. Access has a bit of a
steep learning curve, but you won't get the benefits if you don't do the
up-front work on the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
H

hgoslin

Heather

Please take another look at my earlier responses ... you really don't want
to be using subforms to break up data from your main table. You can simply
put some controls on one tab, and other controls on another tab, and so on.

But from what I've seen, your database has a much larger issue. The table I
believe you are describing sounds more like a spreadsheet than a relational
database. It appears to have repeating fields (multiple "painting" fields,
multiple date fields, ...), which may be the way to handle the data in
Excel, but not in Access.

I'll suggestion that you step away from your keyboard and grab paper and
pencil for this next exercise.

A relational database needs "things about which you will keep data"
("entities"), and a description of how those things related to each other
("relationships").

First, try to isolate the various "things" you have. For instance, it
appears (to this outsider) that you have Buildings, Task Orders, (possible
Task Order Details), Contractors (plumbers, electricians, ...), Invoices,
Payments, and undoubtedly more!

Draw a box for each "entity". Now, what facts about each entity do you want
to store? Put those with their respective entities.

Now, how, if at all, are all these related? For instance, I'll hazard a
guess that Invoice and Payment are related (or need to be).

If this seems like too much work, and if you are able to use a
spreadsheet-like data structure to satisfy your business needs, may I
suggest that you reconsider using a spreadsheet. Access has a bit of a
steep learning curve, but you won't get the benefits if you don't do the
up-front work on the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
















- Show quoted text -
Click to expand...

Thanks once again Jeff
I pasted the first few rows of the database using transpose - the
fields are arranged vertically with each column being a new record.
However i will redesign my form, not using separate subforms on each
tab and see if that solves my problem. Again your patience with a
newbie is greatly appreciated.
Heather
 
J

Jeff Boyce

Heather

I don't know enough about your data to tell if a simple "transpose" will
suffice to normalize your data structure. Each of those "things" I
mentioned in my last response would need to have their OWN table, and not be
stuffed inside a larger, single table.

This is especially true if the relationship between your Buildings and
TaskOrders is one-to-many (i.e., one Building could have multiple Task
Orders). The same would be true for any other entities/things.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top