How update records from joined tables?

[thromada]

I've joined 2 local tables on a primary key and opened a recordset as part of a Form_Load sub. The form displays the text fields and I've added simple Previous,Next, buttons while learning how to display data and move through a table or query. I added an Update button to learn about editing and updating records. It works when I use just 1 table, but not when I've joined 2 tables. How can I update changes to records when 2 tables are joined?

Thanks,
Tom.

 

[kjv1611]

Can you explain your predicament a little more?

I'm guessing you'd need to set your recordset equal to a SELECT query of the two tables for just seeing the records.

If you are wanting to update the 2 tables, I believe you'll need two separate UPDATE queries or SQL statements. You could actually run both together, but they'd have to be separate SQL statements.

Of course, if I'm wrong on something here, I'm sure someone else will be glad to point that out.

--

"If to err is human, then I must be some kind of human!" -Me

 

[fneily]

You have an uneditable query. See:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[thromada]

Thanks for the informative link for Harnessing the Power. I didn't think that could be done.

To describe a little better. If a form is showing data from multiple tables, and you want to edit some of the fields on the form, how can that be done?

This is how I'm getting the data:
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT History.NameID, Customer.Department from History "
strSQL = strSQL & "INNER JOIN Customer on History.NameID = Customer.NameID"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
'Populate the form fields
txtNameID = rs!NameID
txtDepartment = rs!Department

Can the fields on the form be edited and then the data updated to the tables?

Thanks.

 

[fneily]

On your form, you would have UNbound textboxes for fields that are not in your main table. Then on the AfterUpdate event of the control OR your form, you can place code such as:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * FROM tblCustomer WHERE [CustID] = '" & Me![TextboxCustNo] & "'", dbOpendynaset)
With rst
.Edit
.[Notes] = Me![notebox]
.etc
.Update
End With
rst.Close
dbs.Close