Subform Problem

[ScottGem]

Using a composite key means you have to join on 2 fields all the time. And that can be cumbersome as well as sometimes having unpredicatable results. For example you may find queries returning double the number of records you expect. I don't know of anyone would would want to deal with joining tables using a composite key.

You don't have to use comboboxes, but that is a better way. As a general rule, I prefer to have users select values rather then type them in. Typing can produce errors that waste time.

As for Lagbolt's code, there are easier ways.

This one line:
DoCmd.OpenForm "formname",,,,acFormAdd,,Me!txtCaseID
will open a new form in Add to a new record and pass the (autonumber) PK to it. You would then use the following in the On Open event to set the FK Value:

IF Not IsNull(Me.OpenArgs) Then
Me!txtCaseID = Me.OpenArgs
End If
txtCaseID would be a hidden control. Finally, You would have an unbound textbox with a controlsource of:

=DLookup("[ClientID]","tblCases","[CaseID] = " & Me.OpenArgs) & "-" & Dlookup("[CaseNum]","tblCases","[CaseID] = " & Me.OpenArgs)

So when the new form is open, the composite ID is displayed, the CaseID FK is correctly assigned and the user can start entering the appropriate info.

 

[Gilrucht]

Scott, I;ll try what you suggested with the code you posted and see what happens. I guess the reason I have been so resistant is that I have been working on this db on and off for over a year now and I have well over a hundred tables set up with the composite fields as the FK. I am not looking forward to redoing all those tables. and relationships. Not to mention it is hard to change a mindset that has been ingrained for so long.

 

[ScottGem]

Understandable. More importantly if things are working as you want, it might be better to let them alone. I wasn't aware of how far along this project was. You might want to make a copy and make the changes in a copy to see how they work out. Just as long as you understand, you don't have a fully normalized database that follows "Best practices" in design.