How Can Form Field Remember Value?

[Garindan]

Hi Everyone, i saw somewhere how to do this about a month ago but i've just spent 2 hours looking and can't find it anywhere! Doh!

Basically i have a Customers form and tables with address fields and i'm trying to reduce the time spent entering addresses. The counties field uses a lookup linked to a counties table which has all 40 england counties in, so the user can just select the required one.
Obviously to do the same with towns/cities and different areas would need thousands of entries so it cannot be done. I saw somewhere that there was a way to have access remember the values already entered in a form, so that they would only need to be entered once and thereafter would appear in the list, but i can't find where it was!

Could anybody help me out with this? Or does anyone know a link to a tutorial? I am not sure but i think it may have been a tutorial i saw rather than a forum post. Any ideas?

Thanks in advance

 

[Sharky II]

i think if you change the field from a textbox to a combo box it does this automatically?

 

[Garindan]

Just tried it, it doesn't seem to? How does the combo box need to be set up?

 

[Sharky II]

i don't think it matters if it's a combo or not actually (but combo allows user to select from a list, obviously). You have to set your row source.

So in my form i have a drop down/combo, and the row source has to be set as whatever you want. In this case for me it's for name:

SELECT DISTINCT [Customer].[FirstName] FROM Customer ORDER BY [FirstName];

Then if you type 'j' it will come up with Jack, James, Jim, John, Johnny etc

Cheers

 

[Garindan]

Yeah that sounds like it will work, cheers Sharky II!!

Err, i'm not too good at code at the mo. Say i have a 'Town/City' combo box, and i want it to look up town/city field in tblCustomers, how do i do that??

 

[Sharky II]

Hi

Go to the design view, right click the field you want, and then go to properties/data. On the row source, you'd have to have something like:

SELECT DISTINCT [tblCustomers].[Town] FROM tblCustomer ORDER BY [Town];

This assumes that 'Town' is the name of the field. Good luck!

Edd

 

[Garindan]

Yep, worked it out too. If you change/have combo box Row Source Type as table/query then you can use the 'build sql query' option too! Cheers Edd!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Garindan]

One last question..... Any idea's how to have the combo box only show each entry once? i.e No duplicates?

e.g if there are 100 entries for London it's gonna show London 100 times!

Thanks.

 

[Sharky II]

hi

that's what the DISTINCT does mate

 

[Garindan]

Cheers mate!!!!!!!!!!!!!!!!!!!!!!!!! Sorry for being such a dufus! LOL! Thankyou!