UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting Error On A Macro That Was Working, Access 2007    
 
   
nmartin1230
post Oct 10 2017, 11:07 AM
Post#1



Posts: 439
Joined: 20-May 14



I have a macro on form OnLoad event that is erroring on the first part of the macro. The error I receive is Type Mismatch. It is the SetTempVar argument that it erroring on.
Here is the what the macro looks like...
Attached File  macroerr.PNG ( 17.57K )Number of downloads: 8

This post has been edited by nmartin1230: Oct 10 2017, 11:08 AM
Go to the top of the page
 
theDBguy
post Oct 10 2017, 11:35 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Do you need to enclose "Space" in quotes? As in:

SetTempVar "Space",=InStrRev([TempVars]![NewData]," ")

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 11:56 AM
Post#3



Posts: 439
Joined: 20-May 14



No, I thought that too. I tried the enclose in quotes and brackets to no avail.
Go to the top of the page
 
theDBguy
post Oct 10 2017, 12:21 PM
Post#4


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Do you also get the same error if you remove the equal sign (or a different one)?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 12:34 PM
Post#5



Posts: 439
Joined: 20-May 14



Just tried and yes I still get the same error when I remove the equal sign. I removed the equal sign and tried with quotes and brackets around Space.
Go to the top of the page
 
theDBguy
post Oct 10 2017, 12:40 PM
Post#6


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Sorry, I don't use macros much, so I can't see what could be wrong with it from here. Did you say it used to work, the same exact one?

Could you try using VBA?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 12:53 PM
Post#7



Posts: 439
Joined: 20-May 14



Yes, the macro worked in Access 2016 but when I saved it as a 2007 file it said the macro would not open or function. So, I deleted it out and started fresh. I copied word for word from the 2016 format. The first line of it is the only thing that is causing me trouble. I could use VBA instead of a macro. I'm not very good at writing code especially when translated it from a macro. iconfused.gif
Go to the top of the page
 
theDBguy
post Oct 10 2017, 01:35 PM
Post#8


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Initially, you could try having Access do the conversion for you. There should be a button to "convert macros to VBA" somewhere. Let me know if you can't find it. Is this a saved macro or an embedded macro?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 01:44 PM
Post#9



Posts: 439
Joined: 20-May 14



I found the button and will give it a try. It is an embedded macro.
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 01:56 PM
Post#10



Posts: 439
Joined: 20-May 14



Here is what it converted it to and now I get an error stating it had a problem communicating with the OLE server.
CODE
Private Sub Form_Load()
On Error GoTo Form_Load_Err

    TempVars.Add "[TempVars]![Space]", InStrRev(TempVars!NewData, " ")
    On Error Resume Next
    If (TempVars!Space = 0 And TempVars!NewData <> "") Then
        [Last Name] = TempVars!NewData
    End If
    If (TempVars!Space > 0) Then
        [First Name] = Left(TempVars!NewData, TempVars!Space - 1)
    End If
    [Last Name] = Mid(TempVars!NewData, TempVars!Space + 1)
    TempVars.Remove "NewData"
    TempVars.Remove "Space"


Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub
Go to the top of the page
 
theDBguy
post Oct 10 2017, 02:41 PM
Post#11


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Try replacing this line:

TempVars.Add "[TempVars]![Space]", InStrRev(TempVars!NewData, " ")

with this:

TempVars.Add "Space", InStrRev(TempVars!NewData, " ")

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 02:49 PM
Post#12



Posts: 439
Joined: 20-May 14



I get the same error....
Attached File  macroerr.PNG ( 15.7K )Number of downloads: 2
Go to the top of the page
 
theDBguy
post Oct 10 2017, 02:51 PM
Post#13


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


I thought you said the original error was a "Type Mismatch?" The screenshot you posted does not look like it's complaining about a type mismatch.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 03:00 PM
Post#14



Posts: 439
Joined: 20-May 14



When it was an embedded macro it was Type Mismatch, now that I converted it to VBA I got this error.
Go to the top of the page
 
theDBguy
post Oct 10 2017, 03:07 PM
Post#15


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Can you post a screenshot of the form in design view highlighting the button for this code? We need to see the Properties window. Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 03:25 PM
Post#16



Posts: 439
Joined: 20-May 14



It is fired in the OnLoad event, no button.
Attached File  macroerr.PNG ( 69.66K )Number of downloads: 2
Go to the top of the page
 
theDBguy
post Oct 10 2017, 03:53 PM
Post#17


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Okay, try changing the OnLoad event from [Embedded Macro] to [Event Procedure].

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
nmartin1230
post Oct 11 2017, 11:22 AM
Post#18



Posts: 439
Joined: 20-May 14



theDBguy - I figured it out, I was missing part of the macro that I copied from another DB. As an embedded macro, it still gave me an error but as soon as I converted it to VBA everything worked swimmingly.
Thanks for all your help, I really appreciate it!!!!! Sometimes, it's the little things and trips me up. That's why I love this forum and it's members!! uarulez2.gif
Go to the top of the page
 
theDBguy
post Oct 11 2017, 11:35 AM
Post#19


Access Wiki and Forums Moderator
Posts: 70,817
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th October 2017 - 07:12 PM