Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Question Unanswered: subform textbox fill with information from a table that is not the "record source"

    I want to make a subform textbox fill with information from a table that is not the "record source" of the main form or the subform based on the selection made in a combo box on that same sub form.

    When I select a part number from the part combo box I need the desc and the unit price to be pulled from the parts table based on the Part_ID selected the subform is based on a link table with only EstimateandPart_ID Estimate_ID and Part_ID
    Attached Thumbnails Attached Thumbnails F_Estimates.bmp   SF_Parts.bmp  
    Last edited by RogueXXV; 09-01-07 at 20:08.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    dlookup is a candidate

    if Part_ID is numeric:
    SomeTextBox = dlookup("nameOfDescField", "nameOfTable", "Part_ID = " & subformFieldHoldingPart_ID)

    if Part_ID is text:
    SomeTextBox = dlookup("nameOfDescField", "nameOfTable", "Part_ID = '" & subformFieldHoldingPart_ID & "'")

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Red face attempted DLookup (unsuccessful) :(

    Part_ID is numeric as to match Part_ID autonumber key in Parts Table

    Combo Box on Sub Form SF_Parts
    Name : Part_IDcb
    Control Source : Part_ID (from Link Table EstimatesandParts. Only contains Estimate_ID and Part_ID and autonum)
    Row Source Type : Table/Query
    Row Source : Q_Parts

    Private Sub Part_IDcb_AfterUpdate()
    txtPartName = DLookup("PartName", "Parts", "Part_ID = " & [Part_IDcb])
    txtUnitPrice = DLookup("UnitPrice", "Parts", "Part_ID = " & [Part_IDcb])
    End Sub

    (also tried without square brackets)

    Q_Parts is a Query
    Part_ID
    PartNumber
    PartName
    UnitPrice
    PartDescription (sorted Assending)

    This set up leaves the txt boxes both blank and no updates when you change the Selection in the combo box
    txtPartName : unbound
    txtUnitPrice : unbound
    Part_IDcb
    all on SF_Parts
    Last edited by RogueXXV; 09-02-07 at 20:07.

  4. #4
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Red face ok now Semi-successfull

    k messed up a hair in naming but now have combo box filling every box in list still not quite what I wanted lol has exact same effect as

    Me.txtPartName = Me.Part_IDcb.Column(2)
    Me.txtUnitPrice = Me.Part_IDcb.Column(3)

    The Subform is a continuous form layout with no dividing lines and when I select any item from combo box on left the entire list fills with what ever I happened to select.
    Attached Thumbnails Attached Thumbnails SF_Parts_New.bmp  
    Last edited by RogueXXV; 09-02-07 at 20:30.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    probably the continuous-forms would have been obvious to me if i looked at your .bmp ...sadly i didn't.

    but now have combo box filling every box in list ('continous-form' NOT 'list')
    get used to it.
    each instance of an unbound control in continuous-forms contains the same data.
    always.
    that's life!

    this tragic little fact of life has not annoyed me since i stopped using continuous forms many years ago.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Exclamation Oh....

    um .. ok .. well thats not very good... so is there any way to make a list that grows as you put more data in automatically? and stay as a single field when theres no data do I have to have this BIG monster whiile do loop or something in code ? Any assistance would be nice

  7. #7
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Question Still need a bit of coding assistance

    If I dont use a Continuous Form is there any way to make the list grow automatically as I enter more data? IE if I put in one part in the list it shows only one line on the subform and if I put in 25 lines it shows 25 lines (even to the point of being able to scroll through the "list" of items
    Here is the Estimates Form
    and here is the present Parts SubForm
    Im willing to change either or both to make this easy and willing to do a fair amount of code... just a bump in the right direction would be great

    Thanks
    Rogue

  8. #8
    Join Date
    Sep 2007
    Location
    BC Canada
    Posts
    8

    Question hmm...well here's my first attempt at making a list grow with inputs

    Well this looks evil ... and WAYYY too inefficient for words but I dont know of a way to do this better... I can have up to 50 parts on any one estimate so I need 50 of these? akk... is there a way to make this SIMPLER? and smaller maybe make it into a Function that updates the number after each one? Open to any idea to make this pile of junk a smaller pile lol

    Code:
    Private Sub Part_ID1_AfterUpdate()
        Me.PartName1 = Me.Part_ID1.Column(2)
        Me.UnitPrice1 = Me.Part_ID1.Column(3)
    End Sub
    
    Private Sub Quantity1_AfterUpdate()
        If Part_ID1 Is Not Null Then
            If Quantity1 Is Null Then
                MsgBox "You MUST enter a quantity for EACH part that you are about to use on the estimate", , "Quantity Error"
                Quantity1.SetFocus
            Else
                Part_ID2.Visible = True
                PartName2.Visible = True
                UnitPrice2.Visible = True
                Quantity2.Visible = True
                Part_ID2.SetFocus = True
            End If
        Else
            MsgBox "You MUST select a Part PRIOR to selecting the Quantity", , "No Part Error"
            Part_ID1.SetFocus
        End If
            
    End Sub
    Last edited by RogueXXV; 09-10-07 at 17:16.

Posting Permissions

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