Linking Fields

[Sonnyboy]

The title may be wrong, but that is the best way I can describe the problem.

I have a form that is used to input data, and I am trying to discover how to do the following:

When I choose a company from a dropdown list (I know how to do that) I want a field named "Expires" to fill in a date in a textbox on the form, from the record containing the company that I chose.

I am just starting to use Expressions and VBA, mostly what someone else has written, and I have modified to fit my needs.

All suggestions are welcome.

Thanks
Sonnyboy

 

[CraigDolphin]

Sonnyboy,

you might want to research the Dlookup function to find the date in your companies table. I imagine that you would use it in the after_update event of your Company-selecting combo box.(I'm assuming your combo actually stores the numeric ID of the selected company and displays the company name rather than actually storing the company name. You can check this by looking at the column width property of your combo box {on the format tab}. If the first colum width is set to 0 then you likely are storing the numeric id)

Something like

Private Sub YourComboNameHere_AfterUpdate()
Me.Expires = Nz(Dlookup("YourCompanyTableDateFieldName", "YourCompaniesTableName","[CompanyID]=" & Me.YourComboNameHere),Date) 
'uses the Nz & date functions to input today's date if no date exists in the company table field
End Sub

Another function you may want to research could be the DateAdd() function if you'd rather use some other date than today's date where no date exists in the company table.

 

[Rich]

Add an unbound textbox to the form set its control source to =MyCombo.Column(1) where column 1 is the second column in your combo that contains the date field.