Populate Text Boxes based on Check Box

[kgudgel]

Sorry, if this question seems elementary, but I am relatively new to development in Access.

I have a check box (Yes/No) in a form. When I click on this check box I would like to populate a couple different text boxes.

1. Text box, WeightAdjustmentAccept, would be populated with WeightAdjustmentRequested
2. Text box, StartDateforAdjustment, would be populated with today’s date
3. Text box, EndDateforAdjustment, and would be populated with today’s date + TimePeriodforAdjustment

All of the above data points pull from the same table tblWeightAdjustmentHistory.

[WendellB]

You can do that with what are called unbound controls. Is your form bound to the table? (Check to see if you have an entry in the Data Source property of the form.) If it is, then by default the text box controls may already be set to display those values. In that case you can set the visible property for the controls to No if the check bosx is not clicked, and changed it to Yes when it is clicked. All of that presumes you are somewhat conversant with VBA, or at least with Macros. It would also be useful to know what version of Access you are using, as there are some capabilities that are only available in later versions, and a few that are only available in older versions.

If your form is unbound (no data source) then the process is more complex and involves using either ADO or DAO to open up a recordset, and set the values based on a query that gets the appropriate record from the table. The VBA to do that sort of thing is not terribly more complex, but getting the data access commands correct is the complex part until you've done a few dozen times. Hope this helps get you started.

[kgudgel]

Thanks for your quick response.

Unfortunately I am learning, so my familarity with VBA is very limited. I have used macros in the past.

My form is bound to the table. I am using Access 2003.

[HansV]

Try the following:
- Open the form in design view.
- Select the check box.
- Activate the Properties window (View | Properties).
- Activate the Event tab.
- Click in the On Click box.
- Select [Event Procedure] from the dropdown menu.
- Click the button with three dots ... to the right of the dropdown arrow.
- This will take you to the Visual Basic Editor, with the first and last line of the event procedure already created. Let's say that the check box is named chkSwitch; you should see this:

Private Sub chkSwitch_Click()

End Sub

with the insertion point in the empty line in between.
- Now edit the code to look like this (using the correct names):

Private Sub chkSwitch_Click()
  If Me.chkSwitch = True Then
	Me.WeightAdjustmentAccept = Me.WeightAdjustmentRequested
	Me.StartDateforAdjustment = Date
	Me.EndDateforAdjustment = Date + Me.TimePeriodforAdjustment
  End If
End Sub

- Now switch back to Access.
- Close and save the form.

[kgudgel]

Thanks, Hans, for your help with this one!

This worked.

As a follow-up question, after clicking on the check box the data does not actually populate in the text box, unless the form is closed and reopened. Is there a way the data will appear in the text boxes without closing the form?

Karen

[HansV]

Are the names of the text boxes different from their Control Source? If so, try using the name instead of the Control Source in the code. For example, if the text box bound to the field WeightAdjustmentAccept is named txtWeightAdjustmentAccept, change the line

Me.WeightAdjustmentAccept = Me.WeightAdjustmentRequested

to

Me.txtWeightAdjustmentAccept = Me.WeightAdjustmentRequested

Otherwise, I can't explain why the text boxes aren't updated immediately.

[kgudgel]

That was the problem.

Thanks, again.

Karen