Filtering a Form by way of a combo box on another form (1 Viewer)

[mjanalyst]

Good Afternoon. I am new to Microsoft access and have only been using it for little over a year. I am using Access 2010 to create a database for work.

I am looking for help in coding. I would like to select a choice of a company in a combo box on one form and in turn it will filter another form upon it loading as per that selected company. Please feel free to give me any suggestion on how to best do this and what code I should use. Thanks for all your help.

MJ

 

[pbaldy]

How about:

http://www.baldyweb.com/wherecondition.htm

 

[mjanalyst]

Paul. Thanks for the help. I followed the instructions and liked the suggestion a lot, but it didnt work. The 2nd form wouldn't show any records and is asking me for my parameters. Here is my code bellow.

DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19 & "'"

Form 1 that I am selecting the choice from in the combo box has a different data source then form 2's. They do both share the same column "ESCO Name" which row source is the same query. I hope that makes sense and I am wondering if my problem is both forms having different data sources even though the column I am trying to filter from is the same query. Thank you great for your help.

MJ

 

[pr2-eugin]

MJ, could you please be a bit more specific? when you say it is not working, do you mean you are getting an error or, is it not returning any result?
Check these..
* If you are getting any error, what is the error that you are facing?
* Are you sure that the return from the Combo19 is a Text (as you have surrounded it with single quotes)?
* As per Paul's advice, make sure the second Form you are trying to open has a FieldName as 'ESCO_Name'.

 

[mjanalyst]

pr2-eugin,

Thank for the response. I made sure that the field name is matching. I am not receiving any errors. Here are the steps I take and what happens.
1.) Click on combo box and select one of the companies listed.
2.) I click on the button that directs me to my 2nd form. The event procedure code I have is listed bellow.

Private Sub Testing_Progress_Click()

DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19 & "'"
DoCmd.Close acForm, "ESCO Menu"

End Sub

3.) The "Enter Parameter Value" box appears



4.) When I then click the OK button my 2nd form opens with no records appearing.

Please advise and thank you so much for your help.

MJ

 

[pbaldy]

The parameter prompt is telling you that the field name is misspelled. Does it perhaps have an inadvisable space instead of the underscore?

 

[pr2-eugin]

Before trying to open the Form use a msgbox and see what value is returned, commenting out the following statements.. something like..

MsgBox(Me.Combo19)
'DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19 & "'"
'DoCmd.Close acForm, "ESCO Menu"

 

[mjanalyst]

Great catch pr2-eugin!! It is returning the ID # for that record and not the ESCO's Name. What am I doing wrong to make it do that? Thanks.

MJ

 

[pr2-eugin]

Well as expected the value is bound to the ID.. Now we know what the problem is, so what you now need to do is, just use the ID Field of the second form to open the form..
Maybe...

DoCmd.OpenForm "Testing", , , "ESCO_ID = " & Me.Combo19
DoCmd.Close acForm, "ESCO Menu"

If the Form does not have a ID field then use a DLookUp..

Dim valueFromCombo As String
valueFromCombo=DLookUp("[B][I]FieldName[/I][/B]","[B][I]TableName[/I][/B]","ESCO_ID=" & Me.Combo19)
DoCmd.OpenForm "Testing", , , "ESCO_Name =" & valueFromCombo
DoCmd.Close acForm, "ESCO Menu"

Personally I will go for the first way accessing through ID.

 

[bob fitz]

pr2-eugin
I could be wrong, but if "ESCO_ID" is a number then I think that:
DoCmd.OpenForm "Testing", , , "ESCO_ID = '" & Me.Combo19 & "'"
Should be:
DoCmd.OpenForm "Testing", , , "ESCO_ID = " & Me.Combo19

Also

'DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19.Column(1) & "'"
may work

 

[pr2-eugin]

Hello Bob, yes I did make a mistake now I have edited it..
You are absolutely right..
This...

DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19.Column(1) & "'"
may work

will be the most simplest answer.. LOL.. I wonder how I missed it. Thanks for that..

 

[mjanalyst]

Guys thanks for the quick help. I put the code pr2-eugin just said

DoCmd.OpenForm "Testing", , , "ESCO_Name = '" & Me.Combo19.Column(1) & "'"

but I am coming up with the message " Run-time error '3075': Syntax error (missing operator) in query expression 'ESCO_Name ='Brown's Energy Services, Inc.".

I also wanted to check to, because I dont think we can reference the ESCO_ID since the forms have 2 difference data sources and the IDs dont match up. I hope that makes sense. If you want me to paste some jpgs showing you what I mean I would be happy to. Thanks.

 

[mjanalyst]

Guys. Thanks again for your help!! I am heading out of work. If you respond I will get back to you in the morning.

 

[bob fitz]

I think the problem is caused by the apostrophe in Brown's Energy Services, Inc

Try:
DoCmd.OpenForm "Testing", , , "ESCO_Name = """ & Me.Combo19.Column(1) & """"

 

[mjanalyst]

Good Morning Bob. Thanks for your suggestions. I tried that this morning but unfortunately it didn't work. I received that enter parameter value box again for ESCO_Name. When I hit okay it takes me to my 2nd form that i am opening and displays no records. When I hit the cancel button it displays a Run0time error '2501': The OpenForm action was canceled. Sorry for all the trouble. Is there any more information I could provide you guys with to help me or any other suggestions. Greatly appreciated. Thanks.

MJ

 

[bob fitz]

Sorry to hear that you are still having problems. I did try the line of code that I offered before posting it and it work fine for me, even with an apostrophe in the name, so I am a bit stumped.
Please check that you have the correct number of double quotes, three before and four after.
Does it work with a name that has no special characters (like apostrophes).
Can you try this:

MsgBox "ESCO_Name = """ & Me.Combo19.Column(1) & """"
DoCmd.OpenForm "Testing", , , "ESCO_Name = """ & Me.Combo19.Column(1) & """"

and post the message produced in the MsgBox.

 

[mjanalyst]

Hello Bob,

I put in that code and this message box appeared.



Oh and after I hit the ok on the message box access asks me to enter the parameter value again for ESCO_Name.


I am wondering if Access is having the issue of matching up that parameter on the 2nd form that is opened for some reason. Just a thought and I have no idea how to fix that. Let me know what you think. Thanks.

 

[bob fitz]

Can you post a copy of your db. Remove any sensitive data. You would also need to convert it to A2003 mdb version for me to be able to take a look at it.

 

[mjanalyst]

Bob, I have attached a copy of the database. I got rid of the sensitive info and just the shell with company names are in there. I believe this version will be able to be opened in 2003. If it doesn't work let me know. Thanks.

View attachment Management Database.accdb

MJ

 

[bob fitz]

Bob, I have attached a copy of the database. I got rid of the sensitive info and just the shell with company names are in there. I believe this version will be able to be opened in 2003. If it doesn't work let me know. Thanks.

View attachment 43078

MJ

No, this has not been converted to the earlier version. If it had, it would have the .mdb file extension.

Do you have a control on the form you are opening that has ESCO_Name as its Control Source property.