[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create Access Check Box Pick List Form

Posted on 2010-01-08
25
Medium Priority
?
1,801 Views
Last Modified: 2013-11-28
I have an Access 2000 Report which displays data from MS SQL Tables based on a DATE Parameter entered into a form which appears when the end user runs the report from within Access.

What I need to do is to expand on this existing [or start from scratch] process and add an additional step where the end user will be able to select which data is displayed on the form, rather than all data based on the date.

The logic would be:

1. Run the report
2. Prompt user for Date Parameter [single date not range]
3. Prompt user with a form which displays all 'routes' for that date. Each route will have a check box next to it. Once the end user selects all the desired routes using the check boxes, they will click a button which will:
4. Display the report as defined by the parameters above

I have the report design [as it was given to me].
I have the query which displays ALL the 'routes' data in the report.
I know which server, db, and tables will be used.

I am not sure how to tie it all together, especially the check box form [which has yet to be created].
SELECT ROUTE_SETS.ROUTING_DATE, ROUTES.ROUTE_NAME, DRIVER.NAME, DRIVER_1.NAME AS DRIVER2NAME, DRIVER_1.EMPLOYEENUMBER AS EMPLOYEENUMBER2, VEHICLE.NAME, VEHICLE.ODOMETER, CUSTOMER.WHOLE_ACCOUNT_ID, ORDERS.ORDER_ID, CUSTOMER.NAME, CUSTOMER.STREET_ADDRESS, CUSTOMER.PHONE_NUMBER, ORDERS.HW_OPEN_TIME, ORDERS.HW_CLOSE_TIME, ROUTE_STOPS.ARRIVAL_TIME, ROUTE_STOPS.DEPARTURE_TIME, CUSTOMER.STOP_ID, VEHICLE_1.NAME, VEHICLE.DRIVER_ID1, VEHICLE.VEHICLETYPE, VEHICLE_1.VEHICLETYPE, DRIVER.EMPLOYEENUMBER, ROUTE_STOPS.STOP_NUMBER, ORDERS.COMMENT_ROUTE_SHEET, ORDERS.ORDER_SIZE_VOLUME, ROUTES.DISPATCH_TIME, ROUTES.DELIVERY_VOLUME, VEHICLE_1.DRIVER_ID2, DRIVER.TEAMMATEID, ROUTES.DRIVER_ID2, ROUTES.VEHICLE_TYPE3, VEHICLE_2.NAME, CUSTOMER.NICKNAME, STOP.STOP_NAME, STOP.STREET_ADDRESS, STOP.CITY, STOP.STATE, STOP.COMMENTS, STOP.PHONE_NUMBER, STOP.ZIP
FROM (((((ROUTE_STOPS INNER JOIN (((VEHICLE INNER JOIN (ROUTE_SETS INNER JOIN ROUTES ON ROUTE_SETS.ROUTE_SET_INDEX = ROUTES.ROUTE_SET_INDEX) ON VEHICLE.VEHICLE_ID = ROUTES.VEHICLE_ID1) LEFT JOIN VEHICLE AS VEHICLE_1 ON ROUTES.VEHICLE_ID2 = VEHICLE_1.VEHICLE_ID) INNER JOIN DRIVER ON ROUTES.DRIVER_ID1 = DRIVER.INTERNALID) ON ROUTE_STOPS.ROUTE_INDEX = ROUTES.ROUTE_INDEX) INNER JOIN RSTOP_ORDER ON ROUTE_STOPS.STOP_INDEX = RSTOP_ORDER.STOP_INDEX) INNER JOIN (CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CUSTOMER_ID = ORDERS.CUSTOMER_ID) ON RSTOP_ORDER.ORDER_ID = ORDERS.ORDER_ID) LEFT JOIN DRIVER AS DRIVER_1 ON ROUTES.DRIVER_ID2 = DRIVER_1.INTERNALID) LEFT JOIN VEHICLE AS VEHICLE_2 ON ROUTES.VEHICLE_ID3 = VEHICLE_2.VEHICLE_ID) INNER JOIN STOP ON ROUTE_STOPS.STOP_ID = STOP.STOP_ID
GROUP BY ROUTE_SETS.ROUTING_DATE, ROUTES.ROUTE_NAME, DRIVER.NAME, DRIVER_1.NAME, DRIVER_1.EMPLOYEENUMBER, VEHICLE.NAME, VEHICLE.ODOMETER, CUSTOMER.WHOLE_ACCOUNT_ID, ORDERS.ORDER_ID, CUSTOMER.NAME, CUSTOMER.STREET_ADDRESS, CUSTOMER.PHONE_NUMBER, ORDERS.HW_OPEN_TIME, ORDERS.HW_CLOSE_TIME, ROUTE_STOPS.ARRIVAL_TIME, ROUTE_STOPS.DEPARTURE_TIME, CUSTOMER.STOP_ID, VEHICLE_1.NAME, VEHICLE.DRIVER_ID1, VEHICLE.VEHICLETYPE, VEHICLE_1.VEHICLETYPE, DRIVER.EMPLOYEENUMBER, ROUTE_STOPS.STOP_NUMBER, ORDERS.COMMENT_ROUTE_SHEET, ORDERS.ORDER_SIZE_VOLUME, ROUTES.DISPATCH_TIME, ROUTES.DELIVERY_VOLUME, VEHICLE_1.DRIVER_ID2, DRIVER.TEAMMATEID, ROUTES.DRIVER_ID2, ROUTES.VEHICLE_TYPE3, VEHICLE_2.NAME, CUSTOMER.NICKNAME, STOP.STOP_NAME, STOP.STREET_ADDRESS, STOP.CITY, STOP.STATE, STOP.COMMENTS, STOP.PHONE_NUMBER, STOP.ZIP
HAVING (((ROUTE_SETS.ROUTING_DATE)=[Enter Routing Date]) AND ((ROUTES.ROUTE_NAME)=[ENTER ROUTE_NAME]))
ORDER BY ROUTES.ROUTE_NAME;

Open in new window

1-8-2010-11-18-05-AM.png
1-8-2010-11-18-40-AM.png
1-8-2010-11-47-02-AM.png
0
Comment
Question by:jsvb1977
  • 13
  • 12
25 Comments
 
LVL 6

Expert Comment

by:csmart2301
ID: 26213044
I would build one form that has the date selection and checkbox selection on one form. then use that information to build a sql statment.

i am not sure however if you can use control arrays in access i think not
the coding may have to have more if statments in place of the select case
progtest.jpg
0
 

Author Comment

by:jsvb1977
ID: 26213596
Interesting. Not sure if this is EXACTLY what is needed, but it definitely is the right direction.

What I am trying to say is that the main SQL statement should remain in tact. the only variable that needs to be picked is the ROUTE NAME. If we can be able to select and then inject the multiple route names into the query that would be ideal.

Perhaps in the WHERE query?

Maybe like this:

[  ] Route Name 1
[x] Route Name 2
[x] Route Name 3
[  ] Route Name 4
[x] Route Name 5

query would then be:

SELECT....
FROM....
WHERE [ROUTE_NAME] = Route Name 2, Route Name 3, Route Name 5

=============================================

This is just an idea -- not sure if it is practical and i definitely am not confident I can do all this on my own. With a little more direction I think I can take a stab at it.

Thanks for the reply,
Jason

0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26213965
Still using the same sql statment you say sure however you still need a new form with check boxes then code like below to build a filter and call the report with its newly created filter.
it will let you keep your underlying sql and further filter it
'This is the code for the open report button on the selection form
'it is just an example from a form that i use in employee time clock database
Private Sub Command1_Click()
'build the filter as using the values of the check boxes on the form

If check1.Value = True Then
    myfilter = myfilter & " OR "
    myfilter = myfilter & "([ID]=4)"
End If

If check2.Value = True Then
    myfilter = myfilter & " OR "
    myfilter = myfilter & "([id]=5)"
End If

'... so on and so forth
myfilter = Mid(myfilter, 4, Len(myfilter)) 'removes the first OR

' would build something like this
myfilter = "([ID]=4) or ([id]=5)"

    DoCmd.OpenReport "Employee", acViewReport, "", myfilter, acNormal

End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jsvb1977
ID: 26287822
cool thanks -- i will try it and report back to you with my successes and/or failures.

Jason
0
 

Author Comment

by:jsvb1977
ID: 26297662
I have attempted to adapt your code suggestion to my own access db without success. Maybe you could nudge me in the right direction here.

What is the first step to making this modification? Is it to create a whole new form?

If not, how do I get to the point where I can add/edit custom code in vb?

I have attached the db for review. Because the db requires ODBC Connections and is filled with linked tables, it will not run in another environment. But maybe, this will help you assist me in making the mods by myself on my end.

It goes without saying that I could really use some hand holding here. I am not asking you to do it for me, rather, just show me the way [if you know what I mean].

Thank you,
Jason
ROADSHOW-RPT-SQL-.MDB
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26299864
Ok no problem however i am thinking that a multi select list box may be better for ROUTE_NAME how many route names are there? and do you plan on adding more in the future?
And yes it dose involve creating a form which i will work on however i will not be able to test it so you will have to do the fine tunning. Ok i will hopefully have something for you tonight.

Clint
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26300430
here goes it works on my end it will have to be modified. i added a junk table that should pull route name info from your odbc which as we know i did not have. so you'll have to change it our just change the data in the junk table if you don't have to many routes or upcoming addition they would have to match exactly to the info in the odbc.
also i added the form named form cut and paste the form from this testing database to your data base and everything should be ok.

i dont remember how to get to the code in access 2000 in 2007 there are a few ways
To work on code right click on a command button when in design view and select build event
 or select the event from the properties window.
Good luck
Clint

ROADSHOW-RPT-SQL-.MDB
0
 

Author Comment

by:jsvb1977
ID: 26303672
OK, nice work. A couple of items I couldnt work out on my own. Please note that what you see next is after I have integrated your form into my db and modified the source table from the junk table you made to be one of the linked tables in my db [for accurate data].

Please see the screen shots below and let me know if you have any questions about them. I would like to be able to help you help me by being more clear.

One of the items that needs to be addressed is the data field. If you remember from the code i originally posted, the [date] is being populated by the end user, which in turn filters out the routes from any other date.

I would like to filter this one further, as you have done, but allowing the end user to select multiple routes from the list you were able to generate, then run the report. I liked the idea of using check boxes, like you displayed in your first post. Not sure if that is better than a list box, though.

Anyway, I attached some screen shots to maybe help.

Thank you,
Jason
1-8-2010-11-18-05-AM.png
1-8-2010-11-18-40-AM.png
1-8-2010-11-47-02-AM.png
0
 

Author Comment

by:jsvb1977
ID: 26303679
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26304316
How Many Routes are there ie how many check boxes will there be it would be really hard possible but really hard to have the amount of check boxes grow and shrink that is why i went with a list box and sorry the list box select is set to simple however even in extended mode you would only be able to select ranges
ie
a.
b.
c.
d.
 selecting a and c would also give you b so that probably will not work.
So i am thinking twin list boxed one with the full list when you dubble click an item it
would fill a second List box like below.
newscreen.jpg
0
 

Author Comment

by:jsvb1977
ID: 26304459
Agreed. Your screen shot above will work. As for your question, there could be as many as 50+ routes in a day. The total number of routes will change daily, but will hover around 50.

It will be important to be able to pick "a" and "c" but not "b" -- so the double list box is a good idea. Check boxes were my idea because i didnt think of what you posted above.

So, did my previous post help? Should we prompt the user to enter a date before this form loads and pass along the date as a variable used to pre-filter the results before we populate that list box?

Thank you!
Jason
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26304779
I am at work right now and do not have time to write the code i will work on it tonight as for the error it should go away if you change
DoCmd.OpenReport "Delivery Instructions", acViewReport, "", myfilter, acNormal
 to
DoCmd.OpenReport "[Delivery Instructions]", acViewReport, "", myfilter, acNormal
i think the space in "Delivery Instructions" is causing the error so put brackets around it
if that does not fix the problem i will need to see the SQL statement that pops up in the Msgbox.
try that for now i will work on the new form a little later.

clint
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26309704
ok you will have to do the same changes also you will have to change the control source for list2 to include the correct table date reange and all that but it is working
ROADSHOW-RPT-SQL-.MDB
0
 

Author Comment

by:jsvb1977
ID: 26309719
sweet dude. i look forward to working on this in the morning. I will report back to you tomorrow.

Jason
0
 

Author Comment

by:jsvb1977
ID: 26313812
I think i have a problem with this query. I am attempting to adapt it to fit my actual tables. trouble is, the routing_date is located on a different table than the route_name.

How do i write this query so that it hits both tables? will i need to use a join of some kind?

I am modifying the query in the Row Source Box in the List Box: List2 Properties. Check out the screen shot above which shows the relationships of all the tables, you will see what I mean.

Also, even when running your code before i modify it, i get this error [i get this error also after i make my own mods].

What I have so far is listed below:

Thanks,
Jason
SELECT DISTINCT [ROUTE_NAME], [ROUTING_DATE] FROM ROUTES WHERE (((ROUTING_DATE)=[text0])) ORDER BY [ROUTE_NAME];

Open in new window

1-14-2010-8-37-35-AM.png
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26316118
In Microsoft visual basic code window when the error happens press the little square stop macro button that is right under tools and add-ins once the program has stopped  press tools then select references make sure that the checked ones match here if not you may be missing a dll file let me know and yes an inner join will have to be created or just make a query that joins the two fields then go in to sql view steal the statement and paste it whare you need it the make the additions for the text0.

If you are missing or one that is checked says something else then unckeck it restart your computer and try the code again.
references.jpg
0
 

Author Comment

by:jsvb1977
ID: 26317839
I think i am making progress. Here is the screen shot of the references. There is one difference and that is the Microsoft Access 12.0 Object Library. I am running 9. Think that could be the issue?

Check out the screen shot and let me know your thoughts.

If we can get this Compile Error worked out, i will show you the progress i have made and what i had to do to get it to work. Im not going to lie -- it was a lot of trial and error - more so than knowledge [and i am ok with that]

thanks,
Jason
1-14-2010-8-37-35-AM.png
0
 

Author Comment

by:jsvb1977
ID: 26317846
i did it again. here is the image i meant to upload....
1-14-2010-1-28-17-PM.png
0
 

Author Comment

by:jsvb1977
ID: 26317903
FYI -- No Compiler Error when running this in Access 2007 [which btw is not an option because my end users only use Access 2000].

Jason
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26319111
uncheck the utility reboot the computer then try again after you get it working you should be able to recheck the utility ref then every thing should work fine
what is utility ref an add-in if everything still works the i wouldn't re check it.
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26320148
this snipit about utility seems usefull

"I have a major problem. We have several hundred Access databases. All have a
reference to utility.mda. Since this is no longer a part of Access we have a
problem. Every database doesn't function properly. Removing the reference
seems to solve the problem."

it seems that utility.mda is only needed on the computer that don't have 2007 it is also i good idea to make sure you utility.mda version is up to date. remember you can add it back if need be but a system shutdown is needed to make any changes to the refs.
0
 

Author Comment

by:jsvb1977
ID: 26323295
unfortunately, remove the reference to utility.mda then restarting the cpu did not change anything. I am still getting that error using access 2000 on my machine.

i also have access 2007 installed on my machine.
the final product will be run from a work station that has only access 2000 on it.
Not sure what to do at this point.

Jason
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26323498
First let’s get it working in 2007 then save down to 2000 this option will include any references libraries that will be needed.
begin by un-checking all of the ref's reboot then add in only the ones that are listed in the picture above. This problem is caused by conflicts in the reference library. It is usually a simple problem to fix however I have not looked to see where the .add function is located within the libraries. I will do this as a next step.


also what windows are you running if you are running xp or newer install update
Office XP Service Pack 3 (SP3) - Office XP Resource Kit ...
at
http://office.microsoft.com/en-us/orkXP/HA011525351033.aspx
this will upgrade from 9 to 12 also
also keep in mind .add function was around before access 2000 so it will work not to worry, but it will be easier to get it running in 2007 the save backwords. caus- i do not have 2000 installed any more.
0
 
LVL 6

Expert Comment

by:csmart2301
ID: 26323582
i think i just found the problem and you are correct access 2000 did not have the .add function so i am going to build one hold on for a little while.
0
 
LVL 6

Accepted Solution

by:
csmart2301 earned 2000 total points
ID: 26324068
open the code for the add item button and select all this will include the other buttons and replace with the following. this should work with access 2000. but i donot have it so we may have to do a remote session to get the buggs worked out. in such a case i will create us each temporay email addresses so we can better share information.
Option Compare Database

Private Sub Command10_Click()

Dim strItems As String
Dim intCurrentRow As Integer
For intCurrentRow = 0 To List6.ListCount - 1
    If List6.Selected(intCurrentRow) Then
        Listbox_RemoveItem List6, (intCurrentRow)
        'List6.RemoveItem (intCurrentRow)
        Exit Sub
    End If
Next intCurrentRow
End Sub

Private Sub Command11_Click()
Listbox_Clear List6

'removed the following
'Dim strItems As String
'Dim intCurrentRow As Integer
'For intCurrentRow = List6.ListCount - 1 To 0 Step -1
'        List6.RemoveItem (intCurrentRow)
'Next intCurrentRow
End Sub

Private Sub Command4_Click()
    DoCmd.Close
End Sub

Private Sub Command5_Click()
Dim myfilter As String
Dim inlist As String

'This is the section that may have to be edited
  If IsNull(Text0) Or List6.ListCount < 1 Then
    MsgBox "Missing date or route selection."
    Exit Sub
  End If
  Dim strItems As String
Dim intCurrentRow As Integer
For intCurrentRow = 0 To List6.ListCount - 1
inlist = inlist & "[ROUTES.ROUTE_NAME]=#'" & List6.ItemData(intCurrentRow) & "' OR "
Next intCurrentRow
inlist = Mid(inlist, 1, Len(inlist) - 3) ' section not needed ??? --->& "[ROUTE_SETS.ROUTING_DATE]=#" & Text0 & "#"
 'remove the next line once the code is working
MsgBox inlist
 ' old myfilter = "[ROUTE_SETS.ROUTING_DATE]=#" & Text0 & "# AND [ROUTES.ROUTE_NAME]='" & List2 & "'"
 ' this line opens the filtered report
     DoCmd.OpenReport "Delivery Instructions", acViewReport, "", myfilter, acNormal

End Sub

Private Sub Command8_Click()
Dim strItems As String
Dim intCurrentRow As Integer
For intCurrentRow = 0 To List2.ListCount - 1
    If List2.Selected(intCurrentRow) Then
        Listbox_AddItem List6, List2.Column(0, intCurrentRow), False
       ' replaced this line with the one above
       ' List6.AddItem List2.Column(0, intCurrentRow), List6.ListCount
    End If
Next intCurrentRow
End Sub

Private Sub List2_DblClick(Cancel As Integer)
Command8_Click
End Sub

Private Sub Text0_LostFocus()
'Changes the list2 data to reflect the date in text0
List2.Requery
End Sub
'************************************Added Section

'This is equivalent to ListBox1.Clear
Sub Listbox_Clear(lbx As ListBox)
    lbx.RowSource = ""
    lbx.RowSourceType = "Value List"
End Sub
Sub Listbox_AddItem(lbx As ListBox, NewValue As String, Optional Sorted = False)
    Dim xList() As String
    Dim Count As Integer
    Dim I As Integer
    Dim J As Integer
    Dim IdxPtr As Integer
    Count = lbx.ListCount
    ReDim xList(Count + 1) As String
    For I = 1 To Count
      xList(I) = lbx.ItemData(I - 1)
    Next
    xList(Count + 1) = NewValue
    If Sorted Then
      For I = 1 To Count
        For J = I + 1 To Count + 1
          If xList(I) > xList(J) Then
            SwapStr xList(I), xList(J)
          End If
        Next
      Next
    End If
    On Local Error Resume Next
    lbx.RowSource = ListedItems(xList())
 End Sub
Sub Listbox_RemoveItem(lbx As ListBox, Index As Integer)
    Dim xList() As String
    Dim Count As Integer
    Dim I As Integer
    Dim IDX As Integer
    On Local Error GoTo ERR_Listbox_RemoveItem
    Count = lbx.ListCount
    ReDim xList(Count - 1) As String
    IDX = 0
    For I = 1 To Count
      If (I - 1) <> Index Then
        IDX = IDX + 1: xList(IDX) = lbx.ItemData(I - 1)
      End If
    Next
    lbx.RowSource = ListedItems(xList())
    If Index + 1 > lbx.ListCount Then
      lbx.ListIndex = Index - 1
    Else
      lbx.ListIndex = Index
    End If
ERR_Listbox_RemoveItem:
End Sub
Private Function ListedItems(Lists() As String)
    Dim HiEnd As Integer
    Dim NewList As String
    Dim I As Integer
    HiEnd = UBound(Lists)
    NewList = ""
    For I = 1 To HiEnd
      NewList = NewList & Lists(I) & ";"
    Next
    ListedItems = NewList
End Function
Private Sub SwapStr(Tx1 As String, Tx2 As String)
    Dim Tx3 As String
    Tx3 = Tx1
    Tx1 = Tx2
    Tx2 = Tx3
End Sub

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 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