Search Across unrelated forms.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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
 
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
 
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 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!
 
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,
 
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.
 
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.
 
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.
 
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 Ass/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 Ass 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 Ass 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,
 
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.
 
Back
Top