Problem adding new table to form

[Ron Weaver]

I have an 'Orders' form which has a subform, combos, text boxes, etc. It has
been working fine, but now when I try to add a new table to it, my Customer
combo box quits responding. The new table has a list of conference rooms in
it. My plan is to add a combo box to the 'Orders' form so we can select a
room. My new table has a one to many enforced relationship with the 'Orders'
table. The relationship is between RoomID on my new table, 'tblConfRoom', and
RoomID I added to the 'Orders' table. The following is the 'Orders' form's
SQL:
SELECT Orders.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.Phone, Customer.Fax, Orders.OrderID, Orders.CustomerID AS
Orders_CustomerID, Orders.TodaysDate, Orders.StartDate, Orders.EndDate,
Orders.ArriveTime, Orders.StartTime, Orders.EndTime, Orders.Notes,
Customer.CustomerNumber, Orders.CustomerID, Customer.CustomerID,
Customer.Email
FROM tblConfRooms INNER JOIN ((Customer INNER JOIN Orders ON
Customer.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON tblConfRooms.RoomID =
Orders.RoomID;
I hope this will help.
Thanks

 

[Jeff Boyce]

Ron

I'm afraid I'm not following what you mean by "add a new table to ... a
form"...

If you are saying that you want to be able to select a Conference Room for
an Order, you don't need to "add a table". You can add a combo box to your
existing form, and set the Row Source of that combo box to the "lookup"
table.

Of course, if you want to keep the Conference Room associated with the
Order, your Order table will need a new field to hold the ConferenceRoomID.
And your new combobox control will need to have its Control Source pointing
at the new ConferenceRoomID field in your Order table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ron Weaver]

Boy was I confused! I was trying to create a relationship between the new
ConferenceRoom table and the Orders table. When I delete that relationship
and point the combo box back to the Orders table everything works perfectly.
Thanks for help!
Ron

Ron

I'm afraid I'm not following what you mean by "add a new table to ... a
form"...

If you are saying that you want to be able to select a Conference Room for
an Order, you don't need to "add a table". You can add a combo box to your
existing form, and set the Row Source of that combo box to the "lookup"
table.

Of course, if you want to keep the Conference Room associated with the
Order, your Order table will need a new field to hold the ConferenceRoomID.
And your new combobox control will need to have its Control Source pointing
at the new ConferenceRoomID field in your Order table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an 'Orders' form which has a subform, combos, text boxes, etc. It
has
been working fine, but now when I try to add a new table to it, my
Customer
combo box quits responding. The new table has a list of conference rooms
in
it. My plan is to add a combo box to the 'Orders' form so we can select a
room. My new table has a one to many enforced relationship with the
'Orders'
table. The relationship is between RoomID on my new table, 'tblConfRoom',
and
RoomID I added to the 'Orders' table. The following is the 'Orders' form's
SQL:
SELECT Orders.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.Phone, Customer.Fax, Orders.OrderID, Orders.CustomerID AS
Orders_CustomerID, Orders.TodaysDate, Orders.StartDate, Orders.EndDate,
Orders.ArriveTime, Orders.StartTime, Orders.EndTime, Orders.Notes,
Customer.CustomerNumber, Orders.CustomerID, Customer.CustomerID,
Customer.Email
FROM tblConfRooms INNER JOIN ((Customer INNER JOIN Orders ON
Customer.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON tblConfRooms.RoomID =
Orders.RoomID;
I hope this will help.
Thanks