Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding records in two related recordsets (MS ACCESS 2007)

    For each customer that i visit and who places an order, i use an Excel worksheet into which for each Orderitem i enter three data :
    1) klntID the unique client number
    2) Artnr the unique code of the article ordered
    3) Qty the quantity of the article that is ordered

    I send this worksheet as a attachment of a Email to my database
    In my MS Access database i have a routine that is can read the data in the worksheet and import these data in a dedicated Access table," tblImpOrders".
    The goal is now to realize an complete order (inclusive orderitems ) only with the help of these three data elements / Row
    To do it purely programmatically i begin by setting up a recordset rstOrders based on a query "qryOrders" with the tables tblClients and tblOrders in a one to many relationship based on KlntID.
    :
    Dim rstOrders As DAO.recordset
    Set rstOrders = db.Openrecordset ("qryOrders", DBOpenDynaset)
    rstOrders.AddNew ' a new record as added
    rstOrders!KlntID = tblImpOrders!KlntID ' with this information the recordset can get all the necessary information of the client from the table tblClients
    rstOrders!OrderID = Ordernr ' this number which is a unique number is generated by a special routine
    Dim rstOrderdetail as DAO.Recordset ' setting up a second recordset for entering the orderitems, based on a qry "qryOrderdetail" with the tables tblOrders and tblArticles
    set rstOrderDetail = DB.Openrecordset("qryOrderdetail", DbOpenDynaset) ' between the firts recordset and the second one there axists a one to may relationship based on OrderIDl
    rstOrderDetail.AddNew ' a new record is added to the recordset
    rstOrderDetail! Orderid = rstOrders!OrderID ' the same OrderID is added as in rstOrders ( must be the same to respect the relationship
    rstOrderDetail!Artnr = tblImpOrders!Artnr ' the articlecode as added and makes part of the double field primary key of this recordset consiting of OrderID and Artnr
    rstOrderDetails!Qty = tblImpOrders!Qty ' the quantity QTY is added

    The Artnr And Qty makes it possible to get all the rest of the necessary information to complete a orderItem

    Believing that this way odf working is correct, I have a few question to be solved::

    1) When must the updates of both recordsets have place. Is it first rstorders and then rstOrderDetail or in the reverse order and when has it to be done
    2) In order to exclude ending up with orders without a orderitem and reverse, how to prevent
    3) Som calculations as OrderTotal, TotalVAT etc has to be done and the result of these has to be put in tblOrders,How do we do that programmativally from the second recordset
    4) If the order consists of more than one orderitem, how do we handle this problem ... What about updating/orderitem etc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    If I understand you correctly, you already have set up relationships between the tables. To ensure that you can't create orphan records such as an order detail record without a corresponding order, you should enforce referential integrity:
    - Open the Relationships window.
    - Double click a join line.
    - Tick the check box "Enforce referential integrity".
    - Click OK.
    - If you get an error message, you have orphan records; you must correct this first, before you can enforce referential integrity.
    - Repeat for the other join lines.

    There is no need to store information such as OrderTotal and TotalVAT in the orders table. It is derived information that can be calculated "live" in a query.

    About your main question: if you have a number of rows in your Excel worksheet, how do you determine which rows belong to the same order? I assume that the same client can place multiple orders, each with multiple items.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Dear Hans ;
    Yes indeed some customers places a order consisting of more then one orderitem.
    With the code that i a have sent i encounter the following problem during updating:
    After entering data in the first recordset ( rstOrders) i update the new added record, then i continue by adding information to the second recordset. When Ii try to update the second recordset(with the orderitem)
    i encounter the error 3101 - saying MS Access Engine can not find a record in tblOrders of which the key equals the fields = OrderID

    Sorry for the fact that my english isn't so good - I'm speaking Dutch ! Thanks a lot for your help I appeciate it very very much

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    In order to find out how many orderitems a customer has ordered, I preview each row with the KlntID. Then using the imported data in tblImpOrders i do the following. I get the KlntID of the first row and
    put this in a variable. Then I use a do While KlntID = variable .... Once there is a unequality, I know that we have to do with an other client.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Your English is good enough!

    You need to open both recordsets only once, at the beginning. You need to save (update) the new record in tblOrders before saving a related record in tblOrderDetails:
    The following is partly pseudo code since I don't know all the details.

    Dim db As DAO.Database
    Dim rstImpOrders As DAO.Recordset
    Dim rstOrders As DAO.Recordset
    Dim rstOrderdetail as DAO.Recordset
    Dim OrderNr As Long
    Dim KlantNr As Long

    Set db = CurrentDb
    Set rstImpOrders = db.OpenRecordset("tblImpOrders", dbOpenDynaset)
    Set rstOrders = db.OpenRecordset ("qryOrders", dbOpenDynaset)
    Set rstOrderDetail = db.OpenRecordset("qryOrderdetail", dbOpenDynaset)

    ' Set KlantNr to a non-existent value to begin with
    KlantNr = -9999

    Do While Not rstImpOrders.EOF
    ' Do we have a new client number?
    If Not rstImpOrders!KlntID = KlantNr Then
    OrderNr = ... ' calculate new order number here
    ' Remember client number for next time
    KlantNr = rstImpOrders!KlntID
    ' Create a new order record
    rstOrders.AddNew
    rstOrders!KlntID = KlantNr
    rstOrders!OrderID = OrderNr
    ' Save record
    rstOrders.Update
    End If
    ' Create new order detail record
    rstOrderDetail.AddNew
    rstOrderDetail! OrderID = OrderNr
    rstOrderDetail!Artnr = rstImpOrders!Artnr
    rstOrderDetail!Qty = rstImpOrders!Qty
    ' Save record
    rstOrderDetail.Update
    ' On to the next Excel record
    rstImpOrders.MoveNext
    Loop

    rstOrderDetails.Close
    rstOrders.Close
    rstImpOrders.Close

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    All I can tell you is that i admire the way you stay ready to help where you can. It's wonderfull.
    Thank you, thank you.
    Best wishes from Brugge in Belgium !!!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Wow, Brugge! That's one of the most beautiful cities I've visited.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Dear Hans;

    When running the program below by clicking on a button on a form , i still get the following error when the program reaches the programline: rstOrderDetails.Update

    "Microsoft jet database engine could not find a record in the table 'tblOrders with key matching field(s) 'Order_ID' (error 3101)

    The update of rstOrders doesn't result in a error


    Private Sub import_Click()

    Dim WS As DAO.Workspace
    Dim db As DAO.Database
    Dim rstImpOrders As DAO.Recordset
    Dim rstOrders As DAO.Recordset
    Dim rstOrderDetail As DAO.Recordset
    Dim blnSpecialprice As Boolean
    Dim XtraKorting As Integer

    Set WS = DBEngine.Workspaces(0)
    Set Remotedb = WS.OpenDatabase(acbGetLinkPath("tblOrders_ID"), False, False, "") ' 'necessary for getting the new OrderID
    Set db = CurrentDb

    Set rstImpOrders = db.OpenRecordset("tblImpOrders", dbOpenTable)
    If rstImpOrders.BOF And rstImpOrders.EOF Then
    MsgBox ("Geen geimporteerde orders gevonden")
    GoTo Importeren_Exit
    Else
    rstImpOrders.MoveFirst
    End If

    Set rstOrders = db.OpenRecordset("QryOrderimp", dbOpenDynaset)
    Set rstOrderDetails = db.OpenRecordset("qryorderdetails", dbOpenDynaset)

    Klantnr = -9999

    Do While Not rstImpOrders.EOF
    'Hebben we en nieuw klantnr
    If Not IsNull(rstImpOrders!KlantID) And IsNumeric(rstImpOrders!KlantID) Then
    Klantnr = rstImpOrders!KlantID
    Else
    MsgBox ("Het klantnummer van dit ingevoerd order ontbreekt of is verkeerd")
    GoTo Importeren_Error
    End If
    If Not IsNull(rstImpOrders!Artnr) And Not IsNumeric(rstImpOrders!Artnr) Then
    strArtnr = rstImpOrders!Artnr
    Else
    MsgBox ("Het Artikel is niet gepreciseerd in het ingevoerd order")
    GoTo Importeren_Error
    End If
    If Not IsNull(rstImpOrders!QTY) And (IsNumeric(rstImpOrders!QTY) And (rstImpOrders!QTY >= 1)) Then
    QTY = rstImpOrders!QTY
    Else
    MsgBox ("De bestelde hoeveelheid van het ingevoerd order is verkeerd")
    GoTo Importeren_Error
    End If

    If Not rstImpOrders!KlantID = Klantnr Then
    lngCounter = acbGetCounter("tblOrders_ID")
    Ordernr = Trim(Str(lngCounter) & "/" & Trim(Right(Date$, 2)))
    Klantnr = rstImpOrders!KlantID
    Agentnr = rstImpOrders!Agentnr
    rstOrders.AddNew
    rstOrders!Klnt_ID = Klantnr
    rstOrders!Order_ID = Ordenr
    rstOrders!Agentnr = Agentnr
    rstOrders!Orderdatum = Date
    rstOrders.Update
    End If
    strArtnr = rstImpOrders!Artnr
    QTY = rstImpOrders!QTY
    rstOrderDetails.AddNew
    rstOrderDetails!Order_ID = Ordernr
    rstOrderDetails!Artnr = strArtnr
    rstOrderDetails!QTY = QTY
    If rstOrderDetails!QTY <= rstOrderDetails!Voorraad Then
    rstOrderDetails!Geleverd = rstOrderDetails!QTY
    rstOrderDetails!Voorraad = rstOrderDetails!Voorraad - rstOrderDetails!QTY
    If rstOrderDetails!Voorraad <= Minstock Then
    If BlnBijbestellingmsg = False Then
    Call Bijbestellen
    End If
    End If
    Else
    rstOrderDetails!Geleverd = rstOrderDetails!Voorraad
    rstOrderDetails!Nalev = rstOrderDetails!QTY - rstOrderDetails!Voorraad
    rstOrderDetails!Voorraad = 0
    If BlnBijbestellingmsg = False Then
    Call Bijbestellen
    End If
    'Call Naleveringen
    End If

    If rstOrderDetails!Voorraad <= rstOrderDetails!Bestelpunt Then
    Call Bijbestellen
    BlnBijbestellingmsg = True
    End If

    rstOrderDetails!BtwStatus = DLookup("[BtwStatus]", "[tblKlanten]", "[Klnt_ID] = " & Klantnr)
    If rstOrderDetails!BtwStatus = 2 Or rstOrderDetails!BtwStatus = 1 Then
    rstOrderDetails!BTW = DLookup("[BtwTarief]", "[tblArtikelen]", "[Artnr] = """ & strArtnr & """")
    rstOrderDetails![BtwNr] = DLookup("[BtwNr]", "[tblArtikelen]", "[Artnr] = """ & strArtnr & """")
    Else
    rstOrderDetails!BTW = 0
    rstOrderDetails![BtwNr] = DLookup("TariefNr", "tblBtwTarief", "Tarief = 0.00")
    End If
    If Not IsNull(DLookup("[PrijsSpec]", "[tblPrijsSpec]", "[Klnt_ID] = " & Klantnr & " AND [Artnr] = """ & strArtnr & """")) Then
    rstOrderDetails![Eenheidsprijs] = (DLookup("[PrijsSpec]", "[tblPrijsSpec]", "[Klnt_ID] = " & Klantnr & " AND [Artnr] = """ & strArtnr & """"))
    If rstOrderDetails![Eenheidsprijs] > 0 Then
    blnSpecialprice = True
    Else
    If Doorverkoper Then
    rstOrderDetails![Eenheidsprijs] = DLookup("[DoorverkpPrijs]", "[tblArtikelen]", "[Artnr] = """ & strArtnr & """")
    Else
    rstOrderDetails![Eenheidsprijs] = DLookup("[Verkoopprijs]", "[tblArtikelen]", "[Artnr] = """ & strArtnr & """")
    End If
    End If
    End If
    If Not IsNull(DLookup("[Korting]", "[tblPrijsSpec]", "[Klnt_ID] = " & Klantnr & " AND [Artnr] = """ & rstArtnr & """")) Then
    rstOrderDetails![Korting] = (DLookup("[Korting]", "[tblPrijsSpec]", "[Klnt_ID] = " & Klantnr & " AND [Artnr] = """ & rstArtnr & """"))
    If rstOrderDetails![Korting] > 0 Then
    XtraKorting = True
    Else
    rstOrderDetails![Korting] = DLookup("[Korting]", "[tblArtikelen]", "[Artnr] = """ & rstArtnr & """")
    End If

    End If
    rstOrderDetails.Update
    rstImpOrders.MoveNext
    Loop
    rstOrderDetails.Close
    rstOrders.Close
    rstImpOrders.Close

    Importeren_Exit::
    Exit Sub

    Importeren_Error:
    MsgBox Error
    GoTo Importeren_Exit
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    That's a lot of code to read! I don't have time for that now. If nobody else replies, I'll try to look at it later today.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Dear Hans ;

    For a great deal i believe i've solved the problem : see the offending part of the program below:

    Klantnr = -9999

    Do While Not rstImpOrders.EOF
    'Hebben we en nieuw klantnr
    If Not IsNull(rstImpOrders!KlantID) And IsNumeric(rstImpOrders!KlantID) Then
    Klantnr = rstImpOrders!KlantID

    As You can see the klantnr that was given the value -9999 is changed to klantnr = rstImpOrders!klantID
    and so the " If Not rstImpOrders!KlantID = Klantnr Then " is always false ...
    Also there is a mistake in the folowing line : rstOrders!Order_ID = Ordenr
    this must be : rstOrders!Order_ID = Ordernr
    The error is gone and the order with the associate orderdetail is saved. But in my tblImpOrders i have two rows for the same Client,
    and only the first of them is saved.
    I'm trying to find out what's the reason therefore, and if I succeed i'll let you know the solution, otherwise I let you know if I can't sove this problem.
    Hopefully I can do it myself , i'll do my best

    Regards

    Jacques

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding records in two related recordsets (MS ACCESS 2007)

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •