Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2002
    Posts
    117

    Unanswered: DLookup Question

    I have a table called tblPaymentsMyLineToo.
    I have a form called frmPaymentsMyLineToo. I have the table mapped to the form. On the form I have a combo box titled cBoxSupplier. When I click on the supplier cbox it list the vendors....when the vendor is selected I want the servicecharge box(another box on the form) to be filled in as well with the appropriate servicecharge fee.

    I have tried some different OnClick events on the combo box and some other things....I guess my syntax may be wrong.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: DLookup Question

    Originally posted by jmonroe
    I have a table called tblPaymentsMyLineToo.
    I have a form called frmPaymentsMyLineToo. I have the table mapped to the form. On the form I have a combo box titled cBoxSupplier. When I click on the supplier cbox it list the vendors....when the vendor is selected I want the servicecharge box(another box on the form) to be filled in as well with the appropriate servicecharge fee.

    I have tried some different OnClick events on the combo box and some other things....I guess my syntax may be wrong.
    Try using the After_Update event of your control to reconfig the RowSource and requery to dependent controls ...

  3. #3
    Join Date
    Dec 2002
    Posts
    117

    After_Update

    This is what I have in my Sub

    Private Sub cBoxSupplier_AfterUpdate ()

    Me.Filter = "[ServiceCharge] = '" & Me.cboxSupplier & "'"
    Me.FilterOn = True
    Me.Requery

    End Sub

    This gives me error in rutime. Application will be shut down.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: After_Update

    Originally posted by jmonroe
    This is what I have in my Sub

    Private Sub cBoxSupplier_AfterUpdate ()

    Me.Filter = "[ServiceCharge] = '" & Me.cboxSupplier & "'"
    Me.FilterOn = True
    Me.Requery

    End Sub

    This gives me error in rutime. Application will be shut down.
    You're not testing that a value has been selected ...

    Questions:

    What is the ServiceCharge type? What is it?
    What is in the 1st column of the supplier combobox?
    Why are you setting the filter to the main form? I thought you were populating another combobox ...

  5. #5
    Join Date
    Dec 2002
    Posts
    117

    Okay

    ServiceCharge is a number that gets put in the tblMyLineTooSuppliers table.....so when a supplier gets selected, I want the service charge to show up in a box at the bottom of the form.

  6. #6
    Join Date
    Dec 2002
    Posts
    117

    Any Suggestions

    Does anyone else have any suggestions?

    I have also tried the following code.

    OnChange event

    ServiceCharge = DLookup("ServiceCharge", "tblMyLineTooSuppliers", "[SupplierName] =" & Me.cboxSupplier)

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Okay

    Originally posted by jmonroe
    ServiceCharge is a number that gets put in the tblMyLineTooSuppliers table.....so when a supplier gets selected, I want the service charge to show up in a box at the bottom of the form.
    Box at the bottom of the form? TextBox? ComboBox?

    What table do you pull the Service Charge & fee from?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    need more info.

    what's the full spec of the combo:
    column count = ?
    column widths = ?; ?; ?; ?
    bound column = ?
    what are the data types of each column in the column count

    what is the data type of SupplierName as in your code:
    "[SupplierName] =" & Me.cboxSupplier
    ...or do i mean ServiceCharge as in your code:
    "[ServiceCharge] = '" & Me.cboxSupplier & "'"
    and which of the two are you trying to filter/dlookup/whatever on

    finally, i imagine you have at least two tables: tblPaymentsMyLineToo plus some other table for your supplier & service charge etc. Your DLOOKUP() example is pointing back to tblPaymentsMyLineToo ...which doesn't make obvious sense to me. what is the structure of this second (hoped for) table.

    izy


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Posts
    117

    Here it is

    Two tables....

    tblMyLineTooSuppliers

    SupplierID
    SupplierName
    ServiceCharge

    tblPaymentsMyLineToo

    PaymentID
    MyLineTooSupplierName
    Date
    blah blah
    blah blah
    ServiceCharge

    One Form

    frmPaymentsMyLineToo

    Have Combobox titled cboxSupplier that list the supplier names from the tblMyLineTooSuppliers table.

    Have a text box called ServiceCharge that I want to autofill with the ServiceCharge for the selected Supplier.

    cboxSupplier Stats

    Column Count = 2
    Column Width = 0";1"
    Bound Column = 2

    Data Types

    the SupplierName naturally is text and the ServiceCharge is numeric.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    super: your combo already "knows" the service charge.


    how about:

    private sub cBoxSupplier_AfterUpdate()
    ServiceCharge.value = cBoxSupplier.column(0)
    end sub

    WARNING - i think the combo column index is zero based, but i didnt check - if the above craps out, try .column(1)

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Here it is

    Originally posted by jmonroe
    Two tables....

    tblMyLineTooSuppliers

    SupplierID
    SupplierName
    ServiceCharge

    tblPaymentsMyLineToo

    PaymentID
    MyLineTooSupplierName
    Date
    blah blah
    blah blah
    ServiceCharge

    One Form

    frmPaymentsMyLineToo

    Have Combobox titled cboxSupplier that list the supplier names from the tblMyLineTooSuppliers table.

    Have a text box called ServiceCharge that I want to autofill with the ServiceCharge for the selected Supplier.

    cboxSupplier Stats

    Column Count = 2
    Column Width = 0";1"
    Bound Column = 2

    Data Types

    the SupplierName naturally is text and the ServiceCharge is numeric.
    Ok ...

    ServiceCharge.Value=cboxSupplier.Column(2,cboxSupp lier.ListIndex)

    Done.

  12. #12
    Join Date
    Dec 2002
    Posts
    117

    Got it

    M Owens your way works beautfully.

    Thank you guys for your help.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    why?

    M. Owen

    since the combo has two columns and it is used for selecting supplier name and the bound column is 2 there is no typo in jmonroe's post: everything is mutually consistent.

    the combo columns must be (0)ServiceCharge and (1)SupplierName (I checked: it's 0-based)

    so how can .column(2, OptionalRowAnythingAndNotNecessaryInThisContext) get to the ServiceCharge which is in .column(0) ?

    i'm confused!

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Posts
    117

    What you don't know

    See Izyrider.....I have a qry on the combo that pulls in the SupplierID, then SupplierName, then ServiceCharge.....so, to clarify, SupplierName is Column 1 and ServiceCharge is Column 2.

    Hope this helps.

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    gulp!

    i didn't (still don't) see mention of a query anywhere in this post.
    you said:
    Column Count = 2
    Column Width = 0";1"
    Bound Column = 2
    ...so there is no .column(2) in your combo's zero-based world

    maybe it's to do with filters - i never use the things so maybe there is something obvious that i missed.

    anyhow - you have a solution, which is what it's all about

    happy and confused, izy
    currently using SS 2008R2

Posting Permissions

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