473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Search Form

Hello.
I am trying to make a search for for my database. I already have a form created for input of the data. I would like to copy that form and use it as a search form and feed the results of the search into a report that I have already created. It should return all values associated with that field if that particular text box is left blank.

Any and all help/advice would be appriciated!!!

wisni1rr
Nov 16 '11 #1
10 1540
NeoPa
32,556 Expert Mod 16PB
You should understand that a question thread is not a job sepcification form. We help you with your work. We don't provide solutions to your spec.

You may find these help you :

Example Filtering on a Form.
Cascaded Form Filtering.

If not, then we need a proper question from you before we can help you further.
Nov 17 '11 #2
Mihail
759 512MB
If the question is "How to duplicate a form in your database" I think that a good idea is:

-Create a copy of your Database (lets name it "CopyOfDB");
-Open "CopyOfDB", rename the table you need to duplicate, close "CopyOfDB";
-Open the original DB and import the renamed table from "CopyOfDB";

Of course you can duplicate how many objects you need in "one step"

Append: Or use "Save As" and save the form under different name

Good luck !
Nov 17 '11 #3
I apologize if I have used this thread incorrectly, NeoPa.

Hopefully, this will explain it better. If not, please direct me to the appropriate place to post it or let me know if this is not the website I should be trying to find my answer on.

I have a form that is comprised of vertical columns with text boxes for input and a tabs control to flip pages. These are the only control types on this particular form. What I am trying to do is essentially use each bound text box controls like parameter queries where all spaces that are blank will search all records and if a value is added to a text box it will search only records that contain that specific term.

I hope that helps.

Thank you again for taking the time to read this.
Nov 17 '11 #4
NeoPa
32,556 Expert Mod 16PB
You need to look at some of the sticky threads found in most forums (There are two in this one alone) which give you a lot of good advice and direction on how to ask questions so that they can be answered. The Submit a Question page also has a number of clear instructions which cover much of what you need to know.

Your latest post goes some way there, but it's clearly not been checked for grammar or punctuation as I tried to read it and, not already knowing what you were trying to say, could not put it together in any way that made much sense. This is also down to describing your situation in terms that you haven't even covered (so we don't know about) which makes it all but impossible to understand the rest of what you say. I understand that describing things in words is something most people are far from skilled at, but if we're to offer help then that has to start from a position of understanding what you're saying. Face to face is always a lot easier, but it has to be written down to be read so that's the situation. In case it's not clear from this, I see this as necessary advice rather than criticism. You've clearly made an attempt to work within the guidelines so no censure, just explanation (as requested) on what is what.

Clearly you can try other forums if you feel these demands are too stringent, but they all work on fundamentally the same principles, so any questions you ask anywhere will benefit from due care and attention before you commit it to the thread.

If your next post manages to make things clearer then I'll happily look at it again for you and see what I can do to help.
Nov 17 '11 #5
TheSmileyCoder
2,322 Expert Mod 2GB
Unless your very familier with VBA I would suggest the following simpler approach:

Use the built in "Filter By Form" function in Access to do the filtering.
Create a button (btn_PrintReport) to open the report, with the following VBA code behind it:
Expand|Select|Wrap|Line Numbers
  1. Private sub btn_PrintReport_Click()
  2. If Me.FilterOn and Me.Filter & ""<>"" Then
  3.   docmd.OpenReport "ReportName",acViewPreview,,me.filter
  4. else
  5.   docmd.OpenReport "ReportName",acViewPreview
  6. End If
  7. End Sub
Replace ReportName with the name of your report, but leave the "" around the name.

If this solution is not adequate for your needs, come back. I would also suggest that you try searching this forum for other threads on search forms, since there are quite a few threads allready here in the access section.
Nov 17 '11 #6
I think you are on the right track TheSmileyCoder.
I have a form that has text box controls for each field for all my tables in my database. I want the user to enter filter commands into the text box(s) then click a search button and display the filterd results into a report that I've already have designed.
As an example:
Expand|Select|Wrap|Line Numbers
  1. County        [textbox]
  2. Sold          [textbox]
  3. Tax Amount    [textbox]
  4.  
I would like to search multiple values or ranges, such as:
Expand|Select|Wrap|Line Numbers
  1. County         [Brady OR Medow]
  2. Sold           [5,000-7,000   ]
  3. Tax Amount     [              ]
  4.  
I hope this approach is easier to understand. I'm sorry my grammer is poor. I am much better in a verbal conversation, as you have already suspected. I do appriciate your time to view my question and answer it with your advice or solution.

I may be wrong, but am I trying to make a filter by from?

I want my form to open in the search mode and with a button, generate the filtered results into a report that I have already created.
Nov 17 '11 #7
TheSmileyCoder
2,322 Expert Mod 2GB
You can certainly make a search form, but it involves a fair amount of VBA coding to setup, and as I mentioned there are several threads about this allready, im sure you can find what you need in one of those if you look for it.

My alternate approach is not exactly what your asking for, but I think it could take you 80% of the way with only 10% of the work involved. Only you can determine if you need the last 20%, and if your willing to do the extra 90% work for it.

My suggested method simply uses the form you allready have, as well as the built in functionality Filter By Form in access, to build the filter string, and passes that string on to your report, only printing the results that match (Allthough I did not explain that all that clearly in my original post).
Nov 17 '11 #8
NeoPa
32,556 Expert Mod 16PB
It's true that conversation is a much easier approach to transferring information than explaining from scratch all in one go. Your latest post does give us much more in the way of clues though. Let me comment on that somewhat if I may.

What you want is a little complicated (I'm English so when I say that it means pretty darn complicated), especially for someone with little or no experience. Smiley is pretty well on track when he explains you can do much of this with filtering by form, and this is also true of simple filtering by unbound controls as you have them. Example Filtering on a Form gives an idea of the basics of this, but it doesn't cover ranges or lists for a specific field. They are both possible to handle, but you need to recognise such a specification when entered as well as knowing how to handle such matches.

A simple match in the WHERE clause is handled by [Field] = ???. This is covered in more detail in the linked article. You can also specify a wildcard match using [Field] Like ???. Find Wildcard Characters in an Access Database explains what can be done with these. For a range you can use the syntax [Field] Between ??? And ??? and for a list [Field] In(???,???,???).

In all these cases the types of field determine how the literal values used will need to be used. The first link provides examples of numeric, string and date literals. This shows, for instance, that for your County example you'd want something like :
Expand|Select|Wrap|Line Numbers
  1. [County] In('Brady','Medow')
See Quotes (') and Double-Quotes (") - Where and When to use them for a fuller explanation of this.

It seems I already posted one of these links (the most fundamental) earlier in the thread. If you had looked at it then and seen what it could have done for you I doubt we'd still be stuck at this stage of the conversation. We can't do everything for you. You need to start looking at things and make a start for yourself. That way we can continue to help, moving forward with you as you try, and yes - fail, but progressing anyway.
Nov 17 '11 #9
I found a tutorial that does nearly everything I'm looking to do.



Expand|Select|Wrap|Line Numbers
  1. 'Purpose:   This module illustrates how to create a search form, _
  2.             where the user can enter as many or few criteria as they wish, _
  3.             and results are shown one per line.
  4. 'Note:      Only records matching ALL of the criteria are returned.
  5. 'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
  6. Option Compare Database
  7. Option Explicit
  8.  
  9. Private Sub cmdFilter_Click()
  10.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  11.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  12.                     we remove the trailing " AND " at the end.
  13.     '           2. The date range works like this: _
  14.                         Both dates      = only dates between (both inclusive. _
  15.                         Start date only = all dates from this one onwards; _
  16.                         End date only   = all dates up to (and including this one).
  17.     Dim strWhere As String                  'The criteria string.
  18.     Dim lngLen As Long                      'Length of the criteria string to append to.
  19.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  20.  
  21.     '***********************************************************************
  22.     'Look at each search box, and build up the criteria string from the non-blank ones.
  23.     '***********************************************************************
  24.     'Text field example. Use quotes around the value in the string.
  25.     If Not IsNull(Me.txtFilterCity) Then
  26.         strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
  27.     End If
  28.  
  29.     'Another text field example. Use Like to find anywhere in the field.
  30.     If Not IsNull(Me.txtFilterMainName) Then
  31.         strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
  32.     End If
  33.  
  34.     'Number field example. Do not add the extra quotes.
  35.     If Not IsNull(Me.cboFilterLevel) Then
  36.         strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
  37.     End If
  38.  
  39.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  40.     If Me.cboFilterIsCorporate = -1 Then
  41.         strWhere = strWhere & "([IsCorporate] = True) AND "
  42.     ElseIf Me.cboFilterIsCorporate = 0 Then
  43.         strWhere = strWhere & "([IsCorporate] = False) AND "
  44.     End If
  45.  
  46.     'Date field example. Use the format string to add the # delimiters and get the right international format.
  47.     If Not IsNull(Me.txtStartDate) Then
  48.         strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
  49.     End If
  50.  
  51.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  52.     If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
  53.         strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  54.     End If
  55.  
  56.     '***********************************************************************
  57.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  58.     '***********************************************************************
  59.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  60.     lngLen = Len(strWhere) - 5
  61.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  62.         MsgBox "No criteria", vbInformation, "Nothing to do."
  63.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  64.         strWhere = Left$(strWhere, lngLen)
  65.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  66.         'Debug.Print strWhere
  67.  
  68.         'Finally, apply the string as the form's Filter.
  69.         Me.Filter = strWhere
  70.         Me.FilterOn = True
  71.     End If
  72. End Sub
  73.  
  74. Private Sub cmdReset_Click()
  75.     'Purpose: Clear all the search boxes in the Form Header, and show all records again.
  76.     Dim ctl As Control
  77.  
  78.     'Clear all the controls in the Form Header section.
  79.     For Each ctl In Me.Section(acHeader).Controls
  80.         Select Case ctl.ControlType
  81.         Case acTextBox, acComboBox
  82.             ctl.Value = Null
  83.         Case acCheckBox
  84.             ctl.Value = False
  85.         End Select
  86.     Next
  87.  
  88.     'Remove the form's filter.
  89.     Me.FilterOn = False
  90. End Sub
  91.  
  92. Private Sub Form_BeforeInsert(Cancel As Integer)
  93.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  94.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  95.     'The problems are explained at http://allenbrowne.com/bug-06.html
  96.     Cancel = True
  97.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  98. End Sub
  99.  
  100. Private Sub Form_Open(Cancel As Integer)
  101.     'Remove the single quote from these lines if you want to initially show no records.
  102.     'Me.Filter = "(False)"
  103.     'Me.FilterOn = True
  104. End Sub
Full tutorial: Here

The only thing I need to change is how it displays. I would like to have the filtered results display in a report. Is this a possibility with this approach?

Thanks again for your help guys!

I'm not a complete stranger to VBA. I took a course on it at my community college a few years ago.
Nov 18 '11 #10
TheSmileyCoder
2,322 Expert Mod 2GB
Assuming your form and the report have the same fields, you can you use the method I have allready suggested of opening the report with the same filter, as is applied to the form.
Nov 18 '11 #11

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

Similar topics

2
by: misschristalee | last post by:
I'm having a brain blockage day.... Scenario: Search Form with 6 text boxes Query has same six fields Each has this IIF: IIf(IsNull(!!),"",!!) with each dictating the correct text box of...
3
by: Liddle Feesh | last post by:
Hi, I have a table named "Person" in a MSDE (2000) database, and am building a search form comprising of search fields on the top section (forename, surname, telephonenumber, etc) which all...
7
by: jim Bob | last post by:
Hi, This is probably very simple to do so if anyone can point me to the right place for reading, it would be much appreciated. I just want to build a very basic search form where i can enter a...
1
by: Eddie Holder | last post by:
Hi Guys, I hope that someone will be able to help I have a table containing data, lets say products. I have a form with a text box which serves as the criteria for a query to search the product...
31
by: DWolff | last post by:
I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious). I built a data entry form that works fine. Typically, a client will call in and...
9
by: lightning | last post by:
Hi all, I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary... I am trying to build a search form that allows combinations of search terms. For...
6
by: KiwiGenie | last post by:
Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search...
1
by: tamoochin | last post by:
I have a form that registers the user with my website, the form is in farsi language and must use utf-8 standard. I can store data in MS Access and also read it back with any problems. the...
2
by: Mark | last post by:
Hi All, I am creating a music database for a friend and have run into a problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather than the ARTIST_NAME. To do this, I intended to have...
8
by: munkee | last post by:
Hi everyone, I am using the following code adapted from Allen Browne: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.