Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Using Recordsets in Access

    I have an unbound form, I have been told by serveral people I need to use recordsets to get it to do what I want it to. I am still confused what I am supposed to be doing.

    I have a 'find' form that has a series of list boxes that allows my salesmen to select a list of parts. I need that list to go into a temp table until the sales order is saved. Or something like that. Anyway about it I need a sales order. I have the tables I need in place. I just need to get the forms made up and get that find form where it allows you to open the sales order from it after making your selection of parts.

    Thanks,
    JS

    P.S. I know nothing about recordsets so please start at the beginning
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    So far, nothing you have said indicates that you NEED to use recordsets.

    But your explanation seems a bit fuzzy.

    Explain again what you want to do.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay the reason I am fuzzy on why I need the recordsets is because I dont know exactly why I need them. I guess I just need some information about them.

    I will explain what I am trying to do and see what you have to say.

    I have a form. Salesmen in my company select parts from a listbox. It shows up another list box with a value list including "Series" "Class" "Both". When they select one of those it shows either one box for either series or class or both boxes for the both. The user then selects from those boxes and I have a listbox of parts show up. I want salesmen to be able to select parts from that list, and somehow store them till they are ready to click a 'create sales order' button. I was thinking a temp table would be a good way to do it. Because then when they hit the Create Sales Order button it would then just query that table to get the information needed to populate my sales order form. and then have a save/print button at the bottom of my sales order form, that saves the complete sales order to another table. Or something like that. I know how to create a sales order tables and forms and stuff the thing I am having trouble with is the search function being friendly. It needs to be friendly because I took what used to be like 289 tools and turned it into like 1000 part numbers. No one quite has the hang of the part numbers so creating a sales order would be hard without being able to search the data. Hope this makes sense.


    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    A good place to start is here:
    http://msdn.microsoft.com/library/de...lrecordset.asp

    To provide a short intro, a "Recordset" (I'll call it rs from now on) is an object, which you assign to an object variable, that mimics a query. It has all the properties and methods you would expect to find in a query.

    A good next step is to go into a code window and press F2 (to bring up the Object Browser). Under the "Classes" heading, scroll down to Recordset and click on it. In the "Members" pane, you will see all of the properties, actions and methods available for Recordsets.

    By the way, if this is your first time in the Object Browser, it's pretty useful - especially when you start using other application libraries on you application (I use Excel extensively from inside Access, for example).

    Basic Code for you:

    Dim rsData as Recordset ' you may need to be explicit in newer versions
    Dim intKey as Integer

    intKey = Me.txtSalesman

    ' Create the object by assigning all the data in the table that has a matching key value
    Set rsData = CurrentDB.OpenRecordset("Select * From tblTemp Where tmpKey = " & intKey & " Order By [PartID]")

    ' See how many parts there are:
    If rsData.RecordCount > 10 Then MsgBox "You have a lot of parts!",vbOkOnly,"Message from server"

    ' Add a new part
    rsData.AddNew
    rsData("tmpKey") = intKey ' this is "explicitly" telling it what field
    rsData(2) = Me.lstParts ' this is "Implicitly" telling it what field (index starts at 0, so 2 is the 3rd field)
    rsData.Update

    ' Clear the memory for this object
    Set rsData = Nothing

    This just tips the iceberg. You can have multiple recordsets, even from the same data source (just like a query, after you open a rs, you won't see changes made by OTHER operations unless you do a Requery on it, which is one of the Actions).

    So, for your button that adds a selected item to a list, here's the steps:
    1) Open a rs to your temp table - make the opening SQL pull only records that match the current order and the selected part.
    2) Check the recordcount.
    If it is 0, then this part has not been added yet, in which case you do an AddNew
    If it is >0 then this part has already been added - use the Edit method and increase the quantity by 1.
    3) In either case, remember to do the Update when done
    4) Close the recordset
    5) Requery the list box.

    This should keep you busy for a while.

    Have Fun!!

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Re: Using Recordsets in Access

    Originally posted by JSThePatriot
    I have an unbound form, I have been told by serveral people I need to use recordsets to get it to do what I want it to. I am still confused what I am supposed to be doing.

    I have a 'find' form that has a series of list boxes that allows my salesmen to select a list of parts. I need that list to go into a temp table until the sales order is saved. Or something like that. Anyway about it I need a sales order. I have the tables I need in place. I just need to get the forms made up and get that find form where it allows you to open the sales order from it after making your selection of parts.

    Thanks,
    JS

    P.S. I know nothing about recordsets so please start at the beginning
    I don't know exactly why you need them yet, either, but here's a little info on them with a small example:

    A recordset will hold the results of a query for you, in a kind of "list" of records. It's almost like a virtual table. let's say we have a table named tblCar, which has fields of fldColor, fldMake, fldVIN, fldModel, fldYear and fldOwner. If you want to select all the information about blue ford cars, you'd put something like the following:
    Code:
    Dim db As Database
    Dim rs As Recordset
    
    set db = Currentdb
    set rs = db.OpenRecordset("SELECT * FROM tblCar WHERE fldColor='BLUE' AND fldMake='Ford'")
    That would put all the records (just like they look in your database table) of blue ford cars into a recordset. The recordset automatically defaults to the first record. So if you wanted to maybe add the VIN of every car to a listbox called lstVIN, you'd put:
    Code:
       While rs.EOF=False  'while there are still more records
          lstVIN.AddItem(rs.Fields("fldVIN"))
          rs.MoveNext         'go to the next record
       End While
    That will cycle through the recordset ("virtual table") and put the VIN of every car in the listbox.
    A couple notes: the .Fields("fldName") is how you access the info for the current record. so if you had wanted to put the VIN in one listbox and the owner's name in another, you'd simply add a line that said
    lstOwner.AddItem(rst.Fields("fldOwner")) below the one adding the VIN and before you move to the next record in the recordset.

    I'm sure i've probably restated some stuff, and recordsets are much more powerful than i've made them seem, but this kind of seems along the lines of what you're trying to do. good luck.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    From an architectural standpoint, try and simplify the whole process.

    In other words, don't set up two, separate ways to deal with a system, when you could just set up one way.

    From what I am gathering, you have a need for a pre-sales order, and then a sales order.

    The business world is filled with like, two-step paradigms.

    A quote, and a sales order.

    An engineering change proposal, and a change order.

    An estimate, and work order.

    You have the same thing, I think--a pre-sales order, and a sales order.

    Whatever the terminolgy, the traditional way of handling this is with a single system which simply changes a flag, or creates a sales number, in order to move something from the initial stage to the final stage.

    So, what I would do, is create a single-level order system, that would allow the user to drill-down to the selection he/she desires, and save that pre-sales order just like you sould save the actual sales order.

    Am I responding in the correct direction ?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Yes and No

    Yes PracticalProgram and at the same time No (I think). I am a bit confused on how I am going to get this to work.

    I dont really need the Pre-Sales Order though it would be nice. I do need the Sales Order.

    I am fixing to create a Sales Order table and a Sales Order Details table with a form that allows me to create sales orders.

    My salesmen dont know what part number = what part so I wanted them to be able to search for parts and from there create a sales order from a list of parts they selected.

    I have 1000 part numbers I had to manually create. I need the sales guys to be able to scroll through the list thing that I made for them and select what they want. I am stuck at the point of them selecting.

    :-/

    JS

    Let me know if you need more information. (my db is 85mb) I would be willing to let you see it if you needed.


    P.S. I have a current search form that uses a bunch of paremeter queries that does the same thing my listbox search form does with out all the queries and forms. Also another bad thing about my Previous way of searching is if the salesmen wanted to use a part they found, they would have to write the part number down and then open the sales order and put it in there. I want the process more friendly. I could do it no problem but its for pretty much computer illiterate ppl.

    JS
    Last edited by JSThePatriot; 04-15-04 at 11:58.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    JS,

    I think what Ken is leaning too is that in the other thread, there was discussion of using a temp table becasue you didn't want to store all the information between "starting the order" and "ready to committ the order".

    I responeded with a "why?" becasue you are in no danger of overwhelming Jet or Access - save all the data.

    Ken's idea of adding a FLAG (typically a Yes/No field) is good - you can store all the select parts in the sub-table we discussed, and then flag the order when you commit the it.

    If you refer back to the other thread with my "2 table" idea, the addition of the flag on the primary table could be used to "lock" that order - once a SalesOrder is issued, change the flag to Yes and setup your form so that once the flag is set to yes, it can no longer be edited (the saleman can view the order, but would have to start a new one in that case).

    You could even add a second flag to "lock" the order while a user has it open, to prevent 2 users from working with the same order at the same time.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    thanks to the "miracle" of windows (cough, cough, gag) a good presentation allows even "computer illeterate" people to use you application.

Posting Permissions

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