combo box and for the next field to automatically display itself separately

[ozlander]

Hi, I realise that this question has been asked a thousand times on earlier occasions. However, I have searched and searched, read and read, trialed and trialed without success. Most questions and responses are way above my limited experience. I am missing the point completely!

All I need to do is to be able to select a suburb from a combo box on the form. One of my questions (and there are many) at this point, is if it were to work, the postcode field shouldn't have to be a combo, should it?

Unfortunately, the file is loo large to upload (how do you guys shrink them?). However, will upload a jpg of the form. I'll email the db, if anyone wants it! When it works finally, I would like to understand why ... because at the moment I do not understand why all the "stuff" (I know this is not a db term ... lol) does not work!

Tables are
lookupfield (for all info for combo boxes including suburb col3 and postcode col4)
clients (name, address etc) <---- here the combos for suburb and postcode are activated (at the moment .. on this copy anyway)
and another one which has no bearing on this case

Thank you in advance!
Oz

 

[shiv_379]

To shrink database files there are two things you should do. First thing is within access itself choose Tools>Database Utilities\Compact and Repair Database. The next step is get hold of winzip (trial version free from www.winzip.com) and zip the database.

As for your question, if I am understanding right, you want a field for the postcode that will fill automatically when the user selects a suburb?
Now I'm not great with forms myself, but my first impression on this is to set the rowsource of the combo box to a stored query. Within that stored query have a WHERE condition that uses the form!combo box (of the suburb) as a value.

I cannot remember exactly offhand but I think it would be
"WHERE <suburb_field_name_in_table> = Forms!<my_form_name>!<my_suburb_combo_name>"

Obviously replace the <...> bits with the appropriate names of fields and controls.

 

[shiv_379]

If you want to use a text field instead of a combo, use the Record Source value of the text box, but disable the box to stop users entering data manually (if you wish).

 

[neileg]

First off, I'm from the UK so I don't know how your post codes work. You seem to imply that there is a unique postcode for every suburb. If this is the case, you don't need to save the post code anyway. You'll need a table that holds the suburbs and the corresponding post codes. Then any time you need the post code, you can use a query based on the suburb.

The post code text box on your form should be based on a query that uses the contents of your suburb text box as a criterion.

If I have misunderstood the relationship between suburbs and post codes, let me know.

 

[ozlander]

I have no idea what I want... except that I know what should be attainable...all I need is to be able to use a combo box for suburbs and then to see the equivalent postcode (as in equal field) in the next separate space on the form... and I need it to be explained in extremely simple terms... I am not an idiot...but I feel like one here ...

Based on the above table structure, can anyone instruct me on what I should do...

OK...#1 was to get rid of the second combo box for postcode...easy as...done...then what??????


Thanks again, in advance (as you are awake when I have to sleep and vice versa)

 

[neileg]

Try this sample.

 

[ozlander]

lol.. it seems to work as I wish...but now, what do I do with it....
I checked the event on the form...nope..

However, I will never give up... the key has to be there somewhere... I will keep looking...I am so old ...

bb as soon as able ( with I hope results)

ty

 

[neileg]

Sorry, I changed the approach. The combo box has two columns, one for the suburb and one for the postcode, although the second column width is set to zero so you cant see it. The text box for the post code looks at the combo box and reads the value from the second column. Just to confuse things, Access numbers columns starting at 0 so the second column is referred to as column(1)

Hope this helps.

 

[ozlander]

At last I have it working

I was taking the wrong turn right from the beginning ... I had set up the combos in the tables rather than from the form itself. In addition, although I had queries, I was not using them ... lol ... no wonder it wouldn't work.

Thanks Neil for helping me understand
Oz

 

[ozlander]

And thanks Shiv ... now that it is working, I understand your earlier advice

 

[neileg]

Access allows you to set up all sorts of things in tables. However, I usually regret applying them there. The exceptions are indexing and some straight forward validation.

Pleased you got past your 'block'.