Hi Will
Please read below
Will_Harris_ZA said:
Hi Jacob
I'm going to apologise in advance in case I am misunderstanding you. Also
for my arbitrary use of Capitals, but they might make things clearer.
A.1
Thank you. I have the 9000 records imported from Excel into my Access
Database, with relevant fields and tables, combo boxes etc set up.
A.2
Thank you for the query info. I just don't know how to integrate this to
achieve what I need (please refer below, esp how Excel would achieve this,
thank you)
My problem is this, and I might be missing your point here. And for that I
apologise in advance...
what I am trying to do is enter:
"OwnerCoName" (Sub Co, eg Zinc) in a form in the massive 9000 record
database, and have it AUTOMATICALLY return, after pressing a command
button,
either:
1) a value in adjacent field: "OwnerName" (or Holding Company, eg Apple),
or
2) message saying: "Subsidiary is not in companies' database, please
update"
Saying it another way:
enter:
Sub Co, eg Silver, in a form in the massive 9000 record database, and have
it AUTOMATICALLY return, after pressing a command button, either:
1) a value in adjacent field: Holding Company, eg Apple, or
2) message saying: "Subsidiary is not in companies' database, please
update"
Maybe I need to change my thinking, but: Best case scenario is I don't
press
You certainly need to change your thinking, many newcomers to Access from
Excel look at Access as a "better" Excel - this is not the case! Access is
Database application which hanles data in a diffrent way, dont get mislead
just because you can disply data in Access in a table resembling the "look"
of Excel.
a command button, BUT, LIKE EXCEL'S VLOOKUP FUNCTION, THE OWNER NAME IS
AUTOMATICALLY RETURNED. Is a similar operation possible in Access?
The equevalent to VLOOKUP() , but more powerfull in Access is the DLookup()
function.
If I understood you correctly, here is how use DLookup():
Suppose You have a text box "txtSearchCompany" and a command button next to
it "btnSearchCompany". You create On Click event procedure for the button
(untested)
+++++++
Private Sub btnSearchCompany_Click()
Dim TmpCompany as Variant
' Check That there is a value in txtSearchCompany, Exit if none
If Len(Me.txtSearchCompany)=0 then
MsgBox "No Value to search For!", vbExclamation,"Cannot Search For
Blank"
Exit Sub
End If
'Look For Holding Company By it's subsidiary
tmpCompany=Dlookup("[OwnerName]","tblSubsidiaries","[SubCompany]=' " _
& txtSearchCompany & " ' ")
'Remove spaces from ' " and " ' " (I placed them for clarity only)
'Change the names as per the table (9000) and its fields
'Display Results
If IsNull(tmpCompany) Then
MsgBox "Subsidary Does Not Exists or have no Holding Company"
Else
MsgBox "Holding Company Of: " & Me.txtSearchCompany & " Is " &
tmpCompany
End If
End Sub
++++++++++++++
However, you can still create a combo Box to do the same more efficiently
(see futher down here)
I am fully prepared to set up a table linking the many many Sub Co's (Eg
Silver, Nickel, Iron and Red, Green, Yellow, Blue and Oak, Maple) to many
Holding Companies (Eg Apple and Properties Fifteen and Jaguar)
You seem to misundstand the concept of liking. One-to-Many mean One
Holding Company can have many Subsidiaries, You can have as many Parent
Companies as you wish, *each* Parent can have none, one or more
subsidiaries - *but *a subsidairy cannot have more than one parent Company.
I have used masks too so Owner_ID's are reflective of the Holding Co name,
and utterly unique.
masks control how date is entered, not how its stored, although it can do
that too
Subsidiaries do generally have more than one property in them. So it isn't
a
train smash if in my main 9000 records data base these sub's, and their
holding companies, reoccur.
Create a Prperty Table And Link it one-ToMany (the prperty is the many side)
A.3
Unfortunately I can't link 9000 record database with Sub Names (being
entered as deed searches are performed) to your TblOwnersCompanies field.
(Fyi my TblOwnersCompanies field uses my tblOwners for its OwnerName drop
down combo box)
You can use tblOwnersCompanies as basis for for you combo, your combo can be
based on the same table or query as the form, no restriction there
if you do that you can search the 9000 for holding comapnies more
efficiently - this is what I meant here above
This might be because, at this stage, their is no primary key set up. My
primary key is to be the Title Deed info for each property, again set up
as a
mask, and unique. However at this stage the Title Deed info needs to be
captured onto the database, so many records for this field are blank.
You can use the Title Deed as primary key *provided that* you *always have*
a Title Deed, although I will recomand using AutoNumber. In any event you
will need a primary key for the other tables
To add a Primary key to your exiting subsidiary company:
Rename the Old Table (say, Add "OLD" to its name (no quatation marks)
Create a new table with the original name and with exactly the same fileds
as you existing table. Insrerst a primary Key as the first filed make it
AutoNumber, increament
Craete an *Append Query* and import all 9000 records from the old table to
the new one - Access will allocate the primary key to all the recordes in
the new table (keep the old table for refference and backup)
Will, if you wish me to have a look at your DB, your are welcome to Zip it
and send it to me:
(sarichart at ozzienet dot net). if you do so please put in subject Line
"Will's DB" or somthing that will distinguish you e-mail form a spam
Regards
Jacob
