Improve company productivity with a Business Account.Sign Up

x
?
Solved

Query results based on option group in Access on form

Posted on 2011-09-27
7
Medium Priority
?
696 Views
Last Modified: 2012-05-12
Hello to all.  I have this issue here that was solved yesterday but it still is puzzling me a little bit.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27343036.html

The guys were great so here is what I am wanting that's not quite happened yet.

I have an option group box called "ogFind".  In the 3 options in the Option Group, 0=All records, 1=find by ID, and 2=Find by name.

I have a regular command button to run Query1.  I do not have any parameters in the criteria field in the query due to it being in the SQL.  In other words, it's not attached to the form in any way.  I do not have the Like "*" & [Forms]![Form1]![Text1] & "*" in any of the criteria fields.  This is one of the things I'm not sure about.

I have an unbound textbox called "text1".    In the "after update" command code I have the attached code.

It still seems it doesn't work.  Even if there is text in the textbox and I have "All Records" chosen, shouldn't it still bring up all records?  When I choose Option 1 or Option 2, it doesn't seem to be attaching itself to the combo box, in other words, no matter what I choose in the Option group, it still gives me the same results.

I am trying to get the user to type in something in the textbox and have the query find it then display the results based on the option they have chosen.

Any more help would be awesome!

Private Sub Text1_AfterUpdate()
 Dim vSQL
    Select Case ogFind
    Case Is = 0
        vSQL = "SELECT * FROM table1;"
    Case Is = 1
        vSQL = "SELECT * FROM table1 where ID = " & text1 & ";"
    Case Is = 2
        vSQL = "SELECT * FROM table1 WHERE Name1 LIKE " & Chr(34) & "*" & Me.text1 & "*" & Chr(34)
    End Select
    Me.RecordSource = vSQL
    
End Sub

Open in new window

0
Comment
Question by:palmtreeinfotech
  • 5
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36716022
The After Update event of your textbox doesn't seem like the correct place for this code.  I would place it in the After Update event of your option group, or behind a seperate command button.

Secondly, double check the properties of your option group to ensure that you are using the correct name and values in your code.

- Ensure that the name of your option group is ogFind (this is the Name property of the frame surrounding your radio buttons)

- Ensure that your option values are indeed  0,1,2.  These are the Option Value properties under the Data tab for each of the radio buttons (or whatever option controls you are using).  When using the default, these Option Values start at 1.

If these ideas don't help, post a sample of your database including just the relevant parts.  Mask or remove any sensitive data before uploading your sample.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36716034
Also, to ensure that all of your variables are defined, add the following line to the top of your code module, directly under Option Compare Database:

Option Explicit

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36716046
<< it doesn't seem to be attaching itself to the combo box  >>

Finally, what do you mean by 'attaching itself to the combo box'?  Are you trying to limit records in your form, or are you trying to limit rows seen in a combobox?

If you are trying to limit rows in a combo box, the following line needs to be changed:

>>>     Me.RecordSource = vSQL

That defines the records seen in the current form.

THIS defines rows seen in a combo box:

    Me.MyComboBox.Rowsource = vSQL
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:palmtreeinfotech
ID: 36717186
Hi mbizup thanks for your comments.  I have attached the database Access 2010 if you can open it.  What I mean by not attaching itself to the combo box is that nothing seems to be working as planned when using the combobox.  My default value is "0" and "1" and "2".

The way it is setup now, it should run the query and attach itself no matter what I option I choose on the combobox.  It doesn't seem like the combobox and textbox are working together.

There was an old "orders" database that I saw a long time ago but couldn't find it on the net. Database4.accdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36718007
Okay - a few issues.  We didn't have the full picture in your previous question.

1.  The code we gave you is designed to filter records on the same form as the option group (not a seperate query).
2.  We didn't know about the combo box.  Your code is currently referring to the textbox when filtering on the name.
3.  The "run query" button is not related in any way to the optiongroup or your new code.

To fix this don't need to change the code much at all - just the layout of the form:

1.  Move the controls to the form header section
2.  Add a couple of textboxes bound to ID and Name in the detail section of your form, and set the default view to "Continuous forms"
3.  Change the code for "Name" option to this, so that it refers to the combo box where the user selects a name (this is the only line of code that needs to be changed):

        vSQL = "SELECT * FROM table1 WHERE Name1 LIKE " & Chr(34) & "*" & Me.Combo12.Column(1) & "*" & Chr(34)


Give this upload a try, using the "Run Filter" button  - there is a ton more you can do with it... but this meets your basic requirements.
Database4.accdb
0
 

Author Closing Comment

by:palmtreeinfotech
ID: 36890552
hi mbizup and sorry for the late reply.  I will check it out.  yeah I knew there were problems and I was not explaining it too well.  I appreciate you sticking with me and answering my question.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36890576
Glad to help out!  Enjoy your weekend.
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

584 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