Find Record to Delete 2

[bkrampe]

We have a database that when you scan three barcodes, one for the Material Code field one for the width field and one for the length field it fills them in accordingly. Then when u press add, it adds that record into a database.

What I am wanting to do is, create a separate form that when you scan these 3 barcodes it will search through the database and find the record that matches those three fields and then you can press delete and it will get rid of the record.

 

[genomon]

Store the three scanned barcodes in string variables.
On the click event of the delete button, add a string variable to store the following SQL statement, then execute it with the DoCmd object:

"DELETE FROM yourtable WHERE barcode1 = '" & strBarcode1 & _ "' AND barcode2 = '" & strBarCode2 & "' AND " & _
"barcode3 = '" & strBarCode3 & "'"

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[bkrampe]

Sorry do you think you could elaborate anymore?

 

[genomon]

Specifically what additional info do you need?
Otherwise it's pretty basic. Do you have relevant code from the app you could post back and ask a specific question about?

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[bkrampe]

Here is the code i put for the Delete Button

Private Sub DeleteRecord_Click()
On Error GoTo Err_DeleteRecord_Click
Dim strBarcode1 As String
Dim strBarcode2 As String
Dim strBarcode3 As String

"DELETE FROM REF WHERE MaterialCode="" & strBarcode1 &_ "" AND Y-Dimension="" & strBarcode2 &_ "" AND X-Dimension="" & strBarcode3 & ""

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_DeleteRecord_Click:
Exit Sub

Err_DeleteRecord_Click:
MsgBox Err.Description
Resume Exit_DeleteRecord_Click

End Sub

 

[genomon]

Wow. Where does strBarcode1 and its brothers get a value?
You have them dimmed in the click event, but don't assign them anything.

"DELETE FROM REF WHERE MaterialCode="" & strBarcode1 &_ "" AND Y-Dimension="" & strBarcode2 &_ "" AND X-Dimension="" & strBarcode3 & ""

Don't understand what this is? Not a string variable, just a free-floating quote? If it were viable SQL, you would need to insert spaces between the variables and keywords, and single quotes around the variables (as they are strings). If they are numbers, then dim them as such and don't use single quotes.



"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[TheAceMan1]

How are ya bkrampe . . .

If the recordsource of this form is based on the same table where the barcodes were initially stored, then copy/paste the following to the [blue]Click[/blue] event of the [blue]Delete[/blue] button:

[blue]   Dim rst As DAO.Recordset, Cri As String, SQL As String
   Dim Msg As String, Style As Integer, Title As String

   Cri = "[MaterialCode] = '" & Me![purple][b][i]MaterialTextboxName[/i][/b][/purple] & "' AND " & _
         "[Y-Dimension] = '" & Me![purple][b][i]Y-DimensionTextboxName[/i][/b][/purple] & "' AND " & _
         "[X-Dimension] = '" & Me![purple][b][i]X-MaterialTextboxName[/i][/b][/purple] & "'"
   
   Set rst = Me.RecordsetClone
   rst.FindFirst Cri
   
   If rst.NoMatch Then
      Msg = "Combined BarCode Record Not Found or Doesn't Exist!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Find Combined BarCode! . . ."
      MsgBox Msg, Style, Title
   Else
      SQL = "DELETE FROM REF " & _
            "WHERE " & Cri
      DoCmd.RunSQL SQL
   End If
   
   Set rst = Nothing[/blue]

be sure to double check the names used . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[bkrampe]

Ok that will work, but it only delete the data that is in the rows. It wont delete the empty rows, so when we go through and scan 2 different sets of barcodes, it will leave 2 empty rows in the database. Any way we can get rid of the empty rows?

 

[gwinn7]

Ace,

My compliments on your well formed code!

Gary
gwinn7

 

[TheAceMan1]

How are ya gwinn7 . . .

Thank you very much!

Not trying to sound this way or that or all powerful, let this be a lesson in the importance of formatting your own code!

Formatting your own code is a [blue]big matter[/blue] of [purple]readability[/purple], and is very important to us as programmers. Enough of use write in a way that can be deciphered by others.

To me this is wrong . . . [blue]you always format in a way thats easy for you to decipher (as the programmer)! Why! . . . [blue]your gonna be the one debugging everything![/blue] [/blue] There isn't any code I write ([blue]because of formatting[/blue]) that I can't tell you in less that a minute . . . what I was doing! Fortunately for me . . . it works out that its easily deciphered as well. Wether this is good or bad remains to be seen. But I do try to write code (here in the forums) thats not a mystery. [blue]Formatting is the key! . . .[/blue]

[blue]Again . . . many thanks![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

bkrampe . . .

[blue]Ok that will work, but it only delete the data that is in the rows. [purple]It wont delete the empty rows[/purple] . . .[/blue]

Just like catching the fish before you cook it, [blue]your form that adds records to the table, [purple]shouldn't be allowing blanks![/purple][/blue]

Start a new post relating to this problem (you'll be fully open to the forum) and include the code used to save scans.

This is the kind of problem Tek-Tips will eat for breakfast! . . . . . . I'll keep an eye out for it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[gwinn7]

Ace,

I wholeheartedly agree, but I would like to add that commenting code is at least as important as good formatting.

I am not complaining about this in your example because it was just a quick example and short.

Even IF you are the only programmer of that chunk of code, we can't all remember exactly what we did and, more importantly, why, after months away from it.

Good comments describe not so much as 'what you did', but why you did it in the first place and help avoid pit falls if you have to make code changes. I am not speaking of simplistic functions, but it applies more to complex operations.

Bravo Ace!

Gary
gwinn7

 

[bkrampe]

Would there be any way, that if i put in a Quantity field that it would only delete that many records. Per say if there were two that were the exact same, when i press the delete button it wants to delete both. Think a Qty field would work, and if so what modifications would i make to code?

 

[TheAceMan1]

bkrampe . . .

You need to define a blank record!

Do you mean all fields are empty including the primary key?

If the above is true a simple delete query or SQL will remove all blanks . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[bkrampe]

Ace your solution you gave me works great. But now when i enter the criteria, if there is more than one field with the same information it wants to delete all fields that match that. Is there anyway we could put a qty field to have in only delete the quantity that is entered?