Add Record to Subform from Main Form - Getting Null Error on PK

[user9]

I added an "Add Record" button to my main form. The Embedded Macro successfully creates a new record on the subform.

When I press any key any field I get the following error:
"You tried to assign the Null value to a variable that is not a Variant data type".

When I click OK I can proceed, but why am I getting this error? What can I do to remove it (either with VBA or within the Embedded Macro)?

Thank you in advance!

[zmbd]

User9:
};-) SNIDE ANSWER: Give the variable a value or change the data type - (you did see the smile yes?! Macros, YUCK)

No, SERIOUSLY...

Ahh the wonderful hell of Macros. This is one reason most of us avoid them when possible - the error messages (if you get one) are almost useless. Sort of like telling a person they can't breath water when they're drowning... true, but not really helping!

In ACC2010 there is an error traping method for macros, I've played with it some, and it could be better (still not helping that swimmer get out of the water).
With that said... you can attempt to single step thru the macro:
SingleStep Macro Action ACC2010 (click Here)
With this being an embedded macro it may be a hit or miss for working.
If this works, you should find the the code that is causing the error... hopefully a simple fix, if not, take note of the code that caused the error and then:

As I said above, this is telling you that one of the parmeters that was created is not getting a value - most likely refering to an empty control on your form; however, without the actual macro-code, we're not going to be able to help you with either MC or VBA

HOWEVER, all isn't lost:

If you are using ACC2010
Open your form in design.
Right click on the control that has you embedded macro.
Events tab
On Click event - click on the [...] button
select all of the text in shown in the Macro Editor
<ctrl><c> to copy
come back to this thread.
Click on the [CODE/] button in the toolbar
place the cursor between the inserted [Code] tags
<ctrl><v>
Your macro text will now be pasted into the post.
(if you found the code from the single step where the error occured, please note which line it is located)
We'll go from there
(^-^)

IF you are using ANY other version of Access. You will have to open the macro and then cross type the information from the macro editor into the post here between the [Code] tags.

[user9]

Re: Add Record to Subform from Main Form

User9:
};-) SNIDE ANSWER: Give the variable a value or change the data type - (you did see the smile yes?! Macros, YUCK)

No, SERIOUSLY...

Ahh the wonderful hell of Macros. This is one reason most of us avoid them when possible - the error messages (if you get one) are almost useless. Sort of like telling a person they can't breath water when they're drowning... true, but not really helping!

In ACC2010 there is an error traping method for macros, I've played with it some, and it could be better (still not helping that swimmer get out of the water).
With that said... you can attempt to single step thru the macro:
SingleStep Macro Action ACC2010 (click Here)
With this being an embedded macro it may be a hit or miss for working.
If this works, you should find the the code that is causing the error... hopefully a simple fix, if not, take note of the code that caused the error and then:

As I said above, this is telling you that one of the parmeters that was created is not getting a value - most likely refering to an empty control on your form; however, without the actual macro-code, we're not going to be able to help you with either MC or VBA

HOWEVER, all isn't lost:

If you are using ACC2010
Open your form in design.
Right click on the control that has you embedded macro.
Events tab
On Click event - click on the [...] button
select all of the text in shown in the Macro Editor
<ctrl><c> to copy
come back to this thread.
Click on the [CODE/] button in the toolbar
place the cursor between the inserted [Code] tags
<ctrl><v>
Your macro text will now be pasted into the post.
(if you found the code from the single step where the error occured, please note which line it is located)
We'll go from there
(^-^)

IF you are using ANY other version of Access. You will have to open the macro and then cross type the information from the macro editor into the post here between the [Code] tags.

Thanks zmbd. I'm on Access 2007 so I just typed it out below. You're right that macros are a pain, if there's an [Event Procedure] I can run instead I'd be glad to start there.

Condition
Action: "OnError"
Arguments: "Next, "

Condition
Action: "GoToRecord"
Arguments: ", , New, "

Condition: "[MacroError]<>0"
Action: "MsgBox"
Arguments: "=[MacroError].[Description], Yes, None, "

[zmbd]

OK:
Just to verify that what should be there is there I recreated your MC in ACC2010
(and a reason for you to move to ACC2010 - XML encoded macros - :)

You could actually take the following XML, copy, open the macro editor in ACC2010 and paste it in!
Still - YUCK)

The reason I did this was just make sure that there wasn't anything missing.... it's been a long time since I had to mess with macros:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
   <UserInterfaceMacro For="Command11" Event="OnClick">
      <Statements>
         <Action Name="OnError"/>
         <Action Name="GoToRecord">
            <Argument Name="Record">New</Argument>
         </Action>
         <ConditionalBlock>
            <If>
               <Condition>[MacroError]&lt;&gt;</Condition>
               <Statements>
                  <Action Name="MessageBox">
                     <Argument Name="Message">=[MacroError].[Description]
                     </Argument>
                  </Action>
               </Statements>
            </If>
         </ConditionalBlock>
      </Statements>
   </UserInterfaceMacro>
</UserInterfaceMacros>

This matches what you have...
So now we need to take a very careful look at all of the remaing structure of your form.

Somewhere, you have some code that is expecting a value and when you move to the new record, the value is unset and the code is tossing the error; however, there isn't any error trapping in place for that code.

Hopefully there are only one or two places where there isn't any error trapping, if not, then you may have to hunt and peck. I would start with whatever control had focus, then the form's on current event, after that - add error trapping to all of the underlying code.

This is why EVERY code block I write will always have at minimum:

Option Compare Database
Option Explicit
'(...)
Sub somename()
   '(...)
   On Error GoTo zerrtrap
   '(...)
zcleanup:
   '(...)
Exit Sub
zerrtrap:
   MsgBox "error# Sub_[somename] " & Err.Number & vbCrLf & "error: " & Err.Description & vbCrLf & "source: " & Err.Source
    Resume zcleanup
Exit Sub

Yes, this the actual basic template I use with the exception of the first two lines, I have these set at the VBE level. Yes, at least to start with, even my one and two "action" line codes will have error trapping... especially if it will be ran using a "runtime" environment. (The production error code may include specfic code to handle certain errors, to send me an email, or even to record the error to a table or offline text file). Feel free to modify and use my template.

[user9]

Wow, thanks! Now when I create a new record, it does so successfully but my PK field gets populated with an existing value (the first PK value in my table).

The subform shows two records, both the existing record with that PK and the new record with just the PK filled in.

[NeoPa]

I'm not clear what you're reporting here. Are you saying that Z's post was extremely helpful, but that you're not quite there yet? You need more help to get it to work?

[zmbd]

Different problem... new thread
However, similar solution, you are going to have to go back in to the design of either the form, the query (if used), and/or the table.

PK fields, from the start, at the table level should be set to prevent a duplicated value from the start - if you didn't do this then you are in for some tough times.