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

question about form

100+
P: 243
i have a form that i view in the continuous forms view. i have added a search feature to the form by creating two unbound text boxes and creating a search button with the following code...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.     Dim strStudentRef As String
  3.     Dim strSearch As String
  4.     Dim strSql As String
  5.  
  6. 'Check txtSearch for Null value or Nill Entry first.
  7.     If IsNull(Me![numsearch]) Or (Me![numsearch]) = Null Then
  8.     If IsNull(Me![streetsearch]) Or (Me![streetsearch]) = Null Then
  9.         MsgBox "Nothing Entered!", vbOKOnly, "Blank!"
  10.         Me![numsearch].SetFocus
  11.     Exit Sub
  12. End If
  13. End If
i am searching my table for a date and employee....i input a year into one text box and an employee name in the other and click the search button...i know the txtbox names are incorrect i was just too lazy to change them when i started this new database...this works great to show only the results of what i searched for in the form. my question is, how can i then print those results... i tried this...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2. If IsNull(Me!ID) Then
  3. MsgBox "Please select a valid record", _
  4. vbOKOnly, "Error"
  5. Exit Sub
  6. End If
  7. DoCmd.OpenReport "ID", , , _
  8. "ID= " & Me!ID
  9. End Sub
but that only prints one record because it is just looking at the primary key. any help is greatly appreciated... thanks!
Jan 7 '10 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I'm not sure I can make any clear sense of this question I'm afraid, but let me have a stab.

Are you asking for a way to open the report in a way that reflects the criteria already selected on the form?

If so, then it will involve reproducing what's in your earlier code. I can help with that, but you will need to post the code correctly. From my reading of what you've posted there it can never do what you claim. It simply gives an error message if the operator has not selected anything.
Jan 7 '10 #2

100+
P: 243
yeah im sorry i know its a ridiculously loaded question but ill try to explain it a little clearer... the first code i posted is in a 'search' button on my form... it is looking in two unbound text boxes for search criteria... in this case an employee and a year...that part works great... it searches and shows me only the search results in my form...what i would like to do is be able to print just those search results... if i set up a standard print button, it prints everything in the table that the form is based off of instead of just the search results... hope that helps... ive been trying this for a while and cant figure it out... your help is greatly appreciated
Jan 8 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
Please refer back to my previous post (#2). The third paragraph needs to be read again and responded to. If you feel that I'm mistaken and the code does actually do as you say then please point out where the code does any searching or filtering.
Jan 8 '10 #4

100+
P: 243
i apologize for that... i didnt even notice that the entire code did not paste correctly... here is the full code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim strStudentRef As String
  3.     Dim strSearch As String
  4.     Dim strSql As String
  5.  
  6. 'Check txtSearch for Null value or Nill Entry first.
  7.     If IsNull(Me![numsearch]) Or (Me![numsearch]) = Null Then
  8.     If IsNull(Me![streetsearch]) Or (Me![streetsearch]) = Null Then
  9.         MsgBox "Nothing Entered!", vbOKOnly, "Blank!"
  10.         Me![numsearch].SetFocus
  11.     Exit Sub
  12. End If
  13. End If
  14. '---------------------------------------------------------------
  15.  
  16. 'Performs the search using value entered into txtSearch
  17. 'and evaluates this against values in Address
  18.  
  19.     DoCmd.ShowAllRecords
  20.                                               'Chr(42)is ascii code for the wildcard symbol
  21.     strSql = "Select * from [Meters] Where Number & Street Like '" & Chr(42) & Me!numsearch & Me!streetsearch & Chr(42) & "';"
  22.     Me.RecordSource = strSql
  23.  
  24.     'If one or more matching records are found, they can be displayed one at a time via the navigation buttons
  25.     If Me.Recordset.RecordCount > 0 Then
  26.  
  27.         numsearch.SetFocus
  28.         strSearch = numsearch.Text
  29.  
  30.         numsearch.SetFocus
  31.         strSearch = numsearch & Space(1) & streetsearch
  32.         MsgBox "Match Found For: " & strSearch & " ", _
  33.             , "Found!"
  34.             numsearch.SetFocus
  35.         numsearch = Null
  36.         streetsearch = Null
  37.  
  38.  
  39.     'If value not found sets focus back to txtSearch and shows msgbox
  40.     Else
  41.         numsearch.SetFocus
  42.         strSearch = numsearch & Space(1) & streetsearch
  43.         MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  44.             , "Sorry"
  45.             numsearch.SetFocus
  46.     End If
  47. End Sub
Jan 8 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
Ah. Now it's starting to make sense :)

The approach you are taking is good SQL, but it doesn't really use Access in a very natural way. Instead of setting the .RecordSource again within your code, it is more straightforward to keep the original .RecordSource as is, but to set and change the .Filter property instead. The nice things about this approach are that :
  1. Setting the .Filter property can be done easily when calling an object Form OR Report, by passing a filter string as a parameter to the Open call.
    The value is passed as the WhereCondition parameter and is a string in the same format as the SQL WHERE clause, omitting the word WHERE.
  2. It leaves the original .RecordSource available for reference or re-use.

When you change your filtering code (There is more on this with some examples at Example Filtering on a Form) to work this way, the report can easily be opened using exactly the same filter string as was used to set the .Filter value of the form.

From your line #21, I would say the SQL for your form would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [Meters]
The filter would be :
Expand|Select|Wrap|Line Numbers
  1. Set strWhere = "[Number] & [Street]='*" & 
  2.                Me.NumSearch & _
  3.                Me.StreetSearch & _
  4.                "*'"
Using Chr(42) in place of "*" seems entirely pointless to me. It has the same effect but makes the code harder to read, and encourages you to put an ambiguous explanation as a comment (There are many more than one wildcard character. See ANSI Standards in String Comparisons).

I hope this is helpful.
Jan 10 '10 #6

100+
P: 243
wow awesome, ok ill give that a try tomorow, im new at access and have been learning on my own through this website and some others, the people on this site are really helpful... ill let you know how it goes, because im probably going to need some help figuring out how to generate the report from the filter results... thanks!
Jan 10 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
the people on this site are really helpful...
Thank you for saying so. As an administrator I'm always pleased to see comments like that. I think we have some of the finest and friendliest volunteers we could wish for.
because im probably going to need some help figuring out how to generate the report from the filter results...
It will be much easier for you if you let go of that way of thinking. The technique is well illustrated in the article I linked in post #6, but you will not be taking a result set and attempting to re-apply it. What you will be doing is much more simple. After working out your filter from the controls on your form and storing it in a string variable, you will apply it both to the form and the report. The only difference here is that for the form (because it is already open) you will set the .Filter and .AllowFilters properties, whereas for the report, you simply pass the string as a parameter to the DoCmd.OpenReport(...) procedure call when you open it.
Jan 10 '10 #8

100+
P: 243
first just wanted to thank you for your help, after some work, it seems to be working great now. here is my filter code

Expand|Select|Wrap|Line Numbers
  1. 'CheckFilter produces the new Filter depending on the values currently in
  2. 'txtFindAccountCode, txtFindCreationDate & cboFindAccountType.
  3. Private Sub CheckFilter()
  4.     Dim strFilter As String, strOldFilter As String
  5.  
  6.     'txtFindAccountCode - Text
  7.     If Me!empsearch > "" Then _
  8.         strFilter = strFilter & _
  9.                     " AND ([employee] Like '" & _
  10.                     Me!empsearch & "*')"
  11.  
  12.  
  13.     strOldFilter = Me.Filter
  14.     'txtFindCreationDate - Date
  15.     If Me!yearsearch > "" Then _
  16.         strFilter = strFilter & _
  17.                      " AND ([Date1]Like '" & _
  18.                     Me!yearsearch & "*')"
  19.  
  20.  
  21.  
  22.  
  23.     'cboFindAccountType - Numeric
  24.    ' If Me!empsearch > "" Then _
  25.     '    strFilter = strFilter & _
  26.      '               " AND ([employee]=" & _
  27.       '              Me!empsearch & ")"
  28.     'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  29.     'Debug.Print "strFilter = '" & strFilter & " '"
  30.     'Tidy up results and apply IF NECESSARY
  31.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  32.     If strFilter <> strOldFilter Then
  33.         Me.Filter = strFilter
  34.         Me.FilterOn = (strFilter > "")
  35.     End If
  36. End Sub
i just had one more question... i tried but couldnt seem to get it to work, if i wanted to search the date by year only, is that possible... right now with the way its coded i type "*/*/2005" and it brings up that year... is there a way to bypass the wildcards when typing a search? the field is set in the table as a date/time field... thanks again
Jan 11 '10 #9

100+
P: 243
oh and i forgot, this is what i used to create the report from the filtered results

Expand|Select|Wrap|Line Numbers
  1. If Me.Filter = "" Then
  2.         MsgBox "Apply a filter to the form first."
  3.     Else
  4.         DoCmd.OpenReport "Id", acViewPreview, , Me.Filter
  5.     End If
Jan 11 '10 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
look at the year fuction.

Expand|Select|Wrap|Line Numbers
  1. Year(Me!DateField)
Jan 11 '10 #11

100+
P: 243
silly question but where would that go in my code... ive tried a couple things and cant seem to get it to work
Jan 11 '10 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
If, for example, you want to apply it to the following filter.
Expand|Select|Wrap|Line Numbers
  1.     strOldFilter = Me.Filter
  2.     'txtFindCreationDate - Date
  3.     If nz(Me!yearsearch,"") <> "" Then _
  4.         strFilter = strFilter & _
  5.                      " AND Year([Date1])=Me!yearsearch"
This assumes [Date1] is in a valid date format and that Me!yearseach holds a value for the year like 2010
Jan 11 '10 #13

100+
P: 243
when i use that code a message box pops up and asks me to input the year again, and this only works once if i search again it does nothing.... any ideas?
Jan 11 '10 #14

NeoPa
Expert Mod 15k+
P: 31,186
I'm very pleased to see that you got the answer to the main question of the thread. Thanks for posting your code too.

Mary's code, however, should probably just be seen as an illustration of the usage of the Year() function. I doubt she was suggesting it as something to put into your procedure directly.

I suggest you have a play with the function and see what you can get it to do. Essentially it converts a Date value into the numeric value of the year component alone (which seems to be what you were asking for).
Jan 11 '10 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
My advice is to move the value in Me!yearsearch into a local variable in the code. I suspect that Me!yearsearch is losing its value as the filter is applied.

So something like this ...
Expand|Select|Wrap|Line Numbers
  1. Dim searchYr As Integer
  2.  
  3.     serachYr=Me!yearsearch
  4.  
  5.     strOldFilter = Me.Filter 
  6.     'txtFindCreationDate - Date 
  7.     If nz(serachYr,0) <> 0 Then 
  8.         strFilter = strFilter & _ 
  9.                      " AND Year([Date1])=" & serachYr
  10.  
Jan 11 '10 #16

Post your reply

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