Module problem

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a module called GetDocIndex which calculates a sequential number in a
control called CommDocNbrtxt. On the BeforeUpdate property of the form I
have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
Click to expand...
The control isn't updated when I open the form and when I save the form I
get a message that points to this code that says Compile error Expected
variable or function not module. If I put the module code in the form module
it doesn't work either.
Can anyone help here?
TIA
 
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.
 
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
..Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this function If I
called the module something else how would the BeforeUpdate code work? I've
tried putting the code as a seperate function in the form module but it
still doesn't work. I'm lost here can you help?
Tony
 
Tony,

The problem is that because your Module and Function have the same name the
compiler is getting confused.

As an alternative to renaming the module you could reference the function as
GetDocIndex.GetDocIndex. Which is short for Function GetDocIndex in Module
GetDocIndex.

Renaming the Module should have no affect on any existing code unless it is
referenced in the Module.Function way I have described.

Hope that makes sense.

Dave.

Tony said:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost here
can you help?
Tony
Dave Vickery said:
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.
Click to expand...
Click to expand...
 
Sorry Dave I'm a newbie and struggling with this. If I change the name of
the module to say GetNbr and the function within the module is still headed
as Function GetDocIndex() As String are you saying that this should work?
Sorry to be so thick here, I still have a lot to learn about functions and
modules!!
Thanks
Tony
Dave Vickery said:
Tony,

The problem is that because your Module and Function have the same name the
compiler is getting confused.

As an alternative to renaming the module you could reference the function as
GetDocIndex.GetDocIndex. Which is short for Function GetDocIndex in Module
GetDocIndex.

Renaming the Module should have no affect on any existing code unless it is
referenced in the Module.Function way I have described.

Hope that makes sense.

Dave.

Tony said:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost here
can you help?
Tony
Dave Vickery said:
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a sequential
number in a control called CommDocNbrtxt. On the BeforeUpdate
property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save the
form I get a message that points to this code that says Compile
error Expected variable or function not module. If I put the module
code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
No worries mate..

Yeah.. you should be ok to change the name of the module without affecting
anything.

Tony said:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the module
is still headed as Function GetDocIndex() As String are you saying
that this should work? Sorry to be so thick here, I still have a lot
to learn about functions and modules!!
Thanks
Tony
Dave Vickery said:
Tony,

The problem is that because your Module and Function have the same
name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have described.

Hope that makes sense.

Dave.

Tony said:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost here
can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a sequential
number in a control called CommDocNbrtxt. On the BeforeUpdate
property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save the
form I get a message that points to this code that says Compile
error Expected variable or function not module. If I put the
module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
No Luck. Lets go back to square 1 if you have time Dave.
What I'm trying to do is create a sequential number called CommDocNbrtxt
which is a combination of a number plus the last 2 digits of the year eg
103,203,303 etc. The code that does that, (I hope) is the function I posted,
which I have tried as a module and as a function within the form. As a new
record is created the code on the BeforeUpdate property of the form should
check if the value of CommDocNbrtxt is empty and if so, call the function.

BUT nothing happens! CommDocnbrtxt is blank if I have it as a text field and
0 if I have it as a number field.

I did as someone suggested change the BeforeUpdate code
If Me.CommDocNbrtxt = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex

to this
If Len(Nz(Me.CommDocNbrtxt, ""))=0 Then
Me.CommDocNbrtxt.Value = GetDocIndex

But that created an error message in the function that said incorrect use of
Null

I know I'm probably pushing my luck a little here but can you help me with
this?
Thanks
Tony


Dave Vickery said:
No worries mate..

Yeah.. you should be ok to change the name of the module without affecting
anything.

Tony said:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the module
is still headed as Function GetDocIndex() As String are you saying
that this should work? Sorry to be so thick here, I still have a lot
to learn about functions and modules!!
Thanks
Tony
Dave Vickery said:
Tony,

The problem is that because your Module and Function have the same
name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have described.

Hope that makes sense.

Dave.

Tony Williams wrote:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost here
can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a sequential
number in a control called CommDocNbrtxt. On the BeforeUpdate
property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save the
form I get a message that points to this code that says Compile
error Expected variable or function not module. If I put the
module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
How big is the database?

Is it small enough to mail to me so i can have a look?

Tony said:
No Luck. Lets go back to square 1 if you have time Dave.
What I'm trying to do is create a sequential number called
CommDocNbrtxt which is a combination of a number plus the last 2
digits of the year eg 103,203,303 etc. The code that does that, (I
hope) is the function I posted, which I have tried as a module and as
a function within the form. As a new record is created the code on
the BeforeUpdate property of the form should check if the value of
CommDocNbrtxt is empty and if so, call the function.

BUT nothing happens! CommDocnbrtxt is blank if I have it as a text
field and 0 if I have it as a number field.

I did as someone suggested change the BeforeUpdate code
If Me.CommDocNbrtxt = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex

to this
If Len(Nz(Me.CommDocNbrtxt, ""))=0 Then
Me.CommDocNbrtxt.Value = GetDocIndex

But that created an error message in the function that said incorrect
use of Null

I know I'm probably pushing my luck a little here but can you help me
with this?
Thanks
Tony


Dave Vickery said:
No worries mate..

Yeah.. you should be ok to change the name of the module without
affecting anything.

Tony said:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the module
is still headed as Function GetDocIndex() As String are you saying
that this should work? Sorry to be so thick here, I still have a lot
to learn about functions and modules!!
Thanks
Tony
Tony,

The problem is that because your Module and Function have the same
name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have
described.

Hope that makes sense.

Dave.

Tony Williams wrote:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As
String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost
here can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a sequential
number in a control called CommDocNbrtxt. On the BeforeUpdate
property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save
the form I get a message that points to this code that says
Compile error Expected variable or function not module. If I
put the module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
It's about 5 mb I have BT Broadband so not a problem for me. What about you
That would be great if you could
Tony
Dave Vickery said:
How big is the database?

Is it small enough to mail to me so i can have a look?

Tony said:
No Luck. Lets go back to square 1 if you have time Dave.
What I'm trying to do is create a sequential number called
CommDocNbrtxt which is a combination of a number plus the last 2
digits of the year eg 103,203,303 etc. The code that does that, (I
hope) is the function I posted, which I have tried as a module and as
a function within the form. As a new record is created the code on
the BeforeUpdate property of the form should check if the value of
CommDocNbrtxt is empty and if so, call the function.

BUT nothing happens! CommDocnbrtxt is blank if I have it as a text
field and 0 if I have it as a number field.

I did as someone suggested change the BeforeUpdate code
If Me.CommDocNbrtxt = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex

to this
If Len(Nz(Me.CommDocNbrtxt, ""))=0 Then
Me.CommDocNbrtxt.Value = GetDocIndex

But that created an error message in the function that said incorrect
use of Null

I know I'm probably pushing my luck a little here but can you help me
with this?
Thanks
Tony


Dave Vickery said:
No worries mate..

Yeah.. you should be ok to change the name of the module without
affecting anything.

Tony Williams wrote:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the module
is still headed as Function GetDocIndex() As String are you saying
that this should work? Sorry to be so thick here, I still have a lot
to learn about functions and modules!!
Thanks
Tony
Tony,

The problem is that because your Module and Function have the same
name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have
described.

Hope that makes sense.

Dave.

Tony Williams wrote:
Hi Dave, yes my module is called GetDocIndex and here is the code
Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As
String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a seperate
function in the form module but it still doesn't work. I'm lost
here can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a sequential
number in a control called CommDocNbrtxt. On the BeforeUpdate
property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save
the form I get a message that points to this code that says
Compile error Expected variable or function not module. If I
put the module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
Tony,
I tried mailing your published address tw@_tcp.com
It didn't seem to work.
What is your current address?

Dave.

Tony said:
It's about 5 mb I have BT Broadband so not a problem for me. What
about you That would be great if you could
Tony
Dave Vickery said:
How big is the database?

Is it small enough to mail to me so i can have a look?

Tony said:
No Luck. Lets go back to square 1 if you have time Dave.
What I'm trying to do is create a sequential number called
CommDocNbrtxt which is a combination of a number plus the last 2
digits of the year eg 103,203,303 etc. The code that does that, (I
hope) is the function I posted, which I have tried as a module and
as a function within the form. As a new record is created the code
on the BeforeUpdate property of the form should check if the value
of CommDocNbrtxt is empty and if so, call the function.

BUT nothing happens! CommDocnbrtxt is blank if I have it as a text
field and 0 if I have it as a number field.

I did as someone suggested change the BeforeUpdate code
If Me.CommDocNbrtxt = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex

to this
If Len(Nz(Me.CommDocNbrtxt, ""))=0 Then
Me.CommDocNbrtxt.Value = GetDocIndex

But that created an error message in the function that said
incorrect use of Null

I know I'm probably pushing my luck a little here but can you help
me with this?
Thanks
Tony


No worries mate..

Yeah.. you should be ok to change the name of the module without
affecting anything.

Tony Williams wrote:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the
module is still headed as Function GetDocIndex() As String are
you saying that this should work? Sorry to be so thick here, I
still have a lot to learn about functions and modules!!
Thanks
Tony
Tony,

The problem is that because your Module and Function have the
same name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have
described.

Hope that makes sense.

Dave.

Tony Williams wrote:
Hi Dave, yes my module is called GetDocIndex and here is the
code Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As
String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a
seperate function in the form module but it still doesn't work.
I'm lost here can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a
sequential number in a control called CommDocNbrtxt. On the
BeforeUpdate property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save
the form I get a message that points to this code that says
Compile error Expected variable or function not module. If I
put the module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
I don't use my real address in these groups because I got bitten by that
fool whose farming email addresses from newsgroups and flooding us with
supposedly Microsoft updates which are in fact viruses. Anyway its
(e-mail address removed)
I've just emailed the database to you at (e-mail address removed)
Hope that was Ok
Really appreciate your help. Since I retired my grey cells don't seem to
work as well and I'm doing this as a favour for a friend, always a big
mistake!
Thanks again
Tony
Dave Vickery said:
Tony,
I tried mailing your published address tw@_tcp.com
It didn't seem to work.
What is your current address?

Dave.

Tony said:
It's about 5 mb I have BT Broadband so not a problem for me. What
about you That would be great if you could
Tony
Dave Vickery said:
How big is the database?

Is it small enough to mail to me so i can have a look?

Tony Williams wrote:
No Luck. Lets go back to square 1 if you have time Dave.
What I'm trying to do is create a sequential number called
CommDocNbrtxt which is a combination of a number plus the last 2
digits of the year eg 103,203,303 etc. The code that does that, (I
hope) is the function I posted, which I have tried as a module and
as a function within the form. As a new record is created the code
on the BeforeUpdate property of the form should check if the value
of CommDocNbrtxt is empty and if so, call the function.

BUT nothing happens! CommDocnbrtxt is blank if I have it as a text
field and 0 if I have it as a number field.

I did as someone suggested change the BeforeUpdate code
If Me.CommDocNbrtxt = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex

to this
If Len(Nz(Me.CommDocNbrtxt, ""))=0 Then
Me.CommDocNbrtxt.Value = GetDocIndex

But that created an error message in the function that said
incorrect use of Null

I know I'm probably pushing my luck a little here but can you help
me with this?
Thanks
Tony


No worries mate..

Yeah.. you should be ok to change the name of the module without
affecting anything.

Tony Williams wrote:
Sorry Dave I'm a newbie and struggling with this. If I change the
name of the module to say GetNbr and the function within the
module is still headed as Function GetDocIndex() As String are
you saying that this should work? Sorry to be so thick here, I
still have a lot to learn about functions and modules!!
Thanks
Tony
Tony,

The problem is that because your Module and Function have the
same name the compiler is getting confused.

As an alternative to renaming the module you could reference the
function as GetDocIndex.GetDocIndex. Which is short for Function
GetDocIndex in Module GetDocIndex.

Renaming the Module should have no affect on any existing code
unless it is referenced in the Module.Function way I have
described.

Hope that makes sense.

Dave.

Tony Williams wrote:
Hi Dave, yes my module is called GetDocIndex and here is the
code Function GetDocIndex() As String

Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL As
String

' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As [LastDocIdx] " & _
"FROM [tblDocuments] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDocIdx").Value
.Close
End With
Set rsDocs = Nothing

' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt(strLastIdx)

' Increment the index
intDocIdx = intDocIdx + 1

' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)), 2)

' Return the new index
GetDocIndex = strNewIdx

End Function

I thought that using the code I posted earlier would call this
function If I called the module something else how would the
BeforeUpdate code work? I've tried putting the code as a
seperate function in the form module but it still doesn't work.
I'm lost here can you help?
Tony
It looks like you have a Module called 'GetDocIndex'

Rename the module and you should be fine.

Dave.

Tony Williams wrote:
I have a module called GetDocIndex which calculates a
sequential number in a control called CommDocNbrtxt. On the
BeforeUpdate property of the form I have the following code
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
The control isn't updated when I open the form and when I save
the form I get a message that points to this code that says
Compile error Expected variable or function not module. If I
put the module code in the form module it doesn't work either.
Can anyone help here?
TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top