Solved

MS Access Record Selector Question...Can I use them to select a record?

Posted on 2014-07-28
8
1,677 Views
Last Modified: 2014-08-02
In an MS Access query, there is some sort of record selection device along the left of the query.
If you l click on this left edge, the entire row is highlighted.

Can I click on a row, the press a command button, and have the ability to select data from the selected row?

Lets say my query lists hundreds of items.  I would like to click one row then press a command button that I can use in my routines.

Possible?
0
Comment
Question by:pcalabria
8 Comments
 
LVL 13

Expert Comment

by:Russell Fox
Comment Utility
I think your best bet will be to add a column like IsSelected and expose that in a form so the user can click each record they want to manipulate, then you can have another query "WHERE IsSelected = True" to just work on those records. You should probably use the form's OnLoad event to set all IsSelected to False. If you have multiple users, you may need to instead use their UserId instead of a boolean (WHERE UserSelected = CurrentUserID).
0
 

Author Comment

by:pcalabria
Comment Utility
I'll give that a try.  So the other record selection process really is useless?
It's not just that I don't know how to use it?

IS there an easy way to convert from a query to a form?
0
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 100 total points
Comment Utility
Yeah, it's really just for using the right-click options, like copy/delete. You can create a simple form with the form wizard, just use the query as the data source instead of the table.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
Comment Utility
Queries don't fire Events, and other objects in the database cannot interact with a query in standard Datasheet view, so you'll have to do something along the line of what Russell suggests. You could also create a form and use a Listbox, with your query as the RowSource for the Listbox, if that would make more sense from a UI perspective.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
Comment Utility
If you bind a form to a query, and then display it in data sheet view or continuous form view, you can let a user select records by using the SelLeft, SelTop, SelWidth, and SelWidth property.  In fact you can even determine the cells that are selected.

 These indicate the start row/col and the last row/col selected.

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I would like to click one row then press a command button that I can use in my routines.

Can you explain clearly (with an example scenario) what you mean by this?

Perhaps I am not understanding, ...but if you want to :
    "click one row then press a command button"
...Then to me, you might be better off using Scott's listbox suggestion to display the query results.
Then you can retrieve, and use any field value from that row and use it in your "routines", ...something like this:
    Me.Yourlistbox.Column(n)

I do not want any points for my post, because I basically agree with Scott, ...I am just curious about these "Routines"
;-)


JeffCoachman
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 200 total points
Comment Utility
Make a datasheet form (or subform) bound to the query, and then add a DblClick event to a control.  Typically, this is used to open another form for the selected ID, like this, but you can run code or do something else using the ID:
Private Sub cboClientID_DblClick(Cancel As Integer)
'Created by Helen Feddema 18-May-2011
'Last modified by Helen Feddema 18-May-2011

On Error GoTo ErrorHandler

   Dim lngClientID As Long
   Dim strSearch As String
   Dim frm As Access.Form
   
   lngClientID = Nz(Me.ActiveControl.Value)
   
   If lngClientID <> 0 Then
      blnOpenMainMenu = False
      DoCmd.OpenForm "frmClients"
      strSearch = "[ClientID] = " & lngClientID
      Set frm = Forms("frmClients")
      frm.Recordset.FindFirst strSearch
      DoCmd.Close acForm, Me.Name
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Closing Comment

by:pcalabria
Comment Utility
Thanks all,

I replaced the query with a form based on the query and used the double click event of the for to run my code.  It works great.  All tried to distribute points based upon the amount of help yor responses gave me . Thanks!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now