Drop Down List with free text option

[jcbhydro]

Good Evening All,

I have a simple data entry form with drop down facilities on 2 fields. One of these fields incorporates a drop down list from a table but there are occasions when I wish to make a free text entry for the single record, but do not wish to add it to the drop down list.
I have tried to achieve this with a Combo Box but without success, although I am sure that I have read that it is possible.

I have trawled through the forum looking for a similar query but didn't find one.

Any suggestions would be gratefully received.

jcbhydro

 

[plog]

In design view, you change the 'Limit to List' attribute to No. The row source must not be a query based on all the distinct values in that field. Instead you would use a table of 'valid' values for the drop down.

 

[Pat Hartman]

Another solution is to use an unbound combo and a bound text field. The user chooses an entry from the combo and the AfterUpdate event places the text in the bound text box. That gives the user to choose from the combo or enter something free form.

 

[jcbhydro]

Changing the 'Limit to List' property to 'No' produces the column width/bound column error.
My values are Column Width 0cm. 2.54 cm, 2.54 cm & Bound Column 1.
Which values should be adjusted?

Regards,

jcbhydro

 

[Pat Hartman]

If your list is typical with a numeric key value and a text display value, you will need to use my solution.

 

[nanscombe]

An option of "Other" in the Combobox and a Textbox which is only active when the "Other" option is picked?

 

[jcbhydro]

Good Morning again,

As a relatively new Access user I confess that I am somewhat confused!

In the Form Property Sheet the following settings apply;
Control Source Name
Row Source SELECT[Table1][Name]
Row Source Type Table/Query
Bound Column 1
Limit to List Yes

This arrangement gives me the facility to select a Name from the scroll down list from Table1.
However, there are some occasions when I wish to type in a Name which doesn't appear in the Table 1 Name list, which I want to be added to a data sheet but NOT to Table1.

Is there a parameter in the Property Sheet which needs tweaking or must I add a separate data entry box to achieve this.

Regards,

jcbhydro

 

[Pat Hartman]

If you change limit to list to No, you will be allowed to enter names that are not in table1. They will not be added to table 1. You are not updating the RowSource, you are placing a value in the ControlSource.

BTW - if the name of the column is really "Name", now would be a good time to change it. In addition to the proscription against using spaces and special characters in names, you should also not use VBA, Access, and SQL reserved words. "Name" is the name of a property and almost every object has a Name property so unless you are very careful when coding to ALWAYS encase your "Name" in square brackets, you could end up with subtle, strange errors.

A simple way to avoid reserved words is to make compound names such as CustName, StudentName, ProductName, etc. They are also more descriptive.

 

[jcbhydro]

Thanks again and I'm sorry to be a pain, but I have already mentioned that with Limit to List at No, I get the column width/Bound Width disparity error. The column width is currently 0cm,2.54cm,2.54cm (probably a 1" default value) but I don't know which other width to change. There is no Bound Width listed on the property sheet.

No, my field is not listed as 'Name', it is 'Member Name'.

jcbhydro

 

[jcbhydro]

Are their any suggestions as to how to obviate the 'Limit to List/No' error mentioned in my previous post?

Regards,

jcbhyddro

 

[Pat Hartman]

If the RowSource contains multiple columns, you CANNOT use the same column to hold non-RowSource options. The only time you can use the limit to list = no option is if your first column is the visible value and that is the value that is being saved. Your combo has three columns so it can't be used the way you want. Try using the method I suggested.

 

[jcbhydro]

Pat H.
You have been incredibly patient with my inexperienced efforts to solve this problem. Could I try your patience further by asking for a methodology to set up your suggested unbound combo and bound text field as I don't have a clue as to how to go about it.

On the subject of the Row Source data, it currently reads; SELECT Mail List(Member ID), Mail List(Member Name), Mail List(Surname) From Mail List ORDER by (Surname); Are all these references necessary to obtain a drop down table of Mail List(Member Name). In my inexperienced opinion I would have thought that the first SELECT term would suffice to obtain a drop down list of Member Name. I am unaware hoe the Row Source data is generated as I can't find it in any of the Queries I have built.
If my naive assumption is correct then the Limit to List No solution would be the simpler solution.

Regards,

jcbhydro

 

[nanscombe]

I've put together a little demo of how I would use the "Other" option together with a free text name field.

frmMailshot - Entry form
qryMailshot - Example output

ID	Mailshot Name	Member Name	Surname			Other Name	Other
3	Fred Smith	Other		<Please specify>	Fred Smith	Yes
9	Joseph Blake	Other		<Please specify>	Joseph Blake	Yes
7	John Smith	John		Smith					No
6	Fred Jones	Fred		Jones					No
5	Andrew Forsyth	Andrew		Forsyth					No
4	George Adams	George		Adams					No

ID - Record Id
Mailshot Name - Derived name dependent on "Other" or not
Member Name - Member Name
Surname - Surname
Other Name - "Other" name, if entered
Other - Derived field dependent on "Other" or not

Feel free to have a nose around.

 

[gemma-the-husky]

just a question

out of interest, why do you not want to add the new record in the combo box to the underlying table?

 

[Pat Hartman]

I was envisioning this to be something like a ReasonCD where you had a list of standard reasons but you wanted the ability to add something different on occasion. Now that you have enumerated the columns of the combo, I (like Dave) wonder what exactly you are trying to accomplish. I would expect [Member ID] to be a foreign key and foreign keys MUST contain a valid value so you can't simply save something else in its place, especially if that something else is text.

 

[jcbhydro]

Nigel,
Thank you for your considerable effort.

I think that I twig how it works and will try to reproduce the technique in my daatabase. However, am I correct in assuming that it will be necessary to introduce a single record entitled 'other' into my Member name list?
I also noticed that introducing an 'other' record merely replaces a previous 'other' entry. Surely that can't be the case.

regards,

jcbhydro

 

[jcbhydro]

Response to Pat H.

You are misunderstanding. Member ID is a foreign key in the relevant table and does contain valid data.
As I have stated previously, I am using a drop down list sourced from Table 1 to select a Member Name. Occasionally, a non member joins a Group and it is their name which I wish to incorporate as free text without adding it to the Table 1 list.
I trust that all is now clear.

jcbhydro

 

[Pat Hartman]

No I didn't misunderstand. That is exactly what I told you you couldn't do. Your table stores Member ID as a foreign key. You can't just plop a text string in its place.

 

[gemma-the-husky]

you can only do what you want if you actually store the name text in the table.

in this case, the combo box offers a guide to the available names, but you can store other text.

As Pat says - if your combo box is based on a query that uses a record id as the first column, the system will store a number rather than a name in the bound field.


----
it comes down to the entry field. if it's numeric say, then it stores a lookup to the real names table. if it's text, it stores data, either taken from the real names table, or inserted manually.

what you lose is the link to the lookup table. if a name in the lookup table changes, it won't affect the text stored in the entry field.

just try testing it all, and see.

 

[nanscombe]

Nigel,
Thank you for your considerable effort.

I think that I twig how it works and will try to reproduce the technique in my daatabase. However, am I correct in assuming that it will be necessary to introduce a single record entitled 'other' into my Member name list?
I also noticed that introducing an 'other' record merely replaces a previous 'other' entry. Surely that can't be the case.

regards,

jcbhydro

Using my example of a mailshot ...

Yes, you would have a single entry in the members table representing the "other <Please specify>" member. The free text name would be stored in the Mailshot table but only when that entry has been chosen.

The output query uses the name data from the Members table for all of the records except the "other" one, in that case it substitutes the data specified in the Mailshot (my example) table instead.