Full Version: How Can I Separate Form List Into 2 Columns?
UtterAccess Forums > Microsoft® Access > Access Forms
Jrayhan
Hello UA, Good Evening,
I am working on a project where i have total of 30 clients in the tblClient table. I have to create a form where user will be able to select the clients and see the report for selected clients ONLY. So i decided to run this from the ClientTable. Once in a while we add new clients. So i want that form to add a new check box for the new client automatically so that we dont have to do it manually all the time when a new client show up.. So i created a query with a checkbox column. But the list is really long. Like i said, 30 clients. I was wonder how can i separate this form list to two rows by have 15 clients in each side. Or is there a better way to do this?
Please help :/
ipisors
To address only the splitting of views in list or comboboxes from a recordsource with unknown number of records:
Have two listboxes or comboboxes to list your "too-long" vertical list.
Run some code like this:
CODE
dim lngTotal as long
dim lngTheRest as long
lngTotal=dcount("*","TableNameofClients")
lngTheRest=lngTotal-15
Me.Listbox1.Rowsource="Select top 15 * from TableNameofClients"
Me.Listbox2.Rowsource="Select top " & lngTheRest & " * from TableNameofClients"

What this is basically doing is saying: Show the first 15 records from the table in the one listbox, and then show however many else there are, in the other listbox.
You'll probably want to take other things into account, like ORDER BY clause to control how they show up in the TOP queries. But that may give you an idea.
HTH
Bob G
so instead of having a listbox that they can scroll thru you would rather have a fixed number that they can see all the time without scrolling ?
Jrayhan
Is it possible to have check boxs inside a list box? Can you please explain more?
Jrayhan
Okay But how can i add the check box next to each of the clients name? I have the check box (Yes/No) field from the table but when i add to the form it shows Yes o
doctor9
Jrayhan,
orget checkboxes! This sounds like a job for... a multi-select listbox control!
(Yeah, not as dramatic as it could have been, I grant you.) <
Add an unbound listbox control to your form, and use the wizard to set it up to read the ClientID (or whatever you've named your client table's primary key field) and Client name from the table, displaying only the client name.
Set the Multi Select property to "Simple". (This property is found on the "Other" tab of the Property Sheet.)
Then, for the command button that opens the report, you need to loop through the listbox's items, and concatenate a comma-separated list of the ClientID values. For example, if the user selects "Bob Smith", "Joe Jones" and "Greg Gables", you would create a list like "3, 12, 8" because those would be the ClientID values for those three clients.
Once this list is created, open the report, using an argument like this for the WhereCondition argument:
ClientID In( 3, 12, 8)
The In() function allows you to have a comma-separated list of values, which simplifies things. It's much better than "ClientID = 3 OR ClientID = 12 OR ClientID = 8".
After a bit of searching, I found this article in our Code Archive that should help you out.
Hope this helps,
Dennis
ipisors
Jayrahn,
I was going to reply with pretty much exactly what Dennis said.
And I now echo:
Agreed.
Checkboxes are nice but... for a multi select list, multi select listboxes are a great tool and is what I would recommend.
And I do NOT recommend using checkbox-type table fields, truly, at the table level. This may not have any "direct" affect on performance, but it definitely has a "confusion" effect on coding and querying, and greatly muddles the principle of keeping the data and presentation SEPARATE in an Access database. There is no reason why not to use Text or Number at table level, and code to assign values to it using Form checkboxes.
Anyway, multi select listboxes are wonderful, I use them A LOT.
Jrayhan
Okay! This is something i can do as well! But can you please explain me this part where you said
Odid the 1st steps you suggested and i can select more than one clients. So making improvements. Just need to understand the part where i have to give them a value?? like numbers?
doctor9
Jrayhan,
Yan,
Your table of clients should include an Autonumber Primary Key field as well as a text field (or more) that contain the client's actual name. The autonumber field is your best bet for uniquely identifying a client to Access.
Your report should include this primary key field as part of it's recordsource as well.
It's much better to tell Access to generate a report that is filtered to "Client IDs 3, 12 and 8" rather than for "Client Names 'Bob Smith', 'Joe Jones' and 'Greg Gables'."
What are the fields in your Clients table?
Hope this helps,
Dennis
Jrayhan
OH! now i got what u mean. Yes! I do have a unique ID number field and it gives each of the clients an uniqueID Number. In my client table i have Client name and client ID number and some other fields related to job and volume. But I believe for this issue i would only need ID number and client name. But i am wonder how is the loop code going to look like. Any help on that?
doctor9
Jrayhan,
You should base your code on the example found in our Code Archive. I provided you a direct link to the article. It includes VBA code that should be easy to adapt to your situation. If you get stuck, post what you have so far, what has you puzzled, and we'll go from there.
Hope this helps,
Dennis
doctor9
Jrayhan,
Please re-post your private message to me HERE, as that information should be part of the public forum discussion. If we try solving issues using private messaging, then this forum really has no purpose. The idea is that in the future, other people with a similar question will find this discussion, and read how we solved the problem, and apply that solution to their problem.
Thanks,
Dennis
Jrayhan
I have followed the steps from the link you have provided me and created a unbound textbox. made it invisible. The link you gave me to get the code, i looked at it. But i got confused at the part where it says,
http://www.utteraccess.com/forum/Multi-Sel...x-C-t95097.html
CODE
txtCriteria = "[CriteriaFieldName] = "

What should go in there? List box has the clients name. So do i add the client name field?
doctor9
Jrayhan,
You need to create a comma-separated list of Client ID values. You aren't creating a list like in that code. So, as you adapt that code to your purposes, just use the bits you need. For example:
CODE
For Each varItem In Me.List0.ItemsSelected

This shows how you loop through the selected items in a listbox named "List0". You replace "List0" with the name of your listbox to adapt it to your needs.
Next:
CODE
txtTemp = txtTemp & txtCriteria & "'" & Me.List0.ItemData(varItem) & "'" & " Or "

Since you are creating a comma-separated list of numbers, you would change this to fit what you are doing:
CODE
txtTemp = txtTemp & Me.List0.ItemData(varItem) & ", "

Again, replace "List0" with your listbox's name. This should create a list like "3, 12, 8, ". When you exit the loop, you need to remove the final comma, so you would use something like:
CODE
txtTemp = Left(txtTemp, Len(txtTemp)-2)

And so on.
Hope this helps,
Dennis
Jrayhan
This is exactly how i have mine. It is capturing the data but on other hand when it tried to open the Report Form, in the code its keep getting stuck in here and giving me an error message.Not sure why this is happening.
ocus of the tracing bar stays on this line of the code :
CODE
DoCmd.openReport stDocName, , , stLinkCriteria

Click to view attachment
doctor9
Jrayhan,
Please just copy/paste all of the code. A screenshot of VBA code is difficult to work with.
Thanks,
Dennis
Jrayhan
This code i have added on the Afterupdate property of the List box. Name of the invisable unbound text box is "ClientListInfo". I have created a query out of main table and under the criteria of the client table i referenced the invisible text box.
!--c1-->
CODE
Private Sub ClientListBox_AfterUpdate()
Dim varItem As Variant
Dim txtTemp As String
Dim txtCriteria As String
txtCriteria = "[Client]"
For Each varItem In Me.ClientListBox.ItemsSelected
txtTemp = txtTemp & Me.ClientListBox.ItemData(varItem) & ", "
Next
If Len(txtTemp) > 0 Then
txtTemp = Left(txtTemp, Len(txtTemp) - 4)
End If
Me.ClientListInfo = txtTemp
End Sub

Then using that query i create a report form. but its not showing any of the clients name i selected from the list or when i try to open it.
Last part of the code :
CODE
Private Sub Testbtn_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "rptRepCustom"
stLinkCriteria = Me![ClientListInfo]
DoCmd.OpenQuery "qryRepCustomTotal"
DoCmd.openReport stDocName, , , stLinkCriteria
End Sub
Jrayhan
I thought i found the problem and changed the
xtTemp = Left(txtTemp, Len(txtTemp)-4 ) To txtTemp = Left(txtTemp, Len(txtTemp)-2). It was cutting off the string name. but its okay now. On other hand its still giving me the same error.
doctor9
Remember, in Post #6 of this thread, I said the WHERE clause should look like this:
lientID In( 3, 12, 8)
So, your textbox should look something like that before you hit the button. Now, based on your posted code, it looks like your primary key field is named "Client". However, that's not very clear. Am I correct? Or is that the text field that contains the client's name?
Hope this helps,
Dennis
Jrayhan
Oh! Now i know what you ment earlier. I didnt quiet understand that part. Now i got it. As of now its showing name of the clients. How can i see the numbers instead? I added the Client ID field in the list box query. And in the code i changed the client to Client ID. its still showing the name instead of the number.
doctor9
Jrayhan,
That is the SQL for your listbox?
Dennis
Jrayhan
I am sure i have done something wrong in here. When i have one of them under where clause that row disappears.
SELECT qryCustomClients.[Client Name], qryCustomClients.[Client ID]
FROM qryCustomClients
GROUP BY qryCustomClients.[Client Name], qryCustomClients.[Client ID];
doctor9
Jrayhan,
You have the name listed first. Instead, you should have the Client ID first. I think with that arrangement, the listbox item will store the number, while displaying the name. I'm assuming your column widths are something like 3";0" - those will need to be swapped as well.
Neither of these fields is named "Client", which I saw in your VBA code. This is why it's important that your field names be meaningful, so you can read your code a year from now and still understand what's happening.
I did you use the wizard when you placed the listbox control? Did it suggest hiding the numeric field?
hope this helps,
Dennis
Jrayhan
OKay, So i have to apologize for giving you wrong info from the beginning which I didnt even notice. I though each of the clients have a unique ID but turned out Same client have more than One unique ID. So in this situation i think i am going to work with the clients name. but not the ID number. I have done it already. It shows the clients name in the hidden text box when i select them. But for some reason its not pulling any records.
This is how the textbox look like. And in the query i added it under client name criteria field. But i am getting no respond.
Click to view attachment
and this is the query and its criteria
Click to view attachment
doctor9
JRayhan,
!--c1-->
CODE
I though each of the clients have a unique ID but turned out Same client have more than One unique ID.

This is why I recommended using the Primary Key value from your Clients table. In order for a database to work properly, each Client should be able to be uniquely identified by an Autonumber primary key value. Now, if you have some sort of company-generated ID number set that can have multiple values assigned to a single person, that's fine. Those just need to be stored in a separate table from the Clients table. But you need to be able to uniquely identify a client by an ID, NOT by their name. Names can change, whether through marriage, or other legal matters. Names can also be duplicated, even if they aren't blood related.
Ocan't stress this enough. If your data tables aren't set up properly, then problems like this are only going to get worse and worse.
Now, this is the second time I've asked this: What are the fields in your Clients table? And now I need to also ask, how are you handling multiple ID values for one client?
Hope this helps,
Dennis
Jrayhan
Okay, So now i just went back to the main client table and added a Client ID field and assigned Unique ID number for each of them. Since there's not much clients i was able to do it easily.
On my client table i have , Client ID, Client Name , Category, Client avtive, client status date and volume entry.
doctor9
Jrayhan,
ow, base the listbox on the Clients table. At this point, you might want to just delete the old listbox and start a new one. That way you can use the wizard where you select the Primary Key field and the name field. The wizard should recommend hiding the primary key field - do so.
Once the listbox is set up properly, make sure your new primary key field is included in the recordsource for the report.
By the way, since your query already had a "Client ID" field according to previous posts, I'm concerned that you may be creating duplicate field names. You should really make sure your data table structure is properly Normalized before you create even the simplest forms and reports. This isn't a casual recommendation. This is a crucial part of every database, and should not be taken lightly.
Hope this helps,
Dennis
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.