Display a Caption in Form Field when it is Bound to a table

Question

I am trying to figure out how to display text in field entry when the field is blank from the row on the table.  I.E. when I get the field Safety from the table and it is Null or blank, then I would like to display 'Safety' in the field on the form.  When the record is saved, then I want to ensure that it is saved with spaces or Null.  When the user goes to the field, then 'Safety' goes away and they can enter whatever they want.  When the record is updated on the table, then I save the value of what they have entered.  Since I do not use headings for this data on the screen, then I want to have the field display what needs to be entered there.  I thought it would be caption, but that is not it.  I think I need to write code for this, but do not know if Access 2016 has a quick and easy way of doing this and my searched for doing this is not returning the information I am looking for.  The form is Bound also to the table.  Any ideas or can someone let me know where I need to read about how to do this?  Thanx in advance.

Answer 1

 The form is Bound also to the table.  Any ideas or can someone let me know where I need to read about how to do this?

Hi ballj_351,

As the control is bound, you can not write 'Safety' in that control. One way could be to hide the control with an unbound control with text 'Safety'.

In the Load event of the form you can move an unbound 'Safety'-control to the control in question, if it has no value. On enter that control, you can make the 'Safety'-control invisible.

But is al depends on how many of the controls must be covered with some 'Safety'-control, or other '...'-controls.

Imb.

Answer 2

interesting.  Would you know of any code that does this?

Answer 3

interesting.  Would you know of any code that does this?

Hi ballj_351,

By head yes, not any published reference.

You can read the values of the properties Top, Left, Height and Width of the control in question, and assign these values to the same properties of the 'Safety'-control, and set its Visible property to True.

Read the Help about the properties of a control, and especially these dimensional properties.

I do not know exactly what the prevalence is of 2 overlapping visible controls, but it could be that the 'Safety'-control is visible, but "behind" the control in question.

Imb.

Answer 4

I think I get it.  Have the control (ctlSafe) with "Safety" behind the form control that has the table value.  When there is an 'On Got Focus' event to the top control 'Safety', the make the control behind it (ctlSafe) invisible.  Then on a 'On Lost Focus' event, if the control is still blank or nulls, then make the background control (ctlSafe) visible.  If there is a value, then leave the background control invisible.  Then the question becomes, when should I check to see of there is a value from the table before I make it visible or not.  Sounds interesting, just need to understand the configuration for doing this.

Answer 5

Then the question becomes, when should I check to see of there is a value from the table before I make it visible or not.  Sounds interesting, just need to understand the configuration for doing this.

Hi ballj_351,

What do you want? From the beginning that the form is displayed, use the Load or Activate event. When you hit a record, use the Current event of that record, etc.

Imb.

Answer 6

You can certainly write to a bound field with VBA. To do that, use the Current event to fill in records with a Null value in that field:

If Nz(Me.Safety, "") = "" Then
  Me.Safety = "Safety"
End If

To handle this for new records, add the value of "Safety" as the Default value of that control. New records will automatically have that value, and the user can edit as needed. To add a Default value, open the form in Design view, select the control, then select the Data tab in the Property Sheet, and set the Default Value there.

---

-- Scott McDaniel, Microsoft Access MVP

Answer 7

Hello ballj_351,

Since Access does not provide built-in function for this, we have to accomplish this process by ourselves.

I would suggest you use Form_Current event to check the Safety value, once it is empty, set its value "Safety". This part is same as Scott. 

Then we could use the text box's OnEnter event to check the Safety field value once we are trying to edit the Safety field. If the value is "Safety", we could set the value as empty for user editing. Once user finishes editing, we need use OnExit event to check the Safety field again. If the value is empty, set the value as "Safety" again.

At last, once user finishes editing current record and want to save the record, we need use Form_BeforeUpdate event to check the safety field value to prevent saving the "Safety" value into the Safety field.

Here is the code and demonstration.

Option Compare Database

Private Sub Form_Current()
'enter "Safety" in safety field once it is empty
If Nz(Me.Safety, "") = "" Then
Me.Safety = "Safety"
End If
End Sub


Private Sub Safety_Enter()
'clear "Safety" in safety field once use begin to edit the field
If Nz(Me.Safety, "") = "Safety" Then
Me.Safety = ""
End If
End Sub

Private Sub Safety_Exit(Cancel As Integer)
'once use finish editing, enter "Safety" once the value is set as empty
If Nz(Me.Safety, "") = "" Then
Me.Safety = "Safety"
End If
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
'once saving the record, prevent saving "Safety" to the safety field
If Nz(Me.Safety, "") = "Safety" Then
Me.Safety = ""
End If
End Sub

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Hello ballj_351,

Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].