combo box selection criteria 2

[GShen]

I have used combo boxes with drop downs on many forms. What I need to do is somehow limit the list using a parameter from my form.
EG. I have an vendor form with misc. info. I would like to have a drop down combo of all my items on my item file equal to the vendor number on my form.
I know SQL and I know how to change the SELECT but how do I relate the form to the SELECT. I tried using "ME.vendornumber", I tried using forms!myform!vendornumber in the where clause and it didn't work. Can this be done?

Thanks for anyone who has this answer. I could use this in about 15 billion places.

Remember when... everything worked and there was a reason for it?

 

[janetlyn]

Yep, Bob's a genius. Gave him a pink star and would have given him more if Tek-Tips would let me. JL

 

[scriverb]

Congratulations JanetLyn. If feels good to solve a nagging problem. Good luck with your project. Thanks again for the Star.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[GShen]

Bob,
I had time to play with this again. I noticed 2 things. When I look a my form in design view and I click on my combo box I see next to ROW SOURCE the following:
Table/View/StoredProc

HOWEVER, (by the way I had my wizard fixed). When I create a new combo box via the wizard it will only allow me to bring in views or tables or values. It does not bring in stored procedures. Can you verify if this is the way yours works? Maybe something is out of sync. like you suggested. And/or can you email me a sample of the db with the combo box on a form. I want to see if this exactly what I have.
Thanks again.

Remember when... everything worked and there was a reason for it?

 

[scriverb]

My database does not show this as you describe but remember I am not hooked up to a network or have stored procedures available etc. etc. So, because of your situation ACCESS provides these for you. Under RowSource I have tables, queries. Or, I can build a SQL string. Your situation is different because you do have these other items available to to choose from.

Post back if you have any other questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[GShen]

Bob,
The reason I stated this info. is because one of the big differences with a view and a stored procedure is you cannot pass parameters to a view. When I go thru the wizard it does not allow you to pick a stored procedure. I went one step further and on the ON OPEN event of the form, I tried loading the combo box by using the rowsource property. It comes up with the same error. Bob, I do not think this is works with my configuration. I have it working the old fashion way but it is just something I thought was possible, I guess I am back to leaving this alone and in a few months I will try again. If you hear of anything else please let me know. I AM STILL VERY INTERESTED.
Thanks for all your patience and help.

Remember when... everything worked and there was a reason for it?

 

[scriverb]

I will continue to think on this one and see if we can come up with something for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Glen is your table stored in an ACCESS database or is it from SQL server. Maybe we should be using a pass-through query if the later is the case. This way we can build the SQL locally and include the actual value from the form control and then it should work and display the records.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[GShen]

Bob,
Is this to me (GSHEN)or are you working with a Glen to try and duplicate my problem? I am sure I noted somewhere is the voluminouse mess that it is from a SQL server. I am using ACCESS 2000. This is an ADP not an MDB.
Thanks,

Remember when... everything worked and there was a reason for it?

 

[BSman]

I would suggest that you create a query in Access using the query builder where the query includes the criteria that the vendor number column equals the vendor number value in the control on your form.

For the combo box that will display the appropriate values to select from (like the hot dogs and hamburgers), the record source will be this new query. For the OnGotFocus property, put the following VBA code in:
me.cboMyCombo.requery

(Where cboMyCombo is your name for this control.)

By forcing Access to requery the data source for the combo box whenever you go into the combo box, it should display the values appropriate for the current vendor.

 

[GShen]

bsman,
The requery is not my problem. My problem is showing the records from a table. I want to show the records with a column from the table equal to a field on my form. There are no queries to create in an ADP.
Scriverb,
I just tried something else. Instead of coding the select INLINE in the rowsource. I executed a stored procedure with a parameter. I do this all over the place when executing stored procedures from VB. I ran it 1st with no parameter to see if you could do this and it worked. I set the parameter to a hard coded value (while changing the stored procedure to look for this parameter) and it worked. I changed the hard coded value to me.fieldname. NO GOOD. I changed it to forms!myform!fieldname. NO GOOD. I get the same errors as I was getting before with the inline code. I am getting exasperated.

Remember when... everything worked and there was a reason for it?

 

[BSman]

OK, since you are creating an ADP, create a view in SQL like I described, but without the criteria (since SQL cannot see the value in an Access form). Then, when the combo box control has focus, put in something like the following code (assuming the vendorID is a number and is contained in a control called cboVendorID, and your combo box is called MyCombo):

me.MyCombo.source = "SELECT * FROM [View] WHERE [VendorID] = " & me.cboVendorID

me.MyCombo.requery

 

[GShen]

bsMAN,
YOU THE MAN!
I tried a few things and finally got this to work. I took the rowsource out of the combo box on the form. No matter what I did I could not get it to see the value from the field. I think you even mentioned that this is not possible. I then went into the VB code ON OPEN for the form and put in the me.mycombo.rowsource (I couldn't find me.mycombo.source, I figured you meant rowsource) with the rest of the code you had in there. Again, I do not need the requery because the purpose of this is when the form opens up the combo box shows only those records which are equal to the vendor being displayed. I am not adding to this table from this form, only selecting records. I wanted to be able to select only those records which were pertinant, not all the records and do VB code to verify if I had the correct vendor. It was sort of senseless. In other words my list of records is about 600. Each vendor has 0-10 records associated with it on this other table (which is used for my combo box). The buyer would have to scroll down and find them every time and if he or she clicked on a record which did not match the vendor then an error would be displayed. With the new code you gave me just the 0-10 records are displayed. No need for any VB code to verify. THESE ARE THE PROPER RECORDS pertaining to this vendor! I am done. Let me put a star on this before I forget.
Scriverb, thanks a million for all your help also and being patient with me.

Remember when... everything worked and there was a reason for it?