Solved: Access 2003 Form - checking select records

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

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"
 

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?
 

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
 

OBP

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

Center

Thread Starter
Joined
Jan 13, 2009
Messages
38
Excellent! Thank you so much OBP, I just tried it and it worked perfectly!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top