Solved

Access 2010

Posted on 2013-05-31
13
172 Views
Last Modified: 2015-01-20
I'm new to Access, and have a bunch of data that I want to use muliple field to match up and find the right match.

See attached the field I want to search using top two rows

I think I need a seach button but unsure how to write the vba code for this?
0
Comment
Question by:optionsbear
  • 6
  • 5
13 Comments
 
LVL 84
ID: 39210825
You didn't attach anything.

Also, what is "the right match"? That can mean many different things, so please be sure to define that thoroughly.

Also give details as to exactly what you want to match in your "bunch of data". The more details you give us, the more easily we can help.
0
 

Author Comment

by:optionsbear
ID: 39210927
Sorry I'm new to this, I have a table of data (see data data attachment) I want to use the fields that are vertical in the [Order] column.

What I am trying to do is choose (quickly) the order column questions and have the database shake out the best matched items

basically I have a complex price book and I want to search the options and come up with the correct "favor" of product..

If I can get the information ie adders into a table I want to create a search function to configure my product, my hopes it to have Access do the work instead of flipping though a 6" thick price book choosing adders and typing them in excel each time.

thanks for the help in advance...
5-31-2013-6-26-19-AM.jpg
Data-table.jpg
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39211035
So I assume that in the form in your "form view" attachment, you want to enter values in one or more of those boxes, and you would then see what data in the table matches your entries?

You can do that fairly easily, if you're looking for an exact match.

If you're looking for a "closest match", then that's an entirely different matter. You can develop a "grading" system, where you assign various weighted scores to matches on certain columns, and then present the user with the results in order of "weight", but that is a difficult and complex matter (and not well suited for someone who is new to this).

To get an exact match, you'd build a Search feature something like this:

Function Search
  Dim sWhere As String

  If Me.ValveType <> "" Then
    sWhere = "ValveType='" & Me.ValveType & "'"
  End If

  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " AND "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

  etc etc - you do the same for EVERY column where you need to match

  Dim rst As DAO.Recordset
  Set rst = Currentdb.Openrecordset("SELECT * FROM MyTAble WHERE & " sWhere)

End Function

You could then show the results in a Subform, or Listbox. That would be an EXACT match system - in other words, if the user enters a value in any of your boxes, the code would attempt to exactly match the values entered.

If you need something along the lines of a "closest match", you could try using OR instead of AND:

  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " OR "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

This would show you any record where ANY value typed in by the user matches the associated column.
0
 

Author Comment

by:optionsbear
ID: 39211298
Do you do this stuff? What would it cost?
0
 
LVL 84
ID: 39211492
I'd be glad to help you work through this onsite, but if you'd prefer to hire this out please contact me at the email address in my profile.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:optionsbear
ID: 39211699
I am trying to learn this but finding the vba code is beyond my knowledge, it seems like it should be that difficult to do I would like to know more from an expert, why i signed up on this site? and open to your suggestions? please advise the best course of action
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39211764
What you're asking to do - a "ranked" match of records, based on varying criteria - is generally well beyond the capabilities of a novice. We can help you to work through this, but all in all the onus is on you to do the work, and we'll help you through the tough spots.

A good place to start would be to begin building the Search function I showed you above. Start with just a few of your criteria you want to search, and get those working like you want, and then add more criteria.

I've added some comments to that function below, to help you perhaps understand what's going on:

  '/ We check to see if the user has entered a value in the "ValveType" CONTROL on the form. If they have, then we add the first portion of our WHERE criteria to our sWhere variable
 If Me.ValveType <> "" Then
    sWhere = "ValveType='" & Me.ValveType & "'"
  End If
  '/ Next, we check to see if the user has entered a value in the "Size" CONTROL on the form. If so, we then further check to see if we already have criteria by checking the LENgth of sWhere. If we do, then we add an AND (or OR, depending on how you want your search to proceed), and then add the criteria.
  If Me.Size <> "" Then
    Is Len(sWhere) >0 Then sWhere = sWhere & " AND "
    sWhere = sWhere & " Size='" & Me.Size & "'"
  End If

What we want to end up with is a valid WHERE clause.
0
 

Author Comment

by:optionsbear
ID: 39212040
can you send me example?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39212729
I don't really have any complete examples of this sort of thing (at least none that I could send you without violating NDAs). The examples of the Search function are the basis for those solutions, but (again) they are far more complex. As you stated you're a novice in regard to Access, this will be a difficult project for you to undertake.

I'd suggest you take some time and review some working Access database examples:

http://office.microsoft.com/en-us/access/microsoft-access-for-your-business-database-software-and-applications-FX102473447.aspx
http://access.microsofttemplates.org/

Also, do you have a database that you've started work on? You might consider attaching that file here. If you do, be sure to remove or obfuscate any sensitive data, and also provide instructions on using your database (if you have forms and such).
0
 

Author Comment

by:optionsbear
ID: 39212897
I have been reading up on this and tried to take a stab at doing it, I am confused a bit the code you sent but im sure that there is a small simple piece that I am missing..

can you look at this?

the top of the form I want to use as my search criteria, the search button would search through my table BAW_Gears_pt2 to find an exact match and display the information on the lower body of the form?
6-1-2013-6-19-48-AM.jpg
6-1-2013-6-21-30-AM.jpg
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39214267
First: The "search" portion of your form (the Header) should not contain Bound fields (i.e. those fields should not have a ControlSource set). In that top section, you should remove the Control Source for each of those controls.

-- When comparing values, you should use "<>", which generally means "anything other than" - so this:

If Me.txtSize <> "" Then

Means "If there is anything other than an empty string in txtSize" ... which means that the user has entered something. Using ONLY the > or < can result in errant data included in your WHERE clause.

-- If you're going to use the LIKE keyword, you generally will want to use the wildcard indicator:

If Me.txtSize <> "" Then
  varWhere = varWhere & " [Size] LIKE '*" & Me.txtSize & "* AND"
End If

In other words, this would find any record where the value you enter in txtSize is included in ANY portion of the Size field. So if I enter "large" in the Size field, this would return values of "Large", "XLarge", "LargeTall" etc.

Note that LIKE operators don't work for Numeric fields, so if Fields like MaxDiffPressure are Numeric fields it's pointless to use LIKE (and can cause issues as well).

-- Also, I don't see the need to declare your varWhere as a Variant. It's a String, and should be declared as such:

Dim varWhere As String

-- Also, your function should return a STRING, not a Variant:

Private Function BuildFilter() As String

-- Finally, in order to actually show the records from your Search, you'd do this:

Me.RecordSource = "SELECT * FROM YourTable WHERE " & varWhere

This "resets" the Recordsource for the form, and should show you the records based on your search.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now