you can use this code to create a disconnected recordset which is editable. This has only been very roughly tested and may not meet your needs,
Note that you can use this method for changing existing data - if an xtab field was previously null, then you would need to use an insert query (you'll need to test for oldvalue=null) since there is no existing record. Similarly, it is not a way of adding new rows to the xtab, although I guess if the parameters are available you could do.
Also, if you delete an entry - should this delete the underlying record? or just set the value to null?
Your work will be in the xtabAfterUpdate function below and what you need to do depends on the complexity of your xtab and what you want to happen
put this in your form
Code:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("name of xtab query")
If Not rst.EOF Then
Set Me.Recordset = makeADORecordset(rst)
End If
rst.Close
Set rst = Nothing
End Sub
and this in a module
Code:
Function makeADORecordset(ByVal rstD As DAO.Recordset) As ADODB.Recordset
Dim rstA As ADODB.Recordset
Dim Fld As DAO.Field
Dim i As Integer
Set rstA = New ADODB.Recordset
With rstA
.CursorLocation = adUseClient
.LockType = adLockPessimistic
For Each Fld In rstD.Fields
Select Case Fld.Type
Case dbText
.Fields.Append Fld.Name, adVarChar, 255, adFldIsNullable
Case dbMemo
.Fields.Append Fld.Name, adLongVarWChar, 64000, adFldIsNullable
Case dbByte
.Fields.Append Fld.Name, adUnsignedTinyInt, , adFldIsNullable
Case dbInteger
.Fields.Append Fld.Name, adSmallInt, , adFldIsNullable
Case dbLong
.Fields.Append Fld.Name, adInteger, , adFldIsNullable
Case dbSingle
.Fields.Append Fld.Name, adSingle, , adFldIsNullable
Case dbDouble
.Fields.Append Fld.Name, adDouble, , adFldIsNullable
Case dbDecimal
.Fields.Append Fld.Name, adNumeric, , adFldIsNullable
Case dbDate
.Fields.Append Fld.Name, adDate, , adFldIsNullable
Case dbCurrency
.Fields.Append Fld.Name, adCurrency, , adFldIsNullable
Case dbBoolean
.Fields.Append Fld.Name, adBoolean, , adFldIsNullable
Case dbLongBinary
.Fields.Append Fld.Name, adLongVarBinary, , adFldIsNullable
Case dbGUID
.Fields.Append Fld.Name, adGUID, , adFldIsNullable
Case dbBinary
.Fields.Append Fld.Name, adVarBinary, , adFldIsNullable
Case Else
MsgBox "field type not converted"
End Select
Next
.Open
End With
'populate ado recordset
If Not rstD.EOF Then
rstD.MoveFirst
While Not rstD.EOF
rstA.AddNew
For Each Fld In rstD.Fields
rstA.Fields(Fld.Name) = rstD.Fields(Fld.Name)
Next Fld
rstD.MoveNext
rstA.Update
Wend
Set rstD = Nothing
End If
Set makeADORecordset = rstA
End Function
Function xtabAfterUpdate()
msgbox "put some code here to update the source record"
'you may need to bring some additional fields across in your xtab for reference purposes (perhaps even another xtab) but in principle it would be something like
dim db as dao.database
set db=currentdb
with screen.activecontol
'notes- .name may need surrounding with quotes or # depending on datatype. This example is based on a simple xtab displaying some details from a parent table (e.g. employeePK and name) and a child table showing what roles an employee has had
'db.execute "Update tblRolesAssigned set val1=" & .value & " WHERE EmployeeFK=" & .parent.EmployeePK & " AND Role=" & .name
end with
set db=nothing
End Function
and in each of your crosstab controls you want to be able to update put
=xtabAfterUpdate()
against the afterupdate event (instead of [Event Procedure])