asked on

textbox to update a field on current datasheet recordSource

Hi there

I have a set of text boxes working as filters for all the fields on my datasheet, however it would be nice to have another set of update boxes. So after you filter your data, user could enter some data to one of update boxes and do a mass update on filtered rows for update box's field. That would be something to replace lack of excel's copy paste

I was trying to merge update query with record source but no luck (as you can see below)
any hints?

Private Sub updateDrawing_AfterUpdate()
    Dim sqlStr As String
    sqlStr = Me.[FormTagValidationSubform].Form.RecordSource
    DoCmd.RunSQL "UPDATE AssetRegisterTbl SET AssetRegisterTbl.[Drawing Ref] = " & Me.updateDrawing.Text & " WHERE " & sqlStr & ";"
End Sub

Open in new window

mbizup

Give this a try:

Private Sub updateDrawing_AfterUpdate()
    Dim sqlStr As String
    sqlStr = Me.[FormTagValidationSubform].Form.Filter
    DoCmd.RunSQL "UPDATE AssetRegisterTbl SET AssetRegisterTbl.[Drawing Ref] = " & Me.updateDrawing.Text & " WHERE " & sqlStr & ";"
End Sub

Open in new window

It uses the filter property instead of the recordsource. The filter property is like a WHERE clause without the WHERE keyword.

mbizup

If that does the trick for you, I would suggest adding special handling for cases where the filter is blank to avoid errors... either by making it update all records or by posting a message advising the user to enter a filter.

yahooooo

ASKER

i dont use filters.
I update recordsource with vba query. Me.[FormTagValidationSubform].Form.Filter returns ""

remember this thread where you helped me?
https://www.experts-exchange.com/questions/28174736/Custom-filters-based-on-textboxes.html

i finally did everything in vba/sql like:

If Me.FlocFilter = "Null" Then
sqlTag = "(([AssetRegisterTbl].[Functional location]) Is Null)"
ElseIf IsNull(Me.FlocFilter) Or Len(Me.FlocFilter) = 0 Then
sqlTag = "(('1') = ('1'))"
Else
sqlTag = "(([AssetRegisterTbl].[Functional location]) Like ('" & Me.FlocFilter & "'))"
End If

Open in new window

now I thought it could be nice to provide update query for it

ASKER CERTIFIED SOLUTION

mbizup

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

yahooooo

ASKER

that exactly what I've done ;) plus get rid of order by clause
thank you anyway

Private Sub updateDrawing_AfterUpdate()
    Dim sqlStr As String
    Dim beginning As Long, theEnd As Long, bitty As Long

    sqlStr = Me.[FormTagValidationSubform].Form.RecordSource
    beginning = InStr(sqlStr, "where")
    theEnd = InStr(sqlStr, "order")
    bitty = Len(Mid(sqlStr, beginning))
    sqlStr = Mid(sqlStr, beginning, bitty - (Len(sqlStr) - theEnd) - 1) & ";"
  
    DoCmd.RunSQL "UPDATE AssetRegisterTbl SET AssetRegisterTbl.[Drawing Ref] = '" & Me.updateDrawing.Text & "' " & sqlStr
End Sub

Open in new window

mbizup

The above is making the assumption that your form's recordsource looks like this:

SELECT blah FROM blah WHERE Blah Blah

(as opposed to simply the name of a query)

yahooooo

ASKER

above works perfectly.
My query doesnt have a name as it's created in vba and it varies from filter textbox inputs (see part of query for one of the filters in one of my posts above)

mbizup

Just for kicks, a more 'universal' way of doing this would be to use recordset code based on the form's recordsetclone... which would implicitly handle any criteria regardless of what the form's recordsource looks like:

Dim rs as DAO.recordset
Set rs = Me.[FormTagValidationSubform].Form.recordsetClone

if rs.recordcount > 0
   Do until rs.eof
       rs.Edit
       rs![Drawing Ref] =  Me.updateDrawing.Text
       rs.Update
       rs.Movenext
   loop
   Me.[FormTagValidationSubform].Form.requery
end if

rs.close
set rs = nothing

Open in new window