form bound to queries with joins

[italiancholo]

Dear users,
I need help to troubleshoot an issue that I believe typical of databases, but I can’t find any answer either n books or on websites.
Mine is a client/server access application. I use forms connected to ADO recordsets and the instruction set I use to bind the formi s the following:
Dim cn As ADODB.Connection
Dim rsMain As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rsMain = New ADODB.Recordset
With rsMain
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.Source = "exec " & QueryConnection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rsMain
Set Me![ThisSubForm].Form.Recordset = rsMain
Set rsMain = Nothing
Everything’s ok until the records to be showed in the form doesn’t need any join to other tables. The typical example is the following:
1) a table containing products. Every product has a unique ID (counter), a customer code and a description.
2) a table containing for example the data of invoices.
3) a table tblDDTInvoiceItems that realizes a many-to-many relationship, with the list of items sent with the invoices, and that contains the following fields:
IDRow (counter, primary key)
IDInvoice
IDItem (the id of the sent item)
Sequence
Quantity

As you can see the table tblDDTInvoiceItems contains just the item ID which is not meaningful to the user who expects company code and description.
To do so I create a connection query with joins to the item table and I get the recordset I want.

THE PROBLEM IS THAT SUCH A RECORDSET IS NOT UPDATABLE THROUGH THE FORM.

So I need to find another way but I don’t know how. I tried with disconnected recordsets and batch update but when I save the modifications I get back an error message like “too little information about the index blah blah to update the table”. It means that it can’t go back through the joins (the update batch works fine with queries with no join).
When instead I go to a new record in the disconnected recordset the textboxes of the form are locked like when you set the property Allowadditions to false.

Since I truely believe these are standard circumstances in the development of databases I am sure that a solution exists, yet I can’t find it.
I will be grateful to anyone for any help he/she can give me.
Have a nice day,
Jacopo

 

[Rabbie]

Hi

I have produced a query which will update the tables from the form

SELECT AuthorBook.LinkID, AuthorBook.AuthorID, AuthorBook.BookID, Authors.Authorname, Books.Title
FROM Books INNER JOIN (Authors INNER JOIN AuthorBook ON Authors.AuthorID = AuthorBook.AuthorID) ON Books.bookid = AuthorBook.BookID;

HTH

 

[italiancholo]

Hi Rabbie,
thank you for the idea. I changed the joins in my connection query from RIGHT to INNER exactly like yours. I also replicated your tables and query.
When you run the query by itself or you bind the form directly to the query you can add new records, but when you bind the form to the query through a recordset, not only you can't add any other item, but you can't even edit the existing.
Maybe you meant something else.
Jacopo

 

[italiancholo]

Hi Rabbie,
thank you for the idea. I changed the joins in my connection query from RIGHT to INNER exactly like yours. I also replicated your tables and query.
When you run the query by itself or you bind the form directly to the query you can add new records, but when you bind the form to the query through a recordset, not only you can't add any other item, but you can't even edit the existing.
Maybe you meant something else.
Jacopo

 

[thisisntwally]

buon giorno,

due domande:
in primo, when the form is bound directly to the query can you edit or only add records?
in secondo, have you explored changing the form's properties? you may need to change the data entry setting.

 

[italiancholo]

Hi,
yes I can also edit the records. Everything works ok.
Problem is that leaving the form bound to the query I am afraid it implies an overload on the LAN communications since the product tables can easily have 50000 entries.
This is why I wanted an unbound form that exchanges with the server just the information related to the few records in the invoice.
I let the user insert a code on the form.
In the afterupdate event of the text box the form asks the server the information related to that specific code (id, description, and if it exists of course).
When I have checked everything and the user presses save, I give back the record to the server to be added/updated to the table containing the items of the invoices. Easier said than done.

Thank you for the reply and any other advice.
Jacopo

 

[thisisntwally]

yeah, looks like im wayyy out of my league here. sorry.