Lookups for key fields

[MikeAngelastro]

Hi,

I have a few forms where combo box lookups are used to enter values from another table to the form's table. The combo box also represents a part of a multi-field key. If users don't pay close attention, it is possible to select the same item from the lookup list twice. Any attempts to move to the next field are of course blocked by Jet until the user changes the item to one that has not yet been selected.

The message that comes up is too detailed, as well as too crytic for nonprogrammers, and users must take extra time to figure out what it means. I have tried but can't find an event that happens before the Jet message when changing to the next record. I want to trap the error to provide a more useful message to users.

Any ideas for handling this kind of thing in an elegant manner?

Thanks

 

[jimbrooking]

The form's BeforeUpdate event will let you check the value of the combo box you are concerned about, and you can cancel the save if you don't like the value.

I don't know a thing about your database, but would suggest that the problem you describe might be handled better (or avoided) if there was only one form where the foreign key is selectable, that form being the one where the record is created in the first place. Ignore comment if it doesn't make sense in your context.

Jim

 

[MikeAngelastro]

Thanks Jim,

I don't know why I didn't try "Before Update". Also I'm not very familiar with the form's Cancel property. I find no reference to it in the index of the Litwin et al book. Anyway I got it to work thanks to you.

However, I would like to take this to the next plain. How do I enable users to close the form without having to change the duplicate value to cancel the record update? Currently, users must substitute an acceptable value and then delete the record before exiting the form.

Any ideas?

Thanks again,

Mike

 

[jimbrooking]

Mike -

Re the Cancel argument (to the BeforeUpdate event procedure) you just set it to -1 to cancel the save. I guess you found that out. You could also use the BeforeUpdate event for the combo box to check its value.

For your "next level" question, you could always reset the combo box to its previous value and allow the save to proceed - assuming the combo box had a valid value before the user selected a new one. See the Access VBA Help, and look for "OldValue".

Or you could make the RowSource property of the combo box a query that returns only valid values. (Hint: Develop the query in Query Design view, and when it works OK, look at it in SQL view, edit/copy the SQL, and edit/paste it into the RowSource field for the combo box.)

As I said in the original post, it is curious why the user gets a chance to fool around with the foreign key after it's chosen the first time. If you want to show the user the already-selected value, but not allow him/her to change it, you could select the combo box in Design View, click Format>Change To>Text Box, and set the Locked property of the text box to Yes. That would display the value already stored in the field, but not allow the user to change it.

HTH,
Jim

 

[MikeAngelastro]

Jim,

Thanks for your help.

I have a Kit table and a KitDetails table with a lookup to a Materials table. These materials may or may not be in any given Kit. The fields in the KitDetails table are KitID and MaterialsID. These fields combine to make a key to ensure uniqueness. I was able to create a query that provides Materials that are not in a given kit. However, when I use it as the row source for the combo box on the form, the combo boxes come up blank, although they do have values. When I use as the row source a query that shows all possible Materials, the combo boxes are no longer empty.

Do you know how I can get this to work properly?


Mike