 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Select the same record on 2nd subform
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 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 [quoted text clipped - 14 lines] > Thanks > Heather > Heather > [quoted text clipped - 39 lines] > > - Show quoted text - 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 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 > Heather > [quoted text clipped - 16 lines] > Jeff Boyce > Microsoft Office/Access MVP 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 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 >> Heather >> [quoted text clipped - 86 lines] > HTH > Heather > Heather > [quoted text clipped - 133 lines] > > - Show quoted text - 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 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 >> Heather >> [quoted text clipped - 155 lines] > newbie is greatly appreciated. > Heather |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.