Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Def Question (XP)

    Hi All,

    i aint used Access for a while; but

    i hav a table with two columns;

    Column 1 has Product Code and Column Two has the Price the product was sold for.....

    What i want is a VB function / Sub, that will make an Array of the Price Values, based on the value for the Product Code.....

    i.e.

    Sub createValues
    ProductCode = AB90



    Sub

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

    Re: Table Def Question (XP)

    Do you want to create an array of Prices for one Product Code to be specified as argument to the function/procedure? If not, what would you like to get?

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Def Question (XP)

    i've made a form, that has a combo box of the product codes, i want them to be able to pick a product code and in it list the prices we've sold it for (in text boxes below the combo box)......... if i put it into an array i can do lots of stuff with it...........

    cheers 4 the quick reply m8

    Phil

    p.s.

    that was a yes, i want to create an array for one product code.... :-)

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

    Re: Table Def Question (XP)

    Wouldn't it be easier to display the prices in a list box or in a subform? That way, you'd let Access do the work for you.

  5. #5
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Def Question (XP)

    Probably, but wud, but i need them to edit all of the prices..... :- )

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

    Re: Table Def Question (XP)

    A list box is not editable, but if you display the prices in a continuous (sub)form, they ought to be editable.

    You could create a query based on your table, with a where-condition (criteria) on the Product Code field: Forms!frmMyForm!cbxMyCombo, where frmMyForm is the name of the form, and cbxMyCombo is the name of the combo box. This query will return the prices for the product selected in the combo box. You can use this query as record source for a continuous form. You'd have to requery the form when the user selects another item in the combo box.

  7. #7
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Def Question (XP)

    i want 2 be less dependent on Access & use VBA............

    do u know the VBA code to loop through a table and print out the results, i think it's using a for statement with the table def's..............


    Cheers
    P

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

    Re: Table Def Question (XP)

    Please use standard English. This is not a chat session, and there are many Loungers whose native language is not English. Thanks! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I don't understand what you want. First, you wrote that the user should be able to edit the prices, now you want to print out results. And why do you want to be less dependent on Access?

  9. #9
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Def Question (XP)

    I just prefer VBA too sub forms etc.... + i meant print out (put) the array results into text boxes on a form so the user can edit them...........

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

    Re: Table Def Question (XP)

    Here you go. The following function GetArray has five arguments:

    - TableName is the name of the table to get data from.
    - FieldIn is the name of the field used to filter the records.
    - FieldType specifies what kind of field FieldIn is; you can use "Text" (text or memo), "Number" (byte, integer, long integer, single, double, currency, boolean) and "Date" (date/time).
    - FieldValue is the value to filter on.
    - FieldOut is the name of the field whose values should be returned.

    The function returns Null if no records matched the filter, or an array containing the values.

    Example use:

    Dim arrValues
    arrValues = GetArray("tblSomething", "Product Code","Text", Forms!frmTest!cbxSelect, "Price")
    If Not IsNull(arrValues) Then
    ...
    End If

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function GetArray _
    (TableName As String, FieldIn As String, _
    FieldType As String, FieldValue As Variant, _
    FieldOut As String)
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim arr()
    Dim i As Long
    Dim strValue As String

    On Error GoTo ErrHandler
    GetArray = Null

    Select Case FieldType
    Case "Text"
    strValue = Chr(34) & FieldValue & Chr(34)
    Case "Number"
    strValue = FieldValue
    Case "Date"
    strValue = "#" & Format(FieldValue, "mm/dd/yyyy") & "#"
    Case Else
    MsgBox "Incorrect field type specified", vbExclamation
    GoTo ExitHandler
    End Select

    Set cnn = CurrentProject.Connection
    rst.Open "SELECT * FROM [" & TableName & "] WHERE [" & _
    FieldIn & "] = " & strValue, cnn, adOpenKeyset, adLockOptimistic

    ReDim arr(1 To rst.RecordCount)
    For i = 1 To rst.RecordCount
    arr(i) = rst.Fields(FieldOut)
    rst.MoveNext
    Next i

    GetArray = arr

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Table Def Question (XP)

    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> for what it's worth - it's a pretty complex job in VBA to try to provide the same functionallity the you get with subforms, and while the subform construct is really solid, VBA is much more likely to be fragile, and will likely require a rewrite in latter version of Access.
    Wendell

  12. #12
    New Lounger
    Join Date
    May 2003
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Def Question (XP)

    Cheers m8, ur a star and a credit to the forum...............

    phil

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Def Question (XP)

    Please drop the chat talk. I've never "chatted" in my (long) life and don't intend to start. It makes it hard to follow the thread when I have to mentally translate. <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>
    Charlotte

Posting Permissions

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