Storing Data from Form in Table

[whjwilson]

I don't know if I am doing this right or not, but I have set up a conditional formula for a cell on my Form, but the value generated from the Formula in the Form does not store on the table associated with the form, the information only stays in the forms. This is the formula I am using:

=IIf([Rating]="Not Adequate",DateSerial(Year([Inspection_Date]),Month([Inspection_Date])+6,Day([Inspection_Date])),IIf([Type_Inspection]="SAV","",IIf([Type_Inspection]="RAV","",DateSerial(Year([Inspection_Date]),Month([Inspection_Date])+18,Day([Inspection_Date])))))

I know it has to do with where I have the formula put (Control Source). I have tried placing the formula in other boxes (Default Value, Validation Rule) and it does not work. Am I putting this formula in the right location or am I missing something in the formula.

 

[Crowley16]

the control source specifies which column the text box is bound to, since you've got a formula in there, it's not bound to a column, so can't save the value into that column.

if this is a calculated field, then why would you need to store this in the table?

--------------------
Procrastinate Now!

 

[whjwilson]

I am looking for a way to be able to generate a report using the date from this cell as part of that report. Is there a way to bring up the information in this cell in a generated report?

 

[Crowley16]

why not write a query, and have this as a calculated field in your query, then base the report off of the query?

--------------------
Procrastinate Now!

 

[lameid]

Looking at your expression, it doesn't look like there is a need to store it as it can always be recalculated.

That being said you will have to assign the value to a field or control based on some event...

Me!<control/field to store date in> = <your expression>

You will have to pick an event. I suggest the after update event of either (all of rating, inspection date, Type_Inspection) OR the form after update. You will need to test if those controls have values too...

IF nz(Me!Rating,"") <> "" and Isdate(ME!Inspection_Date)  Then 
   If Rating]="Not Adequate" Then
   Me!<control/field to store date in> = DateSerial(Year([Inspection_Date]),Month([Inspection_Date])+6,Day([Inspection_Date])),
    Else
       Select Case NZ(Type_Inspection,"") 
       Case "SAV"
            Me!<control/field to store date in> = Null
       Case "RAV"
            Me!<control/field to store date in> = _ 
                 Dateadd("m", 18, [Inspection_Date])
       Case Else
            Me!<control/field to store date in> = Null
       End Select
    End if
Else
    Me!<control/field to store date in> = Null
End if

Also you were adding 18 to the month which I don't think would work... I changed it in this code to add 18 months (dateadd).

I think I got everything right but you might have to debug it a little.

Also if you are completely new to VBA and events when you are on the event in the property window, hit the build (three dots next to it) and paste the code in the procedure that pops up.. there will be a sub something and and end sub... Code goes in the middle.

 

[whjwilson]

Never thought of that, let me give that a try.

 

[lameid]

Woops I cross posted in the middle of that.

Looks like just calculating it is the way for you to go. Check out the dateserial with the + 18 on the months. I don't think that will work. I think you need dateadd as in my example.