Access VBA Question

Discussion in 'OT Technology' started by Penguin Man, Jul 21, 2004.

  1. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Anyone know how I can run a SELECT SQL query in VBA and get the resulting row into a string variable? I've tried just using variable = docmd.runsql(statement), but that produces errors. There must be a way to do it that I'm just not seeing (probably because I hate Access).

    Or, to make things simpler, is there any way (in VBA) to access the value in a column of a combo box other than the bound column? combobox.value just returns the value in the bound column, but I need the value from a different column.

    TIA all. I hate access.
     
  2. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    OK, so I figured out that I can access the value in a column of the combo box using comboboxname.column(number), but I can't do comboboxname.column(number) = value. How can I set this value?
     
  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
    You can't change the information like that. You either have to rewrite the string in .rowsource if it's a "value list" or change the data that you based the combo box on (ie the table or query).

    What are you ultimately trying to do?
     
  4. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    The idea is that there are three columns in a table, displayed in three combo boxes. In either of the first two combo boxes (with table column data in them), the user can select a value and the other two combo boxes will update with the information from the same row.

    For example, if this is my table:
    Code:
    ID          Description         Units
    00.001      Something           metres
    00.002      Something Else      metres squared
    
    If the user selects 00.002 in box 1, "Something Else" will appear in box 2 and "metres squared" will appear in box 3. If the user selects 00.001 in box 1, "Something" will appear in box 2 and "metres" will appear in box three.

    If there's an easier way to do this, let me know. All that matters is that the user can type in or select a piece of information from the ID column or the Description column and the proper values for the other two will pop up.
     
    Last edited: Jul 22, 2004
  5. 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
    THere's a number of ways to do it, and if it's just for display purposes (and it should be since you really shouldn't be copying data that is one table into another - it should be linked), this is the easiest:

    Let's say you had an order table with a field called customerID. Another Table called Customers and in that table there's fields called CustomerID, LastName, FirstName, and PhoneNumber.

    Now, let's say you build a form off of the order table. When you get to the customer field, what you want to do is select from the list of customers in your customer table. So, you make that field a combo box that is based on the customer table. You then hide the first column (using a width of 0";1"), change the number of columns to 2. It will now allow you to select the customer by the last name (if you want, you could change the number of columns to 3 and the width to 0;1;1 and show the last name as well).

    Now on the order screen you want to pull up ALL of his information - probably just for show since you don't really want to change the customer data here (although you could I guess). All you have to do to get this to work is change the form datasource from the table "orders", to a query that pulls up both the orders and customer info. Join the two tables with the customerID field and pull in all of the fields except for customerID in the customer table. Now just put the customer fields in the form. WHen you change the customerID field (combo box), their associated info is filled in automatically into the other customer fields.
     
  6. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Hm, I'm not sure that I was clear enough about what I'm doing. All three of the fields I'm displaying are in the same table. After the user selects these things and closes the form, the things are copied into a different table (which is already working, this is a new feature I'm adding). I have the two tables linked already, what I need to do is get the data in two of the fields to change to match the one that the user changes. It seems like it should be really simple, which is why it makes me so mad :mad:
     
  7. 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, all I can say is that if you're copying data from one table to another, your design is not correct. The minute you start duplicating data, you've fucked up somewhere.

    Anyway, to do what you're doing, I'd probably made the dataset for the form empty. Make three combo boxes all based on the same table but each box would have a different bound field. Then create an OnChange event for each combo box and set the other two combo boxes with the data in the other two columns of the current field:

    Combo1 Onchange event:

    combo2 = combo1.column(1)
    combo3 = combo1.column(2)

    Combo2 Onchange event:

    combo1 = combo1.column(1)
    combo3 = combo1.column(2)

    Combo3 Onchange event:

    combo1 = combo1.column(1)
    combo2 = combo1.column(2)

    This assumes you make the first column the bound column for each combo box
     
  8. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Jesus, it's always the stupid little thing that you can't think of isn't it. combox = combox.column(x) works perfectly, it's just something that I wouldn't think of, since in any other development environment you can't assign a value to a combo box :doh:

    About the design, I realize it's not the best, but I didn't design the program (it was written a few years ago by a guy who didn't speak english very well and spelled description "descreption" every time he typed it in). I updated it some last summer, and now I'm doing further rennovations. The table that the data is being copied from contains a list of stock descriptions, and isn't editable by the user (I plugged in all 1094 rows and it's obscured from the users). The two tables are actually linked (on all three fields, ID description units), so I'm not actually copying persay, just allowing the user to choose an item to be linked.

    Thanks for the help :)
     

Share This Page