Calling Functions from command buttons

[Tiffosi2007]

Office 2007
Win XP

Hi,

I have been having severe problems with Macros in my DB. Macros were set up to run code but are now returning 2950 errors on 70% of them. The db is trusted so i am not sure of the problem.

To get around this i wanted to run my code (functions) directly from command buttons.

Can someone give me some guidance on this please?

Thanks

Kevin

 

[ErikSnoek]

Office 2007
Win XP

Hi,

I have been having severe problems with Macros in my DB. Macros were set up to run code but are now returning 2950 errors on 70% of them. The db is trusted so i am not sure of the problem.

To get around this i wanted to run my code (functions) directly from command buttons.

Can someone give me some guidance on this please?

Thanks

Kevin

You can set the "On click" event of a command button (in the events tab of the properties window of a command button) to:
=yourFunction()
Whereas "yourFunction" is the name of your function.

 

[Tiffosi2007]

Hi,

This is what i was trying to do but i think i am going wrong somewhere.

The function is called UpdatetblContacts()

Am i calling it =UpdatetblContacts() in the properties on click sheet? (i.e under events?) As when i do build it here it wont accept it!!

 

[ErikSnoek]

Hi,

This is what i was trying to do but i think i am going wrong somewhere.

The function is called UpdatetblContacts()

Am i calling it =UpdatetblContacts() in the properties on click sheet? (i.e under events?) As when i do build it here it wont accept it!!

Are you sure it's a function or is it a sub? Also did you place this function in a module or in a form? If in a form, is the function in the same form as the button that is supposed to call it?

 

[Tiffosi2007]

Its a function, placed in a module.

When i build on the command button - =UpdatetblContacts() it will not save the command. Doesnt like it for some reason!!

-----------------

Option Compare Database

Function UpdatetblContacts() As String

MsgBox "running"
'set the recordset names
'Fullname is for comparing names between two tables
'numrec counts the newly added records

Dim dbs As Database

Dim rstContacts As Recordset
Dim rstOutlook As Recordset
Dim FullName As String
Dim numrec As Integer

Set dbs = CurrentDb

'set to each rst to a table. Must be a dynaset (I think)

Set rstOutlook = dbs.OpenRecordset("tblOutlookLink", dbOpenDynaset)
Set rstContacts = dbs.OpenRecordset("tblContacts", dbOpenDynaset)

'Move to the first record in the outlook table.

rstOutlook.MoveFirst

'Do until end of the outlook table

Do Until rstOutlook.EOF = True

'Set the var fullname to match the first and last name on outlook table.
'However, if there is no last name then it must just copy the first. Doing
'otherwise will cause a duplicate PK and return an error. The code
'below fixes this by telling it to only add first name if last name is blank

If rstOutlook!Last = "" Or IsNull(rstOutlook!Last) Then

FullName = "[First]=""" & rstOutlook!First & """"

Else

FullName = "[First]=""" & rstOutlook!First & """ AND [Last] =""" & rstOutlook!Last & """"

End If

'find the first match

rstContacts.FindFirst FullName

'if no match then add a new record

If rstContacts.NoMatch Then

rstContacts.AddNew

'if the last name field is blank then only add first name as fullname
'otherwise fullname is both

If rstOutlook!Last = "" Or IsNull(rstOutlook!Last) Then

rstContacts!FullName = rstOutlook!First & " "
rstContacts!First = rstOutlook!First

Else

rstContacts!FullName = rstOutlook!First & " " & rstOutlook!Last
rstContacts!First = rstOutlook!First
rstContacts!Last = rstOutlook!Last

End If

rstContacts!JobTitle = rstOutlook![Job Title]

'if company is blank then organisation just = company, otherwise its both
'this is due to department default val being "." even if no company
'if this code was not used then an organisation of " - . " would be created.

If rstOutlook!Company = "" Or IsNull(rstOutlook!Company) Then
rstContacts!Organisation = rstOutlook!Company
Else

rstContacts!Organisation = rstOutlook!Company & " " & rstOutlook!Department

End If

rstContacts!Email = rstOutlook![Email Address]
rstContacts!OfficePhone = rstOutlook!Phone
rstContacts!OfficeFax = rstOutlook![Business Fax]
rstContacts!OutHoursPhone = rstOutlook![Home Phone]
rstContacts!OutHoursFax = rstOutlook![Home Fax]
rstContacts!Mobile = rstOutlook![Mobile Phone]
rstContacts!Pager = rstOutlook![Pager Phone]

rstContacts.Update

'add 1 to the count

numrec = numrec + 1

Else

'otherwise update the record if a match is returned

rstContacts.Edit

'if the last name field is blank then only add first name as fullname
'otherwise fullname is both

If rstOutlook!Last = "" Or IsNull(rstOutlook!Last) Then

rstContacts!FullName = rstOutlook!First & " "
rstContacts!First = rstOutlook!First

Else

rstContacts!FullName = rstOutlook!First & " " & rstOutlook!Last
rstContacts!First = rstOutlook!First
rstContacts!Last = rstOutlook!Last

End If

rstContacts!JobTitle = rstOutlook![Job Title]

'if company is blank then organisation just = company, otherwise its both
'this is due to department default val being "." even if no company
'if this code was not used then an organisation of " - . " would be created.

If rstOutlook!Company = "" Or IsNull(rstOutlook!Company) Then
rstContacts!Organisation = rstOutlook!Company
Else

rstContacts!Organisation = rstOutlook!Company & " " & rstOutlook!Department

End If

rstContacts!Email = rstOutlook![Email Address]
rstContacts!OfficePhone = rstOutlook!Phone
rstContacts!OfficeFax = rstOutlook![Business Fax]
rstContacts!OutHoursPhone = rstOutlook![Home Phone]
rstContacts!OutHoursFax = rstOutlook![Home Fax]
rstContacts!Mobile = rstOutlook![Mobile Phone]
rstContacts!Pager = rstOutlook![Pager Phone]

rstContacts.Update

End If

'Move to next record

rstOutlook.MoveNext

'return to start of the loop

Loop

'Confirm code has finished running

MsgBox numrec & " new contacts have been added. All current contacts have been checked and details updated"

'close recordsets

rstContacts.Close
rstOutlook.Close
Set dbs = Nothing

End Function

 

[ErikSnoek]

Strange, it does work for me. I simply entered the text "=UpdatetblContacts()" in the textfield next to "On Click" under Events. Could you explain exactly what steps you take?

 

[Tiffosi2007]

This may be the same reason why my macros dont work anymore as well. Trying to call the function using runcode in a macro returns an error. Where as other code will work!!

Steps:

create command button
dont assign a task to it, just click finish.
in the properties window input =UpdatetblContacts()
This will now not let me click off the textfield.
Try to do it in build, it accepts it but then the textfield is blank when you close.

I am thinking there is some security issue.

 

[ErikSnoek]

Perhaps you should try it directly from code instead of from the properties window (even though it would be weird if that'd work, but it's worth the try):
Go to the On Click event and choose Event Procedure from the dropdown list. Click the ellipses (...) to enter the VBA editor. Now enter the code "Call UpdatetblContacts()" or simply "UpdatetblContacts" between the "Private Sub~" and "End Sub" lines. If any error message pops up upon clicking the button, please post it here.

 

[Tiffosi2007]

Ok, tried it both ways with errors on both.

1. (call function() this returned ambiguous name detected 'updatetblcontacts'

2. naming function without the call brought error 'expected ='

 

[Tiffosi2007]

Hang on a sec.....................

There must be another function/macro named the same somewhere in the database. Would you agree?

Some pretty good fault finding on your behalf though! Thanks!!

 

[ErikSnoek]

Apparently you have two (or more) functions named "updatetblcontacts". Perhaps you pasted the function twice? Or once in several modules? Try a search (ctrl+F) in the VBA editor and search for "updatetblcontacts" in the "Current project" and make sure you have only one function named "updatetblcontacts".

Edit: you already figured it out before me You're welcome.

 

[Tiffosi2007]

Your correct!

I had an old module i was no longer using, however i did not want to delete it. This was called the same name. Have now renamed it and everything is hunky dory.

Thanks very much for the help!