Results 1 to 15 of 15

Thread: Filling Fields

  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Filling Fields

    Hi,

    Simple question, upon making a choice in a combobox, how do i display a field related to the one chosen in the form? In an order form, i want to choose the product through a combo box. How do i display the unit price once the product is chosen? Do i have to create an event to take care of this, or are there simpluier ways to do it?


    Thanks!!!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    Use DLookUp function in the AfterUpdate or OnChange event handler of your combo.

    Let's say the combo's bound column contains the Pat Numbers of the products, and you wish the Product Description to be appear in a text box, and Price in an other text box when you select a Part Number. Then use the following code:

    txtProductDesc=DLookUp("ProductDesc","ProductsTabl e","[PartNumber]='" & MyComboBox & "'")

    txtProductPrice=DLookUp("ProductPrice","ProductsTa ble","[PartNumber]='" & MyComboBox & "'")

    For more, check out 'DLookUp' in Help.

    BRegs,

    TBÁrpi

  3. #3
    Join Date
    Dec 2003
    Posts
    16
    You could base your combo on a query that returns the field(s) you need. Use the combo's format property to show or hide the field(s) displayed in the box. You can then reference the desired field(s) by using the column property of the box.

  4. #4
    Join Date
    Nov 2003
    Posts
    150
    Cool, thanks guys, this is exactly what i was looking for!

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    Hi,

    Where do i write the code you gave me? Is it in the properties of the combobox, or the properties of the fields i want to fill up?

    I'm also not sure what i should replace with my field names in the expressions.

    a little more detail pls??!!!!

    Thanks a lot!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    1. Open your form in design view.
    2. Click once on your combo box, and open the properties box.
    3. Click 'Events' tab in the properties box.
    4. Place the cursor onto AfterUpdate or OnChange and click the '...' button.
    (Note: AfterUpdate event occurs only if the value selected by the mouse. If you use the keyboard to select a value from the combo then AfterUpdate does not occur.)
    5. Select Code Editor.

    You will be taken to VBA Editor, and the header and the footer of the Sub is automatically created.

    Copy the code between the header and footer.

    '********************
    txtProductDesc=DLookUp("ProductDesc","ProductsTabl e","[PartNumber]='" & MyComboBox & "'")

    txtProductPrice=DLookUp("ProductPrice","ProductsTa ble","[PartNumber]='" & MyComboBox & "'")
    '********************

    where "txtProductDesc" is the name of the text box where the Product Description should appear, "txtProductPrice" is the name of the text box where the Product Price should appear, and "MyComboBox" is the name of your combo box.

    DlookUp function is to get the value of a particular field from a specified table.

    Parameters of DLookUp function:

    "ProductDesc" identifies the field whose value you want to return. Change this to the field name where the Product Description is stored.

    "ProductsTable" identifies the table or query where the field is. Change this to the table name where the Products are stored.

    "[PartNumber]='" & MyComboBox & "'" restricts the range of data on which the DLookUp function is performed. It's just like a WHERE clause of an SQL statement but without the WHERE keyword.

    For more, check out DLookUp topic in Help.

    However, you need to use IF..THEN...ELSE statement to determine if the value of the combo box is not null.

    BRegs,

    TBÁrpi
    Last edited by TBÁrpi; 12-10-03 at 18:59.

  7. #7
    Join Date
    Nov 2003
    Posts
    150

    Smile

    Wow, thanks a lot!!! This is a lot more than i expected!!!

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  8. #8
    Join Date
    Nov 2003
    Posts
    150
    hi,

    after inserting the code you gave me, and changing the names of the text boxes and field names, i get an error message :

    Run-Time Error '3464'

    Data type mismatch in criteria or expression

    Not sure how to fix this. I made sure all my field formats where set to General Number but this hasnt changed anything.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  9. #9
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    If your "PartNumber" field is a numeric field, then use

    "[PartNumber]=" & MyComboBox

    If your "PartNumber" field is a text field, then use

    "[PartNumber]='" & MyComboBox & "'"

    Because the expression needs the string to be enclosed between apostrophes.

    In the first case the expression used by DLookUp will be, for example:

    "[PartNumber]=123465789"

    In the second case the expression used by DLookUp will be, for example:

    "[PartNumber]='123465789'"

    Format property of a field doesn't make difference, you need to have same data types.

  10. #10
    Join Date
    Nov 2003
    Posts
    150
    I still cant get it to work. my field and table names are as follows:

    curUnitRetail ( currency )
    intProductID ( Autonumber )
    tblProductInfo ( Product Table )

    the lines of code that i'm trying to use are:

    Private Sub ProductID_AfterUpdate()

    curUnitRetail = DLookup("UnitRetail", "tblProductInfo", "[intProductID]=" & MyComboBox)

    End Sub

    When running this, i get Runtime Error 3075, Syntax error, missing operator in query expression [intProductID]=

    how can i fix this??

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  11. #11
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    Make sure the bound column of your combo is intProductID.

    What is the name of the field containing the data you want to be retrieved? Is it called "curUnitRetail"? then use this:

    Me.curUnitRetail = DLookup("curUnitRetail", "tblProductInfo", "[intProductID]=" & MyComboBox)

    How is your combo called? Is it named "MyComboBox"? If no, then replace MyComboBox with the name of your combo box on your form.

    However, if it still does not work, post your database, (but COMPRESS and ZIP it before posting), and I'll have a look.

  12. #12
    Join Date
    Nov 2003
    Posts
    150
    Hi,

    Now i'm getting a Compile Error....

    here is my db. Take note that a lot of things arent the way i want them to be. It is simply a test bed before i implement the real thing.

    Thanks a lot for your help!
    Attached Files Attached Files
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  13. #13
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    The problem was that the text box you wanted to be populated automatically, when an element is selected from the combo, is called "UnitRetail" and not "curUnitRetail".

    Now it's working OK.

    In your earlier post you told it's called "curUnitRetail":

    I still cant get it to work. my field and table names are as follows:

    curUnitRetail ( currency )
    intProductID ( Autonumber )
    tblProductInfo ( Product Table )

    the lines of code that i'm trying to use are:

    Private Sub ProductID_AfterUpdate()

    curUnitRetail = DLookup("UnitRetail", "tblProductInfo", "[intProductID]=" & MyComboBox)

    End Sub

    When running this, i get Runtime Error 3075, Syntax error, missing operator in query expression [intProductID]=

    how can i fix this??

    thanks!


    __________________
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  14. #14
    Join Date
    Dec 2003
    Location
    Budapest, HU
    Posts
    18
    Sorry, I forgot to attach the file.
    Attached Files Attached Files

  15. #15
    Join Date
    Nov 2003
    Posts
    150

    Cool

    great!

    thanks a lot. I was pretty sure it was something simple. This is my first venture into VBA, so i dont necessarily pick up on things like this...


    thanks again!!!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

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