Indexes

[Eljefegeneo]

I have been trying to solve a problem with indexes and how to use them in tables. Please see the attached DB. All I want to be able to do is to select an "affiliation" from the unbound combo box on frmContacts and update the list of "affiliations" which are recorded in the tblNameAffiliation. What is driving me crazy is that I have successfully done this before and copied the various codes, but it won't work. Specifically I can add a new "affiliation", but it doesn't show up in the record without going to a new record and going back to the old record. And the same set of data seems to show up for the next blank record when it shouldn't. I think I have set the indexes correctly, but then after several hours, my mind has gone blank as to what to do next. I hope someone can see where I am making my mistakes. Thanks.

 

[jdraw]

I looked at your database.
The control on your form is called ID not NameId. SQL had to be changed. I moved the form.requery to after the db.execute command.
Your forms were not linked?? I linked them on NameId.
The rowsource for the combo had wrong fields linked - I changed that.
I've renamed my copy attached. Give it a try.

 

[Eljefegeneo]

Thank you. I now see where I was making my mistake(s). Trying to work too fast and not taking into consideration that when I changed a field name in the table, I had to change its name in the form. First get the table correct and then make the form. Don't set up a temporary table and then change names. Another lesson learned. Works great!

 

[jdraw]

Happy to help.

 

[Eljefegeneo]

I have been updating the DB, added another sub-form and it seems to work fine, but, and a strange one at that, when I try to delete a record from the table, it won't let me, indicating that there is a related table. When I added a delete button to the form, and try to delete a record that way, all of a sudden I am getting Error 3134. this occurs when I try to update the "affiliations". I thought about this and as a backup, I probably should have a "do not contact" button in the table and form so that I do not duplicate data. But the error message has me stymied. Everything works fine until I try to delete a record, then the code for after update of the combo box seems to crash. Why is this happening?

 

[jdraw]

I'm not sure what you are doing. Your affiliation table has duplicate entries for "b".
Your DELETE button's click uses a macro. I don't use macros.

 

[Eljefegeneo]

I was trying to delete a record in tblNames from the frmContacts. Using a macro it wouldn't work. I am now using VBA instead of a macro and set referential integrity to cascade deletes and it seems to work now. Will continue to test it.

 

[Typ0]

I haven't followed along exactly what you are doing...be careful with those cascade deletes though as they will delete data you might not want to delete. I generally don't set up database wide relationships but my tables are all designed properly. Then I define relationships in queries as I go along...you don't want to delete the last affiliation of a user and have the user record disappear do you?

 

[Eljefegeneo]

Thank you for your input on this. Yes, I would not like to have a record deleted if I deleted an "affiliation". But I would like to be able to delete a record of a entire contact without the code for the "affiliation" combo box OnUpdate going haywire. When I tried to delete a contact record, it wouldn't permit me to do so since it said I had a related record in another table. So have I set up the relationship wrong? If I set the join for referential integrity and cascade deletes it seems to work OK. I have never had this problem before, that is, not being able to delete a record. Are there any general rules I should be following with relationships?