Approach to problem...

[zozew]

Hello....again...y'all

I have a structure question...

Base:
I have a small sweet query that makes a recordSet.This query gets a list of records corresponding to a specific date, *Some of the records will have a field (BR) with the same value, the recordSet is sorted according to this field (BR). There will be a maximum of about 10 unique BR values

Desired result:
A report based on the query (now the problem) plus values from escorts table.
The report is already grouped according to this BR field, then for each unique group of BR it should display multiple user chosen values (in red below) from this escorts table under or next to the BR value
like this:

GUI operation:
1. mainForm opens
2. You choose a date
3. If there are records matching it displays in a subForm (spredsheet view) in the mainForm
4. For each unique BR value the user can chose multiple values from the escorts table from a comboBox
5. when the user has chosen escort values for all unique BR values he clicks print report (looking like the image above)

Question:
What is the best approach to be able to let the user choose these escort values for each unique BR value..?

This is how i see it:
MainForm with a datechoser, then a subForm (spreedsheet) displaying the corresponding records, onClick on a row user choses escorts from a dropdown in the mainForm the values get added to an empty Field in the spreadsheet, then when the user is done he just clicks a button creating the report...

Problem
How do i add dynamically values to a query's recordSet's empty field and then base a report on that??

 

[vbaInet]

GUI operation:
3. If there are records matching it displays in a subForm (spredsheet view) in the mainForm
4. For each unique BR value the user can chose multiple values from the escorts table from a comboBox
5. when the user has chosen escort values for all unique BR values he clicks print report (looking like the image above)

3. If there are records matching it displays in a multi select listbox in the mainForm
4. The user will be able to select (or highlight) multiple values from the list (which is bound to the table - in the form of a query)

Using the listbox's ItemsSelected property you will be able to get all the BRs selected and concatenate it inside the IN operator. Then use this concatenated string in the WHERE part (or parameter) of the DoCmd.OpenReport method.

 

[zozew]

Well hi there again feels almost like i should put you on a retainer....

Your suggestion sounds perfect...but now i changed the structure a little, not a lot though.

Now I open a form (scheduleChose, refered to as mainForm before) with a select dateTextbox then a button "view schedule" opening a Report (scheduleReport) with a recordsource from a query (scheduleQuery) with the criteria of the date selected in the dateTextbox .
And the Report looks like this:

Basically on the right side there is a field Escorts, this is where i want the Multiple selections per unique BR to be placed ( now i have dotted lines for writing it in manually ) either on the first line (same as the Blue square BR number) or on each dotted line. Of course ill change the layout if its easier another way...

Note
The two last fields (Next hearing and Escorts are not from the Query just design objects i put into the Report manually)

*So using multiSelectListbox fits perfect, but im not really clear on how to make the miltiselection for one unique BR at a time? and also how to get it into the Report's layout in line with every unique BR

GUI operation
So for example you get 3 unique BR's for a selected date what will the multi select listbox show excatly? and how will the user "select the Escorts" for each BR?



Note
I really wanted to do this in VBA (thought i have better control of the data) first off and i did, but i couldnt figure out how to set the already created Report's recordsource to the recordset i created in VBA ( a copy of the Query's SQL string ), i got as far as making a duplicate recordset of the one created by the Query.

Then i tried editing the reports recordsource and failed. I read somewhere you have to open the report in designView and the edit and then save and then you can open again in PrintPreview to preview it

 

[zozew]

Is this how you mean? if its not it would still be a nice way of assigning escorts.

If you want to change the already assigned escorts you would just select the escorts and then branch and click the assign button again and it would replace previously selected escorts for that branch....

note:
I shpuld probably switch positions of the branch and escorts listbox to make it clearer

 

[zozew]

Actually this would be even better when it comes to overview for the user assigning Escorts so the user knows how many Inmates are going to the specific branches...

Well now the tricky part how do i accomplish this...

1. query for the branch listbox with Unique values and subTotals per BR value

2. Clicking the assign button (code) and populating the Lower listbox with the Branch, subTotal and assigned Escorts

3. Clicking View schedule and populating the right row with the Assigned Escorts....

I know this is much to ask for but any hint or advice will reduce my sentence considerably

 

[zozew]

1. query for the branch listbox with Unique values and subTotals per BR value

Done with this:

SELECT tblInmateCases.BR, Count(tblInmateCases.BR) AS Total
FROM tblInmatesProfile INNER JOIN (tblInmateCases INNER JOIN tblInmateHearings ON tblInmateCases.CaseNumberID = tblInmateHearings.CaseNumberID) ON tblInmatesProfile.InmateID = tblInmateCases.InmateID
GROUP BY tblInmateCases.BR, tblInmateHearings.Hearings
HAVING (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

With the helpfull link provided by John Big Bootyto Totals query page explaining about groping and things in queries....

Still trying to extract selected values from one single select list box and one multi select list box and get it into one string or populate a tempRecordset....working on it

 

[vbaInet]

Well hi there again feels almost like i should put you on a retainer....

Sadly, I'm not affordable

With the helpfull link provided by John Big Bootyto Totals query page explaining about groping and things in queries....

Thanks to John ay!

Still trying to extract selected values from one single select list box and one multi select list box and get it into one string or populate a tempRecordset....working on it

It looks like you understood the idea quite alright so well done for coming thus far.

For the final bit:

WHERE BranchID = 18 AND EscortID IN (2, 4, 6)

That is how the Where part should look like in the end.

With regards having to re-structure the concatenated string, you don't need to. Concatenate the list of EscortIDs in the Click event of the button that will open the report then, using the WHERE parameter of the OpenReport method, set it like so:

... Concatenate list here first ...

DoCmd.OpenReport "ReportName", , , "[BranchID] = " & Me.BranchesListbox.Value & " AND EscortID IN (" & strConcatenatedList & ")"

 

[zozew]

Hmmm....let me just show you first how far i've gotten before i ask you about your suggestions...

GUI operation:
Well first you select a date, then you click "update branch list" ( cant get the dateChoser to do a form refresh on afterupdate...) then you can click "Preview Hearings schedule" to see the report without assigned Escorts. Then you select Branch then you can multiSelect from the Escorts and then you click "Assign Escorts to Branch" and it populates the lower List value List box. All that does is it fills up the Lower List box (locked) with the strings created by the two upper List boxes. This is just to give the user overview before he creates the report. Then you are supposed to click "View Hearings schedule with escorts" to preview the Report with assigned Escorts.

So to your suggestions....

WHERE BranchID = 18 AND EscortID IN (2, 4, 6)

Im kinda following you here...

... Concatenate list here first ...

DoCmd.OpenReport "ReportName", , , "[BranchID] = " & Me.BranchesListbox.Value & " AND EscortID IN (" & strConcatenatedList & ")"

If im not mistaken this would only get the escorts from one Branch...

The user when he Clicks the "View Hearings schedule with escorts" button will have all the Branches assigned Escorts in the lower list box (sorry i didnt show/explain that part)

....so should i make one SQL string per Branch or combine all within the WHERE...is that possible.....

So how would i change your code suggestion to get all branches listed in the Lower List box (can be up to 12 depending on the date) into the report

 

[vbaInet]

Right, so there could be multiple branches? It will look like this:

WHERE (BranchID = 18 AND EscortID IN (2, 4, 6)) OR (BranchID = 16 AND EscortID IN (1, 5, 3)) OR ...

The listbox at the bottom will not work for Escorts because if a user selects more than 20, then there will be a long horizontal scroll. So I would say, just count how many Escort selections were made and display it in the list. Or maybe restrict them to be able to choose a certain max number of Escorts per Branch.

Save the Escort selections into the second column of the listbox and set its column width to zero so that you can use this data in your report and also you can use it to rehighlight the selections if a user wants to make changes to a particular Branch selection.

 

[zozew]

The listbox at the bottom will not work for Escorts because if a user selects more than 20, then there will be a long horizontal scroll.

Ok i checked with the "jail" and there will never be more then 4 Escorts per Branch that's why i have four colums for escorts. And Right now i have some functionality in the ListBoxes so if you select a branch you already Assigned Escorts for it replaces that item in the lower Listbox, just did a:

Private Sub AssignEscButton_Click()
Dim intI As Integer
Dim escortsAssigned As String
Dim varItm As Variant
Dim rowCount As Integer
'*******************************
' Build the Escorts string
'*******************************
escortsAssigned = branchListBox.Value & ", "
intI = 1
For Each varItm In Me.BjmpEscortsListBox.ItemsSelected
    If intI = 1 Then
        escortsAssigned = escortsAssigned & Me.BjmpEscortsListBox.ItemData(varItm)
    Else
        escortsAssigned = escortsAssigned & ", " & Me.BjmpEscortsListBox.ItemData(varItm)
    End If
    intI = intI + 1
Next
'*******************************
' Populate Assigned Escorts List box
'*******************************
rowCount = AssignedEscortsListBox.ListCount
If rowCount <> 0 Then
    For intI = 0 To rowCount - 1
        ' Check and see if there already is a value for that branch, remove and replace
        If AssignedEscortsListBox.Column(0, intI) = branchListBox.Value Then
            AssignedEscortsListBox.RemoveItem (intI)
        End If
    Next
    AssignedEscortsListBox.AddItem (escortsAssigned)
Else
AssignedEscortsListBox.AddItem (escortsAssigned)
End If
'*******************************
'Deselect all selected Escorts after assigned
'*******************************
For Each varItm In Me.BjmpEscortsListBox.ItemsSelected
    Me.BjmpEscortsListBox.Selected(varItm) = False
Next
End Sub

On the assign button....feels kinda natural...only quirk is that i cant figure out (yet) how to sort the List box on the value of the Branch...looks a little stupid when you replace an item and it gets added to the bottom of the list ( tried adding in the index etc, but ill figure it out...minor cosmetics)

Do you still feel its not possible if there is a max of 4 Escorts....or do you just not recomend that soloution..?

The items in the Lower list box consist of Branch Number and then the Escort strings, I thought it would simplify if the items had the branch number first in the string so when reffering to the Escorts I could use the number of the Branch in the refference....maybe im just complicating things...

Q1:
Do you mean i should only Display the selected Escorts for one Branch in the List box at a time?

Q2:
So if i understand it, you are building a "kinda" recordSet with the SQL string in the DoCmd.openReport and then use that in the report....What im having trouble figuring out is how assign the Escort string into the designview of the report....or am i thinking of it in the wrong way:

I should put a textBox in the "details header" under the escorts right?? Im still trying to figure out how the report part works, havent really played around with it to much....do you have any good link that could cater to my type of problem or needed soloution?

Sorry to be draging you in to all the details of this...if its to much, can you referr me to somewhere i can read my self to it

 

[vbaInet]

Ok i checked with the "jail" and there will never be more then 4 Escorts per Branch that's why i have four colums for escorts. And Right now i have some functionality in the ListBoxes so if you select a branch you already Assigned Escorts for it replaces that item in the lower Listbox, just did a:

Do you still feel its not possible if there is a max of 4 Escorts....or do you just not recomend that soloution..?

On the assign button....feels kinda natural...only quirk is that i cant figure out (yet) how to sort the List box on the value of the Branch...looks a little stupid when you replace an item and it gets added to the bottom of the list ( tried adding in the index etc, but ill figure it out...minor cosmetics

If the "jail" says there will never be a max of 4 then it's fine. Even if there are going to be 10, it's still fine. What will happen is the horizontal scroll bar will become visible.

So, don't go trying to replace those already selected, just:
1. Count how many records there are in the Assigned listbox
2. If it's greater than 0
....2a Loop through the listbox and delete the row (i.e. RemoveItem) where BranchID is equal to the currently selected BranchID.
....2b During this process if you find a BranchID save the Row Index into an Integer variable, if you don't find a BranchID make sure you save the Row Index whose BranchID is just greater than the currently selected BranchID in the Branches Selection listbox. The Row Index variable will ensure that your list is sorted when you use it in the AddItem method (i.e. one of the parameters of this method is to add an item in the specified row)
3. If it's zero just AddItem

Q1:
Do you mean i should only Display the selected Escorts for one Branch in the List box at a time?

Do it as you're doing it at the mo but one of the columns in the Assigned listbox should include a concatenated string of the EscortIDs - i.e. like this 1, 2, 4

Q2:
So if i understand it, you are building a "kinda" recordSet with the SQL string in the DoCmd.openReport and then use that in the report....What im having trouble figuring out is how assign the Escort string into the designview of the report....or am i thinking of it in the wrong way:

I should put a textBox in the "details header" under the escorts right?? Im still trying to figure out how the report part works, havent really played around with it to much....do you have any good link that could cater to my type of problem or needed soloution?

You're building the WHERE part of the SQL string so you can apply it to the WHERE argument of the OpenReport command like so:

DoCmd.OpenReport "ReportName", , , "(BranchID = 18 AND EscortID IN (2, 4, 6)) OR (BranchID = 16 AND EscortID IN (1, 5, 3)) OR ..."

So when you click the View Schedules button you will need to loop through the Assigned listbox and concatenate it as above. It is probably now apparent why I mentioned you should save the EscortIDs in a hidden column in the Assigned listbox. You can also see the WHERE part of the OpenReport command - it is the 4th parameter. That will filter your report before it opens.

 

[zozew]

I mentioned you should save the EscortIDs in a hidden column in the Assigned listbox.

I've go the EscortID string waiting now to be put in a hidden column....but....

I've been going crazy over this....first off if you addItem and its a string that has comma separated words to a multi column List box they (the words) get automatically put in different columns (ok i can live with that) but I've been searching and searching on how to edit a List box's item in a specific column ( so i can add a string to the hidden column, column no.5) but no where is it explained....am I missing something here or am i just pure stupid?? I can referr to a specific column and get the value but not set it.....

I found somewhere ListBox.addItem.Column(colNum, rowNum) but i get an object required error on that....im going mad i think.....

 

[zozew]

ok i got the "comma" problem, just adding the single ' will make the string go into one column, but i cant still figure out how to ad a value to a specific column in an existing item in a multicolumn list box....

I did a workaround adding a second List box with one column where i add the EscortsID string with the same function as the Lower List box displaying the names....only this one displays all in one string branchID, escortID1, escortID2, etc...

 

[zozew]

Ok....uhhha finally....i've managed to make the "WHERE" string and it all looks very nice....but...here i go again...

Q:
I still don't understand how i will be able to extract the names of the selected Escorts and populate the right field in the report...sorry maybe im just a bit thick..


Record source of the Report a Query called "schedule Query":

SELECT DISTINCT tblInmatesProfile.MiddleName, tblInmatesProfile.FirstName, tblInmatesProfile.LastName, tblInmateCases.BR, tblInmateHearings.Hearings, tblInmateCases.caseNumber, tblInmatesProfile.CellLocation
FROM tblInmatesProfile INNER JOIN (tblInmateCases INNER JOIN tblInmateHearings ON tblInmateCases.CaseNumberID = tblInmateHearings.CaseNumberID) ON tblInmatesProfile.InmateID = tblInmateCases.InmateID
WHERE (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

The WHERE string i made from some selections in the schedForm:

(BranchID = 14 AND EscortID IN (2, 4, 6)) OR (BranchID = 16 AND EscortID IN (2, 4, 8)) OR (BranchID = 18 AND EscortID IN (3, 6)) OR (BranchID = 20 AND EscortID IN (1, 2))

I understand i have to change the "BranchID" name to mach the Query's "tblInmateCases.BR" name, but from where do i get the names of the Escorts....(ofcourse the escorts table ) but how do i add that to the query??

 

[vbaInet]

You've got the EscortIDs, what do you need the names for

Your questions: How to populate the right field in the report? How to add that to the query?

Maybe I should quote myself from my last post:

You're building the WHERE part of the SQL string so you can apply it to the WHERE argument of the OpenReport command like so:

DoCmd.OpenReport "ReportName", , , "(BranchID = 18 AND EscortID IN (2, 4, 6)) OR (BranchID = 16 AND EscortID IN (1, 5, 3)) OR ..."

You can also see the WHERE part of the OpenReport command - it is the 4th parameter. That will filter your report before it opens.

The report must include the EscortID field in its record source.

 

[zozew]

You've got the EscortIDs, what do you need the names for.

Well when the report is printed and they us it gather up all the inmates in the morning to head off to the different branches, the person responsible for telling which Escorts go to what branches will have a hard time figuring out what Escort is Number 3 or 4 or 5. So having the name printed out on the report would make it a lot easier.

And about getting the EscortId into the report source...i guess i would have to get the Escort names as well if i want to make it work right....

 

[vbaInet]

I thought since you have built a couple of reports getting the name of the Escort into your report would be a piece of cake. I can see in the query you posted that you followed a similar fashion to get related field values from other tables via a join. This is basic stuff if you think about it.

 

[zozew]

This is basic stuff if you think about it.

You know you are right, tomorrow ill give it my best shot but don't sleigh me down if I'm back here late tomorrow asking again....how the heck i get one Query for the Inmates plus one user defined "selection" to look up values in another table and then lay-outed nicely in my report..

Anyway wish me luck and pray I get it haha

 

[vbaInet]

Think about how you join two tables together via an ID to pull related fields from either tables.

If you have any problems just post back. I will be praying for your redemption from this problem

 

[zozew]

hmm...well Ive been thinking and scratching my head now, have a bald batch to prove it

I have no problems joining queries but how do i join a Table that has no linkage to anything else (and really cant be linked). The Escorts table is totally separated from anything else (just a list of Names). The linkage is made when the user chooses escorts for each branch in the schedForm, then the link between BranchID and Escorts is made. So the data I want to use is held in a List box in a form (for now). And the data is not linked to each record of the Reports Query, rather the Grouping of the BranchID.

So as you suggested before....when the Report is "opening" i need to loop through the data in the List box and there somehow link the List box's BranchId to the Reports BranchID field.

But then im thinking how can i link that....The reports record source is a query with (lets simplify it) Names and BranchID for a specific date, there can be any amount of Names per BranchID and the date comes from the schedForm's datePicker. So......

I cant link it in the Reports query as the Escort picking isn't done yet so it has do be done when clicking the button "View schedule hearing with escorts" in the schedForm like you suggested.

So the result im trying to get is a Report with results from the Query (Names & BranchID) with the User's Picked Date & Escorts (from the schedForm) Displayed either the same Row as the BR value (blue square with the branchID) comma separated (on the dotted line) or one escort (if more then one) on each dotted line under a BranchID grouping.

So Im struggling with an Elephant here...

Getting the data (picked escorts) into the Reports Query result formatted so it Joins the right Group (BranchID) and the right Records and with the right Escorts per branchID, i presume i have to do that in the Query's SQL as its the record Source of the Report...but how

Well i can see from this that im in over my head, in a for me complex problem, as my knowledge of SQL syntax and manipulating comes from a weeks browsing in SQL for dummies

What i would like to know is, is the solution to my problem as basic as you mentioned before or is it a little more complex and of course if you somehow can point me in the right direction based on my vast SQL knowledge

thx for entertaining my long explanations....or even managing to read through them