Can't pull form textbox value into query

[nschroeder]

I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:

Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question
Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields
AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field

The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:

    DoCmd.OpenQuery "LoadAuditQuestions"
    Me.AuditQuestions.Requery

The SQL for LoadAuditQuestions is

INSERT INTO AuditQuestions ( AuditKey, QNum, Question )
SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question
FROM Questions
ORDER BY Questions.QNum;

I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly. Why won't this work when run from the event?

[Update] I added the attachment so you can see what I mean. The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.

 

[JHB]

Change it to the below:

Private Sub BorrowerName_AfterUpdate()
    If Not tbAuditQuestionsLoaded Then ' Hidden text box formula
        [B][COLOR=Red]Me.Dirty = False[/COLOR][/B]
        DoCmd.OpenQuery "LoadAuditQuestions", acViewNormal, acAdd
        Me.AuditQuestions.Requery
    End If
End Sub

 

[nschroeder]

Wow, thanks for the magic. Works perfectly now!

 

[JHB]

Good you got it solved.