Multiple Primary Keys

[BDawg04]

I made a database that was working fine. It was only after I had finished the database, and data was being entered, that I was informed that the field I designated as the primary key would not work. Origanally, I used 'OffCit' as the single primary key. Now they tell me that one 'OffCit' can be entered several times for different reasons. To solve this, I thought about using autonumbers but have been told, and seen, that this may not be a good idea. So, I am trying to use multiple primary keys. So far I have had no luck. I have attached a screen shot of the tables and relationships. I have 'OffCit', 'AreaofLaw', and 'EffectiveDate' as the primary keys. The same 'OffCit' and 'AreaofLaw' will be used several times in different records. That is the reason for the third primary key. I am wondering if I have set this up right or if there is something I overlooked. Perhaps someone has an idea that may work better. Thanks for any input.

 

[Pat Hartman]

Autonumber is the best solution. Why do you think it will not work.

 

[BDawg04]

Sorry for the long wait. We've been getting ready for an audit and things have been really busy. I have attached a shot of the screen. Things are working ok but there is one little kink in the system. The subform 'Memo and Ressponse' is combining all records of the same 'OffCit'. I need them to be seperated by each individual record. For example, there are 6 main records. Three of them are under the Official Citation of Public Law 107-56. You can see on the screen shot where it shows 25 records under that Official Citation. The problem is that those 25 records should be spread over the three records. The first Public Law record should have 21 'Memo and Response' records. The other 2 Public Law records should have 2 each. I have tried to use the autonumber to fix this but have had no luck. I guess I'm using it wrong. The database works fine with the exception of this one thing. Any ideas? Do I need to start over from scratch? Thanks for the help.

 

[Pat Hartman]

Check the master/child links for that subform. It is not being synchronized with the main form.

 

[BDawg04]

Thanks for the help. That seems to do the trick. I have one more question if you have time to help me.
I have a search box at the top of that screen capture. I have the following code for the search:

Private Sub OffCitSearch_AfterUpdate()
'Find the record that matches the control.

Me.Recordset.FindNext "OffCit = '" & Me!OffCitSearch & "'"
If Me.Recordset.NoMatch Then
MsgBox "No Matches Found."
Else
If Not Me.Recordset.EOF Then Me.Bookmark = Me.Recordset.Bookmark
End If
End Sub

This does find a record with the correct Official Citation. I have tried both FindFirst and Find Next with neither doing what I want. I know that FindFirst starts with the beginning and looks until the end and that FindNext starts at the current position and looks until the end. I was wondering how to modify this code to find the next Official Citation that matches the criteria. Right now all it does is find the first match and then, if i hit enter again, it just goes to the next record in the table, not the next match. How do I make it find the next match?

 

[Pat Hartman]

Use an unbound combo to do the search. Let the wizard build the code. The other alternative is applying a filter rather than using FindNext.