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

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

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

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.

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

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

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

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