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.

 

[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

 

[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

 

[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

 

[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?

 

[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

 

[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.

 

[jcbhydro]

Pat H,

I have repeated in 3 separate posts that I was looking for a method to input free text into a 'Member Name' field, NOT repeat NOT into a 'Member ID' field.

jcbhydro.

 

[nanscombe]

Pat H,

I have repeated in 3 separate posts that I was looking for a method to input free text into a 'Member Name' field, NOT repeat NOT into a 'Member ID' field.

jcbhydro.

Hang on a minute.

You have told us that the rowsource for your Members Name field is as follows.

SELECT Mail List([COLOR="Red"]Member ID[/COLOR]), Mail List(Member Name), Mail List(Surname) From Mail List ORDER by (Surname)

And that the Bound column is 1.

You may be choosing "Member Name" from the list but it is "Member ID", column number 1 in your combo box, that is being saved to your table.

 

[gemma-the-husky]

I have done this.

As an example, I have a quote system that loads a list of permitted product descriptions from an external system. If they want to change the description, originally they had to enter it into the external system, and re-export it.

we changed that, so they had a combo box of existing descriptions to guide them, but limit to list was set to no, so they could actually enter whatever description they wanted.

you gain some benefits, and lose some benefits by having free text fields, though.

 

[nanscombe]

That certainly works when you have a combo box which derives its values directly from the underlying table but, in this case, the OP is using a lookup table and saving the ID instead so it won't work.

Option 1: "Other" option, in lookup table, plus free text field in data table
Option 2: Add new values to lookup table

And possibly others I haven't thought of.

 

[jcbhydro]

Nigel,
I have been away from home for a few weeks but I am still keen to achieve the object previously outlined.
I am confident that your proposed coding will achieve the object but in my inexperience I have failed to translate your sample coding to work with my database structure. If I define my structure in more detail perhaps you would kindly advise me further.
The membership details of the society are stored in table 'Mail List'. The PK is 'Member ID' and 1st Name & Surname are concatenated to form a 'Member Name' field.
A 2nd table 'Group Members' gives details of which members are affiliated to one or several of some 50 Groups. The table has fields Group ID, Group Name, Member ID & Member Name. Groups occasionally admit members prior to their joining the society and who are not included in the 'Mail List' table. Hence the need for a text box input as you have depicted in your code.
The entry is made using a Group Members Entry form equivalent to your 'Mailshot' form.
Entry is to be made either from the drop down list of 'Member Name' or as a text box entry for non-members.
I would be extremely grateful if you could provide me with further guidance to translate your coding to suit my database structure.

Regards,

jcbhydro