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: 455
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
Go to the top of the page
 
theDBguy
post Oct 10 2017, 11:35 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,501
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...
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 11:56 AM
Post#3



Posts: 455
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: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Do you also get the same error if you remove the equal sign (or a different one)?
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 12:34 PM
Post#5



Posts: 455
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: 73,501
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?
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 12:53 PM
Post#7



Posts: 455
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: 73,501
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?
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 01:44 PM
Post#9



Posts: 455
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: 455
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: 73,501
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...
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 02:49 PM
Post#12



Posts: 455
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: 73,501
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.
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 03:00 PM
Post#14



Posts: 455
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: 73,501
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.
Go to the top of the page
 
nmartin1230
post Oct 10 2017, 03:25 PM
Post#16



Posts: 455
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: 73,501
Joined: 19-June 07
From: SunnySandyEggo


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

Hope it helps...
Go to the top of the page
 
nmartin1230
post Oct 11 2017, 11:22 AM
Post#18



Posts: 455
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: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 07:01 PM