"Merging" tables in Access Web App using Data Macro

Question

I have built an Access Web App (2013) to keep track of device inventory for a client. They want to be able to mass-update the database using Excel spreadsheets that are generated from orders. I already know that there is no way to import/update data into an existing table in AWA, so I am attempting to build a data macro that will compare an imported table to the existing table, and add new records, or edit existing ones.

For right now, I am just trying to get the first part to work - add new records. However, no matter what I try, my data macro keeps giving me the error "Access can't convert the value of 'N/A' to the specified data type."

I have run a trace on the macro, and it shows that it goes through each step perfectly until the very end - it will create the new record, set the 2 fields I am testing with... and then try to create the record again. It gets stuck because it doesn't go back up to the next "For Each Record" loop for the imported table. I have tried doing a "Stop Macro" after the Create Record to see if I could get it to just do the first record, but it still tries to run again and fails.

I found on one page someone who said "Data Macros forbid the use of “CreateRecord cannot be used inside of a ForEachRecord”" but I haven't found anything to back that up anywhere.

I do not have enough reputation points to post images or even link to them, unfortunately, so I am going to try to accurately represent my macro and trace below (please excuse my relative newness to the site :( ).

My Macro looks as such:

For Each Record In ImportTest
      Where Condition
   SetLocalVar
              Name IsMatch
              Expression = 0
   Look Up A Record In Inventory List
            Where Condition = [InventoryList].[SerialNumber]=[ImportTest].[Serial Number]
      SetLocalVar
                  Name IsMatch
                  Expression = 1
  If [IsMatch]=0 Then
     Create a Record In Inventory List
        Set Field
            Name InventoryList.BuildingName
            Value = 1
        Set Field
            Name InventoryList.SerialNumber
            Value = [ImportTest].[Serial Number]
  End If

So to explain what my goal was, in case I did it wrong: The "For Each Record In ImportTest" is looking through the imported table (no where condition because I want all of them). The first "SetLocalVar IsMatch" is to give the macro a way to know if the serial number in the import table matches another record in the existing InventoryList table (this is where I would then update that record, once I get it all working). Next is my "If [IsMatch]=0" which checks to see if a matching record was ever found (would've been set to 1 if it had found the match before). Since no match is found, it creates a record in InventoryList using the current record that the For Each Record loop is pointed at. I actually have many other fields that will be set, but these two are just for my proof of concept (my InventoryList requires these two fields). At this point, it should finish adding the record, then hit the end of the loop, and start anew. However, my trace shows "CreateRecord" throwing an SQL exception:

MacroName-ActionName-Operand-Output-TargetRow-RuntimeErrorMessage
ImportMacro-ForEachRecord-ImportTest;---
ImportMacro-ForEachRecord---[ID] = 1 ; [Serial Number] = TESTTEST1 ; [Building Name] = Glendaal Elementary School-
ImportMacro-SetLocalVar-IsMatch-0.000000--
ImportMacro-LookupRecord-InventoryList;WHERE [InventoryList].[SerialNumber]=[ImportTest].[Serial Number]---
ImportMacro-If-[IsMatch]=0---
ImportMacro-CreateRecord----
ImportMacro-If-[ImportTest].[Building Name]="Glendaal Elementary School"---
ImportMacro-SetField-InventoryList.BuildingName-1--
ImportMacro-SetField-InventoryList.SerialNumber-TESTTEST1--
ImportMacro-CreateRecord----SQL exception

I can't figure out why it's doing this last "CreateRecord" that throws the exception. Does anyone see what I may have missed, or have suggestions for how to proceed?

Thanks in advance to all who help. ~SetsunaMH

Current version of ImportTest data macro:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro><Statements><ForEachRecord><Data><Reference>ImportTest</Reference></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">IsMatch</Argument><ExpressionArgument Name="Value"><Expression><Original>0</Original><IntegerLiteral Value="0"/></Expression></ExpressionArgument></Action><LookUpRecord><Data><Reference>InventoryList</Reference><WhereCondition><Expression><Original>[InventoryList].[SerialNumber]=[ImportTest].[Serial Number]</Original><FunctionCall Name="="><Identifier Name="InventoryList.SerialNumber" Index="0"/><Identifier Name="ImportTest.Serial Number" Index="1"/></FunctionCall></Expression></WhereCondition></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">IsMatch</Argument><ExpressionArgument Name="Value"><Expression><Original>1</Original><IntegerLiteral Value="1"/></Expression></ExpressionArgument></Action></Statements></LookUpRecord><ConditionalBlock><If><Condition><Expression><Original>[IsMatch]=0</Original><FunctionCall Name="="><Identifier Name="IsMatch" Index="0"/><IntegerLiteral Value="0" Index="1"/></FunctionCall></Expression></Condition><Statements><CreateRecord><Data><Reference>InventoryList</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">InventoryList.BuildingName</Argument><ExpressionArgument Name="Value"><Expression><Original>1</Original><IntegerLiteral Value="1"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.SerialNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Serial Number]</Original><Identifier Name="ImportTest.Serial Number"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Model</Argument><ExpressionArgument Name="Value"><Expression><Original>8</Original><IntegerLiteral Value="8"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.RoomDescription</Argument><ExpressionArgument Name="Value"><Expression><Original>1</Original><IntegerLiteral Value="1"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Manufacturer</Argument><ExpressionArgument Name="Value"><Expression><Original>1</Original><IntegerLiteral Value="1"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.DeviceName</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Device Name]</Original><Identifier Name="ImportTest.Device Name"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.ImagePackage</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Image Package]</Original><Identifier Name="ImportTest.Image Package"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.NeedsVerification</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Needs Verification]</Original><Identifier Name="ImportTest.Needs Verification"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.NewLocation</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[New Location]</Original><Identifier Name="ImportTest.New Location"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.OneOffs</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[One Offs]</Original><Identifier Name="ImportTest.One Offs"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.PurchasedDate</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Purchased Date]</Original><Identifier Name="ImportTest.Purchased Date"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Retired</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Retired]</Original><Identifier Name="ImportTest.Retired"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Software1</Argument><ExpressionArgument Name="Value"><Expression><Original>67</Original><IntegerLiteral Value="67"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Software2</Argument><ExpressionArgument Name="Value"><Expression><Original>67</Original><IntegerLiteral Value="67"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Software3</Argument><ExpressionArgument Name="Value"><Expression><Original>67</Original><IntegerLiteral Value="67"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Software4</Argument><ExpressionArgument Name="Value"><Expression><Original>67</Original><IntegerLiteral Value="67"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Software5</Argument><ExpressionArgument Name="Value"><Expression><Original>67</Original><IntegerLiteral Value="67"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Staff-Student</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Staff-Student]</Original><Identifier Name="ImportTest.Staff-Student"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Swap</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Swap]</Original><Identifier Name="ImportTest.Swap"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.TagNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Tag Number]</Original><Identifier Name="ImportTest.Tag Number"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.TopUser</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Top User]</Original><Identifier Name="ImportTest.Top User"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Warranty</Argument><ExpressionArgument Name="Value"><Expression><Original>[ImportTest].[Warranty]</Original><Identifier Name="ImportTest.Warranty"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.Comments</Argument><ExpressionArgument Name="Value"><Expression><Original>"Test"</Original><StringLiteral Value="Test"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.CreatedBy</Argument><ExpressionArgument Name="Value"><Expression><Original>"Test"</Original><StringLiteral Value="Test"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">InventoryList.UpdatedBy</Argument><ExpressionArgument Name="Value"><Expression><Original>""</Original><StringLiteral Value=""/></Expression></ExpressionArgument></Action></Statements></CreateRecord></Statements></If></ConditionalBlock></Statements></ForEachRecord></Statements></DataMacro></DataMacros>

Current On Update macro for InventoryList Table (currently is stopped so as to not cause issues and to not overload my AuditTrail table while everything is in development. This macro was tested with multiple people using the database at once and works like a charm).

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro Event="AfterUpdate"><Statements><Comment>Macro is stopped to not overload AuditTrail during changes made to initial data.</Comment><Action Name="StopMacro"/><Comment>Save the Old values</Comment><CreateRecord><Data><Reference>AuditTrail</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">AuditTrail.OldNew</Argument><ExpressionArgument Name="Value"><Expression><Original>"Old"</Original><StringLiteral Value="Old"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.RecordID</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[ID]</Original><Identifier Name="InventoryList.ID"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Timestamp</Argument><ExpressionArgument Name="Value"><Expression><Original>Now()</Original><FunctionCall Name="Now"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.ChangedBy</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[UpdatedBy]</Original><Identifier Name="InventoryList.UpdatedBy"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.BuildingName</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[BuildingName]</Original><Identifier Name="Old.BuildingName"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.RoomDescription</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[RoomDescription]</Original><Identifier Name="Old.RoomDescription"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Manufacturer</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Manufacturer]</Original><Identifier Name="Old.Manufacturer"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Model</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Model]</Original><Identifier Name="Old.Model"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.DeviceName</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[DeviceName]</Original><Identifier Name="Old.DeviceName"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.TopUser</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[TopUser]</Original><Identifier Name="Old.TopUser"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Staff-Student</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Staff-Student]</Original><Identifier Name="Old.Staff-Student"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.SerialNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[SerialNumber]</Original><Identifier Name="Old.SerialNumber"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.TagNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[TagNumber]</Original><Identifier Name="Old.TagNumber"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.PurchasedDate</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[PurchasedDate]</Original><Identifier Name="Old.PurchasedDate"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Warranty</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Warranty]</Original><Identifier Name="Old.Warranty"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.ImagePackage</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[ImagePackage]</Original><Identifier Name="Old.ImagePackage"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Swap</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Swap]</Original><Identifier Name="Old.Swap"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.NewLocation</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[NewLocation]</Original><Identifier Name="Old.NewLocation"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Retired</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Retired]</Original><Identifier Name="Old.Retired"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.OneOffs</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[OneOffs]</Original><Identifier Name="Old.OneOffs"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Comments</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Comments]</Original><Identifier Name="Old.Comments"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.NeedsVerification</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[NeedsVerification]</Original><Identifier Name="Old.NeedsVerification"/></Expression></ExpressionArgument></Action></Statements></CreateRecord><Comment>Save the New values</Comment><CreateRecord><Data><Reference>AuditTrail</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">AuditTrail.OldNew</Argument><ExpressionArgument Name="Value"><Expression><Original>"New"</Original><StringLiteral Value="New"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.RecordID</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[ID]</Original><Identifier Name="InventoryList.ID"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Timestamp</Argument><ExpressionArgument Name="Value"><Expression><Original>Now()</Original><FunctionCall Name="Now"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.ChangedBy</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[UpdatedBy]</Original><Identifier Name="InventoryList.UpdatedBy"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.BuildingName</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[BuildingName]</Original><Identifier Name="InventoryList.BuildingName"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.RoomDescription</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[RoomDescription]</Original><Identifier Name="InventoryList.RoomDescription"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Manufacturer</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Manufacturer]</Original><Identifier Name="InventoryList.Manufacturer"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Model</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Model]</Original><Identifier Name="InventoryList.Model"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.DeviceName</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[DeviceName]</Original><Identifier Name="InventoryList.DeviceName"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.TopUser</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[TopUser]</Original><Identifier Name="InventoryList.TopUser"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Staff-Student</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Staff-Student]</Original><Identifier Name="InventoryList.Staff-Student"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.SerialNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[SerialNumber]</Original><Identifier Name="InventoryList.SerialNumber"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.TagNumber</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[TagNumber]</Original><Identifier Name="InventoryList.TagNumber"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.PurchasedDate</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[PurchasedDate]</Original><Identifier Name="InventoryList.PurchasedDate"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Warranty</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Warranty]</Original><Identifier Name="InventoryList.Warranty"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.ImagePackage</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[ImagePackage]</Original><Identifier Name="InventoryList.ImagePackage"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Swap</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Swap]</Original><Identifier Name="InventoryList.Swap"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.NewLocation</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[NewLocation]</Original><Identifier Name="InventoryList.NewLocation"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Retired</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Retired]</Original><Identifier Name="InventoryList.Retired"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.OneOffs</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[OneOffs]</Original><Identifier Name="InventoryList.OneOffs"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.Comments</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[Comments]</Original><Identifier Name="InventoryList.Comments"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">AuditTrail.NeedsVerification</Argument><ExpressionArgument Name="Value"><Expression><Original>[InventoryList].[NeedsVerification]</Original><Identifier Name="InventoryList.NeedsVerification"/></Expression></ExpressionArgument></Action></Statements></CreateRecord></Statements></DataMacro></DataMacros>

Answer 1

After checking the Access web app package, I've identified what the issues was. You currently have a Default Value property assigned to each of the Software lookup fields (1-5) in the InventoryList table. I removed those default values for each of those fields. I then put in some sample dummy data into the related tables. Your button on the List view is currently set to run the ImportMacro2 data macro. When I run that now, the macro completes without error and the data is inserted into InventoryList. You can't use N/A as default values because those are lookup fields that only accept IDs (numbers).