Why Oh Why Oh Why?

[bomber2009]

Why Oh Why Oh Why does my list box not always work?

I created a list box on a form that contains five items. It points to a table that I wish to be updated when one of the five items are selected. However, it doesn't allow me to highlight any item for selection if I don't first, go to Design View and then return to the form.

After I do the above I can select items to my heart's content. Why is this? It doesn't seem normal. I'm using Access 2003 with XP.

Any ideas please?

 

[dbDamo]

Can you upload a copy of your Database so we can have a look

 

[georgedwilkinson]

The words "points to a table that I wish to be updated" scares me. Do you want to display data in a list box (or hopefully in a combo-box) or update data in a table? These are 2 very different things.

 

[bomber2009]

DbDamo. Sorry, I can't give you a look at the database as it belongs to someone's business and it's loaded with customer data etc.

George. When I say it's pointing to a table, I mean that the form is used to update or add records to that table. I don't understand why it works, (i.e. adds or amends data to the particular field in the table after I go into Design Mode and then return to the Form View), but it doesn't allow any updating straight away from the Form View, which is what I'd expect and what I believe it should do.

I've even tried to Requery and Refresh the form on Open but it makes no difference.

Also, do you know what method(s) is performed when it goes into Design View and then returns to Form View? If I can duplicate this with code then maybe this will work in the absence of any other answer.

Thanks Guys

 

[georgedwilkinson]

I'm just concerned about what you say in your first post:

I created a list box on a form that contains five items. It points to a table that I wish to be updated when one of the five items are selected.

What does a list box (or hopefully a combo-box) have to do with updating data in a table? The purpose of a list box/combo box, is to display data from a table or record set different from the table you are currently editing/displaying. You would then select a data element from that list/combo box to update or populate a field in the table that you are editing. It doesn't sound like that is what you expect to happen and that is why I am concerned.

 

[bomber2009]

George

When I'm creating a new record to be added to the table, or, if I'm updating details of a field in that table then I'm using a form to do so. The form was created using the wizard and is bound to that table.

I was asked to include one more field in the table, which I did. There are only five possible items that are to be entered into that field. Therefore, a list box was considered the ideal object for this purpose. The list box is bound to the table......but the problem is that.... it only does what I want it to do (update or add to the table) when I switch first to Design View and the return to Form View.

Until I do that it doesn't allow me to highlight any one of the five items until I go through the above process. I even tried using a text box instead of the list box and the same thing happened.

Adding records and updating records seems fine with the rest of the forms fields.....it's just this new one. (It's not a PK or FK....just an ordinary text field).

I'm not sure why going into Design View and back triggers it off so that it works the way it is suppose to work.

There must be an explanation.......I'm just stumped!

 

[georgedwilkinson]

Can you please post the SQL for the Row Source? Also, wouldn't it make more sense to use a Combo box?

 

[bomber2009]

George

This is the SQL from the Row Source.
SELECT tblClaimStatus.[Claim Status] FROM tblClaimStatus;

Was I not right in thinking that a list box was the correct object to use when there are a specified number of items (five in this case). Isn’t a combo used when listed items can be increased?

Thanks Again!

 

[bomber2009]

George

I Have now tried using a combo box but with the same results. Like the List box, it will allow editing to the desired field if I come out of Form View to Design View and then return back to Form View.

I don't understand why that is but, what if I duplicate the methods (with code) that Access uses when going to and from Design View? This presumably would work wouldn't it? Or, is that just the wrong way of going about this problem?

Thanks

 

[georgedwilkinson]

I'm not sure why you would want to patch the problem with code? Out of the box, this should work. It works millions of times a day. I've personally gotten it to work...I don't know how many times.

Can you post the database or a sample database that displays this behavior? Perhaps I (or someone else) can see if it's a problem with your database, your development methods, or maybe even with your environment (only on your computer?).

 

[dcb]

I would suggest that it is something in the table that you are trying to update:
The column you are updating does not allow duplicates?
The column is a different data type?
The db is expecting a record in another table?

Have you ruled out these?