update record after auto fill

[jon98548]

I have to thank you guys right off the bat. I have been searching for a way to fill one field based on the value of another and found that answer here. So, I decided to join in. This is that best source of information I have found for Access. Many pats on that back to the founders!

Now then, I used Dlookup to automatically fill a text field based on a selection I make in an option group. What I would like to happen is the data show up when I select the option, instead of updating when moving to the next record. Here is what I have to this point and it works great to populate my Finding field after I move to the next record. I put this in the "On Current" event of the form property.

If Me!ComplianceLevel = 2 Then
Me!Finding = DLookup("NoneFinding", "tblCAPProtocolQuestions", _
"[CAPID]=[forms]![frmFindingsEntry].[CAPID]")
ElseIf Me!ComplianceLevel = 3 Then
Me!Finding = DLookup("PartialFinding", "tblCAPProtocolQuestions", _
"[CAPID]=[forms]![frmFindingsEntry].[CAPID]")
Else
Me!Finding = Null
End If

If I choose one compliance level, I get a finding and another compliance level gives me a different finding. I would like to update the field when the selection is made so I can enter some comments about the finding. Also, should I improve what I have so far? Thanks.

 

[Mile-O]

Just thought I'd tidy it for you a bit...not to clear on the comments part...

Select Case Me!ComplianceLevel
   Case Is = 2
      Me!Finding = DLookup("NoneFinding", "tblCAPProtocolQuestions", _ 
         "[CAPID]=[forms]![frmFindingsEntry].[CAPID]") 
   Case Is = 3 
      Me!Finding = DLookup("PartialFinding", "tblCAPProtocolQuestions", _ 
         "[CAPID]=[forms]![frmFindingsEntry].[CAPID]") 
   Case Else 
      Me!Finding = Null 
End Select

 

[jon98548]

The comments are manually entered after the Finding is updated. It works fine to make a selection, go to the next record and the go back to the previous record. I would like to see the Finding 'magically' appear before my eyes.

I'm interested why the Select Case is a better option than If Then.

Thanks.

 

[jon98548]

Great! It works! Thanks, Mile and Pat. I also resolved the issue of having my statement appear when the option was selected. I kept placing the code in the form itself. I finally placed it in the AfterUpdate property of the option group and POOF there it was.