Solved

Creating first Function in Access 2010

Posted on 2015-02-07
10
45 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
  • 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now