subform problems

M

Michael

Does anyone know how to make a button on the mainform delete the records
showing on its subform which is in datasheet view? Thanks for the help!
 
K

Ken Sheridan

Michael:

You can execute an SQL statement in code which deletes all rows from the
table on which the subform is based where the value of the foreign key column
which references the primary key of the parent form's table has the value of
the parent form's current record's primary key. Then you'd requery the
subform control. Say the subform is based on a table Orders with a foreign
key CustomerID which references the CustomerID of the Customers table to
which the parent form is bound, then the code in the button's Click event
procedure would go something like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strMessage As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strMessage = "Delete all records from subform?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Confirm Deletions") = vbYes
Then
strSQL = "DELETE * FROM Orders " & _
"WHERE CustomerID = " & Me.CustomerID
cmd.CommandText = strSQL
cmd.Execute
Me.YourSubformControl.Requery
End If

Note that YourSubformControl is the name of the control on the main form
which houses the subform, not its underlying form object, unless both have
the same name of course.

Be sure to back up the table on which the subform is based until you are
confident this is doing as expected.

Ken Sheridan
Stafford, England
 
M

Michael

The problem is that the records are not linked with the mainform. The
mainform is there only to let them see what they are working with. Each
record in the subform corresponds to its own new primary key
 
K

Ken Sheridan

What is the subform's RecordSource? If its independent of the parent form
then you should be able to delete all rows from its underlying recordset
using similar code, but without the need to include a reference to the parent
form's current record in the SQL statement.

Ken Sheridan
Stafford, England

Michael said:
The problem is that the records are not linked with the mainform. The
mainform is there only to let them see what they are working with. Each
record in the subform corresponds to its own new primary key
Click to expand...
 
M

Michael

The subforms have nothing to do with the mainform. Each subform simply brings
up a place for the users to input information that will be saved into their
own tables
 
K

Ken Sheridan

As I said in my last post simply execute an SQL statement to delete all rows
from the table in question:

<code as before>
strSQL = "DELETE * FROM TheTableName"
<code as before>

If the subform's recordset is restricted in some way then add a WHERE clause
to the SQL statement.

Ken Sheridan
Stafford, England

Michael said:
The subforms have nothing to do with the mainform. Each subform simply brings
up a place for the users to input information that will be saved into their
own tables
Click to expand...
 
M

Martin Nobel

The problem is that the records are not linked with the mainform. The
mainform is there only to let them see what they are working with. Each
record in the subform corresponds to its own new primary key
Click to expand...
 
Top