Creating A More Efficient Search

Jul 13, 2006

please see the example attached, it is a very basic search, double clicking on the return number opens that record.

what i would like to do is show the account name and date is the same box.

below is the code that i use. can someone please someone help me incorporate something into it?

rivate Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchReturn"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub
Option Compare Database
Option Explicit

Private Sub List2_DblClick(Cancel As Integer)
Dim rs As Object

DoCmd.OpenForm "frmquery"

Set rs = Forms!frmquery.Recordset.Clone
rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0))
If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark

DoCmd.Close acForm, Me.Name

End Sub

Private Sub TxtSearch_Change()

Dim vSearchString As String

vSearchString = Me.TxtSearch.Text
'Me.txtSearch2.Value = vSearchString
'Me.List2.Requery
'Err_cmdSearch_Click:



End Sub

View Replies


ADVERTISEMENT

More Efficient Query Design..

May 29, 2007

I have written a query to calculate how many hours we have worked for our clients. This has a number of sub-queries which sum to make different columns in the main query ie: Hours invoiced so far, Hours to be invoiced, Hours remaining from their allocation, etc.
I am refreshing the queries due to a design change and now have a question..

Which is the better design:

To have those sub-queries with selection criteria from a join to the same table in each (to filter out records at a lower level)
OR
To have that same selection criteria and join just once in the main query? (which of course means its processing many more records but the join to the selection criteria is specified once)

answers on a postcard please..

View 3 Replies View Related

Tables :: Most Efficient Way To Store Historical Data

Mar 11, 2013

I'm thinking of 2 different ways, but not sure how Access will handle them.

1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).

This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).

2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)

I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?

View 14 Replies View Related

Efficient Way Of Setting Value Of A Textbox To The Result Of A Select Query

Apr 19, 2005

Anyone know an efficient way of setting the value of a textbox to the result of an sql query?

I am using the following, but it seems to be slow when populating a large form:

Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "

Make.Requery

Make.Value = Make.Column(0, 0)


Let me know if there is a better way to do this.

View 1 Replies View Related

Modules & VBA :: Efficient Comparison Of Multiple Fields On Different Tables

Oct 7, 2013

I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically

Code:

If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then
targetTable.Field3.Value = sourceTable.Field3.Value

The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)

Code:
For i = 2 To 5754
For j = 2 To 3500
If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then
targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value

The above code works but it is really slow (takes about 12 mins on a high-end CPU).

View 4 Replies View Related

Creating A Search Command

Oct 4, 2007

I have a table that was set up from a parameter query. Before the form opens it ask you for a part number. You type in the part number you want the information for and the form pops up to that particular part number. The problem that I am having is that if I want to move to another part number I have to close out the form and reopen it to type in another part number. I tried using the Access Find/Replace feature to do a search for a another part number to open up the information to that part number and I have even tried to create a command function that will do a search, but when I use them they both come up with no records found.

Is there a way to make a search function in a form created from a parameter query? I am not a script writer and am just really getting indepth with Access at the moment.

Thanks
Steve

View 3 Replies View Related

Creating A Search Engine Help

May 27, 2005

Hello,

I have a form with about 30 fields on it, all connected to a table with file information. I want to create a search form using all 30 fields, so that if a user inputs information in any one of these fields and clicks search, it will find records based on the combination of what he/she inputted in the fields. For all the fields that he/she leaves blank, the search engine will ignore in its search.

I have already created a query that does this somewhat. For each field, I have used this as the criteria:

IIf(IsNull([Forms]![File Search Form]![SearchFieldName])=False,[Forms]![File Search Form]![SearchFieldName],[FieldName])

I have put this in the criteria and they are all linked by an And statement. It works fine, except that the program does not seem to match Null fields together. So, if the user leaves a field blank, the search won't ignore that field, it will only show records with some piece of data in that field. All records that are Null in that field are cut out.

So, I guess my question is: how would I make the program be unbiased towards fields that are Null and let it include records that have null in the field? Am I going about this the wrong way?

Any way, hope it was clear. Thanks in advance. =)

View 3 Replies View Related

Question About Creating A Search Function

Oct 17, 2004

Anybody here know how to create a search field on a form to pull up a certain record?

I am practicing with Microsoft Access by creating a database that tracks my college degree progress as well as my wife's college degree progress.

Here is the Database Structure:

tblStudentID
---numStudentID (AutoNumber)
---strFName
---strLName

tblQuarter
---strQuarter

tblDegreeAudit
---numStudentID (Lookup field from tblStudentID)
******(Displays the First and Last Name according to Student ID number)
---strCourseCode
---strCourseNumber
---strCourseTitle
---strDegreeProgram (Yes/No field)
---strQuarter (Lookup field from tblQuarter)
---strYear
---strCredits
---strComplete (Yes/No field)

frmDegreeAudit
---tblDegreeAudit

Now, on the form (frmDegreeAudit), I want to put in a search function that allows me to drop down a list with the Student's name and all of their courses next to the name according to the Student's ID. Below is how I want to list to look when you drop the list down:

First Name-----Last Name-----Course Code----Course Number

If I can't do that, then this is what I want to do then. I want a drop down list that has the first and last name. Then right next to that drop down list, I want another drop down list that lists all of the courses associated with the Student ID. It would look something like this:

List #1=========================List #2
First Name-----Last Name->->->->->->Course Code----Course Number

Anybody understand what it is I'm looking for? I hope so because once I figure this out, I can figure out the other database I've been trying to create for the longest time.

View 1 Replies View Related

Creating A Search Field In A Form

Oct 5, 2005

Hello

I want to create a single search field inwhich the user can search data within two different tables. So for example in both tables there is a field called Container Number. When they type in the number into the search field i want a query to search both tables and return if it is in table one or table two.

I hope this make sense!

View 2 Replies View Related

Creating A Search Button For Key Words

Apr 27, 2012

I havent used access before and need a easy way to seach through 1300 records i have created in a form. Each record has 6 criteria, some with drop down options. I would like to create a button that when you type into a box and click this button it will search all my records and bring up a list of all the records containing the key words searched for.

View 10 Replies View Related

Another Noob Question: Creating Search Form

Apr 25, 2006

I think I can achieve what my boss wants with just one table.

I work in a legal department and we track employee contract complaints/issues. Simple data entry is used and one table (Complaints Table) has been created with fields like: employee name, emp #, what union rep issues the complaint, who from our department responded, when the complaint was sent, when the response was sent, two memo fields to describe the problem and the resolution, and ... perhaps most importantly... contract section.

It is possible that one complaint could touch on multiple contract sections.

The data-entry form I've created has the following fields:
contract section 1:
contract section 2:
contract section 3:

Now I need to create a Search Form and I'm having all kinds of headaches trying to understand how to make this work... trouble with setting up the right macro, too.

I'd like the main form to contain the following search fields:
Rec # (which is set as an autonumber in the "Complaints Table")
Employee name
Employee #
Contract Section
...or perhaps just a "contract section" search form...

1. If a contract section # is entered, I want to push a button and have every record of that contract section # filtered from the database and show exactly as the data-entry form beneath the "search" portion of the main form.
(did that make any sense?). I know how to create a general form and subform, but the subform always shows up as a table object rather than a form object. How can I fix that?

2. Some contract sections might be numbered: 3.A.2. ....others might have a longer name like 22.C.3.a.vi.(b). ....
Obviously, the filter/search needs to catch all records which include the contract section specifically, regardless of which of the three "contract section" fields from the main data-entry form contain data.
What's the easiest, non-VBA way of setting that up?

I just know nothing of VBA.

Any help is greatly appreciated...
tango..

View 3 Replies View Related

Help Creating A Multiple Field Search Form

Nov 2, 2007

Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!!
Thanks
Paul:

View 5 Replies View Related

Creating A Form To Search Field In Database

Nov 6, 2004

I have created a form to search a field in my database by running a query. i can only get it to search for direct matches as I am having no luck getting the "LIKE" Operator to work for me. the format should apparently be, like "A*" , this works but i woud like to replace "A" with a variable from a txt box in my form. which will be a combination of letters and numbers

followed what i think is the right instructions and pasted the appropriate bits in the expression builder to tell it where to look but doesnt work
cant fathom out why its not working, it just produces blank queries

Also in the query design page do i need to do anything with the optional "Total" row dropdown menu?


help much appreciated

View 3 Replies View Related

General :: Creating Search Button On Form?

Jul 22, 2013

putting a search button on a form on my database.

On the navigation buttons at the base of the form there is a search facility where I can type a record id but I would like to get rid of that and search records either by turning the ID field into a searchable box or by adding a button where I can press it and get a search box up.

View 1 Replies View Related

Creating A Dynamic Search Box In Access 2010?

Nov 24, 2011

I am using access 2010 and am trying to create a dynamic search like the one google uses, it will feature a drop down that will populate with results but when its not in use the drop down is not shown.

I'm looking to add a menu system to the form its self so that the users will be able to do certain functions. third image is what I'm looking to create with the menu bar at the top of the screen.

[URL]

View 1 Replies View Related

Creating A Search To Find Nearest Location

May 12, 2014

I have Territory Mgmt database. Sometimes when I get a new lead I need to quickly find other leads in the database that are closest to it.

I am currently not tracking latitude/longitude etc. Do I need to? Is there a way to set certain location marks by address?

If so, once I put in the latitudel/longitude how do I create a search to find the nearest?

View 14 Replies View Related

Forms :: Creating A Search To Populate Subset Of Records

Apr 21, 2014

I have a form that when initially displayed shows all client records in my database with the ability to add new ones through a "Add New" command button. I have another command button called "Search" that displays a second form containing a dozen or so fields to search and filter down on.

When the Search button is pressed, I display search form and enter data in some of the fields for searching. I then click ok and the main form should now display a subset of the records based on the data I entered on the search form.

My dilemma is that the WhereCondition of the docmd.OpenForm doesn't work. The doccmd works to open the form when I don't include the WhereCondition, but the moment I put the WhereCondition in, I get an unknown runtime error (3000).

Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.

Private Sub cmdSearch_Click()
Dim varWhere As Variant
' Initialize the search string to Null
varWhere = Null
If Not IsNothing(Me.srchLastName) Then
varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"

[Code] ....

View 5 Replies View Related

Forms :: Creating A Search Form For Multiple Tables

Jan 9, 2014

I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:

Adult Banding Records
Nestling Banding Records
Historical Banding Records

The specific issue:

I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.

View 10 Replies View Related

Outer Join Creating Duplicates In Dynamic Query Search Results

Jan 22, 2008

Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon

View 4 Replies View Related

Modules & VBA :: Creating A Search Form With Ability To Add Found Record To Table?

Aug 7, 2013

creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.

View 7 Replies View Related

Creating A Search Button That Will Retrieve Data From Table And Populate Form

Jan 24, 2015

I've been able to navigate a lot on my own, but there is one issue I cannot seem to resolve. I have 1 form and 1 table. I have the form set up so that you can enter data, and then press a button, and it will "save" and refresh the form for a new entry. However, I want to be able to pull that entry back up in the form, and fill out additional fields later on.The form is set to data entry = yes because I do want the form to open up as brand new each time.

To sum up my question. I want a text box and search button at the top of my form. When you type an ID number in the text box, and then press search, I want access to populate my form with the information in my data table associated with that ID number.

If I type in the number 1234 and hit search. I want my form to autopopulate with the data in the row for ID number 1234 (all the fields I have already populated). So by searching 1234, the name, phone, background info, etc that is populated in the row will appear.

View 5 Replies View Related

Forms :: Search Form Creating Error When Type First Letter As Lower Case I

Oct 6, 2014

When I type the first letter I into the search text area I get the following error

Run-time error '2110'
Microsoft Access can't move the focus to the control SearchResults

Most of the code is below

QRY-SearchAll
SELECT Clients.ClientId, Clients.ClientFileNumber, Clients.ClientShortFileNo, Clients.Salutation, Clients.FirstName, Clients.LastName, [Group Branches].BranchCode, Clients.Phone, Clients.Mobile, Clients.Fax, Clients.BpayRef, Clients.TradingAs, Clients.EntityType, Clients.ABN, Clients.ACN, Clients.Address1, Clients.Address2, Clients.Town, Clients.State, Clients.PostCode, Clients.Country, Clients.Email, Clients.ClientGroup, Clients.DateCreated, Clients.Notes, Clients.LastModified, Clients.UserCode, Clients.BdmCode, Clients.CollLongNo, Clients.CollShortNo,

[Code] ....

View 1 Replies View Related

Creating Search Form And Show Results Inside The Form?

Jul 27, 2015

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 Related

General :: Creating A Database For Creating Quotations

May 20, 2015

I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?

View 3 Replies View Related

VBA - Search Button To Search Entire Records / Runtime Error 3345

Jun 18, 2014

why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.

The dysfunctional code:

Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind

[Code] .....

The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?

View 5 Replies View Related

Modules & VBA :: Search Code Deactivates Navigation Buttons When Search Results Are Longer Than A Page

Jun 24, 2015

I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.

The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:

Code:

Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus

[code]....

I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved