Record Deletion

[Guest]

Is there a function in Access that will alow a user to delete records that
meet specific criteria with the click of a button located on a form?

Example: Records 1-7 have null values in field "Txt12".
User clicks delete button which deletes records 1-7, but not
8-20.

TIA

 

[Alessandro Baraldi]

Antonio ha scritto:

Is there a function in Access that will alow a user to delete records that
meet specific criteria with the click of a button located on a form?

Example: Records 1-7 have null values in field "Txt12".
User clicks delete button which deletes records 1-7, but not
8-20.

TIA

You can use a MultiSelect ListBox(Extended=True).

So the code wil can use a SelectedItems to Build Where Criteria.

Put a ListBox in your form(RecordSource=Table or Query showing all
Records you need to evaluate).

Dim varIT
Dim sWhere as string
Dim sSQL as string

If List1.ListIndex<>-1 then
For each varIT in list1.Itemselected
' If PrimaryKey is Numeric enable this line
sSQL1=sSQL1 & list1.ItemData(varIT) & ","
' If PrimaryKey is Text enable this line
'sSQL1=sSQL1 & "'" & list1.ItemData(varIT) & "',"
Next
' If PK is Text you need to Subctract 2 at the end
' because the sSQL contain "',"
sSQL1=Mid$(sSQL1,1, len(sSQL1)-1)
'sSQL1=Mid$(sSQL1,1, len(sSQL1)-2)
End if

sSQL="DELETE * FROM T1 WHERE [FieldPK] IN (" & sSQL & ");"

Currentdb.Execute sSQL,dbFailOnerror

In this way no Warnings will be made.

@Alex

 

[John Vinson]

Is there a function in Access that will alow a user to delete records that
meet specific criteria with the click of a button located on a form?

Example: Records 1-7 have null values in field "Txt12".
User clicks delete button which deletes records 1-7, but not
8-20.

TIA

There's no builtin function to do something so specific, but what you
can do is create a Delete Query using appropriate criteria. The Query
can be executed using a macro of (better) a Command Procedure in the
Click event of the form button.

txt12 is NOT a fieldname (of if it is, your table has very odd field
names!) - it is apparently the name of a textbox control on a Form.
Remember that Forms are *just windows* - they do NOT contain any data;
the data is in a Table. What field is being used as the Control Source
for txt12? Note also that there ARE NO RECORD NUMBERS in Access.

John W. Vinson[MVP]