Passing args to a Function

  • Thread starter Thread starter quixote
  • Start date Start date
Q

quixote

Jen,
I have cut this down quite a bit. Here is the function. It
popilautes tblTraveller. But I need all the records in
another table to pass through the function...not sure how
to handle it..thanks:

Public Function GetLetter(Com, Model, LineItem, PO, Qty)

Dim db As DAO.Database
Dim strSQLA As String
Dim strSQLB As String

Set db = CurrentDb


If LineItem < 10 Then

Select Case Qty

Case 1

strSQLA = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO & "0" &
LineItem & "A" & "')"


db.Execute strSQLA


Case 2

strSQLA = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO & "0" &
LineItem & "A" & "')"

strSQLB = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO & "0" &
LineItem & "B" & "')"

db.Execute strSQLA
db.Execute strSQLB

End Select

End If



If LineItem > 9 Then

Select Case Qty

Case 1

strSQLA = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO &
LineItem & "A" & "')"

db.Execute strSQLA

Case 2

strSQLA = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO &
LineItem & "A" & "')"

strSQLB = "INSERT INTO tblTraveller (COM, Model,
Traveller)" _
& "VALUES('" & Com & "', '" & Model & "', ' " & PO &
LineItem & "B" & "')"

db.Execute strSQLA
db.Execute strSQLB

End Select

End If

End Function
 
But I need all the records in
another table to pass through the function...not sure how
to handle it..thanks:
Click to expand...

This should be doable in two simple sql statements without too much
difficulty: (this is not tested, so treat with care!)

INSERT INTO tblTraveller(Com, Model, Traveller)
SELECT Com,
Model,
PO & FORMAT(LineItem,"00") & "A"
FROM OtherTable

and then

INSERT INTO tblTraveller(Com, Model, Traveller)
SELECT Com,
Model,
PO & FORMAT(LineItem,"00") & "B"
FROM OtherTable
WHERE Qty>1

This assumes that Com, Model, PO, LineItem and Qty are fields in the
OtherTable. If not, it might be possible to pass them as parameters. There
are indeed things that cannot be done in SQL, but in general iterating
through large recordsets is likely to mean you're doing it the wrong way.

All the best


Tim F
 
Back
Top