Select multiple records and loop (1 Viewer)

jaryszek · May 26, 2017

Hello,

this is continuity from here:

https://www.access-programmers.co.uk/forums/showthread.php?p=1533492#post1533492

Misty wrote:

Normally you would use an Update query with suitable criteria.
You may want to build a continuous form, and include an update check box or similar on the form.

You could use a multiselect list box, and select the records you want to update. You could simply update records which have suitable date records or not in them.

There are many ways to achieve a similar result.

I would like to factorise this sentence.

I was searching for the best solution in internet but there is a lot of it and i want to implement the fastest one for my purpose.
In attachment you can see example excel file and i want to do exactly the same thing in Access.

1. If you click on button in "Wnioski" sheet you can select the name of person or unique ID number. After that listbox is shown with all columns and fields.

2. User can select multiple columns and push the button in order to loop through all rows.

What will be the best solution for it ?

a) Update query with suitable criteria:
I can see that user could filter criteria all rows will be shown.
So for example there will be criteria for unique ID: 10001.
Listbox will show 5 rows. And now user should have possibility to choose only 2 of them because other criteria are suitable for him.
I think that this will be not the solution what I am looking for.

b) Continuos form required to input checkboxes into my main table. This is not solution which I like.

c) Multiselect list box - i think this is the most similar solution for me

What else? Which method do you recommend?

Please help,
Best wishes,
Jacek Antek

Minty · May 26, 2017

Work policy means I can't open your spreadsheet I'm afraid, however the Multiselect List box is definitely the most elegant solution from your description.

If you do a search on here or even in the sample code repository there are some prime examples of how to use a list box to select and then action those records.

http://www.baldyweb.com/multiselect.htm shows how to build a report filter string from the items selected.

jaryszek · May 26, 2017

Thank you Misty!

I will do research in web about this

Jacek

Gasman · May 26, 2017

jaryszek,

Here is some code I use to create timesheet records for employees in work.
I use a multi select listbox for the manager to select employees, normally all, so they are all selected by default, and then the Create Timesheets button runs this code.

HTH

Code:

Private Sub cmdCreate_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim rstDays As Recordset
Dim strSQL As String, strStartDate As String, strEnddate As String
Dim i As Integer
Dim lngEmployee As Long

strStartDate = Format(Me.txtDateBW, strcJetDate)
strEnddate = Format(Me.txtDateWE, strcJetDate)
Set db = CurrentDb()

SetStatusBar ("Creating Timesheets .....")
' Now loop through selected listbox items
For i = 0 To LstEmployee.ListCount - 1
    If LstEmployee.Selected(i) Then
        LstEmployee.Selected(i) = False
        lngEmployee = LstEmployee.Column(0, i)
        SetStatusBar ("Creating Timesheet for " & LstEmployee.Column(1, i))
        
        'strSql = "INSERT INTO tblemployeeday ( EmployeeID, DayID, DayDate, StartTime, EndTime, Lunch, DateType )"
        'strSql = strSql & " SELECT tbDfltlHours.EmployeeID, tblDates.DayID, tblDates.DayDate, tbDfltlHours.StartTime, tbDfltlHours.EndTime, tbDfltlHours.Lunch, tblDates.DayTypeID"
        'strSql = strSql & " FROM tbDfltlHours, tblDates"
        'strSql = strSql & " WHERE (((tbDfltlHours.EmployeeID)=" & lngEmployee & ")"
        'strSql = strSql & " AND ((tblDates.DayDate) Between " & strStartDate & " And " & strEnddate & ")"
        'strSql = strSql & " AND ((tbDfltlHours.HoursDayNum)=Weekday([tbldates.daydate])))"
        'strSql = strSql & " ORDER BY tblDates.DayID"
        ' Now take into account any startdate midweek
        strSQL = "INSERT INTO tblemployeeday ( EmployeeID, DayID, DayDate, StartTime, EndTime, Lunch, DateType )"
        strSQL = strSQL & " SELECT tbDfltlHours.EmployeeID, tblDates.DayID, tblDates.DayDate, tbDfltlHours.StartTime, tbDfltlHours.EndTime, tbDfltlHours.Lunch, tblDates.DayTypeID"
        strSQL = strSQL & " FROM tblDates, tblEmployee INNER JOIN tbDfltlHours ON tblEmployee.EmployeeID = tbDfltlHours.EmployeeID"
        strSQL = strSQL & " WHERE (((tbDfltlHours.EmployeeID)=" & lngEmployee & ")"
        strSQL = strSQL & " AND ((tblDates.DayDate) Between " & strStartDate & " And " & strEnddate & ")"
        strSQL = strSQL & " AND ((tbDfltlHours.HoursDayNum)=Weekday([tbldates.daydate]))"
        strSQL = strSQL & " AND ((tblDates.DayDate)>=[tblEmployee].[StartDate]))" ' Start date check
        strSQL = strSQL & " AND ((tblDates.DayDate)<= NZ([tblEmployee].[EndDate],#12/31/2100#))" ' End date check
        strSQL = strSQL & " ORDER BY tblDates.DayID"
        db.Execute strSQL, dbFailOnError
    End If
Next i
MsgBox "Timesheets completed"
SetStatusBar (" ")

ExitSub:
    Set db = Nothing
    Set rstDays = Nothing

    
Err_Exit:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitSub
End Sub

Code to select them all by default is

Code:

Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
    'Purpose:   Select all items in the multi-select list box.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
    Dim lngRow As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox (Err.Number & " " & Err.Description & " " & "SelectAll()")
    Resume Exit_Handler
End Function

jaryszek · May 26, 2017

Thank you Gasman I appreciate it !

Your code can be useful!

Jacek

jaryszek · May 26, 2017

One more question:

I want to have 2 comboboxes:
1. name (Com1)
2. ID (Com2)

If i choose the unique ID in Com2 there should be shown name in Com1
There is only one name attached to one ID.
Problem is when I am trying to do it in opposite way.
So choosing name1 it can be 2 IDs - name Jerry can exist twice in table.

Com1 Com2
Jerry ID1
ID2

Com2 Com1
ID Jerry
How can i do that ?

Jacek

Gasman · May 26, 2017

If I understand you correctly, although you have multiple names of Jerry in Com1, they would still have a unique ID.?
Unsure why you need two combos?

I would normally have a combo along the lines of
ID,Name, and anything else I might need

I believe you would need to show enough data to distiguish between different Jerrys?

1,Jerry Jones
2,Jerry Smith

jaryszek said:
One more question:

I want to have 2 comboboxes:
1. name (Com1)
2. ID (Com2)

If i choose the unique ID in Com2 there should be shown name in Com1
There is only one name attached to one ID.
Problem is when I am trying to do it in opposite way.
So choosing name1 it can be 2 IDs - name Jerry can exist twice in table.

Com1 Com2
Jerry ID1
ID2

Com2 Com1
ID Jerry
How can i do that ?

Jacek

jaryszek · May 29, 2017

Hi Gasman,

thank you for your help,
Unique ID is number attached for one name. And user if knows the number, can simply choose it from second combobox and get certain result.

But searching for name is more user-friendly for users (you can do it from your mind). So it should be one more combobox in order to have choice also for this.

If I understand you correctly, although you have multiple names of Jerry in Com1, they would still have a unique ID.?

Yes, you are understanding me correctly.

I would normally have a combo along the lines of
ID,Name, and anything else I might need

Combobox like 1|Jerry Smith ? Is there possibility to search for first string or second string in one combobox?

I believe you would need to show enough data to distiguish between different Jerrys?

1,Jerry Jones
2,Jerry Smith

Yes of course.

In attachment you can see 2 comboboxes image. Below them i would like to put listbox and show only values choosed by user.

thank you,
Jacek

Gasman · May 29, 2017

I have not searched for data in a combo box, but directly in the data.

So searching for Jerry would find

Jerry Smith
John Jerryson

You could have VBA to inspect the column with the full name using Instr.
If greater than zero, you have found a valid record.?

However if you create a FullName (first and last) for the combo, then in the combo as you type, it will go to the relevant record?

To search for records I copied this method

http://allenbrowne.com/ser-62.html

I'v used the combo method for selecting crew members for my Gazette DB

Here is my source for the combo

Code:

SELECT Crew.ID, Trim([surname] & " " & [initials]) AS Name
FROM Crew
ORDER BY Trim([surname] & " " & [initials]);

isladogs · May 29, 2017

In my databases, it isn't realistic to expect users to know ID values.

So my search forms have 2 combo boxes for last name & first name.
Either can be searched.
Users enter a few letters in one or other and press enter.
A listbox then displays all names containing that search string.
E.g Jon in the surname might give
Ann Jones, tom Jones, Fred Jonson etc etc

Users then select the required name from the list to open the selected record

jaryszek · May 29, 2017

Gasman said:
I have not searched for data in a combo box, but directly in the data.

So searching for Jerry would find

Jerry Smith
John Jerryson

You could have VBA to inspect the column with the full name using Instr.
If greater than zero, you have found a valid record.?

However if you create a FullName (first and last) for the combo, then in the combo as you type, it will go to the relevant record?

To search for records I copied this method

http://allenbrowne.com/ser-62.html

I'v used the combo method for selecting crew members for my Gazette DB

Here is my source for the combo

Code:

SELECT Crew.ID, Trim([surname] & " " & [initials]) AS Name FROM Crew ORDER BY Trim([surname] & " " & [initials]);

Nice method you have attached Gasman, thank you for that.
But there is no dynamic list in this textbox method using instr. In combobox you have preview of values and you can choose one of them. My users as I can see they are chosing mainly values from dynamic lists in Excel...(no typing, only scrolling).

The best solution would be combobox where you can write ID or Name depenging on what you want. But i suppose it is no possible to do it in Access...Or maybe there is an solution? Create new column in Query and concatenate all values from 2 columns?

More efficient solution for me will be ridders' method.
There are 2 comboboxes and listbox with all data is showed below after user choice.
Unfortunately my form has to have ID and name combobox.

Maybe preffered solution could be to choice number (ID or name) and listbox should be showed with all necessary values.
There will be usually one, maybe two records.

Best Wishes,
Jacek

isladogs · May 29, 2017

Unfortunately my form has to have ID and name combobox.

Why?
The ID field can be the bound column (hidden) in the listbox.
In other words, it is used but not seen

jaryszek · May 29, 2017

Why?
The ID field can be the bound column (hidden) in the listbox.
In other words, it is used but not seen

Thank you ridders,
unfortunately this is my business requirement....

Best Wishes
Jacek

Gasman · May 29, 2017

OK, so you just have your two combos.
Personally I cannot see the ID one being used unless a user is copying it from a form?, and then it would likely not be the Access ID, but your own reference?

If you did surname & firstname as I did, then you would quickly get to a surname, which is more likely known, as you might just have Mr E.P.Steel ?

Also do not discount a text box search.

I have to use a system for my volunteer work, and that has a combobox for all the providers. And it is long

I cannot find Welsh in any of that easily as I might have
The Royal Welsh
Welsh Guards

and it is because I am not sure what the name of the provider is the reason I am looking in the first place. I have to scroll carefully through the whole list.

jaryszek · May 30, 2017

Gasman,

Exactly, ID can be copied from other place to our form

Yes this is my own reference.

Maybe i will consider text boxes for one of my forms.
But problem is when i will choose name Kowalski John.
And for Kowalski there will be:

Kowalski John 1001
Kowalski John 1002
Kowalski John 1003

and user has to choose one of them to send the ticket for it...

Best Wishes,
Jacek

Gasman · May 30, 2017

OK, so you will need to show 2 columns, remember the first column can combine surname and firstname, but the names could be in seperate columns?

Just add the ID again as a reference as the last column, then the user can tell which Kowalski John they want.?

jaryszek said:
Gasman,

Exactly, ID can be copied from other place to our form Yes this is my own reference.

Maybe i will consider text boxes for one of my forms.
But problem is when i will choose name Kowalski John.
And for Kowalski there will be:

Kowalski John 1001
Kowalski John 1002
Kowalski John 1003

and user has to choose one of them to send the ticket for it...

Best Wishes,
Jacek

jaryszek · May 30, 2017

Gasman,

thank you.

Are you talking about combobox with 2 columns?

List something like that:
Kowalski John | 1001 ??

It is possible to search with these 2 columns, sor for ID and for Kowalski separately?

Jacek

jaryszek · May 30, 2017

hmm I would like to add (please look at screen in attachment) that it can be solved for example like that:

If there is possibility to lookup after second column in combobox the code can be write:

- default value - name
- if there is an number vba can check it and change lookpup column to second one.

Is it possible ?

Jacek

jaryszek · May 30, 2017

Problem is to read value in combobox when user is typing.
I want to get the value "1" if user is starting typing but combobox is showing first value in the list instead.
Why?

Gasman · May 30, 2017

Sorry, not sure what you are asking?

jaryszek said:
hmm I would like to add (please look at screen in attachment) that it can be solved for example like that:

If there is possibility to lookup after second column in combobox the code can be write:

- default value - name
- if there is an number vba can check it and change lookpup column to second one.

Is it possible ?

Jacek

Select multiple records and loop (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Attachments

Enthusiastic Amateur

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread