SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS Access: Combo boxes and variables

    I have an .mdb file and would like to have a form that the user uses to search and display a report from the choices they make from a coupel of combo boxes.
    It is a catalog of items with prices. At the moment I have set it up so that the report shows all items and their prices but can I narrow the list down by doing some fancy coding? I need two drop down lists with the category and brands and a command button that creates a query and then generates a report?
    I would also like to make this happen on an online shopping cart site as well.
    Any help would be appreciated.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are we talking about MS Access forms & reports or ASP forms?

    Since you also talk about online shopping cart I guess it's an ASP form

    Something like this:

    Get selected category & brand from Request (can be empty)
    Connect to DB
    Get recordset with all categories
    Print all categories as <option>s inside the first <select>
    (when printing value == selected category, add the "selected" attribute)
    Get recordset with all brands in selected category (or all)
    Print all brands as <option>s inside the second <select>
    (when printing value == selected brand, add the "selected" attribute)
    Get recordset with all items in selected brand, or category, or all
    Print items & prices

    On the javascript event onChange for the <select>s, post the form => the view is updated

    Do you need more detailed help on any step (connect to .mdb file, sending parameters, sql in general, how to make the correct option selected, autoupdate with javascript)?

  3. #3
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More help!!

    jofa,
    I do actually. It is getting frustrating but I know I can follow the code once I see it. My head is full of code at them moment.
    My main project at the moment is to get my shopping cart on a web site up and happening. I have a number of Brands that supply a number of different categorys. eg. Picasso supply several different kinds of spearguns. As do Omer, and Riffe. They also make different masks etc.
    I would like the page to have two select lists. One with Brands and one with Category. The customer makes their choice, they could also choose "ALL", clicks on a Search button and below in a table the products are listed with Description, and Price. I have four tables in my Access file. tblBrand, tblSupplier, tblCategory and tblInventory.
    If you could send me some code that does the trick, it would save me bucket loads of time and I might be able to sleep.
    Thanks....

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I misunderstood you in my first reply (thought that brand was a subcategory to category)

    Let's say you have these tables:
    ** tblSupplier
    supplier_id
    supplier
    ** tblCategory
    category_id
    category
    ** tblBrand
    brand_id
    brand
    supplier_id (for each brand there's only one supplier)
    ** tblInventory
    inventory_id
    product
    price
    (maybe columns for image, description etc too)
    category_id
    brand_id

    You will also need these tables:
    ** tblCustomer
    customer_id
    customer
    (and columns for username, password, address, phone etc...)
    ** tblCart
    cart_id
    customer_id (who owns this cart?)
    ** tblCartItem
    cartitem_id
    cart_id (
    inventory_id (which product?)
    price (the price at the time the product was added to the cart)
    amount (how many?)

    And you also need tables for order and order items, they will be almost identical to cart and cart items
    Maybe you already have these tables, just didn't mention them because they are not directly involved in the question "how to view inventory"?

    In the page, let's call it viewinventory.asp , the first thing you do is extract the id for brand and category (if present) - the value 0 indicates "all"
    <%
    selected_brand_id = Request("brand")
    selected_category_id = Request("category")
    %>

    I think you have the answer to the "how to fill select lists" in this other thread

    The sql query for the inventory display:
    Code:
    <%
    where = ""
    If selected_brand_id <> 0 Then
      where = "i.brand_id = " & selected_brand_id
    End If
    If selected_category_id <> 0 Then
      If where = "" Then
        where = "i.category_id = " & selected_category_id
      Else
        where = where & " and i.category_id = " & selected_category_id
      End If
    End If
    If where <> "" Then
      where = "where " & where
    End If
    sql = "select i.inventory_id, i.product, i.price " & _
      "from tblInventory i " & _
      "inner join tblCategory c on i.category_id = c.category_id " & _
      "inner join tblBrand b on i.brand_id = b.brand_id " & _ 
      where & " " & _
      "order by c.category, b.brand, i.product "
    Set con = Server.CreateObject("ADODB.Connection")
    con.Open [your connection string]
    Set products = con.Execute(sql)
    If products.EOF Then
    %>
    No products found
    <%
    Else
    %>
    <table>
    <%
      Do Until products.EOF
        inventory_id = products(0)
        product = products(1)
        price = products(2)
    %>
      <tr>
        <td><a href="addtocart.asp?inventory=<%=inventory_id%>">Buy!</a></td>
        <td><%=product%></td>
        <td><%=price%></td>
      </tr>
    <%
        products.MoveNext
      Loop
    %>
    </table>
    <%
    End If
    Set products = Nothing
    Set con = Nothing
    %>
    Last edited by jofa; Sep 3, 2002 at 08:12.

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmmm... now I read your question again, was the big problem for you how to make the select lists work, not the display of search results?
    Should I post an example of a search form?
    Or should I continue, explaining the page addtocart.asp?
    Or should I write an article "Build your own shopping web site with ASP and Access"?

  6. #6
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could if you and feel up to the challenge write the whole shopping cart thing...only if you have the time and energy that is. I appreciate you time already.
    I sometimes lose grip of my ideas sometimes and how complicated a shopping cart can be. I see I will need to re-build my database and add some more things to my tables. I will work on that tonight. Do you think I should get a complete working model in Access first or would that be a waste of time? At present I view the database to check prices grouped by Brand. I have made separate reports for each Brand. This way I can scroll down the list to the category and read off the RetailPrice. So the project has the Access database with its queries and reports for internal administration purposes and then the same database inventory list, not necessarily the queries and reports will reside at my server for the web page/shopping cart.
    I will check through your code you have posted and start building again. I will no doubt have more questions for you soon. If you end up building a complete shopping cart with my particular features that would be even better. We will see which comes first.....
    Check out this site to give you and idea of the kind of thing I am trying to achieve. http://www.legendary.com.au/store/products.asp
    Thanks again...

  7. #7
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "feel up to the challenge"? - yes
    "have the time"? - no

    I don't think a complete working model in Access (with forms and reports) is necessary, the important things are tables and relationships.
    For administration purposes you will of course build asp pages

    Things to consider (more to come, certainly):
    Can a product belong to more than one category? (e.g. a book can belong to both history and computer science if it's about Charles Babbage)
    Wouldn't it be nice with some kind of tree view for the categories?
    And a "quantity" text field next to the "buy" button, instead of the default "add 1 to cart"?
    Is the unit price on an order item the current price from products or the price when the product was added to the cart, or the price when the order was confirmed?
    How do you handle integer/double quantities? (e.g. you cannot buy 0.5 book, but 0.5 Kilo of apples is OK)
    Are the shoppers allowed to start adding cart items without logging in? Should the shopping cart id be saved to a cookie then?

  8. #8
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay a few things.... I do not have any sub-categories but a Supplier can supply more than one Brand. There is only one of my Suppliers' has this unique property. They all can supply, of course items from the same Category. eg Publisher X can supply a range of books, a range of magazines and a range of newspapers. Publisher Y might supply only books and magazines. So my visitor might like to see the prices of ALL the books from Publisher Y, or maybe ALL the newspapaers from Publisher Z..... Maybe even ALL the prices of books from ALL the Publishers.

    So if I have an HTML page with a link, called "GO TO CATALOG", to a new page that has the Select lists, is this page written in ASP? or can it be a HTML file with some VBScript of Javascipt to get the variables from the Select options? When you send me some code snipet, I don't know where it should fit or go into which page so can you explain further where to insert the code between the <% and %> tags? I figure the page that generates the results needs to be an .ASP file?

    The question of whether cookies are going to be involved, I didn't really figure on this feature but I think it is handy. I understand why I need to include CustomerID and CustomerName etc in a tblCustomer table.

    With all these features, do you think I can add them on later. I would like to get a basic working catalog of item with prices up and running and then later down the track tweek it a bit to suit me needs. What if I leave out the "Add to shopping cart" feature and just let cusomers check prices and email me back with questions and orders?

  9. #9
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do not have any sub-categories
    Not now, but maybe in the future?
    =>Design the category table with this possibilty in mind

    ...new page that has the Select lists, is this page written in ASP? or can it be a HTML file
    If you want to fill the select lists with options from the database: ASP

    I figure the page that generates the results needs to be an .ASP file?
    Yes, and the search form and the results table can be the same ASP page (if form submitted then show results, else show search form)

    With all these features, do you think I can add them on later.
    Of course. First build the "view products", then add the shopping cart stuff later

    I will be back with an example of the search form

  10. #10
    SitePoint Member
    Join Date
    Sep 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I am not really making any head way. That's frustrating. I have gone back to my copy of UltraDev 4 and grabbed a library book to help. I am sure the answer is in there somewhere. I can seem to get the live data feature to work. Anyone familiar with this? I get this message that says something about no application server mapping to the right directory.
    Bugger.....


Bookmarks

Posting Permissions

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