Case statement to update empty form field with parent values

[rdougherty]

Hello,

I am trying to get child records to auto-populate a date from the parent record, and there are three situations.

Case 1: If Rejected_Date is not null, use it.
Case 2: If Accepted Date is not null, use it instead.
Case 3: Both are null, and the user needs to be forced to enter one before continuing.

Here is my code thus far: (it is currently throwing a compile error "Statement invalid outside Type block")

Can you see what I am doing wrong? THanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim msgrslt As Variant


    If IsNull(Me.Defect_Date) = True Then
    
    Select Case Me.Defect_Date
    Case IsNull([Forms]![frm_final_inspection].Rejected_Date) = False
      Me.Defect_Date = [Forms]![frm_final_inspection].Rejected_Date
    Case IsNull([Forms]![frm_final_inspection].Accepted_Date) = False
      Me.Defect_Date = [Forms]![frm_final_inspection].Accepted_Date
    Case IsNull([Forms]![frm_final_inspection].Rejected_Date), IsNull([Forms]![frm_final_inspection].Accepted_Date)
        msgbox("Rejection Date is a required field.  Please enter a value before continuing.", vbOKOnly, [Required Field]) As msgrslt
    Exit Sub
      
  End Select
    End If
End Sub

[davegri]

The Case evaluation statements under "Select Case Performance" must refer to the value of Performance.

[rdougherty]

Oh crap, I had a copied snippet from Microsoft's explanation for "Performance" because they entered it as a function()...

I replaced with Select Case Me.Defect_Date and it throws the same error...

Still not working!

[davegri]

None of your Case statement refer to a value of Defect_Date.
They refer to Rejected_date and Accepted_Date.

You should code this as If---Else. Scrap the select case.

[rdougherty]

The value on the child record is Defect_Date, but the dates I want to grab from are on the Parent record, Accepted_Date and Rejected_Date...

I see your point about If--Else, but I was just trying to use this opportunity to learn Case statements. I'll try that instead, perhaps.

Thank you,

[Gicu]

You could use something like this or break it down into smaller pieces as desired. Don't forget to Cancel the BeforeUpdate event if the validation returns False.

Me.Defect_Date=IIf(Not Isnull([Forms]![frm_final_inspection].Accepted_Date),[Forms]![frm_final_inspection].Accepted_Date,IIf(Not Isnull([Forms]![frm_final_inspection].Rejected_Date),[Forms]![frm_final_inspection].Rejected_Date,InputBox("Rejection Date is a required field! Please enter a date!","Required Field")))

Cheers,

[Micron]

Sometimes I use case blocks that evaluate to True. That way you can use a mix of references with logical operators as in

Case A = B And C = D
Case E = F

Just be aware that case blocks stop evaluating as soon as the first case is satisfied.

[rdougherty]

thanks all for the different options...

I got it to work with the if then statement, and it seems the issue was my syntax or usage of the msgbox line originally... just making it a very basic msgbox with no arguments other than the text, works. But, I don't know why it was giving error before...

The following code is not stopping the save of the record and returning the cursor to the Defect_Date field... should I be using a different event, or do I have this coded incorrectly?

 Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Defect_Date) = True Then        If IsNull([Forms]![frm_final_inspection].Rejected_Date) = False Then
        Me.Defect_Date = [Forms]![frm_final_inspection].Rejected_Date
        ElseIf IsNull([Forms]![frm_final_inspection].Accepted_Date) = False Then
        Me.Defect_Date = [Forms]![frm_final_inspection].Accepted_Date
        Else
        msgbox ("Rejection Date is a required field.  Please enter a value before continuing.")
        
        Me.Defect_Date.SetFocus
        Exit Sub
        End If
    End If
   
End Sub

[Gicu]

What about this?

Private Sub Form_BeforeUpdate(Cancel As Integer) 
If IsNull(Me.Defect_Date) = True Then        
	If IsNull([Forms]![frm_final_inspection].Accepted_Date) = False Then
        Me.Defect_Date = [Forms]![frm_final_inspection].Accepted_Date  'Accepted_Date has priority
    ElseIf IsNull([Forms]![frm_final_inspection].Rejected_Date) = False Then
        Me.Defect_Date = [Forms]![frm_final_inspection].Rejected_Date
    Else
        Msgbox "Rejection Date is a required field.  Please enter a value before continuing.", vbCancel,"Required field"       
        Me.Defect_Date.SetFocus
		Cancel=True 'to prevent saving the record
        Exit Sub
    End If
End If
End Sub

[Micron]

The error message you posted doesn't seem related to the problem, but to answer your question about the message box problem, it's because you must not use ( ) around the message box function unless it is returning a value.

[rdougherty]

thank you Vlad and Micron!

Those answers helped. Perhaps now I will go back and use the case statement just for practice.