Requerying subform combo box based on changing value in main form

[tomcone]

In a form called Boxes, there is a combo box called MasterBox.

In a continuous subform Files, there is a combo box FileName which uses MasterBox in the criteria for its query to lookup only files that are in MasterBox. I want to use the original value of MasterBox when entering the first few records in the subform and then change the value of MasterBox and enter more records in the subform without affecting the records already entered. The form and subform are not linked.

I have tried using the Requery command when Got Focus on FileName. This almost worked fine, but applies to all records in the subform. I want it to only apply to recrods from that record on.

I figure that there is a way to do this without writing the records one by one as entered to another table which will not be affected by the requery.

HHHEEELLLPPP

Thanks

 

[jksmi]

Hi, sounds like you want masterbox to be the default value for any new records in the subform - try changing the default value for the control on the subform to the value of masterbox...

for example

Private Sub MasterBox_AfterUpdate()
Me.Child0.Controls("txtWantsToBeSameAsMasterBox").DefaultValue = Me.MasterBox
End Sub

HTH, Jamie

 

[tomcone]

Will give it a try.

Also I want to set an On Change event which sets an unbound field in the subform to be the equal to the result of a SQL Select query. What is the VBA code to say:

UnboundCntrol= Select .. from ... where ...;

 

[jksmi]

Hi, its probably as quick (and easy) in this sort of case to use a DLookup, syntax is me.mycontrol = DLookup("field name", "table name", "[i.e.Usernumber]=" & me.txtUsernumber)
3rd bit (criteria) requires datatype qualifiers as per normal SQL, i.e. #date#, 'string'

HTH, Jamie