How to Set Value on form using macro

[FJ Questioner]

I have an invoice input form which includes the fields InvoiceDate and
InvoiceDueDate. I use the DatePicker for InvoiceDate and just regular manual
entry for InvoiceDueDate.

How do I get the value of InvoiceDueDate to change whenever the InvoiceDate
is changed? (I want the InvoiceDueDate to be equal to the InvoiceDate plus
a constant from a field in another table, called Terms. I have a query which
makes this calculation.

I thought this would be easy. Have the InvoiceDate trigger an On Upate
macro which sets the value of InvoiceDueDate to the query result. However,
since all this happens before the record is complete, the query doesn't
update when the InvoiceDate is changed. It only updates when the entire
record is completed.

Am I missing something obvious here? Any suggestions?

Thanks,

FJ

 

[Brian]

Use something like this bit of code for the AfterUpdate event of the
InvoiceDate:

Private Sub InvoiceDate_AfterUpdate()
If IsNull(InvoiceDate) then
InvoiceDueDate = null
Else
Dim DaysToAdd as Integer
DaysToAdd = DLookup ("[<HowManyDaysToAdd>]","[Terms])
InvoiceDueDate = DateAdd("d", DaysToAdd, InvoiceDate)
End If
End Sub

This way, the InvoiceDueDate will be blank if the InvoiceDate is blank, but
will be set to the InvoiceDate plus the appropriate number of days.

Now, if the # of days never changes, you could consider not storing the
InvoiceDueDate at all - just make that an unbound text box and put something
like this as its ControlSource:

= InvoiceDate + Dlookup("[HowManyDaysToAdd]","[Terms]"

 

[FJ Questioner]

Thanks for the code. I'm having some difficulty getting it to work though.

I should have mentioned that the number of days to be added to [InvoiceDate]
is in a field called [Assignors.InvoieDue] embedded in the form. So upon
updating [InvoiceDate] I just need to set the [InvoiceDueDate] equal to the
[InvoiceDate] + [InvoiceDue] located on the same form.

I know nothing about VBA code but I tried amending yours to the following,
without success but I think I'm on the right track ....? BTW, what is
the "d" in your last line?

Thanks again.

Private Sub InvoiceDate_Updated(Code As Integer)
If IsNull(InvoiceDate) Then
InvoiceDueDate = Null
Else
Dim DaysToAdd As Integer
DaysToAdd = DLookup("[InvoiceDue]", "[Assignors]", "[InvoiceDue]='" &
[Assignors].[InvoiceDue] & "'")
InvoiceDueDate = InvoiceDue("d", InvoiceDue, InvoiceDate)
End If
End Sub

Use something like this bit of code for the AfterUpdate event of the
InvoiceDate:

Private Sub InvoiceDate_AfterUpdate()
If IsNull(InvoiceDate) then
InvoiceDueDate = null
Else
Dim DaysToAdd as Integer
DaysToAdd = DLookup ("[<HowManyDaysToAdd>]","[Terms])
InvoiceDueDate = DateAdd("d", DaysToAdd, InvoiceDate)
End If
End Sub

This way, the InvoiceDueDate will be blank if the InvoiceDate is blank, but
will be set to the InvoiceDate plus the appropriate number of days.

Now, if the # of days never changes, you could consider not storing the
InvoiceDueDate at all - just make that an unbound text box and put something
like this as its ControlSource:

= InvoiceDate + Dlookup("[HowManyDaysToAdd]","[Terms]"

I have an invoice input form which includes the fields InvoiceDate and
InvoiceDueDate. I use the DatePicker for InvoiceDate and just regular manual
entry for InvoiceDueDate.

How do I get the value of InvoiceDueDate to change whenever the InvoiceDate
is changed? (I want the InvoiceDueDate to be equal to the InvoiceDate plus
a constant from a field in another table, called Terms. I have a query which
makes this calculation.

I thought this would be easy. Have the InvoiceDate trigger an On Upate
macro which sets the value of InvoiceDueDate to the query result. However,
since all this happens before the record is complete, the query doesn't
update when the InvoiceDate is changed. It only updates when the entire
record is completed.

Am I missing something obvious here? Any suggestions?

Thanks,

FJ