Okay, firstly the error message indicates that the independent database
needed the DAO reference. Open the code window, choose References from the
Tools menu, and check the box beside:
Microsoft DAO 3.6 Library
More info on references for each version of Access:
http://allenbrowne.com/ser-38.html
The code works with tables from 1 to millions of records. You do need to
adapt the code to your actual table names and field names, and the way you
have them releated.
You have a Corp table, with [Corp ID] as primary key, and other fields. Your
Site table should then have a CorpID field, not a a CorpName field. You must
store the value of the Corp table's primary key in this table. If [Corp ID]
is an AutoNumber in the Corp table, then you want a Number type field in
your Site table.
Once you have the matching field in the Site table, choose Relationships on
the Tools menu. Drag the [Corp ID] field from the Corp table onto the [Corp
ID] field in the Site table. Access pops up a dialog. Check the box for
Referential Integrity. Now enter the right numbers into the field for all
sites.
Once the relationship is set up and the data matches, you should be able to
get the code to work. In the list box, you probably want to show the Site in
the first column, the Corp Name in the 2nd one, and so the CorpID will be in
the 3rd one. As this is now a Number field, change the line:
strWhere = "[Corporation] = """ & Me.List1.Column(1) & """"
to:
strWhere = "[Corp ID] = & Me.List1.Column(2)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Jade5 said:
Hi Allen:
I tried the code on a simple database independent of my database and it
still doesn't work. My database has a Corp table with Corp ID, Corp
name - a
Site table with Site ID, Site Name, Corp Name and a Type table with Type
ID,
Type name, Site ID. My list form is based on a query of the Site table -
Site
ID, Corp ID, Site Name
The Corp form has Corp fields at the top, Site fields in the middle and
Type
sheet at the bottom.
Did you try your code on a small database, if so, did it work for you and
how did you set it up?
Thanks for your help.
BTW, when I tried it on the indpendent database, I got message
User-defined
type not defined - related to DAO.Recordset...I changed it to Object and
it
worked by taking me to the Corporation not the site.
Jade5.
:
The form is not filtering correctly.
Perhaps it is already open at the time you run the code, and so the
WhereCondition of OpenForm is not being applied?
Or perhaps the list box columns are not matching what I expected.
Thanks Allen:
That helped but for some reason the code only works for the first
Corporation in the the Corporation table. For example if the
Corporations
are
MDX and ABC and the sites for MDX are TS and JS and the sites for ABC
are
BT
and JX then when I click on BT or JX in the list box it works because
ABC
is
the first corporation in the corporation table but when I click on TS
or
JS I
get the message Site not found in subform and the ABC corporation and
BT
site
is displayed. I am new to Visual Basic and can't figure out what logic
is
missing.
Also, all the assumptions that you stated are correct.
Thanks,
Jackie.
:
The example below assumes your list box shows the Site in the bound
column,
and the Corporation in the 2nd column, and it is not a multi-select
listbox.
It opens the form named frmCorporation filtered to the corporation in
the
2nd column of the list box. It then finds the record for the site in
the
subform named Sub1. As per your example, we assume that both the
Corporation
and Site fields are Text (not Number) types.
This will not work if frmCorporation is already open, so you might
want
to
check if it IsLoaded and close it.
Dim strWhere As String
Dim rs As DAO.Recordset
If Not IsNull(Me.List1) Then
strWhere = "[Corporation] = """ & Me.List1.Column(1) & """"
DoCmd.OpenForm "frmCorporation", WhereCondition:=strWhere
If Forms("frmCorporation").NewRecord Then
MsgBox "Corporation not found."
Else
strWhere = "[Site] = """ & Me.List1 & """"
With Forms("frmCorporation")![Sub1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Site not found in subform"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
End If
Hello:
I have a form that contains a list box (list of companies). When you
click
on a company a form that has 2 subforms opens. The main form is the
corporations. The first subform has the sites pertaining to the
corporation
and the second has the types pertaining to the sites. There is a
1-to-many
relationship between corps and sites and a 1-to-many between sites
and
types.
When I click on a company how can I jump to the specific company in
the
corporation form.
For example if the Corporation is MDX with sites TS and JS when I
click
on
JS in the list box how can I open the Corp form and jump to JS in
the
subform? Thanks, Jade5.
