Solved

Creating first Function in Access 2010

Posted on 2015-02-07
10
50 Views
Last Modified: 2016-02-11
Hi Experts,
I am attempting coding my first Function in Access 2010.
Here is my code but it doesn't work and I can't seem to get it right.

Calling Code
Dim txtFirstName As String
txtFirstName = "Robert"
Dim txtLastName As String
txtLastName = "Collison"
Dim txtFullName As String

txtFullName = MessageTypeMessageI01

Called Code
Public Function MessageTypeMessageI01() As String
On Error GoTo Err_MessageTypeMessageI01

'MsgBox "Step 000-Lookup Message Details."
'Dim txtFirstName As String
'Dim txtLastName As String
Dim FIRST_NAME As String
Dim LAST_NAME As String
[FIRST_NAME] = txtFirstName
[LAST_NAME] = txtLastName

MessageTypeMessageI01 = [FIRST_NAME] & ", " & [LAST_NAME] '"T0001-Test Message."

Exit_MessageTypeMessageI01:
    Exit Function
Err_MessageTypeMessageI01:
      MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
      Resume Exit_MessageTypeMessageI01
End Function

Could you please let me know what is incorrect?
Thanks.
Bob C.
0
Comment
Question by:Bob_Collison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40595824
What are you trying to do with these lines

Dim FIRST_NAME As String
 Dim LAST_NAME As String
 [FIRST_NAME] = txtFirstName
 [LAST_NAME] = txtLastName

MessageTypeMessageI01 = [FIRST_NAME] & ", " & [LAST_NAME] '"T0001-Test Message."

if you want MessageTypeMessageI01 to become
[fred],[Bloggs] T0001-Test Message.

then the code should be

Dim FIRST_NAME As String
 Dim LAST_NAME As String
FIRST_NAME = txtFirstName
LAST_NAME = txtLastName

MessageTypeMessageI01 = "[" & FIRST_NAME & "],[ " & LAST_NAME &  "] T0001-Test Message."

If you want it to be something else then please post what you want the output to be
0
 

Author Comment

by:Bob_Collison
ID: 40595844
Hi Expert,
Sorry for the confusion.   This is just to develop the Calling and Called (Function) code.
 I want to pass two Strings (FIRST_NAME and LAST_NAME) to a Function and have it return a Result (FULL_NAME) that concatenates  the FIRST_NAME field and the LAST_NAME field with a comma and space between them so the result in (FULL_NAME) is ROBERT, COLLISON.

Doing the concatenation is not the problem.

The problem I am having is with the correct Calling and Called Function Syntax.

Thanks.
Bob C.
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40596015
Thanks, then your code is almost correct,

The calling code is perfect, the error is in the strings, just change the variable names removing the square brackets as in my original post

FirstName = xxxxx rather than [firstName] = xxxxx

In VBA you also need to add a RETURN x at the bottom of the subroutine where x is the variable to0 return back to the calling routine. Its not a good idea to use the function name as a variable. In .Net each function has a build in variable that is the variable name and that is automatically returned so there is no need for a Return x statement.

Calling Code

 Dim txtFirstName As String
 txtFirstName = "Robert"
 Dim txtLastName As String
 txtLastName = "Collison"
 Dim txtFullName As String

 txtFullName = MessageTypeMessageI01

Open in new window

Called Code
 
Public Function MessageTypeMessageI01() As String
 On Error GoTo Err_MessageTypeMessageI01

 'MsgBox "Step 000-Lookup Message Details."
 'Dim txtFirstName As String
 'Dim txtLastName As String
 Dim FIRST_NAME As String
 Dim LAST_NAME As String
Dim ReturnedValue as String

 FIRST_NAME = txtFirstName
 LAST_NAME = txtLastName

ReturnedValue = FIRST_NAME & ", " & LAST_NAME

return ReturnedValue

 Exit_MessageTypeMessageI01:
     Exit Function
 Err_MessageTypeMessageI01:
       MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
       Resume Exit_MessageTypeMessageI01
 End Function

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Bob_Collison
ID: 40596144
Hi ChloesDad,

I have setup the following code per your recommendation however I receive an 'Expected End of Statement' compile error on the 'Return  Statement' line.  I. e Return ReturnedValue

Calling Code
Dim txtFirstName As String
txtFirstName = "Robert"
Dim txtLastName As String
txtLastName = "Collison"
Dim txtFullName As Variant
txtFullName = MessageTypeMessageI01()

Called Code
Public Function MessageTypeMessageI01() As String
'MsgBox "Step 000-Lookup Message Details."
Dim txtFirstName As String
Dim txtLastName As String
Dim FIRST_NAME As String
Dim LAST_NAME As String
Dim ReturnedValue As String
FIRST_NAME = txtFirstName
LAST_NAME = txtLastName
ReturnedValue = FIRST_NAME & ", " & LAST_NAME

Return ReturnedValue

Exit_MessageTypeMessageI01:
    Exit Function
Err_MessageTypeMessageI01:
      MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
      Resume Exit_MessageTypeMessageI01
End Function

Thanks.
Bob C.
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40596164
Sorry, I missed that you were using access 2010, this means that you can use the function name as a variable and don't need the return statement.
Called Code
 Public Function MessageTypeMessageI01() As String
 'MsgBox "Step 000-Lookup Message Details."
 Dim txtFirstName As String
 Dim txtLastName As String
 Dim FIRST_NAME As String
 Dim LAST_NAME As String
 FIRST_NAME = txtFirstName
 LAST_NAME = txtLastName
 MessageTypeMessageI01= FIRST_NAME & ", " & LAST_NAME



 Exit_MessageTypeMessageI01:
     Exit Function
 Err_MessageTypeMessageI01:
       MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
       Resume Exit_MessageTypeMessageI01
 End Function

Open in new window

0
 

Author Comment

by:Bob_Collison
ID: 40596858
Hi ChloesDad,

I made the change and data is returned but what is returned is just the comma between the names.

From debugging the values being sent from the Calling Code are not being received by the Called Code.

Thanks,
Bob C.
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40596905
Your not sending any variables from the calling code to the function

To do that you need to pass the values as parameters from the calling to the function.

txtFullName = MessageTypeMessageI01(txtFirstName, txtLastName)


Public Function MessageTypeMessageI01(firstName as string, lastName as string) As String
 'MsgBox "Step 000-Lookup Message Details."
MessageTypeMessageI01= firstName & ", " & lastName

Open in new window


There is no need to create extra variables you can just use the ones declared in the parameters directly
0
 

Author Comment

by:Bob_Collison
ID: 40596947
Hi ChloesDad,

It still is not working.

Here is the complete current code.  It returns a value of 'False'.

Calling Code
Dim txtFirstName As String
 txtFirstName = "Firstname"
 Dim txtLastName As String
 txtLastName = "Lastname"
 Dim txtFullName As String

 txtFullName = txtFullName = MessageTypeMessageI01(txtFirstName, txtLastName)

Called Code
Public Function MessageTypeMessageI01(firstName as string, lastName as string) As String
'MsgBox "Step 000-Lookup Message Details."
MessageTypeMessageI01= firstName & ", " & lastName

Note: I replace my names with generic names for anonymity.
Thanks,
Bob C.
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 40597025
This

 txtFullName = txtFullName = MessageTypeMessageI01(txtFirstName, txtLastName)

should be this

txtFullName = MessageTypeMessageI01(txtFirstName, txtLastName)

a = a = b will calculate a=b and put the value into a, in your case you will always get a = "FALSE"
0
 

Author Closing Comment

by:Bob_Collison
ID: 40597076
Hi ChloesDad,

The last solution works perfectly.

I have copied in the complete exact code that works below.

Calling Code
Dim txtFirstName As String
txtFirstName = "Firstname"
Dim txtLastName As String
txtLastName = "Lastname"
Dim txtFullName As String
txtFullName = MessageTypeMessageI01(txtFirstName, txtLastName)

Called Code
Public Function MessageTypeMessageI01(firstName As String, lastName As String) As String
'MsgBox "Step 000-Lookup Message Details."
MessageTypeMessageI01 = firstName & ", " & lastName

Thanks for your assistance.
Bob C.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question