Update or append query, which one is my solution?

[silversun]

Hi all,
Please help!
I have a DB with several relational tables. I want to add new records to a table (tbl_returns). To add new records (serial number of returned cards) I am using a regular form. There is a serial number for each returned card. The problem came up when I wanted to add more than one record each time.
For instance a range of serial numbers between 6729 and 6731 & another range of 8213-8219 are returned, must be recorded in tbl_returns.
There is already an inv_ID for each returned card in tbl_allPins showing that it was sold before. Thus inv_ID in front of each serial number in tbl_allPins must be removed (assign to NULL) as well.
Normally each card is returned once, but in some cases returned cards can be sold somewhere else and they still can be returned. Sell's date and date of returning cards is always different.
Therefore serial numbers in tbl_returns is not necessarily a unique number!
A copy of DB is attached.View attachment DB4Test_mod1.zip
Thanks for your time

 

[Pat Hartman]

Here's a code snippet I use to add a range of drawing numbers. You can adapt it to your purpose.

Private Sub cmdAdd_Click() 
   Dim StartDwgNum As Long
    Dim EndDwgNum As Long
    Dim CurDwgNum As Long
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    
On Error GoTo Err_Proc

    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If IsNumeric(Me.txtBulkDrawingNumFrom) Then
        StartDwgNum = Me.txtBulkDrawingNumFrom
    Else
        MsgBox "From Drawing Number is required.", vbOKOnly
        Me.txtBulkDrawingNumFrom.SetFocus
        Exit Sub
    End If
    If IsNumeric(Me.txtBulkDrawingNumTo) Then
        EndDwgNum = Me.txtBulkDrawingNumTo
    Else
        MsgBox "To Drawing Number is required.", vbOKOnly
        Me.txtBulkDrawingNumTo.SetFocus
        Exit Sub
    End If
    If Me.cboBulkDrawingTypeID & "" = "" Then
        MsgBox "Drawing Type is required.", vbOKOnly
        Me.cboBulkDrawingTypeID.SetFocus
        Exit Sub
    End If
    If Me.txtBulkPfx & "" = "" Then
        If MsgBox("Prefix is empty.  Is that correct?", vbYesNo) = vbYes Then
        Else
            Me.txtBulkPfx.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboBulkSfx & "" = "" Then
        If MsgBox("Suffix is empty.  Is that correct?", vbYesNo) = vbYes Then
        Else
            Me.cboBulkSfx.SetFocus
            Exit Sub
        End If
    End If   
 CurDwgNum = StartDwgNum
    Set db = CurrentDb()
    Set td = db.TableDefs!tblDrawings
    Set rs = td.OpenRecordset
    Do Until CurDwgNum > EndDwgNum
        'add drawing
        rs.AddNew
        rs!JobID = Me.JobID
        rs!DrawingPfx = Me.txtBulkPfx
        rs!DrawingNum = CurDwgNum
        rs!DrawingSfx = Me.cboBulkSfx
        rs!DrawingTypeID = Me.cboBulkDrawingTypeID
        rs!Desc = Me.cboBulkSfx.Column(1)
        rs.Update
 
        CurDwgNum = CurDwgNum + 1
    Loop
    Me.sfrmDrawings.Form.Requery
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
            Resume Exit_Proc
    End Select
End Sub

 

[jonathanchye]

Good code However when using recordsets it's good practice to close when done. So something like

rs.close
set rs = nothing
db.close
set db = nothing

when closing database or form.

 

[silversun]

Hi Pat Hartman,
Thanks for your respond. It took me a long time to find variables in your code and switch them with matched variables in my form. Anyway, there are some parts missing that I have no idea how to complete them. If you look at the form "frm_returns " in my database I am sure it is pretty simple for you to fix it. I just removed some validation parts temporarily to make it simple and easy to understand for myself. Please Let me know if I am doing something wrong.
Please look at the attachment including all the VBA codes I modified in a .txt file.
View attachment VBAanswers2.txt
I did not understand line 20, 27 and 30 so I did not modify them but I'm sure they need modifications.
Thanks for your helps in advance.

 

[silversun]

Hi Jonathanchye,
I had a problem and someone gave me a set of VBA codes to fix it and you completed it. Now I have difficulty when matching variables in my database. Can you please help me on that? If yes, please look at the questions and answer in this thread.
Thanks in advance.