Help with Limit to List and Column Widths property

[djgolds]

I have a database set up for all of my customers. One table lists customer names and addresses. Another table lists types of businesses. It has 2 fields, one is the primary key auto number and the second field is the type(insurance, attorney, etc.). I have created a form for my customers and I want to be able to add to the "types" list if it's something that does not exist within the table I created for types of businesses. When I change the form property LimitToList to no so I can add new types, it tells me that the first visible column, which is determined by the ColumnsWidth property isn't equal to the bound column. Adjust the ColumnWidths property first and then set the LimitToList property. Can anyone tell me what I'm supposed to do? Thanks!

 

[jgoodman00]

It is more efficient to store a number which corresponds to the business type, but entering dat this way is difficult, so you create a combo box which displays the corresponding text, but actually stores the IDnumber unknown to the user. This can be achieved by setting the bound column to column 1 (the primary key), but setting it's columnwidth to 0. You then give the second field a positive column width so that ONLY the text shows. Then the user comes along & enters something new....the computer tries to add this new record, but it is trying to add text to an autonumber field. At this point, I am not sure of the easiest way of solving this, but a possiblity is to create a macro which will add the new record to the table when a new value is encountered. This is fairly simple, & then needs attaching to the 'On Not In List' property of the field.....

Hope this helps

 

[jgoodman00]

If & when u work out an efficient way of solving this, let me know please because it has plagued me in the past!!!

 

[jagilman]

Try using the not in list event this way. Create a form for entering the new data for the combo box. When the not in list event fires just open up this form in dialog mode, enter the required information and close the form. Then requery the combo box and you will have the new value.

John A. Gilman
gms@uslink.net

 

[jgoodman00]

That is the way I have always done it, but there should be some way of being able to add them directly.....

 

[jagilman]

Are you somewhat familar with using DoCmd.RunSQL? If so just trap the 'Not In List' event and then confirm that the user wants to add the new data to the list, then use the DoCmd.RunSQL "INSERT...(your info goes here)" to put the new value into your table. Then requery the combo box and to make life easier for your user re-insert the value they typed back into the combo box for them.

Send an email if you need more help. I could send you a sample db


John A. Gilman
gms@uslink.net

 

[djgolds]

Thank you all for your help on this, but I'm still going to need more (sorry, I'm slow!). John, I'm not sure what you meant by opening it up in dialog mode, and also, from your last email, no, I'm not familiar with using DoCmd.RunSQL. Sorry, but I'm one of those that needs step-by-step instructions But, I'd really like an easy way to do this. Can you tell me how I "trap" the Not in List Event? Thank you!!

 

[kramerica]

Not In list. If the item is not in the list I want to open a form and allow the client to key in the form. When the form closes and returns to the initial list box, i want the client to click on the initial list and the data will now be in the list.

Here is what's going wrong. A.) I get a warning that says, "Your item is not in the list". B.) when I return from entering in the new data the inital combo box is still displaying the item that was previously not in th list. If I click on the field and erase the data and then refresh the form it works. I want the data to erase and refresh automatically.

Bottom Line: So when I return to the combo box after I have already keyed in the new data HOW DO I: Clear the combo box, and have the combo "Auto refresh" so the list is current?

 

[jgoodman00]

If you search the Access help file for the Not In List event, I believe it gives an example of how to handle the even efficiently. A simple Me.Requery will requery the combo box & include any newly entered data.
Setting the field to null can be tricky, if the underlying field is required.

If you lookup the help example, it covers all of the points you have mentioned... James Goodman