Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating first Function in Access 2010

Posted on 2015-02-07
10
Medium Priority
?
51 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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