Method of selecting specific records (2 Viewers)

[Harris@Z]

I have a database with data stored in a backend.

I have a Form with a recordsource linking two tables, e.g.,
SELECT Companies.CompanyName, Orders.* FROM Companies INNER JOIN Orders ON Companies.Company_ID = Orders.Company_ID;

I would like a user to be able to select specific records (tickbox) which are then processed via a function.

I suspect that the easiest method is to allow a user to select specific records is to create a field, say 'Select1', in one of these tables which allows a user to select specific records prior to further processing. If I have other tables that requires similar selections, I would do the same for these.

This seems not very efficient, and if multi users are using the database, there may be issues.

Is there another appropriate method of getting the same result, i.e., being able to select specific records that can be processed further?

Hope I have explained this clearly!
Harris

 

[jdraw]

Perhaps a little more context will get more focused responses.
What is the "selection process"?
What is the "function" to act on selected records?
A sample would be useful.

 

[plog]

You really haven't given us a great view into the ultimate aim of what you are really trying to accomplish. You've taken a few steps down a path, that may or may not be the correct path, and are now asking for help on the path you have chosen.

I'd back up a few steps and tell us exactly where you are trying to go.

With that said, identifying specific records is all about primary key values. Since orders is the most granular element of your data source, you would need to work with the primary key of the orders table.

 

[The_Doc_Man]

Are you asking for a mechanical method or are you seeking strategic advice?

Mechanically, you build your form with a recordsource that includes some unique identifier (Primary Key) that unequivocally defines the one and only record you want. Then in the form, you build a combo box, making sure that the control wizards are enabled. The combo box asks what you want to do with the selected value, and one of the possible options is to navigate the form to the record with the given key.

IF this form has to do updates and has to be built on a JOIN query, there is the potential for issues if either of the contributing tables has multiple records corresponding to the selected record, since updates cannot occur to non-unique queries. By non-unique, I specifically mean that for the JOIN, selecting a record from one contributor does NOT form a JOIN to a unique record from the other contributor, AND there is also the problem that if the JOIN is based on a 1-to-1 relationship, you have to question the validity of the relationship as a design issue.

 

[Gasman]

As you are using a query, you can just add that extra boolean field and use that as which records to work on?

 

[Harris@Z]

Thanks for asking. I was struggling to express myself clearly!
If for example I have a list of records (primary keys shown) listed in a continuous form:

1111 sal@co.com
1112 fred@co.com
1113 ann@co.com
1114 gef@co.com
etc

I would like my user, via a checkbox, to select 1112 and 1114.
When they click a button, emails are generated for these two records only.

 

[moke123]

That might be easier with a multi-select listbox rather than a continuous form.

 

[The_Doc_Man]

For the simple case you just described, you can even build a list-box with multi-selection enabled and come pretty close to that ability by just stepping through the .Selected collection that is part of a list-box's properties. But the issue of a query returning multiple records still has to be considered if something about the selection process isn't unique.

 

[Harris@Z]

@The Doc Man
Thank you, this is a novel solution I did not think of.
However it becomes 'cumbersome' if the user is scrolling through say 50 records that they will be making a selection from.

 

[jdraw]

it becomes 'cumbersome' if the user is scrolling through say 50 records that they will be making a selection from.

What exactly is the "selection"/condition that causes the user to select a given record?


Your example

1111   sal@co.com
1112   fred@co.com
1113    ann@co.com
1114    gef@co.com

===return 1112 and 1114 could be stified with something like:
select * from your query where email like "*f*"

but I'm quite sure that isn't one of your selection criteria.
So what are the conditions for selection-- maybe you just need a query or two.

 

[LarryE]

Most likely, the best way is to create a Yes/No Date Type field in your table(s) and bind the field to a checkbox on the forms. You can then create UPDATE queries to Select All and De Select All records using a separate command button or unbound checkbox. If users are going to need to select many records at one time, they can then use the Select All UPDATE query to select all records, but then De-Select only those few records they don't need (if any). That might save time in scrolling through numerous records and having to select each one individually.

Your report query could then select only those records where the Yes/No field is True.
I use a form format like this:

when the unbound Select All checkbox is checked (condition TRUE) an UPDATE query is run that selects all the records and sets their condition to True. When it is unchecked, an UPDATE query is run that sets all the records to a False condition. I put the Select All checkbox in the forms Header section.

I use this simple code when the checkbox I am calling SelectDeSelectAll

Private Sub SelectDeSelectAll_Click()
If Me.SelectDeSelectAll = True Then
    DoCmd.OpenQuery "SelectAll", acViewNormal, acReadOnly
Else
    DoCmd.OpenQuery "DeSelectAll", acViewNormal, acReadOnly
End If
Me.Requery
Me.Recalc
Exit Sub
End Sub

 

[Ken Sheridan]

This seems not very efficient, and if multi users are using the database, there may be issues.

The following is code behind a button in a dialogue form in which multiple addresses can be selected in a Multi-select list box.

Private Sub cmdConfirm_Click()

    Const conSQL = "UPDATE SelectedAddresses SET Selected = FALSE"

    Dim varItem As Variant
    Dim strSQL As String
    Dim strAddressIDList As String
    Dim strFilter As String
    Dim strBooleanValue As String
    Dim ctrl As Control
    
    ' set Selected column to FALSE in all rows
    ' in SelectedAddresses table
    CurrentDb.Execute conSQL, dbFailOnError

    Set ctrl = Me.lstLastNames
    
    ' loop, through selected iterms and build value list of names
    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strAddressIDList = strAddressIDList & "," & ctrl.ItemData(varItem)
        Next varItem
        
        ' remove leading comma
        strAddressIDList = Mid(strAddressIDList, 2)
        
        strFilter = "AddressID IN(" & strAddressIDList & ")"
        
    End If
    
    ' update Selected column in SelectedAddresses
    ' for selected names.  If no names selected then
    ' update all rows to FALSE
    If strFilter <> "" Then
        strBooleanValue = "TRUE"
    Else
        strBooleanValue = "FALSE"
        strFilter = "TRUE"
    End If
    
    strSQL = "UPDATE SelectedAddresses" & _
        " SET Selected = " & strBooleanValue & _
        " WHERE " & strFilter
        
    CurrentDb.Execute strSQL, dbFailOnError
    
    ' close dialogue form
    DoCmd.Close acForm, Me.Name

End Sub

The code updates a Selected column of Boolean data type in a SelectedAddresses table which includes one other column, AddressID. The selected addresses are then returned in a query which joins this table to the Addesses table. The query consequently returns only the selected addresses by virtue of the INNER JOIN. These can be returned in a form or report based on the query.

As regards the possibility of conflicts in a multi-user environment the SelectedAddresses table can be in each user's front end file, while the Addresses table is in a shared location on a server. A query joining the tables will therefore return rows specific to each user.

 

[Mark_]

This seems not very efficient, and if multi users are using the database, there may be issues.

If your users know exactly which customers need to be emailed, the multiselect drop down would be the way to go.

If your users need to spend a bit of time and have a "Working list", you will need a split table for this. You would have a table in your front end that keeps the Company_ID of each selected record (effectively 1 to 1 relationship) for the records that users selects.

This avoids having two people working on the same list.

Depending on how you want your emails to work you would either build a "To" list form the selected companies OR have this in a loop so each gets their own personalized.

This goes back to posts #2, #3, and #4. What do your users need to do? Different approaches based on "Is this done all at once VS done over time".