Set default value for new RecordSource

Question

I have a form that utilizes a query as a RecordSource to update a table in the back end. The query and table are identical. Right now, I have a few columns hidden in the query so they don't show on the data sheet in the split form view when records are being updated. However, because these fields are hidden, new record can't be updated with enough information to get the values to the table because the values for the hidden columns aren't set.

Is there a way to set a default value for every new record created for hidden columns? For example say I want a column named "Location" to pull a default value for any new record created that is not shown on the form. I have created a temporary variable that will be the default value for that new record. I tried setting the tables default value to a temporary variable however it doesn't work.

EliteRaceElephant EliteRaceElephant · Accepted Answer · 2015-04-27T21:13:37

You should use the BeforeUpdate_Event of the form. That way before your new record is committed to the table and the error message pops up you can assign values to your hidden columns.

dim mTempVariable as String
'* Set the value for your temporary variable. 
'* Make sure it is always set. The Form_Load Event could be a good place.

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Me.txtLocation = mTempVariable

End Sub

Set default value for new RecordSource

1 Answers