asked on

Creating first Function in Access 2010

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.

ChloesDad

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

Bob Collison

ASKER

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.

ChloesDad

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

Bob Collison

ASKER

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.

ChloesDad

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

Bob Collison

ASKER

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.

ChloesDad

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

Bob Collison

ASKER

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.

ASKER CERTIFIED SOLUTION

ChloesDad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Collison

ASKER

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.