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?

 

[scriverb]

Your must use the long reference and not the Me:

FORMS![yourformname]![yourcomboname]

That should do it.

[COLOR=006633]Bob Scriver[/color]

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

 

[scriverb]

One more think make sure that the Bound column of your combobox is the value you want to compare to your table field. If not then the column in the query can be accessed by adding the .column(x) property and reference any of the columns in the query. Remember that the columns are enumerated starting with 0 rather than 1.

[COLOR=006633]Bob Scriver[/color]

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

 

[phil009]

Hey GShen,

scriverb is right, but if you still have any problems with it just let me know because I just made a db exactly like what you are talking about and could email it to you if you still need help.

Just let me know,
Phil

 

[GShen]

Scriverb and/or phil009,
Maybe I am confused or maybe I did not explain myself properly. I am trying to display a list of rows from a table equal to a field on my form. The bound column for the combo box is a different than the data I am trying to match on from my row set. It will look something like the following:

combo box
----------

1 starches vendor 222
2 hot dogs vendor 111
3 hamburgers vendor 111

The bound field is the 1st column (1,2 or 3). Each of these products however is assoicated with a vendor #. If the vendor on my form is vendor 111, I only want to display 2 entries in my combo box. eg (hot dogs and hamburgers).

I tried setting the where clause using the long version and it did not like the syntax. I was getting an error when it found the ! sign.
It is something like this.
Select key,desc
from tblxxxx
where vendor = (here is where nothing works, I tried me. and I tried the long version)

I hope this cleared up what I am trying to do.

Thanks again for all your help. Phil009 if this is what you example is then I will be interested unless of course either one of you can figure out what I am doing wrong.

By the way using ACCESS 2000 and SQL server. This is an ADP. I do not know if that makes a difference.

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

 

[scriverb]

Okay when you create a record on your form you identify who the vendor is. There must be a textbox where you enter the vendorID. Here is the SQL to selecting by that vendorID

Select key,desc
from tblxxxx
where vendor = FORMS![formname]![vendorIDcontrol]

This can be a saved query or just SQL in the combobox. What is important is that you put a Me![comboboxname].requery in the AfterUpdate of the vendorIDcontrol. This will requery the combobox as your user inputs a new id into the textbox of your form.

Post back if need more assistance.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

scriverb,
I tried it again. I keep getting a syntax error. Here is my code (I copied it)

SELECT tblclf_CostLink, tblclf_Description
FROM tblCostLinkFile
WHERE tblclf_Vendor = Forms!frmBillBackCostLink!tblfbbh_Vendor

----------
The form I am on is frmBillBackCostLink
The field name on the form is tblfbbh_Vendor. My control source for this field comes from another form. It displays fine on this form.

After I try and run it after my 1st error, the 2nd time it just gives me a blank drop down box.

By the way I do not have to refresh it. My costlink table is already created. This table has nothing to do with what I am trying to do on this form other than selecting the cost link I click on and then I want run some procedure bases on this number.

thanks,

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

 

[scriverb]

I don't see any reason for the problem. Let's test the query to see if it is working as you expect:

SELECT tblclf_CostLink, tblclf_Description
FROM tblCostLinkFile
WHERE  tblclf_Vendor = [Enter Vendor code];

Run the above in a query. See if this selects the records you expect to see.

If it does then the query should work fine as long as the control on the form is properly updated with the same type of vendor code information.

Let me know.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

not working. It comes up with "Invalid Column Name" the 1st time and a blank drop down after than.

I just want to reitterate so there is no confusion on what I am telling you. This is for the ROW SOURCE code of the combo box, not the DATA SOURCE for the form. I am going to lunch. I am getting exasperated.
From what it seems, the SELECT query for the ROW SOURCE for this combo box is mutual exclusive from anything happening on the form. If I used the query you suggested on the DATA SOURCE, it would indeed prompt me for the vendor number.
Last but not least, if you are telling me that it should work, could there be a patch to ACCESS that I need?
ttyl,
Thanks again.



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

 

[scriverb]

Yes, I do not want you to change the forms RecordSource. What I have been providing has been the RowSource for the combobox. The last one with the prompting I just wanted you to copy and paste it into a new query SQL window and run it. When prompted type in valid vendor id. See if it works. I suspect that it doesn't. That is the problem. The error you mentioned seems to indicate that one of the columns is not a valid field or something.

Don't get exasperated. We can figure this out. I will work with you until it is working as needed.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

Hi,
We are on the same page. I guess what you are telling me is that this should prompt me just like I was running the query from SQL. It doesn't do that. I didn't have the semi colon after it like you did because that is not needed. I added it and got the same message.
If know the code is ok because if I hard code it, it works.
EG.
SELECT tblclf_CostLink, tblclf_Description
FROM tblCostLinkFile
WHERE tblclf_Vendor = 12345

This pulls up all the records with vendor 12345.

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

 

[scriverb]

Okay, the below code should do exactly the same thing but use the value found in the textbox on your form. The only thing suspiscious here is the bolded control name below. Make sure that this is the name of the CONTROL and not the name of the field. Maybe you have them named the same. Thanks okay but if not we must be referencing the Control name.

SELECT tblclf_CostLink, tblclf_Description
FROM tblCostLinkFile
WHERE  tblclf_Vendor = Forms!frmBillBackCostLink![b]tblfbbh_Vendor[/b]

Let me know.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

Ok, I am using the control name. Since, this is not the form I am getting it from, I know I have the syntax correct.
The reason why I know this is because I have a field on my current form (the one with the combo box) called txtVendor, I have it set to this control source and it displays fine on my form. It copied and pasted this code into my select statement. There has to be something stupid which is not set properly. Are there any properties that need to be set differently for me to do this?

thanks for all your continued patience.

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

 

[scriverb]

Look at my profile and send me a db with just the form in it. Let me take a look. I can't see anything wrong with what I have given you if what you have told me about the form is correct.

I will post back here my findings and the solution.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

I just sent it to your email.
thanks a million.
going on vacation for a few days.... need a break... fishing in Canada.

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

 

[scriverb]

The SQL in the combobox is referencing a totally different form that the one open. also, the combobox name. So, put the following SQL in the RowSource for your ComboBox:

SELECT tblclf_CostLink, tblclf_Description  FROM tblCostLinkFile WHERE  tblclf_Vendor = Forms!frmBillBacksCostLink!cboCostLinkFrozen;

Also, put a Me.Combo73.Requery in the AfterUpdate of cboCostLinkFrozen combobox.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

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

 

[GShen]

Hi Scriverb,
I not if you received my lastest email. I decided to post here.
I tried this and it still does not work. You are a little incorrect as to what I am trying to do. The field to the left of my combo box is the vendor number. The form is indeed open. I know this because I do not close it and also the vendor number displays fine in the field tblfbbh_Vendor. This has nothing to do with adding anything to the combo box. All I want to do is SELECT all the costlinks from my costlink table with the vendor number (on this table) equal to the vendor number (tblfbbh_Vendor) on my form. If you notice the data for the field tblfbbh_Vendor is the same as the data I am trying to match in the SELECT. It does not like it. I went as far as adding a unbound field on my current form and tried setting it equal to that. It just doesn't like it. If I hardcode the SELECT equal to a number as follows, it works:
SELECT tblclf_CostLink, tblclf_Description FROM tblCostLinkFile WHERE tblclf_Vendor = 31190

I looked high and low again in the help screen and there is no place that says you can do this. I am starting to think that this just cannot be done. Sorry for the delay. I was swamped yesterday. Just got to this now and I am as exasperated as before I went on vacation.... need another vacation.


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

 

[scriverb]

Well Gary, I have looked over your database form, SQL, and comboboxes. Don't see a thing wrong with what you are doing. I have done this many times and there should not be a problem referencing the value of a textbox on an open form in your WHERE statement.

We know that the SQL statement works with a direct value coded in and you said earlier in this thread that if there is a prompt for the value that it doesn't find any matching records. So, sounds to me like there may be a corrupted database or installation. Try opening a new database and creating a couple of new forms doing exactly what you are doing here. You can import the tables but create the comboboxes and textboxes all new. Just make a simple SQL select with the criteria reference to the textcontrol to see if it works. It should. If not then I believe you have an ACCESS installation problem. This is a technique that is simple and expected to work. Because it makes no sent not to it does make you crazy. Go to another machine if you have one and test this technique out. You will probably see that it works fine.

Post back with your findings.

[COLOR=006633]Bob Scriver[/color]

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

 

[janetlyn]

Bob, thank you for your suggestion in the third box down. I went to check and see if my stuff was setup correctly and I had the [Forms]![FRM].... under the wrong column in the query. I have had two or three people look at this and could not find the problem. I have been working on this forever. If you were here I would hug and kiss you I am so happy to have found your solution. Have a most wonderful day, Janet Lyn

 

[GShen]

scriverb,
I don't have another machine to go to. I have tried it a number of more times. Funny you should mention about a installation problem. When I try and add a combo box using the wizard I get an error that says only 1 instance of this is allowed. It must be a security issue because someone else's signon, it doesn't happen. The tech guy doesn't want to hear about it. I will just have to live with it.

Janetlyn,
Did you get this working?

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