Help with a text field & a Date field, please

[terbolee]

I have a [OrgCode] text field and a [InvDate] dd/mm/yyyy field. My third field [InvNum] needs to be automatically filled in with the first 3 chars of [OrgCode] and mmdd of the [InvDate] field.

Here's an example: "CASTOR" in [OrgCode] & 15/01/2007 in [InvDate]

I need "CAS0115" in [InvNum] as an After Update Event.

Can you help? Thankx.

 

[MStef]

Look at "DemoOrgDateA2000.mdb"

 

[terbolee]

Many thanks for that. It works beautifully in your sample MDB file. I made some modifications to your code and tried it but got an error msg: Runtime error '94' Invalid use of Null

======================================
Private Sub Form_Current()

GetInvNum

End Sub

Private Sub Invoice_Date_AfterUpdate()

GetInvNum

End Sub

Sub GetInvNum()

Me.[Invoice Number] = Left(Me.[Organisation Code], 3) & Format(Month(Me.[Invoice Date]), "00") & Format(Day(Me.[Invoice Date]), "00")

End Sub


Private Sub Invoice_Number_AfterUpdate()

GetInvNum

End Sub

Private Sub Invoice_Period_To_AfterUpdate()

Me.[Invoice Period To] = Me.[Invoice Period From] + Me.[Days in Period]

End Sub

Private Sub Organisation_Code_AfterUpdate()

GetInvNum
=================================


Private Sub Organisation_Code_AfterUpdate()...does not have an End Sub because other codes follow.

Am I missing something? Thanks.

Look at "DemoOrgDateA2000.mdb"

 

[MStef]

If my be if you link form on the table.
Replace Sub "GetInvNum" in Sub "Form Before_Update",
and rub the other 3 sub.

 

[Bat17]

in 'Sub Form_Current', 'Invoice_Date_AfterUpdate' and 'Invoice_Number_AfterUpdate' you are calling the invoice sub

if there is no value in Me.[Organisation Code] when it is called then that is probably where your error is kicking of from.

I can't see any porpuse in calling it from the current event anyway.

Peter

 

[terbolee]

Many thanks for the useful info. Using your advice, I managed to get it working with the following modified code.

=====================================
Private Sub Form_Current()

GetInvNum

End Sub

Private Sub Invoice_Date_AfterUpdate()

GetInvNum

End Sub

Sub GetInvNum()
If Me.[Organisation Code] <> "" And Me.[Invoice date] <> "" Then
Me.[Invoice Number] = UCase(Left(Me.[Organisation Code], 3) & Right(Year(Me.[Invoice date]), 2) & Format(Month(Me.[Invoice date]), "00"))
End If
End Sub


Private Sub Invoice_Number_AfterUpdate()

GetInvNum

End Sub
===============================

You're my shining heroes!!! Thanks.