My Form won't realize I've fixed my Key Relationship problem 1

[Nevermoor]

I have a database with three tables: cases, firms, and manymany. Each case and firm has an autonumber key. manymany allows a many-many relationship by storing a case ID and firm ID in each row.

When doing data entry, I want to enter some data about the case, and then associate firms with it. Initially this failed because the case I was entering data into had not yet been created, so inserting a line referencing that case's soon-to-be ID violated the table relationships. In response I added the following code:

Private Sub Citation_LostFocus()
    Dim strSQL As String
        
    If IsNull(CaseNo.Value) And Not IsNull(Citation.Value) Then
        strSQL = "INSERT INTO Cases(Citation) VALUES (" & Citation.Value & ");"
    
        DoCmd.RunSQL (strSQL)
        Me.Form.Refresh
    End If
End Sub

Basically, it creates the new case once the first bit of information is entered (as a lost focus event). The code works, and once I enter the information I can see in the record navigation bar that I am no longer working on the * record. It does not, however, prevent the key error problem.

If I navigate forward to the newly created * record and back to the one I'm working on, I can suddenly associate firms without any error.

My question, then, is what command(s) to use instead of "Me.Form.Refresh" so that my form realizes that the table relationships are in fact not being abused.

Thanks in advance,
Frank

 

[Remou]

Perhaps Requery.

 

[Nevermoor]

No dice.

 

[Remou]

If you are entering into a form bound to the case table, saving the record should be sufficient, the SQL should not be necessary.

The usual way to do this would be to create a form bound to the case table with a subform bound to the manymany table, which has a combo showing the firm list bound to the firm ID of the manymany table. It would be similar to an Order, Order detail set-up as can be found in the Northwind Sample database.

 

[Nevermoor]

I'm trying to do things somewhat differently, I'm attaching my database to help explain, but I guess I don't really understand why my way can't work. I haven't done much with Access before, so I'm running on general VBScript / SQL experience.

If I'm doing something fairly simple bass-ackwards I guess I wouldn't be too surprised.

Thanks in advance!

 

[Remou]

I would not use the Lost Focus event for this kind of thing. This should suit:

Private Sub Citation_AfterUpdate()
    Me.Dirty = False
End Sub

 

[Nevermoor]

That's the ticket. Thanks!