Need help with a multi view table editable form

[JohnReaume]

I'm hope you can help me with a problem that was not an issue when using DAO-Jet/mdb's

Ok I have three tables...
#1 (Users) W/USRID as a primary key
USRID USRName
1 John
2 Fred
3 James
Ect.

#2 (Rooms) W/ID as a primary key
ID Category
1 Apt#1
2 Apt#2
3 Apt#3
4 Apt#4
5 Apt#5
6 Suite#1
7 Condo#123
Ect.

#3 (Details) W/Date and ID the primary keys
and a relationship between table #2 ID and table #3
and a relationship between table #1 USRID and table #3
Date ID ID USRID
5/08/5006 1 1
5/08/5006 2 2
5/08/5006 3 3
5/09/5006 6 2
5/10/5006 7 3

For this example, let's say I'm only looking at date of 5/8 on my form.
I wish all ROOMS from table #2 to be displayed with the match from #3 (Details)

In a mdb, all I have to do was use a LEFT JOIN from #2 table "filtered by date" to the #3 table

The above would end up as...
Rooms (Text) Name (Combobox)
Apt#1 John
Apt#2 Fred
Apt#3 James
Apt#4
Apt#5
Suite#1
Condo#123

This worked perfectly and adding to the other Rooms was not an issue

In a adp, I can edit and delete with no problem, but it will not add records (the blank areas) without error messages when trying to update

It's like SQL/adp thinks it's an editable record on table #3 when it's a new record

Anyway around this?
Note: The above is greatly simplified and nowhere near the complexity of my database

 

[belovedcej]

have you checked to see that you can add records in the tables themselves?

Sometimes when converting to adp, the reference to the primary key is lost. If the primary key is not set, Access will not allow records to be added.

 

[JohnReaume]

Thanks for responding belovedcej

Yes, I can add directly in the table and the primary keys and relationships are all present and setup correctly

Anyone else had to do this?

 

[cmmrfrds]

An Access MDB uses dynasets where you can combine multiple tables in an updatable query.

The ADP is closer to using ANSI SQL where only 1 table in an SQL statement is updatable. In one of the properties on the data tab you can designate what table is updatable. If you want to update multiple tables in a 1-M relationship then use a Form and Subform setup to isolate the tables.

 

[JohnReaume]

cmmrfds,

I'm only editing one table...like the detail table above
the Details table is entered in the Unique Table form property to make it edit/delete...but it will not add correctly

 

[cmmrfrds]

Can you show your SQL and how you are joining tables.

 

[JohnReaume]

Sure,
I created this sample SP with the same idea as above

ALTER PROCEDURE dbo.Room_Details
(@DateID datetime)
AS
SELECT dbo.Rooms.Category, dbo.Rooms.ID AS RoomID, dp.[Date ID], dp.ID, dp.USRID
FROM dbo.[Rooms] LEFT OUTER JOIN
(SELECT dbo.[Details].*
FROM dbo.[Details]
WHERE (dbo.[Details].[Date ID] = @DateID)) AS dp
ON dbo.[Rooms].[ID] = dp.[ID]

The form has all five controls in it but RoomsID, Date ID, ID are not visible

The form displays like below with all the categories and these added notes.
* The USRID is a combobox with the rowsource to the users table.
* The forms Unique Table is set at the details table
allowing record edit and deleting

Category USRID
Apt#1 John
Apt#2 Fred
Apt#3 James
Apt#4
Apt#5
Suite#1
Condo#123

Adding a record is the problem

 

[cmmrfrds]

Are you trying to update the Details table? If so, that information is not being conveyed in the SQL.

One way to get rid of the derived table in the SQL.
SELECT dbo.Rooms.Category, dbo.Rooms.ID AS RoomID, Details.[Date ID], Details.ID, Details.USRID
FROM dbo.[Rooms]
LEFT OUTER JOIN dbo.[Details]
ON dbo.[Rooms].[ID] = dp.[ID]
WHERE dbo.Details.USRID is null or
dbo.[Details].[Date ID] = @DateID

 

[JohnReaume]

Details table are being conveyed in the alias name "dp" in the derived area and in the select statement
All editing/deleting and adding are in the form

I could have done this as a inline table function and JOIN this SP to that function...I'd get the same issue in the form

Note: Your SQL statement above will return all the other dates if the USRID was entered as a blank in the detail table And will only display the rooms when they have never been used

That's why I filter first and then do the LEFT JOIN

 

[cmmrfrds]

I didn't mean for my code to be definitive, all I was suggesting is a way to eliminate the derived table - I don't know the data like you do.

Are you sure that ADO has sufficient information to tie the Alias back to the table Details. My thought was it does not that is why I suggested getting rid of the derived table.