delete one fields value.

[yeatmanj]

I need a way to delete one fields value without having to delete the whole record. and I don't understand all the help files on delete methods.

 

[norm]

A delete query will delete the entire record as your question suggests. In order to work with a single field, create a recordset by using the OpenRecordSet method of the docmd object which will allow you to access individual fields.

(There may also be ways to fudge it ie. go to the correct record in a bound form and then set the control = "" or null.)

here's a quick example with recordsets:

Sub RecordSetExample()
Dim DB As Database
Dim rst As Recordset
Dim strSQL As String
Dim varA as variant

'varA would be the value of the unique record identifier in your table/query.

strSQL = "SELECT * FROM TABLEA WHERE (TABLEA.ID = " & varA & ");"
Set DB = CurrentDb
Set rst = DB.OpenRecordset(strSQL, dbOpenDynaset)


With rst
.MoveFirst

.Edit
!ID = ""
.Update

End With

End Sub

best of luck!

norm

 

[yeatmanj]

Norm,
May I ask where you got the example and where I may find more detailed explainations of the example you gave me. I thank you for the help.

 

[Former]

Would it not be possible to run an update query, updating the field to null, to get the required result ?

 

[yeatmanj]

The last time I tried to run an update query in this db i got a lot of validation rule errors because of the relationships, but I'll try again. Thanks.

 

[norm]

Yeatmanj,

You're right. My apolgies if I threw you off. It's a lot easier to travel in a straight line!!

Read 'When can I update data from a query?' in the help files. You should find some answers there regarding validation rule errors.

The sample was from me but it's pretty generic. You can probably find samples just like it in the help files.

good luck,
norm