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.

 

[Pat Hartman]

Select Case is a better choice because once you get to 2 or more conditions, most people find the Case easier to read and understand than the If statement.

I would also use early binding rather than late binding. Change the bangs (!) to dots (.) wherever possible.

Me.ComplianceLevel
Me.Finding

Early binding lets Access resolve references at compile time rather than at execution time. When you start typing Me.c, Access will give you a pick list to choose a field from. This doesn't happen when you type Me!c. Also, after the field is selected and you type a following dot, you get a list of the properties.

 

[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.