Edit form problems

[allie357]

I am fixing a previously created database created by someone else. In
their original db, there were not normalized tables, and this edit from
searched one table. Now I have a new edit form with the violator's
name in a combo box and many other combo and text boxes throughout. I would like to have a button open a search and replace box like the
original but right now it is telling me that it is linked to other
tables so I cannot change the record. Any suggestions on how to improve the design? Should I used forms and subforms? Thanks in advance for any help...

In the original form it was all text boxes and when you pressed the
find record button it opened a search and replace box with this macro
below.

Option Compare Database 


'------------------------------------------------------------ 
' Edit_Violation_Goes_to_last_record 
' 
'------------------------------------------------------------ 
Function Edit_Violation_Goes_to_last_record() 
On Error GoTo Edit_Violation_Goes_to_last_record_Err 


    DoCmd.GoToRecord acForm, "Violations_Edit", acNewRec 


Edit_Violation_Goes_to_last_record_Exit: 
    Exit Function 


Edit_Violation_Goes_to_last_record_Err: 
    MsgBox Error$ 
    Resume Edit_Violation_Goes_to_last_record_Exit 


End Function

 

[VicRauch]

Could you explain what you want to do by just explaining what data you have in what tables, and how you want to search and what you want to replace? I think that would help to understand where you want to go.
Thanks,

 

[allie357]

My database

My database is for policy violations with tables for violators, preparers (people who do prepare the violations), original documentation, missing documentation, departments, policies, and the violations table.

The search form is based on the violations table and the old one was only based on the violations table (it was not normalized) and the new one is based on the violations table with a combo box that allows the user to choose a violator's name from a combo box. I wanted to add a button to open a subform to allow the user to add a violator's name if is not in the list.

The old search form opened a version of the find a replace form. This new search form needs to allow a user to find a record based on the violator's name, search for words in the details field, or any of the other fields on the form.

Since I added the combo box the old search button does not work. I hope I have explained myself.

Thanks.

 

[VicRauch]

It looks like you are asking two questions.
1. How to add an item to a combo box.
2. How to search on more than one field.

Please don't worry about what used to work. All we should be caring about now, is what do you want the new form to do. We really don't care that some other form has done anything. Let's just concern ourselves with the new form. (Actually, the old form discussions just confuse things.)

Thanks,

 

[allie357]

Edit/Search Form--searching all fields?

Yes, I do need to know how to do both things and I need them to the whole form to work.

1. How to add an item to a combo box.

As for adding an item I do know about the Not In List event and adding code there, but someone told me that it would cause problems when there were two people with the same name in the list, so I should have a subform pop up to add the person to the Violators table. I would like the combo box to requery and add the person to the list once they are added to the table... is this possible?

2. How to search on more than one field.

This is where I am having problems. Some of these fields on the form are combo boxes and some are text boxes, but I need to be able to search them all, including the combo box that I mentioned above that has the names in it.

Thanks in advance for any help/advice...