Help running Module (Easy fix I think)

[WyldeCarde]

I wholesale lifted this from another thread on the site, tailored it to do what I needed and if I run it from VBA it does exactly what it should.
[small]

' I created a table, with the table name being "CARENOTES_Names", and 3 fields, FullName, Surname, and Forename.
' The query CARENOTES_namesfix_prep populates this table with the current linked CARENOTES_StaffMember data
' The following code then populates the Last and First name fields.
' One thing I did not take into account, is Middle Initials, or titles, like Dr., Mr., Mrs., etc
' the data in Carenotes seem to make this redundant
Sub FixNames()
Dim dbs As Database
Dim rst As Recordset
Dim Surname As String
Dim Forename As String
Dim fullname As String
Dim i As Integer

Set dbs = CurrentDb
' Set the recordset to table "CARENOTES_Names"
Set rst = dbs.OpenRecordset("CARENOTES_Names")
rst.MoveFirst
While Not rst.EOF
' Set temp "fullname" variable to databases' FullName variable
fullname = rst!fullname
i = InStr(fullname, " ")
Forename = Mid(fullname, 1, i - 1)
Surname = Mid(fullname, i + 1)
' Tell Access we're going to edit the record now.
rst.Edit
' Set the records' Forename and Surname fields to the Forename and Surname variables
rst!Forename = Forename
rst!Surname = Surname
' Set the Fullname field of the record, to the format "LastName, FirstName"
' Update the record
rst.Update
' Move to the next record
rst.MoveNext
Wend
' Release the object references.
Set rst = Nothing
Set dbs = Nothing
End Sub

[/small]

How do I run it from a macro though? I would have thought using 'runcode' and running 'FixNames' would do it but apparently not. I've only used functions in queries and on forms before, help please! I know it's a stupid question

 

[Remou]

You can change it from a Sub to a Function, however, this is not the sort of code that should be allowed to run unchecked.

 

[TheAceMan1]

How are ya UKEWildCard . . .

Curious . . . why run it from a macro when you have VBA?

BTW: [blue]Remou[/blue] is correct. [blue]RunCode can only call functions.[/blue]

See Ya! . . . . . .

 

[JerryKlmns]

And an

rst.Close

should be fired before releasing the object references.

 

[WyldeCarde]

Curious . . . why run it from a macro when you have VBA?

Well I was writing as a macro because the other people here are not very Access literate, so if they need to add any other queries they will be able to.

 

[WyldeCarde]

You can change it from a Sub to a Function, however, this is not the sort of code that should be allowed to run unchecked.

The data is reimported and this run against it every time the macro is run, so it shouldnt cause any serious issues.