Hi Jane,
Perfect, glad to hear everything is going fine so far.
Here are the next steps to complete.
1. Create a new standalone data macro which we will use to populate the new Customer Name lookup field in the Orders table.
Start by closing all open objects and then click the Advanced button on the Home ribbon tab (it's in the Create ribbon group).
Select the Data Macro option from the drop-down list of choices beneath the Advanced button.
2. Access opens a new empty data macro and displays the macro design surface. Have you created any data macros before in Access 2013 web apps or 2010 style web databases? Data macros are very powerful. Not to worry if you haven't, I have a completed one that you can just copy.
3. Essentially we want to do the following (in English):
- Loop through each record in the Orders table one at a time
- Within that loop we will read the current value in the Customer Name Old field. (The one that has the text data of the Customer Names.)
- We will then attempt to find that customer name within the Customers table using a LooukupRecord action.
- Once we find that matching record, we will grab the CustomerID field value which is the unique identifier for the record. We will save that value in a local variable
- We then will write that local variable into the new Customer Name lookup field in the Orders field for the record we are currently reviewing.
- We then move to the next record in the Orders table and complete the same steps above. We repeat this process until all Order records have been processed.
- In theory, when finished, there should be data in the Customer Name field for each record in the Orders table.
All make sense?
4. Ok, let's get started.
Here is a screenshot of what the data macro logic will look like - I'm hoping your field names match my example perfectly. Take a few minutes and just study this to understand the macro logic in place and it how it relates to my explanation above. Note that I've added in extra macro comments to further explain.
5. You can either type/input that into the empty macro window manually or just copy and paste the below logic I have posted here. To copy and paste this logic, do the following:
- Copy the logic below (it is in XML format) to your clipboard. Be sure you start exactly with < character below and the ending > character and copy that all to your clipboard.
- Click on the macro design surface away from any other objects - just click the empty white surface so your focus is there.
- Now press CRTL+V to paste all that macro logic from your clipboard onto the macro design window.
- It should just all appear now magically in your window. Let me know if this step doesn't work.
Here is the logic:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro><Statements><Comment>This data macro will loop through all the records in the Orders tables and fill in the corect related ID value for the Customer Name lookup field.</Comment><ForEachRecord><Data><Reference>Orders</Reference></Data><Statements><Comment>Find a match in the Customers table where the name matches what is in our current short text field in the Orders table.</Comment><LookUpRecord><Data><Reference>Customers</Reference><WhereCondition><Expression><Original>[Customers].[Customer Name]=[Orders].[Customer Name Old]</Original><FunctionCall Name="="><Identifier Name="Customers.Customer Name" Index="0"/><Identifier Name="Orders.Customer Name Old" Index="1"/></FunctionCall></Expression></WhereCondition></Data><Statements><Comment>Now that we found a match, set a local variable equal to the CustomerID value for this record.</Comment><Action Name="SetLocalVar"><Argument Name="Name">varCustomerID</Argument><ExpressionArgument Name="Value"><Expression><Original>[Customers].[CustomerID]</Original><Identifier Name="Customers.CustomerID"/></Expression></ExpressionArgument></Action></Statements></LookUpRecord><Comment>Now write this data into our new lookup field.</Comment><EditRecord><Data/><Statements><Action Name="SetField"><Argument Name="Field">[Orders].[Customer Name]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varCustomerID]</Original><Identifier Name="varCustomerID"/></Expression></ExpressionArgument></Action></Statements></EditRecord></Statements></ForEachRecord></Statements></DataMacro></DataMacros>
6. Save and close this new named data macro. Name the macro something like dmFillInLookupData. Let me know if this fails to save for any reason. If it fails to save, you won't be able to continue with the next steps until that is resolved.
7. Now that we have the completed data macro logic, we have to execute it. We have to call it somehow. There are many ways to execute this, but let's take a simple approach of adding a temporary command button to one of the views to call it.
8. Open the Form/View called Customers List in design view. In Access 2013 web apps we use the term views to describe user interface forms used for data entry in the web browser. They are listed though under the Forms heading in the client Navigation Pane. Confusing? Yes I agree. Just remember that a View = Form in an Access 2013 web app.
- Once you open the view in Design view, click the Command Button option in the Controls group on the ribbon and Access will place a new command button control onto the form design grid.
- Select the command button control and three charm buttons appear next to it. Click the charm button that has a lightning bolt on it- this is the Actions charm.
- You see the Actions flyout menu and there will be one option called "On Click" since command button controls support this event. Click On Click and you'll see a macro window open here modally.
- In the drop-down list on the empty macro window select the action called RunDataMacro. Access places the action on the design window.
- There is one argument for this action (Macro Name) with a drop-down list. Select the dmFillInLookupData run data macro option from this drop-down list here. This is what we named it earlier.
- That's all we need to do for the design here so click Save on the ribbon to save your macro changes and then click Close to close the macro window.
- You should be back on the view window now. Click Save again to save your view changes.
9. Now that you've created a temporary place to run this named data macro one time, let's execute it in the browser. Click The Launch App button on the ribbon to open the app in your web browser.
- Navigate to the Customers table and then the List view in your browser (it will probably open to this view by default).
- Click the command button one time to execute it. Let me know if there are any errors here.
10. The data should be set so let's do a quick check. Go back to Access client and open the Orders table in datasheet view within client. Do a quick scan comparing the values in the Customers Name Old field and the new Customers Name lookup field. They *should* appear to be all identical.
Do you have any blanks for the new Customers Name field in the Orders table at this point? Let me know if there are any blanks.
OK, let me know how you progressed through these steps and the results of the data in the Orders table. I'd like to check your progress here before we do all the final cleanup work. Don't delete anything yet.
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation
Author -
Microsoft Access 2013 Inside Out
Author -
Microsoft Access 2010 Inside Out
Co-author -
Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info:
http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------