Using text descriptions in a combo box to write numbers in a form...

[chin chin]

Hi,

I am a beginner using Access 2007 and I'm currently working on making my forms user friendly. I have some numbers to enter in a form (corresponding to business names in a different table) and would like for the user to be able to choose from the names rather than the numbers.

The way I was planning to do this was to make the number field invisible and write to it based on the selection from an unbound combo box containing the names. I thought there might be a tidier way though so any ideas welcome.

One more issue...

I would like to make the forms so that clicking next record will not create a new entry after going past the last record. It is pretty annoying if you do this by accident especially if there are some 'required' fields so you can't just click back again.

Thanks a lot for any help - this board really is a godsend.

Chin chin

 

[vbaInet]

For your first issue, look into using a combo box. Drop a combo box onto your form and follow the wizard. Play around with it until you get what you want.

Second problem, set the AllowAdditions property to No.

 

[chin chin]

For your first issue, look into using a combo box. Drop a combo box onto your form and follow the wizard. Play around with it until you get what you want.

Second problem, set the AllowAdditions property to No.

Thanks for pointing out the allowadditions property - that'll work.

Re: Combo box, I am already using one but I need it to show the business names yet write the corresponding 'business number'. I've messed around for a while trying to get it to do that but the implementation I've mentioned above is the only way I've found to make it work and it's a bit messy.

Chin chin.

 

[vbaInet]

Type the number in the textbox, set the Control Source of the combo box to that textbox. The first column and bound column of the combo box must be the Business ID.

 

[chin chin]

Type the number in the textbox, set the Control Source of the combo box to that textbox. The first column and bound column of the combo box must be the Business ID.

Could you please clarify 'type the number in the textbox' and also, what is the 'first column' please? The others are done.

Chin chin

 

[vbaInet]

I was just telling you that once its setup when you type the number in the textbox it will set the combo box's current record.

Drop a combo box onto your form, select the Business ID followed by the Business Name. Follow the wizard to the end and ensure that on one of the options you hide the first column. The first column is the Business ID

 

[chin chin]

It worked - nice one. Yet again I was overthinking things.

Thanks a lot,

Chin chin

 

[vbaInet]

You're welcome!

You can also use a textbox to display the business name if you don't want the combo box. Just hide the combo box and set the textbox's control source to this:

=[Combobox1].[Column](2)

 

[chin chin]

You're welcome!

You can also use a textbox to display the business name if you don't want the combo box. Just hide the combo box and set the textbox's control source to this:

=[Combobox1].[Column](2)

Hmm.. can't get this working - I assume I replace combobox1 with the name of the one I just set up (combo25)? And column with the name of the column?

 

[vbaInet]

Only replace combobox1.

 

[chin chin]

Got it - for some reason it needed a (1) at the end not (2). Nice trick. Thanks again.

chin chin

 

[vbaInet]

Oops... that was my bad. Columns start from 0, so yes the second column is 1.