Forms :: Search Fields On Form With Results In Subform
Mar 10, 2014I want to enter text boxes on a form that are linked to certain fields on a table and when search criteria is typed in show the results in a table on a subform.
View RepliesI want to enter text boxes on a form that are linked to certain fields on a table and when search criteria is typed in show the results in a table on a subform.
View RepliesI created a search form. It has 6 unbound text boxes and 2 combo boxes in the header. Users can select values from the combo boxes and/or enter names in the other text boxes. These values all go into a filter on my Main table and the filtered results show up in the detail section. That used to work fine.
Now, I've been trying to convert the filter into a query and show the query results in the detail section instead.
(Why? Because of the ever-changing business requirements, of course!)
For some reason, the detail section went blank. All white. When I change the Data Entry property to No, it fixes that.
However, the text boxes for entering the search criteria will not accept any values anymore. It's like they are disabled.
When I change Data Entry property to Yes, I can enter text into the text boxes again. But the detail section blanks out again.
Windows 7 Professional, MS Access 2007/2010
Hey all,
I did a search and i found a thread that seemed like it would answer my question but for some reason the attachment isnt working. :(
Okay heres the question..
I have a form and it contains 3 fields and 3 command buttons.
When i hit the command button it uses a query to display the results.
What i want to do is instead of having the results display in a new window, i would like it to display on the same form .. maybe on the bottom of it.
Should i use a subform or listbox/combobox..
Can anyone help me figure this out?
Thanks in advance
Hi, I'm just wondering if it's possible to display the result of a query on a subform which does not have a relationship with any other table.
What I'm doing is creating a search form where a query is built from the users input (through combo boxes and text boxes) of what table, attribute and condition they want to search from. When they press the search button, I want the result to be displayed in a datasheet below the selection criteria
The first thing I thought of using to display information with was a subform, but I think the purpose of having the subform is so that it can be linked with a form. So, is there another control or way of displaying results. The results can come from any table.
Thanks.
sugoi_kat
My Form is taking its results from a parameter query, and since my subform also has the same control source (the query).
It always prompts me twice, although not a major problem it is irritating!
Is there any way that when I type the search results once the sub form takes the results from the main form?
(P.S. The form asks me for parameters even when printing and saving, is there not a way that this can be stopped too by maybe taking the results off the current page?)
I have two unbound subforms on an unbound "display form". I managed to link the two subforms by following this method: [URL] This is great and works well enough, however, by design, subform2 will only display records relevant to the very first record on subform1. I need subform2 to be filtered based on ALL of the relevant records being displayed in subform1 not just the first one.
Previously, subform2 was based on a query using subform1's records as criteria, but this gave me the same result.
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies View RelatedI have a Main form with a sub form.The main form is a customer record (form view), and the sub form is all other customers (same fields) datasheet view.I have a navigation bar to filter (using different queries) the results in the sub form
same postcode
Same company (different location)
everyone except Main form customer
I cannot get a different filter (Query) to show only customers whose name begins with the same letter as the customer in the main form?I have been trying Like but think I am referencing it wrong, as it prompts me to enter a value rather than using the value in the field in the main form.This is what I have in the query (of the subform)
Customer ID: <>[Forms]![NewAllCustomerListF]![CustomerID]
Customer Name: Like "[Forms]![NewAllCustomerListF]![CustomerName]" & "*"
I'm novice in access 2007 I've a project that contain a Table a form with Subform bound to that table, on tthe main form I've unbound [text boxes] and a [search button]. I want that my form act as a search form , so when I click the search button it show the only result an that table subform. I've read a couples of article, it look like I've to use some VBA code!
View 4 Replies View RelatedI have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
The following works when I am searching a form on a value in a sub-form where the value is text
Private Sub find_boats_by_enq_no_Click()
Dim stDocName As String
Dim strSQL As String
Dim trz As String
trz = InputBox("Enquiry number??")
stDocName = "find_boats_by_enq_no"
strSQL = "SELECT * FROM boats INNER JOIN prices ON boats.[boats-prices#] = prices.[boats-prices#] " _
& "WHERE prices.[stn-enquiry-number-1]= '" & trz & "'"
Me.RecordSource = strSQL
End Sub
What changes do I need to make if "prices.[stn-enquiry-number-1]" is a number rather than text. I'm thinking I dont require some of the "dims" and I'm not sure if I don't need to do something with the quote marks...
I am currently working on a project in access 2010 and I am having a search for record dilemma. I have a main form that has a subform in it under a tab control. I place a search box at the top of the form so that user could select either the ID, the internal case number, and/or the reviewer name in the text boxes to search the record.
I have tried the using the search for record macros and the convert the macros to VBA and possibly write so extra coding for calling the subform into the search but nothing seems to work in getting the search in sync between the main form an the subform. VBA code that will look for same record in both forms.
The reason for the tab control is because depending on the data entered the users switch between mid section data about case and so one set of info goes into one tbl and the other goes into another, but the subform that I am referring to in this question is being generated from the same tbl as the main form.
have set up a navigation form which contains 5 unbound subforms that are made visible or not visible thru the on click event of command buttons on the main form, the subforms have a command button with an embedded macro that searches for a record based on what has been keyed to an unbound text box on the subform, this works fine when the sub forms are opened independently of the navigation form but when the search is done from within the main form, the error message is, "....cannot find the referenced from frmAQIFAList" subform, I tried referencing the main form name infront of the subform name but this also doesn't work, perhaps this would be better with code ? or is it because the subforms are unbound ?
View 4 Replies View RelatedI have successfully created a dynamic search form, which filters a listbox as the user is typing into a textbox.However, I am now trying to make the search more basic, which I cannot do. I want the listbox to be blank, and only display results where the number in the textbox matches one of the fields in the listbox.
Code:
Sub SearchFor()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchForProp.Text
[code]....
I have a [Order Central table] that compiles products from four different categories that employees can order from.
Employees using the database will select from four categories on buttons which opens up the order form EX: [Category 1 form].
They enter their order info on [Category 1 form] into a subform sourced from [Category 1 Table] called [Category 1 subform].
Is there a way that they could select the "Category 1" button, which would run a macro opening [Category 1 form] with the [Category 1 subform] having a where condition of 1=0 (or just so that the subform appears blank)?
This is an employee timekeeping database. I have a main form "frmTEMain", with a tab subform named "frmGridsub" with a tab named "Grid" on the lower half. On the tab page is the "frmGridsub" a continuous form that looks like a datasheet, many horizontal rows of data. The subform works correctly with several combo boxes that auto populate. There are many different employees listed in the Grid subform. "Employee" is the name used for the field that holds the unique ID number in the tables that feed the subform. The subform populates another table "dbo_BCRC."
When I click on a row in the subform I want fields to update on the Main Form with Employee, LastName, FirstName, that come from the table that was populated when data was entered into the subform. As the person clicks on the various rows in the subform, the main form fields need to synch with the row.
Do I use "Got Focus" event? Do I need to create another subform and place it on the top half of the Main Form?
I can provide screen shots....
So I created a search form which filters my results. Apparently I don't have enough posts to link to the source of the code though. The code is from this website, /forums/showthread.php?t=188663
I then wanted to allow users to double click a result in the listbox to take them to the form which contains details about that record, however the listbox doesn't point to the correct record.
-When nothing is specified in the search field, all records are shown in the listbox, but double clicking on the first record takes me to a form that has no information. Clicking the second record takes me to the Detail Form of the first record.
-When information is put into the search field, the results are filtered and only a few display, however clicking the first result again takes me to a blank form while the second result takes me to the first record of the unfiltered list. There is no way to reach the Detail Form for the last result of the listbox.
-I tried right clicking the results in the listbox and copy pasting the information. The first result gave me a "0", the second a "1", and so on and so forth. This was the same whether or not the information was filtered or not.
-I tried switching to a combo box and got the same results.
creating a searching form and to show the results inside the form! It's like a Library type searching. u want to search for some type of monument or so and it shows the results, all the info, photos and that!
View 6 Replies View RelatedI am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description
CarNum
SerialNum
Category
Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
I have built a form that holds details of training records. What I want is when a drop down is selected in the main form, that it will populate some of the fields in the subform. I have this working at the moment, that for example, when a certain course is selected, that their modules will appear in the subform. Where my problem arises is that I have a relationship between two tables that I want to appear on the subform, so that details can be filled in on the subform against the list of modules that automatically appear.
View 2 Replies View RelatedI am trying to lock records on a form and subform after a checkbox has been ticked, have used the code below from a previous post.
Private Sub Form_Current()
If Locked = -1 Then
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
[Code] .....
This is locking the Main form records is there a way to code this so that the fields on the subform are also locked when the checkbox is ticked?
I am wanting to grey out/disable a field on a form based on entries in two other fields being the same. One of these fields that contains the data to be referenced in on a sub-form.The form is called 'Payment_Information-Form', the field to be greyed out/disabled is called 'SUF A Paid?'
The fields that are being referenced are: 'Set up fee in s (SUF) Recrt Restriction A <=' which is on the main form, and 'SUF_Letter' which is on a sub-form (within the main form). The sub-form is called 'Costings_11_Previously_Paid_SUF subform'.If 'Set up fee in s (SUF) Recrt Restriction A <=' equals 'SUF_Letter' then grey out.I tried the following formula in both the On Current and After Update sections but it hasn't work.
=IIf([Forms]![Payment_Information-Form]![Costings_11_Previously_Paid_SUF subform].[Form]![SUF_Letter]=[Set up fee in s (SUF) Recrt Restriction A <=],[SUF A Paid?].[Visible]=True,[SUF A Paid?].[Visible]=False)
I have a form and when it's opened you get 3 different search questions to answer or leave blank. My question is.... If a search parameter is entered but no results are found to match it, how do I create an error message telling the requestor "No Data Found"?
View 7 Replies View RelatedI'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
PS: Access 2010, Win7 64bit