very new user, question about DLOOKUP and Access in general

[kcphila]

New user, going from basically data entry with Access to some more complicated, but I'm pretty sure still quite basic, tasks. I've got something I think I need DLOOKUP to do, but i'm not sure.

What I need to do is to fill a column with the data that corresponds to the value in Wt Code, with the name of the Columns after weight code. So, if Wt Code is "2" the data I'd need would be the data from the 2 column. I thought I could do this with data keys, but I think I need to use DLookup. Is that the correct tool?

[Perceptus]

IN Theory, Not tested.

Public Function GetCustPrice(ByVal strCustomer As String)
On Error GoTo Err_GetCustPrice
    Dim inWtCode As Integer ' declare variable to store WT Code in
    intWtCode = DLookup("[Wt Code]", "[Main Table]","[Cust#]='" & strCustomer & "'") ' Get the WT Code and store in intWTCode
    ' Return the associated field with Wt Code, If there isn't one return 0
    GetCustPrice= Nz(DLookup("[" & intWtCode & "]", "[Main Table]", "[Cust#]='" & strCustomer & "'"), 0)
Safe_GetCustPrice:
    Exit Function
Err_GetCustPrice:
    Debug.Assert Err.Number <> 0
    Resume Safe_GetCustPrice
End Function

[ranman256]

use a query. the query would link to the price table and update all fields price/wt etc.
Dlookup is for a form to pull 1 bit of info.
If youre filling a table, use a query.

[June7]

Since there are only 6 cost fields, perhaps an expression in query will serve:

Choose([Wt Code], [1], [2], [3], [4], [5], [6])

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[kcphila]

An expression was how I've been trying to do it. So I should use CHOOSE. Thanks!

[June7]

This presumes the table with the 6 cost fields is included in the query, as shown in the image for Query3.

[kcphila]

use a query. the query would link to the price table and update all fields price/wt etc.
Dlookup is for a form to pull 1 bit of info.
If youre filling a table, use a query.

I did that, when I ran the query it came up with a prompt which asked me to "name the parameter". I'm not sure what that's asking me to do, but whatever I name the parameters is is what it returns in the column.

[kcphila]

This presumes the table with the 6 cost fields is included in the query, as shown in the image for Query3.

I did that, when I ran the query it came up with a prompt which asked me to "name the parameter". I'm not sure what that's asking me to do, but whatever I name the parameters is is what it returns in the column.

[June7]

The image showed field names of [1], [2], [3], [4], [5], [6]. How did those fields get created? Use correct field names in the Choose() function.

[kcphila]

I just realized my error. Sorry! It's working exactly how I need it too. Thanks!