What you wanted to do was not especially clear to me, so here's my guess
at a solution.
I added a Table, [SiteName], to the two you defined, to contain the site
names, and used the [reserve] Table to allow linking more than one
[reservations] record to more than one [SiteName] record.
My guess at what might be in your Tables looks a bit like this:
[reservations] Table Datasheet View:
reservationsid descrip Date1 Date2
-------------- ------- ---------- ----------
-694355251 Wet 12/11/2005 12/12/2005
-141645573 Cold 12/11/2005 12/11/2005
91627093 Dry 12/2/2005 12/8/2005
1202009632 Hot 12/1/2005 12/3/2005
[SiteName] Table Datasheet View:
SiteNameID Name
----------- -------------
-1695578594 Moscow
-535197881 Point Barrow
46567804 Atlantis
155682225 Death Valley
The [reserve] Table contains only keys linking the other two Tables.
[reserveid] is its primary key, and [reservationsid] and [SiteNameID]
are the foreign-key links.
[reserve] Table Datasheet View:
reserveid reservationsid SiteNameID
----------- -------------- -----------
-1052527886 91627093 155682225
-550872518 -141645573 -535197881
872544307 1202009632 155682225
892093450 -694355251 46567804
1311498379 91627093 -1695578594
1488451517 -141645573 -1695578594
To make this easier to read, I set Lookup properties for the two foreign
keys. To do this, in Table Design View, select [reservationsid], which
is one of the foreign keys, click on the Lookup tab, and change
properties as follows:
Display Control = List Box
Row Source = reservations
Column Count = 2
Column Widths = 0;1
Now the contents of the Table look like this, though you need to
remember that the data actually stored in the Table are those numeric
key values.
[reserve] Table Datasheet View (with Lookups):
reserveid reservationsid SiteNameID
----------- -------------- -----------
-1052527886 Dry Death Valley
-550872518 Cold Point Barrow
872544307 Hot Death Valley
892093450 Wet Atlantis
1311498379 Dry Moscow
1488451517 Cold Moscow
My version of the SQL you posted (which I had to change to match the new
Table design) looks like this.
[Q_Sites] SQL:
SELECT SiteName.Name AS site,
reserve.reservationsid,
reservations.reservationsid
FROM reservations, SiteName
INNER JOIN reserve
ON SiteName.SiteNameID = reserve.SiteNameID
WHERE (((reserve.reservationsid)
=([reservations].[reservationsid])));
The middle column displays the lookup values, otherwise the 2nd & 3rd
columns contain the same information.
[Q_Sites] Query Datasheet View:
site reserve reservations
.reservationsid .reservationsid
------------ --------------- ---------------
Point Barrow Cold -141645573
Death Valley Hot 1202009632
Moscow Cold -141645573
Death Valley Dry 91627093
Moscow Dry 91627093
Atlantis Wet -694355251
For your main Form, I defined a Query to display site names, basically
just a sorted copy of the names in the [SiteName] Table.
I think you said you wanted to click on a site name and see a list of
related records in the Subform (but maybe I misunderstood you... but
this is the assumption I used).
[Q_SiteNames] SQL:
SELECT SiteName.SiteNameID, SiteName.Name
FROM SiteName
ORDER BY SiteName.Name;
[Q_SiteNames] Query Datasheet View:
SiteNameID Name
----------- -------------
46567804 Atlantis
155682225 Death Valley
-1695578594 Moscow
-535197881 Point Barrow
For the Subform, I defined the following Query to display the descriptions.
[Q_Reservations] SQL:
SELECT SiteName.SiteNameID, SiteName.Name AS site,
reservations.descrip, reservations.Date1,
reservations.Date2
FROM SiteName INNER JOIN (reservations
INNER JOIN reserve
ON reservations.reservationsid = reserve.reservationsid)
ON SiteName.SiteNameID = reserve.SiteNameID
ORDER BY SiteName.Name;
and the full list looks like this:
[Q_Reservations] Query Datasheet View:
SiteNameID site descrip Date1 Date2
----------- ------------ ------- ---------- ----------
46567804 Atlantis Wet 12/11/2005 12/12/2005
155682225 Death Valley Dry 12/2/2005 12/8/2005
155682225 Death Valley Hot 12/1/2005 12/3/2005
-1695578594 Moscow Dry 12/2/2005 12/8/2005
-1695578594 Moscow Cold 12/11/2005 12/11/2005
-535197881 Point Barrow Cold 12/11/2005 12/11/2005
Now we set up a Form and Subform.
In [F_Sites] Form, properties are ...
Record Source = [Q_SiteNames]
I put a List Box and a Subform into this Form.
In List box [F_Sites]![lbxMain], properties are...
Row Source = [Q_SiteNames]
Bound Column = 1
Column Count = 2
Column Widths = 0;1
On Click = [the event procedure shown here:]
Private Sub lbxMain_Click()
Me.Recordset.FindFirst _
"SiteNameID=" & Me.lbxMain.Value
End Sub 'lbxMain_Click()
In Form [F_Sites_Subform], properties are ...
Record Source = [Q_Reservations]
===
Having defined all this, we see in the Form a list of site names and, in
the Subform, a list of descriptions which changes as we click on various
site names.
[F_Sites]![lbxMain] List Box on [F_Sites] main Form displays
Atlantis
Death Valley
Moscow
Point Barrow
This list doesn't change, but clicking on "Death Valley" in
[F_Sites]![lbxMain] causes the Subform to display ...
[F_Sites_Subform]:
descrip Date1 Date2
------- --------- ---------
Dry 12/2/2005 12/8/2005
Hot 12/1/2005 12/3/2005
.... and clicking on other names, such as "Atlantis" or "Moscow", causes
different sets of records to appear in the Subform.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
I need some help figuring something out. I'm not sure if I heading in
the correct direction. What I'm trying to accomplish is the following.
I have a table that has id, discrip, and 2 date fields. I am populating
this information
from a sub from on a main form. The Id and date fields are tied
together in the forms. I am
trying to get the descrip field to show up in my main form under a
listbox. I have
created a sql query to try to accomplish this. I can have multiple
lines of information for each id. That's why I'm trying to show in
list box. The problem I am having is I cannot get it to look for
specific id of the current page I'm on in the form. It prompts me for
id. If I enter the id the list box displays correct info. I would like
for it to see what page I'm on in my form take the id from there and
fill the list box accordingly. When I change sheets it would
automatically update. I have pasted sql query below. I would really
appreciate any insight on how to make this work. Thanks ahead of time.
Perhaps Im not making myself clear. I want a list box to show
information from a table in a form. The table is not the table that
main form is driving. I want the list box to see the reservationid from
the form on which ever record is active, and to show only the
information where the 2 reservation id's are =. The list box will work
if I type in reservationid, but it won't get it from the active form
page like I want. Hopefully this bettrer explains problem.
SELECT [reserve].[site], [reserve].[reservationid],
[reservations].[reservationsid]
FROM reserve
WHERE (([reserve].[reservationid])=([reservations].[reservationsid]));
