Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Understanding Recordsets (97)

    I am trying to understand and learn more about recordsets.

    In the attached sample db I have three tables and one module.

    Let me first explain that the main table, tblSalesCash, is my main source table which I get by linking, via ODBC, to an Informix server so I cannot amend the layout of the tblSalesCash., the other two (tblCashHeader and tblCashDetails) I have manually created by copying data in from excel which I have transposed from my original source table.

    The tblSalesCash table is used to record money we have received from customers and which invoices they are paying, as you can see from the tblSalesCash table the invoices (ITEM_NUMBERS) they are paying and the amounts are all recorded on one line (up to 31 invoices can be settled on one line before a new page is created)

    What I would like to do is split the tblSalesCash into two separate tables: namely tblCashHeader and tblCashDetails, that way I can link to my invoice list table so that I can record when each invoice was settled.

    I have started creating module basSplitData which I think is the right way to solve this, which is to get the data into a recordset and export the various bits of it to the new tables by defining arrays, but my first problem/question is how do I do this?

    My second question is whether my idea to solve this is in fact the right way to go about it? (Would I still need to make use of the two additional tables I have created? Am I completely missing the plot with what I want to do?)

    Many thanks as always
    Hayden
    Attached Files Attached Files

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

    Re: Understanding Recordsets (97)

    I am using Access 2002; normally I am able to open or convert Access 97 databases without problem. When I try to open your database, however, I get an error message stating that I don't have sufficient rights; same when I try to import objects from the database. Did you happen to post a secured database?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding Recordsets (97)

    Lets try this one

    Regards
    Hayden
    Attached Files Attached Files

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

    Re: Understanding Recordsets (97)

    Yup, that's better. I'll have a look at it.

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

    Re: Understanding Recordsets (97)

    I suppose that you want to store the common details in tblCashHeader and the details of the individual invoices in tblCashDetails. If that is correct, the fields ACCOUNT_NO, PAGE_NO and REFERENCE belong in tblCashHeader, not in tblCashDetails. And shouldn't tblCashDetails contain fields for DISCOUNT_TAKEN_IND, ITEM_ALLOC_AMT, CASH_ITEM_RATE, CASH_ITEM_OP and DISCOUNT_TYPE? Or are these not important for your purposes?

    (I suppose that the original table in Informix had more fields, since the table in Access has fields DISCOUNT_AMT01 through 06 instead of through 31.)

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding Recordsets (97)

    Hello Hans

    Your assumption regarding the contents of the tblCashHeader and tblCash Details is correct and I see your point about the ACCOUNT_NO, PAGE_NO and REFERENCE being in the tblCashHeader and not in the tblCashDetail. The tblCashDetails table can contain the fields you mentioned. At the moment I'm not sure if I will need them or not thats why I didn't bother to include them, I just wanted to make sure I was heading in the right direction first.

    You are also right about the source table containing many more fields, in fact the original source table has 255 columns, I have just selected the columns I think I will need, the DISCOUNT_AMT01-06 column is correct by the way there are only the 6 columns in the source table.

    Regards
    Hayden

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

    Re: Understanding Recordsets (97)

    Shouldn't you have a third table then, to hold up to 6 records for each record in tblCashHeader, corresponding to DISCOUNT_AMT01 ... 06 ?

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding Recordsets (97)

    I suppose I could have a third table, although I'm not 100% confident I understand why (I can't picture it at the moment).

    There are other discount fields in the source table, but I have chosen these(6) ones that mention amount because we use the discount field to record any bank charge amounts in the informix system and I thought I would need them.

    Just some further info for you is that if the amount we receive from the customer is less than the invoice amount, and provided it is within an acceptable range or confirmed by our bank we accept the difference as a bank charge.

    I hope this answers your question.

    Regards
    Hayden

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

    Re: Understanding Recordsets (97)

    I have attached the database with the code in the basSplitData module. It would have been possible (and more efficient) to populate the tblCashHeader table using an append query, but since you wanted to know how to work with recordsets, and since we have to do that to populate the tblCashDetails table anyway, everything is done in the module. I have interspersed the code with comments, so I hope it is clear how it works, but feel free to ask if you want more explanation.

    Note: if you get syntax errors, select Tools | References... in the module, and make sure that there are no MISSING references and that a reference to the Microsoft DAO 3.5n Object Library is set.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding Recordsets (97)

    Hans, I am truly amazed by your knowledge and understanding. I think the best I can do right now is take comfort in the fact that I guessed I had to use a recordset somehow, but I am really blown away by all of this. Thank you very much!

    I think I understand the first bit of the code that updates the tblCashHeader table, but as for the second bit I

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

    Re: Understanding Recordsets (97)

    The basic idea of the part that populates the tblCashDetails table is that you don't want to write out the code for all 31 items.

    Instead of referring to a field directly, in the form rst!FieldName, it is done indirectly, by assembling a string that contains the field name. For example, if the loop index i = 7, the variable strI holds "07". We can refer to the field ITEM_NUMBERS07 as rstSales("ITEM_NUMBERS" & strI). The & operator concatenates the fixed string "ITEM_NUMBERS" and the variable strI = "07".
    This makes it possible to loop through the 31 items, and write the values to a separate record in tblCashDetails for each item.

    To see how the code operates, you could single-step through a part of it (press F8). You can inspect the value of variables by hovering the mouse above them.

  12. #12
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding Recordsets (97)

    Oh I see it now, I wasn't sure how you were able to get the data in a horizontal line to populate a table vertically, but I think I understand now.

    Thank-you for you patience

    Regards
    Hayden

Posting Permissions

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