populate date in subform

[redrover]

I'm using a subform that includes a date field. The user enters the date and
at least 1 other value for each record in the subform. I'd like to
automatically update any subform records that contain blank date fields with
the first date that the user enters. I'm sure I need to use VB coding to
perform this but I'm having difficulty getting started. Can anyone suggest a
method to get this done?

 

[vbasean]

I'm using a subform that includes a date field.  The user enters the date and
at least 1 other value for each record in the subform.  I'd like to
automatically update any subform records that contain blank date fields with
the first date that the user enters.  I'm sure I need to use VB coding to
perform this but I'm having difficulty getting started.  Can anyone suggest a
method to get this done?

you could have some VBA that loops through pertinent records and adds
the date if neccesary.

Dim rs as DAO.Recordset
Dim db as DAO.Database
set db = CurrentDb
set rs = db.Open("SELECT [the date field you want to update].[your
table/query name] WHERE [the primary key of that table].[your table/
query name] = " & me.[name of field that contains the child field])
rs.MoveFirst
Do while Not rs.EOF
if IsNull (rs![the date field you want to update]) Then
With rs
.Edit
rs!![the date field you want to update] = me.[name of date field the
user just updated]
.Update
End with
End If
rs.MoveNext
Loop

 

[vbasean]

I'm using a subform that includes a date field.  The user enters the date and
at least 1 other value for each record in the subform.  I'd like to
automatically update any subform records that contain blank date fields with
the first date that the user enters.  I'm sure I need to use VB codingto
perform this but I'm having difficulty getting started.  Can anyone suggest a
method to get this done?

you could have some VBA that loops through pertinent records and adds
the date if neccesary.

Dim rs as DAO.Recordset
Dim db as DAO.Database
set db = CurrentDb
set rs = db.Open("SELECT [the date field you want to update].[your
table/query name] WHERE [the primary key of that table].[your table/
query name] = " & me.[name of field that contains the child field])
rs.MoveFirst
Do while Not rs.EOF
if IsNull (rs![the date field you want to update]) Then
With rs
.Edit
rs!![the date field you want to update] = me.[name of date field the
user just updated]
.Update
End with
End If
rs.MoveNext
Loop

You could add this code to the form after update event


Private Sub Form_AfterUpdate()
Dim rs as DAO.Recordset
Dim db as DAO.Database
set db = CurrentDb
set rs = db.Open("SELECT [the date field you want to update].[your
table/query name] WHERE [the primary key of that table].[your table/
query name] = " & me.[name of field that contains the child field])
rs.MoveFirst
Do while Not rs.EOF
if IsNull (rs![the date field you want to update]) Then
With rs
.Edit
rs!![the date field you want to update] = me.[name of date field the
user just updated]
.Update
End with
End If
rs.MoveNext
Loop
End Sub

 

[vbasean]

I forgot the FROM statement in my SQL

set rs = db.Open("SELECT [the date field you want to update].[your
table/query name] FROM [your table/query name]
WHERE [the primary key of that table].[your table/
query name] = " & me.[name of field that contains the child field])

 

[redrover]

I'll give this a try -- will there be a problem updating the recordsource
since I already have it open in the subform?