 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006Search Across unrelated forms.
Hi All, I am not the best a VBA so a little help would be welcome. I would like to search for Last Name's across 5 different forms from a main menu (Front end) form. The forms have no relationships assigned. I would like the search result to list the forms that contain the correct result and allow me to navigate straight to that form and record(s). Is this at all possible, and if so, how? Thanks in advance! The first problem we have here is Forms have no data. Tables and queries based on those tables contain the data. So, you would not be searching Forms for data, you would be searching the tables for data and opening the appropraite form. How you will do this depends on what, exactly, you want to do, and the structure of your database. To help, we will need to know the following: Are you searching a specific table or a list of tables or all tables? Are you searching a specific field, some fields, or all fields? Does the match need to be the entire field or some part of a field? Do you want to find the first match or all matches? What are you going to do once you find a match? What are you going to do if no match is found? > Hi All, > [quoted text clipped - 8 lines] > > Thanks in advance! Hi Klatuu, Thanks for replying, the answers ti your questions are as follows: I want to search a selection of table, 6 in total. I want to find records using a specific field to search for, in this case, it is a Site Ref that would look like..."GLN 0123" for example. I would like the search to find records that match the entire field, butif nor match is found for say, GLN 0123, all GLN sites would then be shown (i.e. 0001 - 0300). If no match at all is found or i enter in the search criteria wrongly then i would like an error message. I want to be able to edit/delete/append records that i search for. You are exactly right when you say i wanna be able to search the tables and bring up the appropriate forms, that ideally what i would like. Hope this is clearer! Thanks > The first problem we have here is Forms have no data. Tables and queries > based on those tables contain the data. So, you would not be searching Forms [quoted text clipped - 21 lines] > > > > Thanks in advance! It is very unusual that you would want to do the same search across multiple tables, in a properly designed relational database. I think you may have designed your tables to match the forms! This is generally not correct. The tables should be designed to reflect the logical structure of the information, regardless of how that information is eventually displayed on the forms & reports. Tell us the following details for each table: - the table name; - the names (not types & lengths) of the fields; - which field(s) are the primary key for that table. Then we could comment on the table structure (if you'd like us to do that). HTH, TC Hi TC/Klatuu, You are both right! - the reason i want to do a search like this is because I havent set up my table structure correctly and have found problems. Not being an advanced database person, I found it too complicated to normalize and creating relationships caused an infinate number of errors. I have a long list of field names, too many to list - but i could email you them to another address if thats possible. The table names are the same as the form names - i created them directly from the tables due to my aformentioned problems. I would like to remedy this though so long as i dont have to completely re-create the entire database. Table names: Edge_Tracker South_CDMable South_Non-CDM South_Rollout Microcell_Upgrade Microcell_RO I know that by having my database thee way it is now, i will get lots of redundant and duplicate adat, but i couldnt find a way to normalize and create the right relationships - too complicated! If i can send either of u a copy of the list, please supply me an email. Thanks! > It is very unusual that you would want to do the same search across > multiple tables, in a properly designed relational database. I think [quoted text clipped - 14 lines] > HTH, > TC Normalization is definitely a two-edged sword. It's certainly quite hard to understand, at the start. But if you do not do it properly, it will /undoubtledly/ come back to bite you in the backside! I understand & sympathize that you don't want to rock the apple cart. If it aint broke, why fix it? But believe me - once you've spend hours & hours & hours deleting duplicate data, and trying to get reports to work properly, and editing different copies of the data so they all match up, and so on, you'll soon begin to rue the day, that you did not do it properly to start with! I'm sure that we could help you normalize the structure properly. Believe me, it actually works quite simply, once you've got the structure correct. But please don't email anyone. Few if any of the regular posters will accept files via email. Cheers, TC [MVP Access] TC, How could i show you all the information you would need to help me normalize it? Regards, > Normalization is definitely a two-edged sword. It's certainly quite > hard to understand, at the start. But if you do not do it properly, it [quoted text clipped - 16 lines] > Cheers, > TC [MVP Access] See my post on Jan 4 in this thread. Cheers, TC [MVP Access] RemySS, I don't like to post my E-mail address. I have received a ton of junk mail from having done so previously. It may not be necessary to post all the fields. A description of what each table is used for and what differentiates them would do for now. What I suspect, based on your table names, is that they are mostly the same with some minor differences. For example, South_CDMable and South_Non-CDM look like they are probably identical and really only need an identifying field to define whether it is CDMable or Non-CMD. > Hi TC/Klatuu, > [quoted text clipped - 43 lines] > > HTH, > > TC TC/Klatuu, Okay, what i will do is spend some time writing a description of how it should be and post that with some more info. will i be able to keep the forms or do i have to redo them all? i also have reports and queries that i would like to keep, all with VBA code too. Thanks for the help so far guys. > RemySS, > [quoted text clipped - 54 lines] > > > HTH, > > > TC Post back to this thread so I will know it is here. I doubt you will have to get rid of all your objects. Probably just need to modify them. > TC/Klatuu, > [quoted text clipped - 63 lines] > > > > HTH, > > > > TC Hi Klatuu/TC, I have written out a description for you, hope this is clear enough! My role is to liaise with contractors who carry out work on communication sites. I need the database to make my role of recording site activities, invoicing, and searching for site progress easier and much less time consuming. An improvement in Data input errors are also and obvious requirement. Each site (a macro or microcell site) is given a 3 letter prefix and then 4 numbers to identify it, e.g. HER 0123 = Herefordshire 0123. The county is also and indication of the region (North, south, or GLN (greater London)). There are 2 types of site – Rollouts (sites which are in the planning stages of being built or are currently undergoing construction) Upgrades (sites that are built and have different works carried out on them, i.e. installing new/replacing old technology. There are also two types of upgrade stage - 1. CDM – there is one principal contractor working on site and all other contractors carrying out work with the principal contractor are under its control. 2. Non-CDM – means that all contractors carrying out work on the site are independent of each other. An upgrade type is exactly that – the type of upgrade that is being carried out on-site. There is one upgrade number per upgrade type. The upgrade number is the single most unique field – there are no duplicates. A site can have many upgrade numbers, but an upgrade number can only be for one site. Upgrade types can occur on different sites and with different upgrade numbers. A Site can have up to 3 upgrade numbers at a time. Phase is either 2G Or 3G – this can be different for two records of the same site. Most of the other field (listed below) are the stages and different actions that have to be completed. This varies from programme to programme. A site in tables Non-CDM, CDMable, Micro Cell Upgrade, and Rollout can have what is called a B-End site too. This is a site which is a link between different sites. A site can have many B-End sites, and each B-End site has a upgrade number which is unique to that site (no duplicates anywhere at all, like the upgrade number). List of programmes: Edge Upgrades (no rollouts) Campbell’s Microwave (RO+UG) Eve’s Programme (RO+UG) Nokia NDB Deployment (RO+UG) LSE Base UG (UG) Base midland UG (UG) Alcatel (RO+UG) South NDB C&I (RO+UG) Nokia NDB North (RO+UG) Forms I Currently have (I refer to them as trackers): Edge Non-CDM Upgrades CDMable Upgrades Rollout Microcell Rollout Microcell upgrades Every Tracker has (with exceptions): SiteRef Region Site Name Upgrade Number (Not Rollout Trackers) Upgrade Type (Not Rollout Trackers) Phase Scenario Edge Tracker also contains: 1st I&C Contractor RR Issued to 1st I&C Schedule E Received Date Schedule E Received Payment Milestone Comments Non-CDM Tracker also contains: De-Install (Yes/No) Programme (Text) 1st I&C Contractor (Text) RR Issued to 1st I&C (Text) Schedule E Received (Text) Payment Milestone (Date/Time) U3 (Text) U410 (Text) U6 (Text) 2nd IandC Contractor (Text) RR Issued to 2 IandC (Text) Schedule E Received from 2 IandC (Text) B End SiteRef (Text) B End Third Party Site (Text) Link Ref Number (Number) 3 IandC Contractor (Text) RR Issued to 3 IandC (Text) Schedule E Received from 3 IandC (Text) 4 IandC Contractor (Text) RR Issued to 4 IandC (Text) Schedule E Received from 4 IandC (Text) Comments (Text) CDMable Tracker also contains: Upgrade Number2 Upgrade Number3 Upgrade Type2 Upgrade Type3 Third Party Site Principal Contractor Designer DC/AD B End SiteRef B End Third Party Site Link Ref Number Request 1 for DRA/Drawings Request 2 for DRA/Drawings U3: GA Drawing Received/Reviewed U3: Designers Hazard a.s/RR Received U3: Initial F10 Issued to HSE U410: Initial F10 & PTHSP Issued to D&C,1 Request for CDHSP Plan D&C Completed F10 issued to HSE Second Request for CPHSP PC's CPHSP Received and Approved U6: Stage 1 Payment Milestone Request 1 for H&S File Request 2 for H&S File D&C Sched E Provided H&S File Compiled and Issued U8: Stage 2 Payement Milestone 1st I&C Contractor U8: RR Issued to I&C U10: Shed E Provided from I&C I&C Contractor Name U8: RR Issued to I&C-2 U10: Shed E Provided from I&C-2 IMS Sched E Received and H&S File Updated U10: SMC Payment Milestone Comments Rollout Tracker also contains: Principal Contractor Designer DCorAD Request 1 for Design Info Request 2 for Design Info U3 GA Drawings RR MS6 Designers Hazard a.s R MS6 Initial F10 Issued to HSE MS13 Initial F10 PTHSP Issued MS13 Payment Milestone D&C Completed F10 Issued to HSE Request 2 for CPHSP Plan MS12 PCs CPHSP Approved Request 1 for H&S file Request 2 for H&S file D&C Sched E Provided H&S File Compiled and Issued MS16 Stage 2 Payement Milestone I&C Contractor Name MS16 RR Issued to I&C MS23 Shed E Provided from I&C-2 IMS Sched E Received and H&S File Updated MS23 SMC Payment Milestone B End SiteRef B End Third Party Site Link Ref Number Comments Micro Cell Rollout also contains: Contractor Designer Programme DCorAD Request 1 for Design Info Request 2 for Design Info Sched E DRA Received Request 1 Method Statement Request 2 Method Statement Site Spec Method Statement Received Updated Sched E Forwarded to 2 Contractor Returned Sched E Received Handover Pack Approved and IMS Updated Payment Milestone Comments Request 1 for Design InfoCopy Request 2 for Design InfoCopy U3 GA Drawings RR MS6 Designers Hazard a.s R MS6 Initial F10 Issued to HSE MS13 Initial F10 PTHSP Issued MS13 Payment Milestone Micro Cell Upgrade also contains: Third Party Site 1st I&C Contractor RR Issued to 1st I&C Programme Schedule E Received Payment Milestone U3 U410 U6 2nd IandC Contractor RR Issued to 2 IandC Schedule E Received from 2 IandC B End SiteRef B End Third Party Site Link Ref Number 3 IandC Contractor RR Issued to 3 IandC Schedule E Received from 3 IandC 4 IandC Contractor RR Issued to 4 IandC Schedule E Received from 4 IandC Comments The tables are the same, so I’m guessing duplicate data will occur. I know its probably simple to normalize the tables and set the relationships so I don’t get duplicate/redundant records, but I seem to be doing it wrong – I would really appreciate a heads up on how I would go about this. If at all possible, I would like to keep the form designs the same, and then just add controls again if I had to. Thanks in advance, > Post back to this thread so I will know it is here. I doubt you will have to > get rid of all your objects. Probably just need to modify them. [quoted text clipped - 66 lines] > > > > > HTH, > > > > > TC All sites should be in the same table. You can use a Yes/No field to determine whether is is CDM or NonCDM. You could use the same form for both and apply a fiter when you only want to see one or the other. The same would be true for Macro or Micro and the Upgrade Type, Roll Out or Upgrade. Since a site can have multiple B-End sites and a B-End site links multiple sites, you will need two tables for that because that is a many to many situation and needs a joining table. First would be a table that defines a B-End site, but does not reference any specific site. The a table that joins the B-End sites with the sites. Site is one to many with B-End/Site table. As to your programmes, One of the objectives to normalize a database is to eliminate or minimize redundant data. A typical way to do this when you have a recurring list of things like programmes; rather than include the text description of the programme in each upgrade record, create a table that has a primary key and the description of the programme. Then in the upgrade table records, carry the primary key value of the related programme rather than the text description. This table then becomes a child table to the upgrade table. This accomplishes a couple of things. First, it reduced data base size and if the description of the programme changes at any time, then it is not necessary to find and replace it in every record in the upgrade table. You only need to change it in the programme table. B-End site is one to many with B-End/Site table The B-End/Site table would need: BEND_SITE_ID Autonumber - Primary Key BEND_ID Long - Foreign Key - Contains the primary key of the record in the B-End table it relates to SITE_ID Long - Foreign Key - Contains the primary key of the record in the Site table it relates to For the Upgrades, you should have an Upgrade table. If I understand correctly, an Upgrade is unique to a site. This table should be a child table to the Site table and have a field that contains the primary key value of the site record it is associated with. I hope my response is a clear an complete as your description. If you have further questions, please post back. > Hi Klatuu/TC, > [quoted text clipped - 284 lines] > > > > > > not correct. The tables should be designed to reflect the logical > > > > > > structure of the information, regardless of how that information is Thanks for the info. TC's comments regarding your database structure are correct. It is not usual to have the same field with the same data in 6 tables; however, I know from painful experience, we don't always have control over the data structure. So, here is how I think I would approach it. The code below will first look at each table for an exact value and if it finds any, it will open the form used for editing that table. If it finds no exact matches, it looks for any occurances of the field beginning with GLN. If any are found, then it opens the form. It will cycle through all the tables and if no matches are found, a message bos will be displayed to alert the user: Dim dbf as Database Dim rst as Recordset Dim lngCount as Long Dim strSQL1 as String Dim strSQL2 as String Dim strFormName As String Dim blnNoMatch Set dbf = CurrentDb lngCount = 1 blnNoMatch = True Do While lngCount < 7 Select Case lngCount Case 1 strSQL1 = _ "SELECT SiteRef FROM Table1 WHERE Table1.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table1.SiteRef LIKE 'GLN*';" strFormName = "Form1" Case 2 strSQL1 = _ "SELECT SiteRef FROM Table1 WHERE Table2.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table2.SiteRef LIKE 'GLN*';" strFormName = "Form2" Case 3 strSQL1 = _ "SELECT SiteRef FROM Table1 WHERE Table3.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table3.SiteRef LIKE 'GLN*';" strFormName = "Form3" Case 4 strSQ1L = _ "SELECT SiteRef FROM Table1 WHERE Table4.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table4.SiteRef LIKE 'GLN*';" strFormName = "Form4" Case 5 strSQL1 = "SELECT SiteRef FROM Table1 WHERE Table5.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table5.SiteRef LIKE 'GLN*';" strFormName = "Form5" Case 6 strSQL1 = "SELECT SiteRef FROM Table1 WHERE Table6.SiteRef = 'GLN 0123';" strSQL2 = _ "SELECT SiteRef FROM Table1 WHERE Table6.SiteRef LIKE 'GLN*';" strFormName = "Form6" End Select Set rst = dbf.OpenRecordset(strSQL1) If rst.RecordCount > 0 Then rst.Close blnNoMatch = False DoCmd.OpenForm strFormName Else rst.Close Set rst = dbf.OpenRecordset(strSQL2) If rst.Recordcount > 0 Then rst.Close blnNoMatch = False DoCmd.OpenForm strFormName End If End If lngCount = lngCount + 1 Loop Set rst = Nothing Set dbf = Nothing If blnNoMatch Then MsgBox "No Matches Found" End If > Hi Klatuu, > [quoted text clipped - 42 lines] > > > > > > Thanks in advance! |
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
|
©2008 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.