Combo / Drop down in a query

[tiggy]

Hi, this is probably simple but how do I make a field in a query into a selectable drop down?

I have a table upon which I have based a simple query that asks a user to type in a country and a weight which produces a rate and a transit time.

Looking at it though the user will have to be a good speller or typist to avoid '0' returns. If I could make my country box a selectable list it would make it easier.

I have made a form based on my country table that uses a drop down thinking that I could incorperate this into a query but I can't seem to get anywhere.

Any help appreciated


Thanks


Tig

 

[Mile-O]

If you have combobox on your form with the list you require then it should be easy to get the results you want:

Assuming your form is called MyForm and your combobox is called MyCombo then you need to put this in the criteria of the country field.

[Forms]![MyForm]![MyCombo]

 

[tiggy]

Thanks

but! (isn't there always a but)

Being new can you tell me where that statement needs to go?

I opened a new query double clicked 'country' from tblCountry and then typed [FORMS]![COUNTRY]![COMBO2] in the criteria
box but no joy I also tried [FORMS]![frmCOUNTRY]![COMBO2]

I know I am being a klutz and its probably simple (but then again so am I)

Thanks


Steve

 

[Mile-O]

You have the right idea - the form needs to be opened and it needs to have a value selected.

 

[tiggy]

Sorry,

Tried all day but I can't see the wood for the tree's.

I have tried many things and this is the most promising but it still doesnt work

1. I have made a query and then made a form based on it. I have then opened the form in design view and changed it to make it a combo box. The query runs as originally intened

[Enter Country Name]
[Enter Weight]
Rate
Zone

However the combo appears in the answer and not the question.

2. I have made an additional table with only a country field and made it a combo. In the query I have made a relationship between it, the country table contain all my facts and the rate table. Still no good.

Any help appreciated otherwise please snail mail me C/o the Sunnvale Mental Assylum.

Cheers

Steve

 

[tiggy]

Perhaps I have not phrased my question very well, I am very much a novice so perhaps I do not make it very clear.

I would like, from a form, to be able to select a Country, once I have selected the Country I would like to be asked to input a weight which would produce a result based on details in 2 tables.

I have attached a zip of what it is I am trying to do, any advise please.

Tig

 

[Hayley Baxter]

Try this and see if this is what you are looking for.

 

[tiggy]

Hi H

Yes, yesterday thats what I wanted, I have however been up all night and tried to refine it a bit. I have read heaps of stuff and I know I am quite close but I can't see what I am doing wrong. If you don't mind would you look at the zip and let me know what it is I am missing so that I can further understand it.

Cheers

Tig

 

[Hayley Baxter]

Here's what I think is happening. In you query under the country field you have [myform]![mycombo] however on the form itself the bound column is 1 which is the countryID and not the country fields so place [myform]!mycombo] under the countryID field rather than the country field. You then want to open the query on selection from the combo or report however you want the information displayed.

 

[tiggy]

Thanks H but that did not work, I'll keep on scratching my head and reading the really useful MS help files

Cheers

Tig

 

[Hayley Baxter]

It's working for me. Which form do you want me to amend for you? You have two forms with a similar setup.

 

[tiggy]

Hi H

Its both as they are both getting their results from different tables but above all else I need to know exactly what I did wrong so that in future I will know.

In the query I went to the column

CountryID
Country

and then put in the expression [forms]![frmExpressCourier]![cboCountry]

and removed the statement from the other column.

However on opening my form the rate, DocumentTransit, and ParcelTransit are all saying #Error still.

Same goes for the frmEconomyCourier

What I expect to happen is to select a country from the dropdown, select a weight from the dropdown and heypresto the rate and both transits appear.

Boy am I glad I don't do this for a living!!


Steve