Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: How to update all form records from an unbound field?

    Is there a simple way to program an event procedure that will update all of the records on a continuous form? Let's say the form opens and loads 100 records, and the user filters it to show only the 15 records that they need, each having a blank field (FileID). I would like to have the user enter text in an unbound textbox in the footer, and then click a button to update that text into the FileID for the 15 records.

    Please post any ideas. Thanks in advance!

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    How is the user filtering the data? Do you have some filtering fields on the form? If so, you could use them to construct an SQL statement to update the records through the filter fields on the form.

    C

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Provided the form is based on an updatable data source (table or query):

    Code:
    Private Sub Button_Update_Click()
    
        Dim strSQL as String
    
        If Len(Me.Filter) Then
            strSQL = "UPDATE " & Me.RecordSource & " SET FileID = " & Me.Text_Unbound & " WHERE " & Me.Filter
            ' Must be:  ... SET FileID = '" & Me.Text_Unbound & "'... if FileID is of Text type.
            CurrentDb.Execute strSQL ' possibly with some options: dbseechanges etc.
            Me.Requery
        End If
    
    End Sub
    Have a nice day!

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    Sinndho - you have uncovered a really useful function! It will basically work like the "check all" option does in yahoo mail, where you can craft a list for delete, and then place the check mark in those records.

    Here is what I used, for syntax:

    -----
    Dim strSQL As String
    Dim dbs As Database
    Set dbs = CurrentDb

    If Len(Me.Filter) Then
    strSQL = "UPDATE " & Me.RecordSource & " SET FileID = """ & Me.filename & """ WHERE " & Me.Filter
    'MsgBox strSQL 'for good measure
    dbs.Execute (strSQL)
    Me.Requery
    End If
    -----

    It works great. Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •