Module problem

[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_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.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

[Wayne Morgan]

You can't call a "module", you have to call procedures in the module. Make sure that none
of the procedures have the same name as any module or any built in procedure.

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message news:bl******** *@titan.btinter net.com...

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_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.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

[Tony Williams]

Thanks Wayne but being a newbie I'm a little confused. Here is the code for
my "function"
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.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
..Fields("LastD ocIdx").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

As I said earlier if I put this code as a function in the form I still don't
get my control to update with the right numbers numbers. What am I doing
wrong here?
Thanks
Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:Iy******** *********@newss vr33.news.prodi gy.com...

You can't call a "module", you have to call procedures in the module. Make sure that none of the procedures have the same name as any module or any built in procedure.
--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** *@titan.btinter net.com...

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_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.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

[Wayne Morgan]

Where are you putting this code in the form? If you are just adding the function, you need
something to tell Access to run the function. Under what circumstances do you want this
function to run? That will determine where you place the call to this function. For
example, if you want the function to run each time you move to another record, you would
place a call to it in the Form's OnCurrent event. You could also use it as the Control
Source for an unbound text box. To do that, set the Control Source of the text box to

=GetDocIndex

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message news:bl******** **@titan.btinte rnet.com...

Thanks Wayne but being a newbie I'm a little confused. Here is the code for
my "function"
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.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDo cIdx").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

As I said earlier if I put this code as a function in the form I still don't
get my control to update with the right numbers numbers. What am I doing
wrong here?
Thanks
Tony

[Tony Williams]

Wayne, I have added the function and am using this code in the forms Before
Update property
Private Sub Form_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.Value = GetDocIndex
End If

End Sub
The control I want the number to appear in is CommDocIndextxt
I am not correct here? It doesn't work
Thanks
Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:hm******** ********@newssv r33.news.prodig y.com...

Where are you putting this code in the form? If you are just adding the function, you need something to tell Access to run the function. Under what circumstances do you want this function to run? That will determine where you place the call to this function. For example, if you want the function to run each time you move to another record, you would place a call to it in the Form's OnCurrent event. You could also use it as the Control Source for an unbound text box. To do that, set the Control Source of the text box to
=GetDocIndex

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@titan.btinte rnet.com...

Thanks Wayne but being a newbie I'm a little confused. Here is the code for my "function"
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.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDo cIdx").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

As I said earlier if I put this code as a function in the form I still don't get my control to update with the right numbers numbers. What am I doing
wrong here?
Thanks
Tony

[Tony Williams]

Sorry forgot I want the control to be populated with a new number each time
a new record is created
Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:hm******** ********@newssv r33.news.prodig y.com...

Where are you putting this code in the form? If you are just adding the function, you need something to tell Access to run the function. Under what circumstances do you want this function to run? That will determine where you place the call to this function. For example, if you want the function to run each time you move to another record, you would place a call to it in the Form's OnCurrent event. You could also use it as the Control Source for an unbound text box. To do that, set the Control Source of the text box to
=GetDocIndex

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@titan.btinte rnet.com...

Thanks Wayne but being a newbie I'm a little confused. Here is the code for my "function"
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.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDo cIdx").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

As I said earlier if I put this code as a function in the form I still don't get my control to update with the right numbers numbers. What am I doing
wrong here?
Thanks
Tony

[Wayne Morgan]

You say you want the result of GetDocIndex to appear in the ComDocIndextxt control, yet
you are assigning the result to the ComDocNbrtxt control.

Just to play it safe, do your controls have a different name than the fields they are
bound to? If the control's Control Source and the control's name are the same, you can run
into problems. This is usually handled by placing a prefix on the name of the control.
(i.e. Field Name = MyField, Control Name = txtMyField). I see you've appended txt to the
end of the names you are using, have you done that to accomplish this?

Also, you are doing this in the BeforeUpdate event. If it does place the value, it may do
so quickly, save the record, and move to the next record. You may not see it unless you go
back to that record and look for it.

You mentioned that you want to do this for a new record, what about an old record that the
field was left blank on? The way you currently have it, if you make a change to that old
record it would also run the function to update the old record with a number. If you want
to limit it to only new records, you could add an additional If statement.

If Me.NewRecord Then.....

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message news:bl******** **@sparta.btint ernet.com...

Wayne, I have added the function and am using this code in the forms Before
Update property
Private Sub Form_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.Value = GetDocIndex
End If

End Sub
The control I want the number to appear in is CommDocIndextxt
I am not correct here? It doesn't work
Thanks
Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:hm******** ********@newssv r33.news.prodig y.com...

Where are you putting this code in the form? If you are just adding the

function, you need

something to tell Access to run the function. Under what circumstances do

you want this

function to run? That will determine where you place the call to this

function. For

example, if you want the function to run each time you move to another

record, you would

place a call to it in the Form's OnCurrent event. You could also use it as

the Control

Source for an unbound text box. To do that, set the Control Source of the

text box to

=GetDocIndex

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@titan.btinte rnet.com...

Thanks Wayne but being a newbie I'm a little confused. Here is the code for my "function"
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.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then strLastIdx =
.Fields("LastDo cIdx").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

As I said earlier if I put this code as a function in the form I still don't get my control to update with the right numbers numbers. What am I doing
wrong here?
Thanks
Tony

[Tony Williams]

Thanks Wayne, I think I get confused with field name and control name I
assumed they had to be the same. So, do I make the control source = field
name in the table and the control name whatever I like. In which case when I
want to refer to the field in code or a report do I use the control source
name. If that's the case what is the purpose of the control name?

Sorry about the confusion the control is ComDocNbrtxt, I appended txt to
names so that I know it's a text field, have I got the convention the wrong
way round?

Being a newbie at this at 58 means I have to work harder on the grey cells
to make them cooperate!!!
But thanks for your patience and advice, I'll now try and put it into
practice and let you know how I got on.

Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:Dq******** *********@newss vr31.news.prodi gy.com...

You say you want the result of GetDocIndex to appear in the ComDocIndextxt control, yet you are assigning the result to the ComDocNbrtxt control.

Just to play it safe, do your controls have a different name than the fields they are bound to? If the control's Control Source and the control's name are the same, you can run into problems. This is usually handled by placing a prefix on the name of the control. (i.e. Field Name = MyField, Control Name = txtMyField). I see you've appended txt to the end of the names you are using, have you done that to accomplish this?

Also, you are doing this in the BeforeUpdate event. If it does place the value, it may do so quickly, save the record, and move to the next record. You may not see it unless you go back to that record and look for it.

You mentioned that you want to do this for a new record, what about an old record that the field was left blank on? The way you currently have it, if you make a change to that old record it would also run the function to update the old record with a number. If you want to limit it to only new records, you could add an additional If statement.

If Me.NewRecord Then.....

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@sparta.btint ernet.com...

Wayne, I have added the function and am using this code in the forms Before Update property
Private Sub Form_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.Value = GetDocIndex
End If

End Sub
The control I want the number to appear in is CommDocIndextxt
I am not correct here? It doesn't work
Thanks
Tony
"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:hm******** ********@newssv r33.news.prodig y.com...

Where are you putting this code in the form? If you are just adding the

function, you need

something to tell Access to run the function. Under what circumstances

do you want this

function to run? That will determine where you place the call to this

function. For

example, if you want the function to run each time you move to another

record, you would

place a call to it in the Form's OnCurrent event. You could also use

it as the Control

Source for an unbound text box. To do that, set the Control Source of

the text box to

=GetDocIndex

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@titan.btinte rnet.com...

> Thanks Wayne but being a newbie I'm a little confused. Here is the

code for

> my "function"
> 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.OpenR ecordset(strSQL )
> With rsDocs
> If Not .RecordCount = 0 Then strLastIdx =
> .Fields("LastDo cIdx").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
>
> As I said earlier if I put this code as a function in the form I

still don't

> get my control to update with the right numbers numbers. What am I

doing > wrong here?
> Thanks
> Tony

[Wayne Morgan]

Yes, you can make the control name whatever you want, but I would recommend keeping it
similar to the field name so that you don't have a lot to remember. Sometimes with both
names the same, Access doesn't know which of the two items you are referring to and it
will cause a problem.

A good example of naming conventions can be found at
http://www.mvps.org/access/general/gen0012.htm

Does the function return the correct value, just the value doesn't go into the control?

--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message news:bl******** **@sparta.btint ernet.com...

Thanks Wayne, I think I get confused with field name and control name I
assumed they had to be the same. So, do I make the control source = field
name in the table and the control name whatever I like. In which case when I
want to refer to the field in code or a report do I use the control source
name. If that's the case what is the purpose of the control name?

Sorry about the confusion the control is ComDocNbrtxt, I appended txt to
names so that I know it's a text field, have I got the convention the wrong
way round?

Being a newbie at this at 58 means I have to work harder on the grey cells
to make them cooperate!!!
But thanks for your patience and advice, I'll now try and put it into
practice and let you know how I got on.

[Tony Williams]

I have the control CommDocNbrtxt on my form as a General Number and all that
appears in the control on a new record is a "0", on current records the
control is blank.
The latest state is this:
I have the code as a function in my form module, with the function called
GetDocIndex, the first line being
Function getDocIndex() As String
In the OnCurrent property of the form I have the following code
Private Sub Form_Current()
If Me.CommDocNbrtx t = "" Then
Me.CommDocNbrtx t.Value = getDocIndex
End If
End Sub

At the moment the control name and control source are both CommDocNbrtxt .
If I change the control name to say Docnbr which name do I use in the
Function and On Current property, the control name or the control source?

Thanks for the link for conventions I'll change all my names when I've got
this to work!!

I am wondering whether I could get some simpler code which just gives me
sequential numbers and concatenate the year from another field but that's
not what my friend wants and that would be like giving in!!
Thanks again for your help.
Tony

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:%R******** ********@newssv r24.news.prodig y.com...

Yes, you can make the control name whatever you want, but I would recommend keeping it similar to the field name so that you don't have a lot to remember. Sometimes with both names the same, Access doesn't know which of the two items you are referring to and it will cause a problem.

A good example of naming conventions can be found at
http://www.mvps.org/access/general/gen0012.htm

Does the function return the correct value, just the value doesn't go into the control?
--
Wayne Morgan
"Tony Williams" <[e-mail]> wrote in message

news:bl******** **@sparta.btint ernet.com...

Thanks Wayne, I think I get confused with field name and control name I
assumed they had to be the same. So, do I make the control source = field name in the table and the control name whatever I like. In which case when I want to refer to the field in code or a report do I use the control source name. If that's the case what is the purpose of the control name?

Sorry about the confusion the control is ComDocNbrtxt, I appended txt to
names so that I know it's a text field, have I got the convention the wrong way round?

Being a newbie at this at 58 means I have to work harder on the grey cells to make them cooperate!!!
But thanks for your patience and advice, I'll now try and put it into
practice and let you know how I got on.