Text Box DLookup (1 Viewer)

[margt_a]

I have a main form that shows project information with many subforms on many tabs. I would like to have a text box at the top of the form that shows the [CompanyName] of the one record on the [frmContactSubNew] subform that is also the [Client]. The Client is designated by a check box.

After digging around some of the old posts on DLookups, I have tried to put the following formula in my text box.

=DLookUp("[Company]","frmContactSubNew","[Client] = True")

This gives me the #Error thing in my text box. I can remove the Criteria part and the text box shows the first record in the [frmContactSubNew] subform. The problem must be in my criteria. How do I indicate that I want only the record where the [Client] checkbox is checked?

Thanks!
Margaret

 

[Rich]

Is the Company Name already displayed on the subform

 

[margt_a]

Yes, the company name is one of possibly several entries on a subform on the "Contact" Tab. The "Client" check box is also there. I want the Client name displayed at the top of the form (off the tabs) for ready reference, but the data entry will still take place on the subform.

 

[Rich]

Then you don't need DLookUp, you can either set reference on the main form to the subform, or set the main form textbox value from the subform with vba, is the company name always displayed or is it as a result of the checkbox?

 

[margt_a]

I have been able to pull the CompanyName from the subform with just an =[CompanyName] expression, but it pulls the first entry in the subform. What I need is for the value in the top text box to be constrained by the value of the [Client] check box. I guess what I'm looking for is the equivalent of a WHERE clause in a query. Does a DLookup function not accomplish that?

The CompanyName is always available as one entry on a subform. The Client checkbox is on that subform - one of several that distinguish different company roles.

Thanks,
Margaret

 

[Rich]

SubForm Current Event
Forms!MainForm!TextBoxName = Me.txtCompName
End Sub

MyCheckBox After_Update
Form_Current
End Sub

 

[margt_a]

Thanks for the code, but it wasn't quite right. It populated the text box when the check box after the update, but looking up records the check box was blank. Maybe if I refresh the checkbox when the record changes, it will work. Can I make the code pull whatever value is there, passively, and not be entry-based?

Margaret

 

[Rich]

SubForm Current Event
If Me.MyCheckbox = True Then
Forms!MainForm!TextBoxName = Me.txtCompName
Else
Forms!MainForm!TextBoxName =" "
End If
End Sub

 

[margt_a]

Thanks for your help, Rich. I think that will work.

Margaret