Calling a function in VBA with different inputs.

[silversun]

Hello,
I am new in Access and need help. I want to write a VBA code to do the same job more than once with different inputs. To be more clear the following is a VBA code to update/record values in two different tables. The values are serial numbers in different ranges (serial numbers to be added in tbl_returns) and inv_ID (inv_ID to be removed from tbl_allPins). How can I add more serial numbers in different ranges and update records in tbl_allPins.inv_ID in the same range in one click?
Please help me if you understand what I explained or ask for more clarification.
Thanks in advance.
My VBA code:
Private Sub cmdAdd_Click()
On Error GoTo myErrorHandler
'-------------------defining the variables
Dim str_srl_num As Long
Dim to_srl_num As Long
Dim CurSrlNum As Long
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim rs1 As DAO.Recordset

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
'*set the variables and database
str_srl_num = Me.serialFrom1
to_srl_num = Me.serialTo1
CurSrlNum = str_srl_num
Set db = CurrentDb()
Set td = db.TableDefs!tbl_returns
Set rs1 = td.OpenRecordset

'*update the first range of serial numbers
Do Until CurSrlNum > to_srl_num
'add returned serial numbers
rs1.AddNew
rs1!serial = CurSrlNum
rs1!inv_num = Me.cbo_invNum
rs1!Reason = Me.cbo_reason
rs1!returnDate = Me.txtReturnDate
rs1!notes = Me.txtNotes
rs1.Update
CurSrlNum = CurSrlNum + 1
Loop

' Removing the invoice ID from tbl_allPins (in the given range
' Declare a query definition object and reference the query "qry_remove_invID":
Dim qryscnd As DAO.QueryDef
Set qryscnd = db.QueryDefs("qry_remove_invID")

' The query needs 2 values. These have to be set from left to right:
' First the value of 'SerialFrom':
qryscnd.Parameters(0).Value = serialFrom1.Value
' Second the value of 'SerialTo':
qryscnd.Parameters(1).Value = serialTo1.Value
' Run the update query:
qryscnd.Execute


' Selfexplaining (I hope...)
If qryscnd.RecordsAffected = 0 Then
MsgBox "There were no records to be assigned.", vbExclamation
End If
rs1.Close
Set db = Nothing
Set rs1 = Nothing
Exit Sub

myErrorHandler:
' trap missing empty boxes error (94)
If Err.Number = 94 Then
Dim ErrMsg As String
ErrMsg = "You can not leave the serial number boxes empty."
ErrMsg = ErrMsg + " Please enter a serial number"
MsgBox ErrMsg
serialFrom1.SetFocus
End If
End Sub
--------------------------------------------
The above VBA must run four times with serialFrom1, serialFrom2, serialFrom3, serialFrom4 and serialTo1, serialTo2, serialTo3, serialTo4
(different serial number ranges)
:banghead:
I want to avoid writing this code several times in my procedure.

 

[John Big Booty]

Please have a read of this POST

 

[John Big Booty]

... and edit your OP in accordance.

 

[silversun]

How to make a function working with different inputs at the same time?

Hello,
I am new in Access and need help. I want to write a VBA code to do the same job more than once with different inputs. To be more clear the following is a VBA code to update/record values in two different tables. The values are serial numbers in different ranges (serial numbers to be added in tbl_returns) and inv_ID (inv_ID to be removed from tbl_allPins). How can I add more serial numbers in different ranges and update records in tbl_allPins.inv_ID in the corresponding serial numbers with one click?
Please help me if you understand what I explained or ask for more clarification.
Thanks in advance.
My VBA code:

Private Sub cmdAdd_Click()
On Error GoTo myErrorHandler
 '-------------------defining the variables
    Dim str_srl_num As Long
    Dim to_srl_num As Long
    Dim CurSrlNum As Long
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs1 As DAO.Recordset
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    'set the variables and database
    str_srl_num = Me.serialFrom1
    to_srl_num = Me.serialTo1
    CurSrlNum = str_srl_num
    Set db = CurrentDb()
    Set td = db.TableDefs!tbl_returns
    Set rs1 = td.OpenRecordset
     
    'update a range of serial numbers
   Do Until CurSrlNum > to_srl_num
     'add returned serial numbers
     rs1.AddNew
     rs1!serial = CurSrlNum
     rs1!inv_num = Me.cbo_invNum
     rs1!Reason = Me.cbo_reason
     rs1!returnDate = Me.txtReturnDate
     rs1!notes = Me.txtNotes
     rs1.Update
     CurSrlNum = CurSrlNum + 1
   Loop

   ' Removing the invoice ID from tbl_allPins (in the given range)
   ' Declare a query definition object and reference the 
   ' query "qry_remove_invID":
    Dim qryscnd As DAO.QueryDef
    Set qryscnd = db.QueryDefs("qry_remove_invID")

    ' The query needs 2 values. These have to be set from left to right:
    ' The inv_ID will be assign to NULL during execution this query in 
    ' corresponding serial numbers.
    ' First the value of 'SerialFrom':
    qryscnd.Parameters(0).Value = serialFrom1.Value
    
    ' Second the value of 'SerialTo':
    qryscnd.Parameters(1).Value = serialTo1.Value
   
    ' Run the update query:
    qryscnd.Execute
     

     ' Selfexplaining (I hope...)
    If qryscnd.RecordsAffected = 0 Then
        MsgBox "There were no records to be assigned.", vbExclamation
    End If
    rs1.Close
    Set db = Nothing
    Set rs1 = Nothing
    Exit Sub

myErrorHandler:
    ' trap missing empty box error (94)
    If Err.Number = 94 Then
      Dim ErrMsg As String
      ErrMsg = "You can not leave the serial number boxes empty."
      ErrMsg = ErrMsg + " Please enter a serial number"
      MsgBox ErrMsg
      serialFrom1.SetFocus
    End If
End Sub

The above procedure must run four times with serialFrom1, serialFrom2, serialFrom3, serialFrom4 and serialTo1, serialTo2, serialTo3, serialTo4 as input parameters (different serial number ranges).
I want to avoid writing this code several times in my procedure.
:banghead:

 

[John Big Booty]

Re: How to make a function working with different inputs at the same time?

You could turn this piece of code into a Module, and call that module whenever you need to us it.