Find a record and make changes using DAO

[jazzz]

I have a popup form that when I delete this form I need it to delete data in one field in my master table that corresponds to the pop up record number.&nbsp;&nbsp;In the code below the number is 4838 and this code works fine as long as I put the correct number in.&nbsp;&nbsp;However, I want to get the number from the current pop up form and open up the table and delete the corresponding number in the master table.&nbsp;&nbsp;I indexed the field used <br>rec.index = &quot;soldto&quot;<br>rec.seek &quot;=&quot;, soldto&nbsp;&nbsp;'didn't work either<br><br>I can't get it to work how can I capture the number on my current popup and make it the value that my DAO statement will look up and delete.<br><br>Here is my code below.<br>Private Sub cmdDeleteSaveToNew_Click()<br>On Error GoTo Err_cmdDeleteSaveToNew_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rec As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strTable As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strTable = &quot;tblMasterinfo&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rec = db.OpenRecordset(strTable, dbOpenDynaset)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rec.FindFirst &quot;[soldto] = 4838&quot; 'right here I need code<br>&nbsp;&nbsp;&nbsp;&nbsp;rec.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;rec![SoldTo] = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;rec.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;rec.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>'These lines clean up the sold to drop down<br>'to reflect that it was deleted.<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If IsOpen(&quot;frmMasterinfo&quot Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!frmMasterinfo.Refresh<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!frmMasterinfo.Command305.SetFocus<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!frmMasterinfo.cmdEditSoldto.Enabled = False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!frmMasterinfo.cmdDeleteSoldToNew.Enabled = False<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Me.Requery<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Forms!frmSoldtoNew.SetFocus<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Exit_cmdDeleteSaveToNew_Click:<br>&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_cmdDeleteSaveToNew_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_cmdDeleteSaveToNew_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub

 

[jimmythegeek]

I find this method to be more efficient, as you are returning only the record(s) that match your criteria, and you don't have to use FindFirst:<br><br><br>Private Sub cmdDeleteSaveToNew_Click()<br>On Error GoTo Err_cmdDeleteSaveToNew_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;SELECT * FROM tblMasterinfo WHERE(((SoldTo)=&quot; & Me.YourFieldName & &quot);&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .RecordCount &gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;!SoldTo = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End if<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>' Continue Code ........<br>&nbsp;&nbsp;&nbsp;&nbsp;<br><br>&nbsp;&nbsp;OR This way:<br><br>Private Sub cmdDeleteSaveToNew_Click()<br>On Error GoTo Err_cmdDeleteSaveToNew_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strTable = &quot;UPDATE tblMasterinfo SET SoldTo = Null WHERE(((SoldTo)=&quot; & Me.YourFieldNameHere & &quot);&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Execute strSQL<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>' Continue Code .........&nbsp;&nbsp;&nbsp;&nbsp;<br><br><br>Hope one of these help.<br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[jazzz]

Thanks I'll give it a try.