Save record after updating

[Xenix]

I have written this function to remove all "-" or spaces in a string and it seems to work fine. But as I go record by record and remove them it don't seem to store them in the table here is my code:

Private Sub Remove_hyphens_Click()
On Error GoTo Err_Remove_hyphens_Click
    Set db = CurrentDb
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.MoveLast
    rs.MoveFirst
    Do While Not rs.EOF
        Me.PartNumber = RemoveSpacesAndDashes(rs.PartNumber)
        Me.Refresh 'try to force a save here
        MsgBox Me.PartNumber 'just check to see if all is well
        rs.MoveNext
    Loop

Exit_Remove_hyphens_Click:
    Exit Sub

Err_Remove_hyphens_Click:
    MsgBox Err.Description
    Resume Exit_Remove_hyphens_Click
End Sub

any help you can give me will be most helpfull.

Thank you in advance

Mike

 

[Mile-O]

Save yourself the time. Just make an UPDATE Query.

UPDATE MyTable SET PartNumber = RemoveSpacesAndDashes(PartNumber);


Change MyTable to your table, save that and run it. And that's it.

 

[Xenix]

ok that would be great but I have never done an update query before?

it is easy to do? And how do I store my function in a query?


Thank you

Mike

 

[Mile-O]

• Open a new query
• Switch from Design View to SQL View
• Copy the SQL I posted above into the SQL View
• Change MyTable to the name of the table the field to update is in
• Save the query
• Run the query

You store functions in modules; not queries.

 

[Xenix]

Now message:

Undefined function 'RemoveSpacesAndDashes' in expression.

So how do I make it use this function which is in a form?

Thank you

Mike

 

[Mile-O]

Your function, provided that it makes no mention to the form, can be removed from the form's module and placed in an empty module.

At the start, where it says: Function, change this to Public Function.

 

[Xenix]

hmm I have done this just left the form open and run the query but it still gives the same error message

I have attached it so you can see.




Kind regards

Mike

 

[Mile-O]

I said put it in an empty module - you still have it in a form's Class module.

 

[Xenix]

Thank you very much my friend. I just did not see what you meant by empty module. but now I understand.

Best regards

Mike