CODEWITHSUNDEEP
vbams-access
veryBadProgrammer
veryBadProgrammer

Reputation: 115

How to add an editable column to non-editable query

I have a Query in Datasheet View in a Subform which holds information that the user needs to add to. Specifically, 2 columns need to be edited. The Query is non-editable because of a ton of GROUP BY clauses. Is there a way that I can add these 2 columns and make them editable per row without changing the query/rewriting it to make it editable?

So far, I've tried adding an unbound column but that doesn't hold the data per row. Now, I've set a new table with the two columns I want to add as Control Source but I can't edit anything in the Datasheet because the record is read-only.

Upvotes: 0

Views: 977

Answers (2)

viilpe
viilpe

Reputation: 775

You can do that this way: create ADODB recordset and fill it with data from recordset of your form.

This is sample from my db:

Private Sub Form_Open(Cancel As Integer)
Dim RS As New adodb.Recordset, RST As DAO.Recordset, InvTotal As Currency

Set RS = New adodb.Recordset
With RS
    ' It's a free field that doesn't belong to form query:
    .Fields.Append "InvCounter", adInteger

    .Fields.Append "InvProduct", adVarChar, 255
    .Fields.Append "InvUnit", adVarChar, 3
    .Fields.Append "InvQuantity", adInteger
    .Fields.Append "InvPrice", adCurrency
    .Fields.Append "InvValue", adCurrency

    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With

Set RST = CurrentDb.OpenRecordset("SELECT * FROM tblWarehouse WHERE wOrder = " & idf, dbOpenSnapshot)
InvTotal = 0
If RST.RecordCount Then
    Do Until RST.EOF
        RS.AddNew
        ' I fill my free field with row number but i can edit it
        ' manually in form:
        RS.Fields("InvCounter") = RST.AbsolutePosition + 1
        RS.Fields("InvProduct") = RST("wProduct")
        RS.Fields("InvUnit") = "p"
        RS.Fields("InvQuantity") = RST("wQuantity")
        RS.Fields("InvPrice") = RST("wPrice")
        RS.Fields("InvValue") = RS.Fields("InvPrice") * RST("wQuantity")
        InvTotal = InvTotal + RS.Fields("InvValue")
        RS.Update
        RST.MoveNext
    Loop
    Set Me.Recordset = RS
    Me.InvTotal = InvTotal
    RST.Close
    Set RST = Nothing
    RS.Close
    Set RS = Nothing
End If
End Sub

If you wanna save your edits in that field you have to do it through VBA too.

Upvotes: 0

SunKnight0
SunKnight0

Reputation: 3351

You cannot edit in a grouped query because there is no way to tell the database which of the grouped records you are actually editing. You do not mention if you need these columns editable just in he context of the form (and any data in them is thrown away as soon as the form is closed or reloaded with different data) or if these edits are expected to somehow also update your records.

In either case the way to do it is by using a temporary table and fill it with the query data. That will allow you to edit it in datasheet view and maintain per-record values. However if you expect these values to somehow permanently affect your original data you will have to manually handle that in VBA, probably capturing form close or reload events and using the temporary table data to update the original tables.

Upvotes: 1

Related Questions