First, a syntactical point. The ! operator is not used to separate a table
name and a field name (e.g., [tblService]![txtDate] would more properly be
noted as [tblService].[txtDate]).
Second, in order to edit or delete the record in tblService, the subform
must know what the primary key field's value is for the record that is to be
edited or deleted. As I recall from our earlier thread, you were not
specifically passing a primary key value to the tblService? You must have
that value to do what you want to do. Otherwise, ACCESS has no way of
knowing which record is the "right" one.
In your setup, you will need to distinguish between the entry of new data
(the code that we worked out in the earlier thread, which, as you've noted,
runs every time a value is entered into (or changed) in txtDate control) and
the editing / deleting of existing data. One way this can be done is to test
for whether you're on a new record and then decide which code would be run.
For example, using the code that we had from before:
Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
If Me.NewRecord = True Then
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", _
dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Fields("NameOfDateField").Value = Me.txtDate.Value
rst.Fields("NameOfClientIDField").Value = Me.ClientID.Value
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End If
End Sub
The above code now will add a new record to tblService only if you're on a
new record in the form that is running the code.
A similar If .. Then setup could be used to handle edits (but not
deletions):
Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
If Me.NewRecord = True Then
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", _
dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Fields("NameOfDateField").Value = Me.txtDate.Value
rst.Fields("NameOfClientIDField").Value = Me.ClientID.Value
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Else
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", _
dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.FindFirst "PrimaryKeyFieldName=" & "primary key value from
form"
If rst.NoMatch =False Then
rst.Edit
rst.Fields("NameOfDateField").Value = Me.txtDate.Value
rst.Fields("NameOfClientIDField").Value =
Me.ClientID.Value
rst.Update
End If
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End If
End Sub
To handle deletion, you'll need to do something similar but would be
deleting the rst recordset's record (or you could run a delete query that
deletes the one record).
--
Ken Snell
<MS ACCESS MVP>
Heather said:
Hi Ken!
My subform contains [txtDate] and [txtServiceCode].
[txtDate] contains the code you suggested yesterday on
the AfterUpdate property, which sends the information in
[subfrmAssessment]![txtDate] to [tblService]![txtDate]
and [tblService]![txtServiceCode] = 43.
What I need is: if a user changes data in
[subfrmAssessment]![txtDate], can the change be reflected
to the same record that was generated by your code in
[tblService]![txtDate]?
Also, if they delete the record in [subfrmAssessment] can
it be deleted in [tblService]?
I know this isn't the best way to store the data, I would
have made the tables work together, but my client insists
that the data be store in both tables....
Hope this helps! And thanks again!
Heather
-----Original Message-----
Heather -
You'll need to give us a bit more info re: which data item (field) is being
edited and that should not add a new record but rather edit an existing
record.
I think that you should consider using a subform for that "child" table's
data instead of just a simple form for the parent data. But you need to give
details about your setup.
--
Ken Snell
<MS ACCESS MVP>
.
