Use combo box to filter for records and allow navigation

Put this in the criteria section of the the query driving your form:

Forms.YourFormName.YourComboBoxName

Then, put this in the After Update event of your combo box:

Private Sub YourComboBoxName_AfterUpdate
   Me.Requery
End Sub

Substitute YourFormName and YourComboBoxName with the actual names of your objects (form and combo box)

mx

Evening Gents,
The Combo box wizard can do this for you. Have you given that a go?


<ot>
Joe, My Boy's nearby you in S.D. in Marine Bootcamp!
I was condiering flying out to see him grad. Will let you know
as Mar14th comes closer.
J
</ot>

Yes ... fly out.  Of course, I'm in Rancho Cucamonga about 110 miles from SD.

My son-in-law (and daughter) were just here.  Aaron is a E7 hospital corpsman in the Navy. Up until a few months ago, he was stationed at Camp Pendleton for 3 years, which include two trips to Iraq.  Is that where your son is?

mx

DatabaseMX:

I tried that and have used similar setup (like Forms.YourFormName.YourComboBoxName) in other queries.  They work fine in the where clause.  For some reason it won't work for the query.  It doesn't even prompt me value when I run the query as a stand alone.  I have also tried the requery, but I guess if my query doesn't work, it won't work either,right.  See my query below.

SELECT TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time, TimeBillingExcel.TimeBillings_Activty
FROM TimeBillingExcel
WHERE (((TimeBillingExcel.ServiceProvider_Initials)=[Forms]![View_Time]![cbInitials]))
ORDER BY TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time;

Select allOpen in new window

I don't see anything obvious.  Are you say, with the from *close* that you do not even get prompt for the Forms expression ?

mx

Yes.  When I run the query from the query view, it does not prompt me.  

wrap your [forms]![yourform]![yourcontrol] statement in NZ

nz([forms]![yourform]![yourcontrol])

it forces Access to evaluate it. If the form isn't open, then it will prompt you. Unfornuately it will prompt you like this

Enter Parameter Value
[forms]![yourform]![yourcontrol]

But it will prompt you

still nothing

SELECT TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time, TimeBillingExcel.TimeBillings_Activty
FROM TimeBillingExcel
WHERE (((TimeBillingExcel.ServiceProvider_Initials)=nz([Forms]![View_Time]![cbInitials])))
ORDER BY TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time;

Select allOpen in new window

Sumptin' ain't right ....

Can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

Then, provide me the link EE-Stuff gives you to the file location ... back here in this thread.

Note: There is a  2 MB upload limit.

mx

If you want to prompt the user on this one if the form isn't open, then you need to use a function

SELECT tbltest.FullName
FROM tbltest
WHERE (((tbltest.FullName)=IIf(FormLoaded("main"),[forms]![main]![fullname],[enter something])));

'-------------------------------------------------------------------------------------------------------------------------------------------------------
Function FormLoaded(ByVal sFormName As String) As Boolean
     
    If SysCmd(acSysCmdGetObjectState, acReport, sFormName) = acObjStateOpen Then
            FormLoaded = True
    End If
   
End Function
'-------------------------------------------------------------------------------------------------------------------------------------------------------

Can you tell if its getting to the form at all to get the value? If the value is in a combo box, is it the right column that's being passed to the query?

I don't know what I did but it prompts me now.  Let me try adding the code back and see if it works.

Standing by ....

mx

Trying to upload the db.

I've stripped it down and it's still 4MB.  Any suggestions?

Did you Compact and Repair it?  Then Zip it up ?

mx

Invalid Question value

I really appreciate your help.

Sorry, the page is given me an error Invalid Question value.

I copied the question from this post so ...?

The upload thing has BUGS!

Try posting in the entire URL for the Question ... that seems to work.

mx

Here you go.

https://filedb.experts-exchange.com/incoming/ee-stuff/6744-Copy-of-Invoicing-System.zip 

Got anything

Has anyone  figured out how to add a dropdown on a form and have it return only the records that are equal to the selected item in the list?

strWhere = CreateWhere(strForm)    

Will the where be 'fieldName= comboBox'?

Here is my query.

SELECT TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time, TimeBillingExcel.TimeBillings_Activty
FROM ServiceProvider INNER JOIN TimeBillingExcel ON ServiceProvider.ServiceProvider_Initials = TimeBillingExcel.ServiceProvider_Initials
ORDER BY TimeBillingExcel.ServiceProvider_Initials, TimeBillingExcel.Matter_SubAcct, TimeBillingExcel.TimeBillings_Date, TimeBillingExcel.TimeBillings_Time;

Select allOpen in new window

What?

it will build a where clause if you follow the instructions. So your SQL will be appended with something like

WHERE [yourfieldname] = 'somevalue'

Did you ever try to make your combo box through the wizard? if all you want to do is locate a record on the form that matches a record from a combo box, then the combo box wizard will do that for you

Yes, I tried using the wizard.  Here's the delima.  I have a query that I want to filter by one column.  This particular column has multiple alike entries and I don't want to populate the combo box like that.  For example:  A A A B B B B B C C C C D D D D .  I need A B C D.  So I created a separate query to populate the combo box, and used a where clause in the other: WHERE column = comboBox.Text.  It did't work.

ok, so your last comment was about the code that I pasted in? or was it about the combo wizard?

you don't refer to a control as combobox.text, it would be combobox.value if anything.

I'll download your DB in a little while and see what is up.

MX, anything on your end?

J

the wizard.  I can get the dropdown to populate no problem.  I just can't get the other fields to populate.  (Excuse some of my terminology.  I am not a VBA coder.  Mostly everything else other than VB.  So in OOP land, it would be dropdownlist.selecteditem.value (or text).  I think that most of my efforts have been surrounded around a C++, Java, C#, etc coding.  This would have been so much easier but my client want a complete Access interface not just the backend.)  I dreamt about your code all night.  True programmer, huh.

I tried to open the file you uploaded, and evidently its in a version > 2000

Have you pasted the code into a code module?

Do you understand how to use it?

Open the properties for your Combo box

In the TAG property, put the name of the field that you're trying to filter.

NOTE: the value for the item you select in your combo box is the item in the Bound column that YOU specified when you created your combo box. This value should correspond with a value in your data within the field you're trying to filter.

Put a call to this function in the after update event of your combo box

The call should look something like     MakeMyQDF me.name

That passes your form name where the combo box is to the function. Change the lines below as required.

Function MakeMyQDF(strForm As String)
Dim QDF As DAO.QueryDef
Dim strSQL As String
Dim strWhere As String
    strSQL = "Select * from [YOURTABLEORQUERY]"           '<--Sets the strSQL. Your table goes here!!!!!!!!!!!!!
    strWhere = CreateWhere(strForm)                                     '<--The Where Clause
    strSQL = strSQL & strWhere                                                   '<--Puts them together
        If QueryExists("TempQry") Then                                      '<--QueryExists function here
            DoCmd.DeleteObject acQuery, "TempQry"                 '<--This deletes the old query
        End If
    Set QDF = CurrentDb.CreateQueryDef("TempQry", strSQL)              '<--Creates the new query
    Forms![frmFilterby]![FilterSub].Form.RecordSource = "TempQry"   '<--Sets the recordset of the form or subform!!!!!!!!!!!!!
End Function

MakeMyQDF me.name
Form name == View_Time
so
name == View_Time??  I am getting an error with this.  I can't even get Me.Forms.View_Time to work.  So what the format?

never mind

???

Any progress?

no, still working on the function call but thanks for asking.

I name the function and module the same thing.   Got it fixed.  Stepped through the module and at the end, error - can't find the form frmfilterby referred to by the macro or vb code.
error here
Forms![frmFilterby]![FilterSub].Form.RecordSource = "TempQry" '<--Sets the recordset of the form
func call
MakeMyQDF ("View_Time")

again, use the call to the fnction by literally passing me.name to the function.

The recordset that you're trying to filter, is that a subform on your main form?

the line that you're saying is failing, is setting the target form's recordset

can you tell me what your form or form names are?

Has the code made it through to that point?
J

My form's name is View_Time.  It is a main form.  The code makes it through all of the functions and when it returns back to MakeMyQDF, it is QueryExists is false.  IT does the set, and then tries to do the assignment and errors with that message.  I'll send "Me.View_Time" to the function.

no, if that is the form name, and its making it that far, then that part is working.

The syntax to assign a value to the form depends on the configuration of your form(s)

answer this:

Is the recordset that you're trying to filter the main form's recordset? or a subform on your main form?

if it is the main form, then use this syntax

Forms![View_Time].Form.RecordSource = "TempQry"  or

Forms![View_Time].RecordSource = "TempQry"

give that a go.
J

I do not have any subforms what so ever.
 
Take a look here.  https://filedb.experts-exchange.com/incoming/ee-stuff/6786-upload-Invoicing-System.zip

First, thank you.  

If I hard code it, it works fine.  But I have several forms that will need to use this function and passing the form name as a string is causing the error.  

I would love to see the DB that you uploaded, but as I mentioned before, I have Access 2000 and it won't open the version that you are uploading. I suggest making a copy of your database and saving it as a version 2000 then trying the upload again.

Also, you said its failing where you pass the form name as a string.

But you also said it was making it all the way through the code to the point where you're assigning the recordseource to the form...so I'm quite confused at this point what you do and don't have working.

If I hardcode it, it works.  If I pass it, it doesn't.  I'll upload it again.

https://filedb.experts-exchange.com/incoming/ee-stuff/6787-upload-Invoicing-System-2000.zip

I am still not able to get your database to open. It says something about me needing to upgrade to a new version of IE.

Can you paste in the line where its failing, and what you're trying to pass or not pass.

Are you adept at debugging? do you know how to use the Immediate window?

Put on SP3 for office.  That will get rid of that error.  Here is the download.
http://www.microsoft.com/downloads/details.aspx?FamilyID=5C011C70-47D0-4306-9FA4-8E92D36332FE&displaylang=EN

The error that fails is the same I paste before.  

error - can't find the form frmfilterby referred to by the macro or vb code.
error here
Forms![frmFilterby]![FilterSub].Form.RecordSource = "TempQry" '<--Sets the recordset of the form
func call
MakeMyQDF ("View_Time")

I haven't used the immediate window on the watch window.

I haven't used the immediate window only the watch window.  I'll play with it.

I typed

?Forms![strForm].RecordSource = "TempQry"

in the immediate window and get the same error.

Jeff ... sorry, I've got a tight schedule between now and tomorrow am. Haven't been able to follow this.

mx

Vibert,
Do you feel the code is working as you expect it to up until that line of code?

When you "Step" through the lines of code and watch it does it build the where clause as you expect it to look?

Do you understand what it is all doing so that as you move forward, you'll be able to maintain it and use it again if you had to?

Yes, yes, and yes.  


All works now.  I used Forms(strForm).RecordSource = "TempQry" and it worked.  

This guy is great

Glad we finally got this one going. Nice work!
For future reference, try to always include as much information about what you've got, what you've tried and what is broken. Its always good to know your skill level specifically when it comes to de-bugging code that isn't working. And if you upload a copy of your DB to EE-Stuff, make sure it's in the lowest Version of access that is applicable to those helping you. You can never give too much information when explaining your situation.

Let us know if you need any more help.
J

Because my query and forms were name alike, I was able to further expand this function by changing the following
strSQL = "Select * from [YOURTABLEORQUERY]"
to
strSQL = "Select * from " & strForm.