1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2003 Form - checking select records

Discussion in 'Business Applications' started by Center, Jan 3, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Center

    Center Thread Starter

    Joined:
    Jan 13, 2009
    Messages:
    38
    Hello,

    I've searched on the forums and google but I've been unable to pin down a solution that would help with this issue.

    I have a form (see attachment), bound to a table (called tbl_samp). There are 4 fields in the table: X (Yes/No field), Plan, Year, and Type.

    In the example, I have the first 4 records highlighted, and I would like a way to have the X checkbox checked for those 4 records (using either VBA or keyboard shortcut if it exists). So for example, perhaps a subroutine where I:
    1) Select the desired rows (like in the attached image)
    2) Press "Spacebar" (or command button or something) that takes the selected records, and marks "Yes" for each record.
     

    Attached Files:

  2. karlhaywood

    karlhaywood Account Closed

    Joined:
    Jan 17, 2010
    Messages:
    680
    You could use an update query to update the records and put a command button on the form to run the query

    Just some simple VBA like:

    DoCmd.OpenQuery "YourQueryName"

    This will update all the "X"
     
  3. Center

    Center Thread Starter

    Joined:
    Jan 13, 2009
    Messages:
    38
    Thanks karlhaywood, but the issue is how to write the "YourQueryName". I'm a beginner/intermediate with Access VBA, but is there a way to for VBA to determine which records are highlighted?

    So let me write pseudo-code of what I'm hoping to have:
    Code:
    Sub XMarkSelected()
    CurrentDb.Execute "Update tbl_samp Set X = TRUE if record.selected = TRUE;", _
      dbFailOnError
    End Sub
    
    Where I'm stuck is the "record.selected = TRUE" part. In the attachment I u/led, I have the first 4 records selected, and I'd like a way to set tbl_samp.X to TRUE by highlighting the records within the Form. Is this even possible?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will look at this later, I have to go now.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This can be done, leave it with me.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, The following code only works for Blocks of Records, no nonselected records allowed.
    There are 3 problems to overcome, first you can't use any form controls to initiate the VBA because as soon as you click on anything the record selection is lost.
    So ideally you want to use an Excel style keyboard shortcut, but Access doesn't have them.
    Last but not least you have to set up the form to allow for "Key Preview" to use a pseudo key shortcut.
    So you need the form in design View, find the "Key Preview" and set it to "Yes".
    In the Event Procedure above it called "On Key Press" select "Event Procedure" and paste the VBA code below.

    'This VBA code is designed to work with Ctrl + Q
    Dim count As Long
    On Error GoTo errorcatch
    If KeyAscii = 17 Then
    'MsgBox "you pressed Ascii Value: " & KeyAscii
    If Me.SelHeight = 0 Then Exit Sub
    With Me.RecordsetClone
    .MoveFirst
    .Move Me.SelTop - 1
    For count = 1 To Me.SelHeight
    .edit
    .X = -1
    .Update
    .Bookmark = .LastModified
    .MoveNext
    Next count
    End With
    Me.Refresh
    End If
    Exit Sub
    errorcatch:
    MsgBox Err.Description
     
  7. karlhaywood

    karlhaywood Account Closed

    Joined:
    Jan 17, 2010
    Messages:
    680
    Excellent coding OBP
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It is a Useful routine, I have added to my VBA code document.
     
  9. Center

    Center Thread Starter

    Joined:
    Jan 13, 2009
    Messages:
    38
    Excellent! Thank you so much OBP, I just tried it and it worked perfectly!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972446

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice