By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

problem with 2 text boxes and 2 list boxes

P: 48
I am using Access 2007 and I have two list boxes where the user is suppose to type a range of dates.
Then I have a list box so the user can select wheter he/she wants to pull letters or proposals.
Finally I have a list box to see what report the user wants to open (date document was received, deadline, date when the doc. was replied).

I want the user to be able to type the dates on the textboxes select a report and type of document and a report will be generated with those choosen fields.

Do someone has an example code for this situation, I need it badly.

Thanks in advance.
Oct 15 '07 #1
Share this Question
Share on Google+
24 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Form1
txtStartDate
txtEndDate

Query1
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table1
  3. WHERE EventDate BETWEEN Forms!Form1!txtStartDate AND Forms!Form1!txtEndDate;
  4.  
Oct 15 '07 #2

P: 48
Form1
txtStartDate
txtEndDate

Query1
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table1
  3. WHERE EventDate BETWEEN Forms!Form1!txtStartDate AND Forms!Form1!txtEndDate;
  4.  

Thank you Sir;
However I am not too familiarize with sql code, is that all the code that I need? and how is that related with the first part. Do I only need to declare the text boxes for the first part?
Thanks again
Oct 16 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
Thank you Sir;
However I am not too familiarize with sql code, is that all the code that I need? and how is that related with the first part. Do I only need to declare the text boxes for the first part?
Thanks again
This is only an inkling of what you need. But it's enough to get you started on creating a report that uses a query that pulls its criteria from a form. Opening the report is a simple matter of running DoCmd.OpenReport from an event of your choosing.
Oct 16 '07 #4

P: 48
This is only an inkling of what you need. But it's enough to get you started on creating a report that uses a query that pulls its criteria from a form. Opening the report is a simple matter of running DoCmd.OpenReport from an event of your choosing.
Thank you sir. However can I do somehting like the code below using cases for a combo box?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command38_Click()
  2. Dim stDocName
  3. Dim stLinkCriteria
  4. Dim pstCriteria As String
  5.  
  6.  
  7. Select Case frmOptionX
  8.  
  9.     Case "DateReceived"
  10.         stDocName = "rptDateReceived"
  11.  
  12.         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  13.             pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
  14.  
  15.         DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
  16.  
  17.             Else
  18.                 MsgBox "You must enter two dates"
  19.  
  20.   End If
  21.  
  22.  
  23.     Case "DateRepliedOn"
  24.  
  25.         stDocName = "rptDateRepliedOn"
  26.  
  27.  
  28.           If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  29.               pstrCriteria = "[rptDateRepliedOn] >= # " & [txtFirstDate] & "# and [rptDateRepliedOn]<=#" & [txtEndDate] & "#"
  30.  
  31.             DoCmd.OpenReport "rptDateRepliedOn", acViewPreview, , pstrCriteria
  32.  
  33.         Else
  34.              MsgBox "You must enter two dates"
  35.  
  36.   End If
  37.  
  38.     Case "ReplyDeadline"
  39.  
  40.          If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  41.               pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"
  42.  
  43.             DoCmd.OpenReport "rptReplyDateline", acViewPreview, , pstrCriteria
  44.  
  45.         Else
  46.              MsgBox "You must enter two dates"
  47.  
  48.     End Select
  49.  
  50.  
  51. End Sub
  52.  
Thanks in advance
Oct 18 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
That could work but it would be easier just to do:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command38_Click()
  2. Dim stDocName
  3. Dim pstrCriteria As String
  4.  
  5.         stDocName = frmOptionX
  6.  
  7.         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  8.             pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
  9.  
  10.         DoCmd.OpenReport stDocName, acViewPreview, , pstrCriteria
  11.  
  12.             Else
  13.                 MsgBox "You must enter two dates"
  14.  
  15.   End If
  16. End Sub
  17.  
Thanks in advance[/quote]
Oct 18 '07 #6

P: 48
That could work but it would be easier just to do:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command38_Click()
  2. Dim stDocName
  3. Dim pstrCriteria As String
  4.  
  5.         stDocName = frmOptionX
  6.  
  7.         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  8.             pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
  9.  
  10.         DoCmd.OpenReport stDocName, acViewPreview, , pstrCriteria
  11.  
  12.             Else
  13.                 MsgBox "You must enter two dates"
  14.  
  15.   End If
  16. End Sub
  17.  
Thanks in advance
[/quote]

Thank you. This is great. However this will only work for one report since pstrCriteria = "[DateReceived]" will change for each report. For instance "[DateReceived] " will change for "RepliedOn" and "RepliedDeadline". How can I fix this small problem.

Thank you so much.
Oct 18 '07 #7

Rabbit
Expert Mod 10K+
P: 12,315
Oh, I didn't realize the fields were named differently. In that case, then yes, you would have to use the Case statement that you used earlier.
Oct 18 '07 #8

P: 48
Oh, I didn't realize the fields were named differently. In that case, then yes, you would have to use the Case statement that you used earlier.
Thank you. However, when I run the code below with the cases I get an error "End Select without Select Case." My question is, do i need to declare the case differently? or what am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command48_Click()
  2.  
  3. Dim stDocName
  4. Dim pstCriteria As String
  5.  
  6.  
  7. Select Case frmOptionX
  8.  
  9.     Case "DateReceived"
  10.         stDocName = "rptDateReceived"
  11.  
  12.         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  13.             pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#"
  14.  
  15.         DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
  16.  
  17.             Else
  18.                 MsgBox "You must enter two dates"
  19.  
  20.   End If
  21.  
  22.  
  23.     Case "DateRepliedOn"
  24.  
  25.         stDocName = "rptDateRepliedOn"
  26.  
  27.  
  28.           If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  29.               pstrCriteria = "[DateRepliedOn] >= # " & [txtFirstDate] & "# and [DateRepliedOn]<=#" & [txtEndDate] & "#"
  30.  
  31.             DoCmd.OpenReport "rptDateRepliedOn", acViewPreview, , pstrCriteria
  32.  
  33.         Else
  34.              MsgBox "You must enter two dates"
  35.  
  36.   End If
  37.  
  38.     Case "ReplyDeadline"
  39.  
  40.          If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  41.               pstrCriteria = "[ReplyDateline] >= # " & [txtFirstDate] & "# and [ReplyDateline]<=#" & [txtEndDate] & "#"
  42.  
  43.             DoCmd.OpenReport "rptReplyDateline", acViewPreview, , pstrCriteria
  44.  
  45.         Else
  46.              MsgBox "You must enter two dates"
  47.  
  48.     End Select
  49.  
  50.  
  51. End Sub
  52.  
Thank you!!
Oct 18 '07 #9

Rabbit
Expert Mod 10K+
P: 12,315
It looks like you fogot an End If on your last case.
Oct 18 '07 #10

P: 48
It looks like you fogot an End If on your last case.
Thank you very much, it is working now! One more question. If I want to add 2 checkboxes so the user can select a date, the drop down, and a check box and the report will be generated. Is there a way that I can add the check box to this code?
Thank you again.
Oct 19 '07 #11

Rabbit
Expert Mod 10K+
P: 12,315
Thank you very much, it is working now! One more question. If I want to add 2 checkboxes so the user can select a date, the drop down, and a check box and the report will be generated. Is there a way that I can add the check box to this code?
Thank you again.
Yes, how you do it depends on what those checkboxes represent. But there's no reason why you can't incorporate it into the code.
Oct 19 '07 #12

P: 48
Yes, how you do it depends on what those checkboxes represent. But there's no reason why you can't incorporate it into the code.
Thank you sir, I will start working on it.
Oct 19 '07 #13

Rabbit
Expert Mod 10K+
P: 12,315
Thank you sir, I will start working on it.
No problem, let us know if you run into any problems.
Oct 19 '07 #14

P: 48
No problem, let us know if you run into any problems.
I added a new ListBox so the user needs to select letter, proposal, or all. So I declared strSelect and gave the selection to that variable. However when I open it, it will open the report but it will not show me any information. Thank you for your time.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command48_Click()
  3.  
  4.  
  5. Dim pstCriteria As String
  6. Dim strSelect As String
  7.  
  8.  
  9.  
  10. Select Case frmOptionX
  11.  
  12.  
  13.     Case "rptDateReceived"
  14.  
  15.  
  16.         If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  17.             pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
  18.  
  19.  
  20.         DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria, , strSelect
  21.  
  22.             Else
  23.                 MsgBox "You must enter two dates"
  24.  
  25.   End If
  26.  
Oct 22 '07 #15

Rabbit
Expert Mod 10K+
P: 12,315
You use Code tags by surrounding it with [ Code=vb ] and [ /Code ] without the spaces between the [ and ].

As for this:
Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
  2.  
You can't have two assignments on the same line. But that is moot as you don't need two.

Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.frmTest & "'"
  2.  
Also, a note about naming conventions. I usually reserve the frm keyword when naming forms. If it's a listbox I usually use lst. Using frm is confusing as people might take it to mean that you're trying to return the value of a form.
Oct 22 '07 #16

P: 48
You use Code tags by surrounding it with [ Code=vb ] and [ /Code ] without the spaces between the [ and ].

As for this:
Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "#" & strSelect = "DocumentType = '" & Me.frmTest & "'"
  2.  
You can't have two assignments on the same line. But that is moot as you don't need two.

Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.frmTest & "'"
  2.  
Also, a note about naming conventions. I usually reserve the frm keyword when naming forms. If it's a listbox I usually use lst. Using frm is confusing as people might take it to mean that you're trying to return the value of a form.
Thank you!! However is there a way that I can select more than one option? Because if I want to select ALL how would I do that??

THanks again.
Oct 23 '07 #17

Rabbit
Expert Mod 10K+
P: 12,315
If I wanted to select all I would use:
Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType Like '" & Me.frmTest & "'"
  2.  
And if I wanted all document types, I would have frmTest return *.
Oct 23 '07 #18

P: 48
If I wanted to select all I would use:
Expand|Select|Wrap|Line Numbers
  1. pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType Like '" & Me.frmTest & "'"
  2.  
And if I wanted all document types, I would have frmTest return *.
Thank you sir. However, I cannot select more than one option even if I do Shit and try to select more than one option. It will only return the value of one option.
Thabk you.
Oct 29 '07 #19

Rabbit
Expert Mod 10K+
P: 12,315
Selecting all and selecting some are different problems. With a select some, you need a multi-select list box. You'll need to create a comma delimited string by looping through the selections in the list box. And in your criteria you'll use the In keyword.
Oct 29 '07 #20

P: 48
Selecting all and selecting some are different problems. With a select some, you need a multi-select list box. You'll need to create a comma delimited string by looping through the selections in the list box. And in your criteria you'll use the In keyword.
Thank you Sir. This is what I did following your advise. I do not get any errors but it does not work properly. It will open the report but blank. Could you please help. Thank you so much.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Criteria As String
  3. Dim pstCriteria As String
  4. Dim strSelect As String
  5. Dim i As Variant
  6.  
  7.    Criteria = ""
  8.    For Each i In Me![lstTest2].ItemsSelected
  9.       If Criteria <> "" Then
  10.          Criteria = Criteria & " OR "
  11.       End If
  12.       Criteria = Criteria & "[DocumentType]='" _
  13.        & Me![lstTest2].ItemData(i) & "'"
  14.    Next i
  15.  
  16.    Me.Filter = Criteria
  17.    Me.FilterOn = True
  18.  
  19.  
  20.  
  21. Select Case frmOptionX
  22.  
  23.  
  24.     Case "rptDateReceived"
  25.  
  26.  
  27.              If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  28.               pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"
  29.  
  30.  
  31.         DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
  32.  
  33.             Else
  34.                 MsgBox "You must enter two dates"
  35.  
  36.    End If
  37.  
  38.   End Select
  39. End Sub
  40.  
  41.  
Oct 29 '07 #21

Rabbit
Expert Mod 10K+
P: 12,315
Thank you Sir. This is what I did following your advise. I do not get any errors but it does not work properly. It will open the report but blank. Could you please help. Thank you so much.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Criteria As String
  3. Dim pstCriteria As String
  4. Dim strSelect As String
  5. Dim i As Variant
  6.  
  7.    Criteria = ""
  8.    For Each i In Me![lstTest2].ItemsSelected
  9.       If Criteria <> "" Then
  10.          Criteria = Criteria & " OR "
  11.       End If
  12.       Criteria = Criteria & "[DocumentType]='" _
  13.        & Me![lstTest2].ItemData(i) & "'"
  14.    Next i
  15.  
  16.    Me.Filter = Criteria
  17.    Me.FilterOn = True
  18.  
  19.  
  20.  
  21. Select Case frmOptionX
  22.  
  23.  
  24.     Case "rptDateReceived"
  25.  
  26.  
  27.              If IsDate([txtFirstDate]) And IsDate([txtEndDate]) Then
  28.               pstrCriteria = "[DateReceived] >= # " & [txtFirstDate] & "# and [DateReceived]<=#" & [txtEndDate] & "# AND DocumentType = '" & Me.lstTest2 & "'"
  29.  
  30.  
  31.         DoCmd.OpenReport "rptDateReceived", acViewPreview, , pstrCriteria
  32.  
  33.             Else
  34.                 MsgBox "You must enter two dates"
  35.  
  36.    End If
  37.  
  38.   End Select
  39. End Sub
  40.  
  41.  
When using the Code tags, you don't put spaces between the brackets [ ].

There are a few things wrong with this.

1) You're using the Filter property of the form, this has no effect on a report.
2) You have multiple criteria strings but you only use one of them in your OpenReport.
3) Your string is constructed impropery. It should be of the format:
Expand|Select|Wrap|Line Numbers
  1. FieldName In ('Value1', 'Value2', 'Value3')
Oct 29 '07 #22

P: 48
When using the Code tags, you don't put spaces between the brackets [ ].

There are a few things wrong with this.

1) You're using the Filter property of the form, this has no effect on a report.
2) You have multiple criteria strings but you only use one of them in your OpenReport.
3) Your string is constructed impropery. It should be of the format:
Expand|Select|Wrap|Line Numbers
  1. FieldName In ('Value1', 'Value2', 'Value3')
Thank you Sir, howeve I am not sure I understand what you mean with the code below. Thanks again.
Expand|Select|Wrap|Line Numbers
  1. FieldName In ('Value1', 'Value2', 'Value3')
[/quote]
Oct 30 '07 #23

Rabbit
Expert Mod 10K+
P: 12,315
That's what your criteria string should look like after you've looped through your listbox to build it. What you currently have is incorrect. It's not that the syntax is wrong, it'll work, but if there's too many selections, your string will become too long.
Oct 30 '07 #24

P: 48
That's what your criteria string should look like after you've looped through your listbox to build it. What you currently have is incorrect. It's not that the syntax is wrong, it'll work, but if there's too many selections, your string will become too long.
Ok thank you Sir. I will try to fix it.
Nov 6 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.