Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
38 Posts
Discussion Starter · #1 ·
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

·
Registered
Joined
·
680 Posts
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"
 

·
Registered
Joined
·
38 Posts
Discussion Starter · #3 ·
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?
 

·
Registered
Joined
·
19,896 Posts
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
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top