E
Elaine
I created a form with a textbox, named txtSemester, whose Default Value is
set to a function: =setSemester()*
However, when I open the form, sometimes I get a #Error, other times I get
the result I want......
When I do get the #Error for the first time, I will keep on getting it,
until I add a new textbox2 with the exactly same function as Default value.
As soon as I add textbox2, txtSemester will start giving me the result I
want.... it is really driving me mad!
I know there's nothing wrong with the function because it does work.
The only thing that is different with txtSemester is that I use it as an
argument for the DLookup** function in a 3rd textbox. But I dont think this
would be the cause of the error Im getting? or is it?
Does anybody know of a reason for this odd behaviour? is it a bug?
Please let me know if you need more details.
Many thanks in advance
Elaine
*Public Function setSemester() As String
Dim varCriteria As String
On Error GoTo errSemester
varCriteria = ""
varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & " AND
[semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")
setSemester = DLookup("[Semester]", "tblSemester", varCriteria)
errSemester:
If Err.Number <> 0 Then
MsgBox ("Error: " & Err.Number & " " & Err.Description)
End If
End Function
**=DLookUp("[Invoiced Date]","tblInvoices","[Semester] =""" &
Me!txtSemester)
set to a function: =setSemester()*
However, when I open the form, sometimes I get a #Error, other times I get
the result I want......
When I do get the #Error for the first time, I will keep on getting it,
until I add a new textbox2 with the exactly same function as Default value.
As soon as I add textbox2, txtSemester will start giving me the result I
want.... it is really driving me mad!
I know there's nothing wrong with the function because it does work.
The only thing that is different with txtSemester is that I use it as an
argument for the DLookup** function in a 3rd textbox. But I dont think this
would be the cause of the error Im getting? or is it?
Does anybody know of a reason for this odd behaviour? is it a bug?
Please let me know if you need more details.
Many thanks in advance
Elaine
*Public Function setSemester() As String
Dim varCriteria As String
On Error GoTo errSemester
varCriteria = ""
varCriteria = "[semStart] <= " & Format(Date, "\#mm\/dd\/yyyy\#") & " AND
[semEnd] >= " & Format(Date, "\#mm\/dd\/yyyy\#")
setSemester = DLookup("[Semester]", "tblSemester", varCriteria)
errSemester:
If Err.Number <> 0 Then
MsgBox ("Error: " & Err.Number & " " & Err.Description)
End If
End Function
**=DLookUp("[Invoiced Date]","tblInvoices","[Semester] =""" &
Me!txtSemester)