Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Update order records with item information

Posted on 2011-09-10
Medium Priority
Last Modified: 2012-05-12
I have two tables one containing order information and another containing item information. one order to many items. I need to update the order table with the item information. Please post the VBA on how to loop through the item table and update the order table.

Order table fields look like this:

item table fields look like this:

within the item table there could be up to 6 item records per uniqueid.
Question by:Dalexan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
LVL 16

Expert Comment

ID: 36517241
Usually a simply form/subform set should do the job. No VBA required. Noting the field names qty1,qty2,qty3.... I suspect that the problem is with your table design. Can you explain what are these fields used for. Even better if you could post a sample of you DB


Author Comment

ID: 36517289

I have uploaded a sample database to be used to show an example of what the tables look like. They are actually much bigger with many more fields. I need to know how to update the order records with the item information using VBA. The actual tables are linked tables to a larger mysql remote database.
LVL 16

Expert Comment

ID: 36517338
As I mentioned in the previous post, I think that there is a problem with your table design.  Instead of developing complex code to cater for poor design it's better to fix the design, which if not fix will give you a lot of problem as you progress with the development.

So Please answer the following question

why do you have qty1,qty2,qty3.... and sku1,sku2,sku3.... in your order table.

I am stressing on this because I would normally expect something like

OrderID, Name,Date

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.


Author Comment

ID: 36517352
These are linked tables in a database that I cannot modify. I have to loop through the order table get the uniqueid, match it to the corresponding uniqueid in the items table and update the order records qty and sku values with the values from the item table.

I want to stress again that I cannot modify the table structure, these tables are part of a much larger complex system and are used for other process's. My task is to update the quantity's 1 through 6 and sku's 1 through 6 in the order table.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36517397

I agree with sb9.

While it may be true that you cannot modify the tables, it is also true that they (or at least this one table) are not "Normalized".

So the need here is for the original designer of these tables to "modify" them.

Left in this "un-normalized" state this table will forever require workarounds to get it to function like a true "Normalized" table.
(Like your situation here)

Other disadvantages of this design will be that it may have forced other tables to follow a similar design, and that getting summaries, or "Range Data" out of a table like this will be difficult.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36517406
I am not saying that this is impossible, or that we won't help you...

It's just unfortunate that you have been given an inefficient design and been asked to "make it work".

So sit tight, perhaps a workaround will be forthcoming...


Author Comment

ID: 36517421
Thank you, I agree that is is very unfortunate that I have to work with this table structure. The short and simple explanation is that the order table is a temporary table that is used to export a FIXED FIELD text file that is imported into a mainframe system.....rex....or something like that. This may explain why this table is structured the way it is.

I'm just looking for some VBA code that will loop through the order record, get the unique id, then loop through the items matching that unique id and fill in qty1-6 and sku1-6 in the order record.

Thanks for your quick attention and comments.
LVL 31

Accepted Solution

Helen Feddema earned 1040 total points
ID: 36517540
It is unfortunate for a developer to have to work with a poorly designed, non-normalized table structure, but sometimes it does happen, usually in the circumstances you describe (exporting to a flat-file database).  Supposing that there are no more than 6 SKUs per ID in the Details table, this code should do the job:
Option Compare Database
Option Explicit

Public Sub UpdateOrders()
'Created by Helen Feddema 10-Sep-2011
'Last modified by Helen Feddema 10-Sep-2011

On Error GoTo ErrorHandler

   Dim rstOrders As DAO.Recordset
   Dim rstDetails As DAO.Recordset
   Dim lngID As Long
   Dim strQuery As String
   Dim strSQL As String
   Dim intCounter As Integer
   Dim strSKUField As String
   Dim strQtyField As String
   Dim lngCount As Long
   Set rstOrders = CurrentDb.OpenRecordset("DataTableOrders", dbOpenDynaset)
   strQuery = "qryTemp"
   Do While Not rstOrders.EOF
      'Data type conversion needed because the matching fields are of
      'different data types
      lngID = CLng(rstOrders![uniqueid])
      If lngID <> 0 Then
         strSQL = "SELECT * FROM DataTableItems WHERE " _
            & "[ord_id] = " & lngID & ";"
      End If
      Debug.Print "SQL for " & strQuery & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuery, strSQL)
      Debug.Print "No. of items found: " & lngCount
      If lngCount > 0 Then
         intCounter = 1
         Set rstDetails = CurrentDb.OpenRecordset(strQuery)
         Do While Not rstDetails.EOF
            strSKUField = "SKU" & CStr(intCounter)
            strQtyField = "QTY" & CStr(intCounter)
            rstOrders(strSKUField) = rstDetails![SKU]
            rstOrders(strQtyField) = rstDetails![odi_qty]
            intCounter = intCounter + 1
      End If
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in UpdateOrders procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window

LVL 31

Expert Comment

by:Helen Feddema
ID: 36517550
I had to convert the linking field to a Long, because it is text in one table and Long Integer in the other -- I assume you can't change that either.
LVL 31

Expert Comment

by:Helen Feddema
ID: 36517552
If there could be more than 6 SKUs, you could add a few lines of code to stop at the 6th.
LVL 16

Expert Comment

ID: 36517567
I have added a temporary table and a test form to the sample that you post. Clicking the button on the temp table will populate the temporary table. If the result in the temporary table is what you would like to insert in the item table we can proceed further with this approach.

Author Comment

ID: 36517830
I will review this and get back tomorrow, thanks for your quick responses.

Author Closing Comment

ID: 36523779
Thank you Helen, This is exactly what I was looking for. Works excellent.

Also thanks for the debug window prints. Makes it much easier to see whats happening when debugging.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question