Queries for Combobox

[davies107]

Hello everyone,

can anyone help me? I have a combo box that will get values from a different table or query. i am having a problem with the query and the row source on what to write so i could get values from the table/query. i tried with the resources i gathered but still i dont get the right values.

Field names:
RefYearID
RefYearName
EscName

Query name:
QueryTEST

Table name:
TableHighLevel1


i would be delighted if someone could help me.
Thanks.

 

[John Big Booty]

I'm sorry, but what is the question

 

[davies107]

my question is to how to get values from my combobox to other table/query.

ex.

i have a combobox in SAMPLE FORM. i dont want to get the values from the rowsource of SAMPLE FORM, i want to get values for my combobox to other table/query.

 

[John Big Booty]

You can reference a combo in a query by using the following criteria;

Forms!YourFormName!YourComboName

Is that what you mean

 

[davies107]

okay thanks.. but how about the row source of the combobox in the form's Property Sheet? and the control source of the text box that will show the values selected from the combobox?

 

[John Big Booty]

If you want to reference the combo's Row Source on the current form you can use the following to populate an unbound text box;

= YourComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X represents the number of the column you wish to display, remembering that the columns in a combo or listbox are numbered from zero on up.

 

[davies107]

i tried your sample before about combobox (same as your reply now) and it works but the only problem is that it goes universal, i mean whenever i chose something from the combobox the entire textbox will get the same values from the combobox.

attached is a photo.

every Year should have a different Escalation (depends on the combobox selection), not that when i clicked Year 2009 every Escalation will be 115%... thats why i wanted to get values from other table/query. i did create a separate table/query for this but i cant get the right values.

 

[John Big Booty]

OK, you have a continuous form. With a continuous form all derived controls will show the value related to the control that currently holds focus.

You can get around this by using a query as the Record Source of your form, in that way you can explicitly populate your fields. You may well find that your Record Source is not up-datable, so you will need to use a pop up form to add new records or edit existing records.

 

[davies107]

now, im kinda confused ...

another problem is that i dont need another pop-up form, these combo boxes and text boxes should be in one form.

 

[John Big Booty]

They would all be on the one form.

However I'm reasonably sure that you will find that if you use the sort of query need to populate the form as required, that it would be non up-datable. So to add or edit records you would need to create a form similar to what you currently have but in single record mode (filtered to the appropriate record) to allow edits or additions to the underlying record source.

 

[davies107]

it will take me some time to do this because im not sure if i got what you mean here. sorry im not an expert with Access. anyway, thanks John.

 

[John Big Booty]

I'll have a look and see if I have something in my samples library (I can post) when I get home.

 

[davies107]

thanks a lot John.

 

[John Big Booty]

Have a look at this sample.

 

[davies107]

actually i have this kind of function in my DBS which i got it from one of your samples also - adding or updating records or items...

 

[davies107]

my question here is kinda different. its not adding/updating because the records are there already, its fixed - no more no less, like:

when 2009 is clicked 115% should appear in the text box
when 2010 is clicked 110% should appear in the text box
when 2011 is clicked 105% should appear in the textbox
when 2012 is clicked 100% should appear in the textbox

BUT, the result is per row, different input/results in the different rows (ex. 1st row 2009 then 115% will appear, on the 3rd row its 2011 then 105% will appear and NOT a universal result that when i click 2009 all the textboxes rows from 1st - 10th have 115%). every row has a combobox as per photo attached and it has only 1 textbox only (escalation).

 

[John Big Booty]

I think I already mentioned that you will need to create a query to collect all that data and display it as you are dealing with a continuous form so dependent controls will not work properly, as they will all show the data that is relative to the control that currently holds focus :banghead:

 

[davies107]

sorry for not getting it at first. yes i did create a separate query and separate table. when i created the Combox, i get the values from those tables and queries. but the problem is that i dont get the right values, i know there is a problem with my commands or placing of my commands.

in the EscName Criteria field in the query, i placed:

Forms!YourFormName!YourComboName ---> [Forms]![FRM_SearchMulti]![Combo765]

and in the Row Source of the Combobox i placed :

SELECT [QueryTEST].[RefYearID], [QueryTEST].[RefYearName], [QueryTEST].[EscName] FROM QueryTEST;

On the Control Source of my textbox:

=[Combo765].[Column](39)


did i miss something?