Access Guru's! Check in here.

Discussion in 'OT Technology' started by DatacomGuy, Nov 10, 2004.

  1. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    Quick question.

    I have a form with a subform. The main form is designed for a quotation work sheet. The subform has fields with qty, item, desc, item cost, and extended cost. The item field is a drop down box that pulls from a product table.

    We want to clean this up and make it even easier. Say the customer has many different types of products (THOUSANDS) and they want to group each product type into, well, a type. e.g., Boxes table will contain blue, purple, green, brown, etc (just an example)

    Is it possible to have a drop-down box that lists the available product types (each type has its own table); then once a product type is selected, the item field drop-down box will pull from the selected type's table... Follow?

    I know it's doable. Just.. How? :rofl:
     
  2. Rob

    Rob OT Supporter

    Joined:
    Jul 6, 2002
    Messages:
    88,612
    Likes Received:
    36
    Location:
    Atlanta, GA
    Access is good.....................for cataloging your DVD collection......:fawk:
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Sure, you just need to do an on event update on the first combo box. The event then changes the 2nd combo box's control source to whatever you want. You can even refer to the selection in the 1st combo box in the query of the 2nd combo box so that the query limits itself to the 1st's selection. You may still need to requery the 2nd box on the change however, but atleast you don't have to change the query on the fly

    For example in the 2nd query, you could limit the selection (in the criteria row) to:

    forms!quote!subformcontrolname.form!1stcombobox
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    :mamoru: :rofl:
     
  5. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    :rofl:

    Yeah, something like what Joe said. It's a bitch and a half to implement though, Access fucking sucks for any kind of programming.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    :dunno: Pretty simple if you ask me.
     
  7. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    It's beyond my knowledge base, unfortunately.. but I'll give it a shot.

    And Rob. :fawk::rofl:
     
  8. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    You must know something I don't. There seems to be no way to run a query and get the output in VBA :dunno:
     
  9. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    Yes, please educate.
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I don't really know what you are asking to be honest - give me an example.
     
  11. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Say I have a table and I want to run some query on that table and read the results into a string in VBA so I can use it somewhere in my program. How would I do this?
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well the quick and dirty way is to use dlookup. This will return one value, from any field from any table or query. The other way would be to open the query up via a recordset and read back whatever you wanted just like you would a table. I guess you could even write a generic function that would read any table/query and throw the results into an array as well. Lots of options and it all really depends on what kind of data, what you want to do with it, and how much data you want to return.

    Basically select querys = tables in access and pretty much anything you do with a table, you can do with a query.
     
    Last edited: Nov 11, 2004
  13. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    :coold: I could never figure out how to do it, although I think in the end I did figure out how to get values from single cells.
     
  14. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    well VBA supports fully ADO/DAO libraries. With this being the case, your possibilities are endless as far as querying data goes. ADO's recordset object supports .GetString() and .GetRows() [i believe.... it's been awhile] the latter will fill your array for you, and the getString basically just takes all teh data in a recordset, and tab delimits it for you. :)
     
  15. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well you would put something like this:

    set db = currentdb

    set recset = db.openrecordset("QueryInQuestion",dbopendynaset)

    with recset

    while not .eof

    string1(z) = !field1
    string2(z) = !field2
    z=z+1
    .movenext

    wend

    You get the idea...
     
  16. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    the ADO way:

    Code:
    Function GetQueryString(sql As String)
        Dim rs As ADODB.Recordset
        
        rs.Open sql, yourConnection
        GetQueryString = rs.GetString
    End Function
    
    
    psudo code obviously
     
  17. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Never tried that way before, but then I've never wanted to simply return the whole recordset into a string before either.
     
  18. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    I posted my same question on Microsoft's forums. Here is a response I got..

    This sound right?
     
  19. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yes, but if you're clever enough in your 2nd query's design, you can refer to the first combo box as part of the criteria. THen you don't have to rewrite your rowsource, but you may need to requery it so it forces it.
     
  20. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    I think I've seen that before but could never get it to work :embd:
     
  21. DatacomGuy

    DatacomGuy is moving to Canada

    Joined:
    Oct 14, 2002
    Messages:
    16,546
    Likes Received:
    0
    Location:
    Tampa, FL
    OK. I understand the underlying theory.. but I get lost on the actual verbage for the query to work properly.

    Care to take some time and give me a hand?

    I'm actually at the part of the db that I need to get this part done. I've been avoiding it for a while so I could do more research.. Now I need to do it. I've made the combo box, but I've set no event parameters yet. Considering I know jack about VB or SQL for that matter, I get lost easy.
     

Share This Page