noob creating macros question

[SadGrl]

Hi! I need to create a macro in Access 2000. I've never used access before, so sorry if this comes off as a stupid question. I've scrounged together some vb code that will allow me to delete a table's contents, then import new contents from an Excel spreadsheet but so far the only way I can run it is by running it directly from the Visual Basic Editor(ie. Tools > Macro > Visual Basic Editor). How can I make a macro out of this or make it available in a user-friendly way? (basically, I want to make it easier to access for people--like clicking a button, having it as a menu option,etc.) Thanks!

Here's the code: -----------------------------------------------

Function GET_RESIDENT_DATA()
On Error GoTo GET_RESIDENT_DATA_Err

CurrentDb.Execute "Delete * from [Resident Nutritional Profile]"
DoCmd.OpenTable "Resident Nutritional Profile", acViewNormal, acAdd
DoCmd.GoToRecord acTable, "Resident Nutritional Profile", acFirst
DoCmd.TransferSpreadsheet acImport, 8, "Resident Nutritional Profile", "C:\Resident Nutritional Profile.xls", True, ""


GET_RESIDENT_DATA_Exit:
Exit Function

GET_RESIDENT_DATA_Err:
MsgBox Error$
Resume GET_RESIDENT_DATA_Exit

End Function

 

[wazz]

use a button's OnClick event. you just have to put the name of the function in the body of the code.

ButtonName_OnClick()
GET_RESIDENT_DATA()

make sure the function is Public or in the same module as the OnClick.

 

[SadGrl]

Thanks wazz. I'm in the right place now. How can I add this function to the same module as OnClick? Making the function public and pasting the function name into the code doesn't seem to work. I just get a compile error looking for "=", so I guess it wants more. Below is what my edit of the OnClick code looked like (just a snippet from the bottom of the code) -- my function is all highlighted red and mean looking, as if it would like to tell me to stick to something I know about

Private Sub Import_Residents_Click()
GET_RESIDENT_DATA()

End Function

 

[DJkarl]

Thanks wazz. I'm in the right place now. How can I add this function to the same module as OnClick? Making the function public and pasting the function name into the code doesn't seem to work. I just get a compile error looking for "=", so I guess it wants more. Below is what my edit of the OnClick code looked like (just a snippet from the bottom of the code) -- my function is all highlighted red and mean looking, as if it would like to tell me to stick to something I know about

Private Sub Import_Residents_Click()
GET_RESIDENT_DATA()

End Function

Dropping the parentheses from the function call should resolve the issue.

Private Sub Import_Residents_Click()
GET_RESIDENT_DATA

End Function

 

[SadGrl]

Ah, never mind. I made the following adjustments and it works. woohoo

Private Sub Command29_Click()
On Error GoTo GET_RESIDENT_DATA_Err

CurrentDb.Execute "Delete * from [Resident Nutritional Profile]"
DoCmd.OpenTable "Resident Nutritional Profile", acViewNormal, acAdd
DoCmd.GoToRecord acTable, "Resident Nutritional Profile", acFirst
DoCmd.TransferSpreadsheet acImport, 8, "Resident Nutritional Profile", "C:\Resident Nutritional Profile.xls", True, ""


GET_RESIDENT_DATA_Exit:
Exit Sub

GET_RESIDENT_DATA_Err:
MsgBox Error$
Resume GET_RESIDENT_DATA_Exit

End Sub