delete subform record

R

redrover

I have a subform that is the many side of a 1 to many relationship. How do I
delte a record from the subform? Do I have to declare a recordset?
 
J

JamesDeckert

I assume you want to do this programmatically.

You can do it my declaring a recordset and doing an rst.find to locate the
record and delete it.

You could also use a Delete SQL statement to remove the statement.

I think you can also do something like the following if your cursor is in
the record you want to delete.
Dim rstMeForm As DAO.Recordset
Set rstMeForm = Me.Recordset
rstMeForm.Delete

James
 
R

redrover

Yes, I do want to do this programmatically. There's probably a better way to
do what I need to do but I'm working with a previously designed database. I
have a form that accesses the subform (actually there's a subform within the
subform that is opened). The subform within the subform has the data that I
need to manipulate. The data on the forms are keyed by SSN. The subform
data I want to loop through is the incidences for a SSN. If the data is a
certain type and is at least a year old, I want to delete it. I also need to
total the data for the various types. Can I do this from the main form with
an 'On Open' or 'On Focus' event by declaring the subform data and looping
through it there? Either way, I need to delete some records and bring the
calculated data back into the main form and use it there.

Thanks for any guidance you can give me.
 
J

JamesDeckert

you can loop through the recordset of a subform using the previous format
which I posted. Replace 'Me' with the name of the subform. Then use the
recordset as normal (looping, modifying, deleting).
Dim rstMeForm As DAO.Recordset
Set rstMeForm = Me.Recordset

Another method you could use would be a query. Create a query which does
what you want it to do and place a valid SSN in the criteria row on the
query. When you get the query to work then View the SQL and copy it to your
code, replacing the temporary SSN with the SSN from your form (e.g. [ssn]).
You'll have to make sure the SSN field is interpreted.

If you want to delete records you will do something like
DoCmd.RunSQL "DELETE * FROM [FileName] WHERE [SSN]=" & [SSN]
This format works for action queries only, not select queries.

HTH,
James
 
I

Illya

Frends, please help me!! SOS!!! I making all as you say, but where i writing
cod:
Dim rs As New ADODB.Recordset
sql="Select .... "
rs.CursorLocation = adUseClient
rs.Open Ssql, OAdoConnection, adOpenForwardOnly, adLockOptimistic
Set Me.Recordset = rs

VBA give massage on line "Set Me.Recordset = rs" : Method "Recordset" of
object "<form name>" Failed....

Why???? This cod compiled on Access 2003 (Office 2003 SP1)


JamesDeckert said:
you can loop through the recordset of a subform using the previous format
which I posted. Replace 'Me' with the name of the subform. Then use the
recordset as normal (looping, modifying, deleting).
Dim rstMeForm As DAO.Recordset
Set rstMeForm = Me.Recordset

Another method you could use would be a query. Create a query which does
what you want it to do and place a valid SSN in the criteria row on the
query. When you get the query to work then View the SQL and copy it to your
code, replacing the temporary SSN with the SSN from your form (e.g. [ssn]).
You'll have to make sure the SSN field is interpreted.

If you want to delete records you will do something like
DoCmd.RunSQL "DELETE * FROM [FileName] WHERE [SSN]=" & [SSN]
This format works for action queries only, not select queries.

HTH,
James




redrover said:
Yes, I do want to do this programmatically. There's probably a better way to
do what I need to do but I'm working with a previously designed database. I
have a form that accesses the subform (actually there's a subform within the
subform that is opened). The subform within the subform has the data that I
need to manipulate. The data on the forms are keyed by SSN. The subform
data I want to loop through is the incidences for a SSN. If the data is a
certain type and is at least a year old, I want to delete it. I also need to
total the data for the various types. Can I do this from the main form with
an 'On Open' or 'On Focus' event by declaring the subform data and looping
through it there? Either way, I need to delete some records and bring the
calculated data back into the main form and use it there.

Thanks for any guidance you can give me.
Click to expand...
Click to expand...
 
Top