Call a function

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

quixote

I asked this yesterday but didn't really understand the
answers. I have the following abbreviated function
created. I need the input values to be values from another
table, but I'm not sure how to do this. I would like to
automate the task via macro or whatever. The function
populates a table. I know what I want to do but am not
sure of how.

Logic I want to do:
Go through each record in myTable and insert
myTable.Field1, myTable.Field2, myTable.Field3, etc into
the following Function:



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
 
It might be simpler to do this using a single INSERT SQL statement that
looks something like this:

INSERT INTO
tblTraveller
(
COM,
Model,
Traveller
)
SELECT
myTable.Field1,
myTable.Field2,
myTable.Field4 & Format(Field3, "00") & Chr(Asc("A") + Self.Field5 - 1)
FROM
myTable
INNER JOIN
myTable AS Self
ON
myTable.Field1 = Self.Field1
AND
myTable.Field2 = Self.Field2
AND
myTable.Field3 = Self.Field3
AND
myTable.Field4 = Self.Field4
WHERE
myTable.Field5 = 1
AND
Self.Field5 <= myTable.Field5

This assumes Field1 is the COM, Field2 is the Model, Field3 is the LineItem,
Field4 is the PO, Field5 is the Qty, and the combination of the first four
fields is unique.

However, are you sure you want to combine different pieces of information
into one field (Traveller)? This denormalizes your data, and makes it
harder to get the information back later if you need to. For example, how
would you query tblTraveller by PO, particularly if the PO was not a fixed
number of characters?
 
Thanks Brian,
The Traveller table is temporary. Info is deleted when I
am done with it(I can access the info elsewhere if
needed). I have a query that hits our ERP that gathers
Open PO's(Purchase orders). I have another query that
gathers Model information....these combined make a table
with Open PO/Model info. This is the table I get info from
to make travellers. Someone here is manually making labels
for each unit on a PO. This is a lot of clerical work. My
task is to automate the label making(eventually dump info
into excel and use a label making program...this is the
easy part). On a PO there can be no more than 99 line
items and 26 items per line. So the traveller is a number
on a label that identifies and travels with a "unit" on
the production line. The PO is always a fixed number. Here
is an example of the output and what my code is doing:

PO: 123456
Line: 3
Qty: 4

Then the travellers for these items would be
12345603A
12345603B
12345603C
12345603D

The unit label has this info:
COM MODEL TRAVELLER#


How could I use the logic below for this because of the
conditional statement, if the line item is less than 10
then I have to concatenate a 0 to it? I'm lost a bit also
with the where clause.

Thanks
quixote
 
quixote said:
I asked this yesterday but didn't really understand the
answers. I have the following abbreviated function
created. I need the input values to be values from another
table, but I'm not sure how to do this. I would like to
automate the task via macro or whatever. The function
populates a table. I know what I want to do but am not
sure of how.

Logic I want to do:
Go through each record in myTable and insert
myTable.Field1, myTable.Field2, myTable.Field3, etc into
the following Function:
Click to expand...
Public Function GetLetter(Com, Model, LineItem, PO, Qty)
Click to expand...

quixote,

here's a routine (beware, untested!):

Sub ProcessMyTable()

On Error GoTo ErrProcessMyTable

Dim db As DAO.Database
Dim rs As DAO.recordset

Set db = CurrentDB
'Load a Recordset with all records from myTable
Set rs = db.OpenRecordset("myTable", dbOpenSnapshot)

'See if there are records. If yes, proceed
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

'Loop over all records and call the function
'for each one
Do Until rs.EOF

'Make sure you pass the fields in the correct order!
'(i.e. MyField1 for Com, MyField2 for Model,
'MyField3 for LineItem etc.)
Call GetLetter(rs!MyField1, rs!MyField2, rs!MyField3,
rs!MyField4, rs!MyField5)
rs.MoveNext
Loop
End If

ExitProcessMyTable:
On Error Resume Next
'Clean up before leaving
rs.Close
Set rs = nothing
Set db= Nothing
Exit Sub

ErrProcessMyTable:
MsgBox Err.Number & ": " & Err.description, vbOKOnly + vbCritical,
"ProcessMyTable)
Resume ExitProcessMyTable

End Sub

You can call this Sub by clicking a command button for ex. The OnClick
event procedure would look like:

Call ProcessMyTable

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
You rule.
-----Original Message-----



quixote,

here's a routine (beware, untested!):

Sub ProcessMyTable()

On Error GoTo ErrProcessMyTable

Dim db As DAO.Database
Dim rs As DAO.recordset

Set db = CurrentDB
'Load a Recordset with all records from myTable
Set rs = db.OpenRecordset("myTable", dbOpenSnapshot)

'See if there are records. If yes, proceed
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst

'Loop over all records and call the function
'for each one
Do Until rs.EOF

'Make sure you pass the fields in the correct order!
'(i.e. MyField1 for Com, MyField2 for Model,
'MyField3 for LineItem etc.)
Call GetLetter(rs!MyField1, rs!MyField2, rs! MyField3,
rs!MyField4, rs!MyField5)
rs.MoveNext
Loop
End If

ExitProcessMyTable:
On Error Resume Next
'Clean up before leaving
rs.Close
Set rs = nothing
Set db= Nothing
Exit Sub

ErrProcessMyTable:
MsgBox Err.Number & ": " & Err.description, vbOKOnly + vbCritical,
"ProcessMyTable)
Resume ExitProcessMyTable

End Sub

You can call this Sub by clicking a command button for ex. The OnClick
event procedure would look like:

Call ProcessMyTable

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
.
Click to expand...
 
My comments are embedded below.

quixote said:
Thanks Brian,
The Traveller table is temporary. Info is deleted when I
am done with it(I can access the info elsewhere if
needed). I have a query that hits our ERP that gathers
Open PO's(Purchase orders). I have another query that
gathers Model information....these combined make a table
with Open PO/Model info. This is the table I get info from
to make travellers. Someone here is manually making labels
for each unit on a PO. This is a lot of clerical work. My
task is to automate the label making(eventually dump info
into excel and use a label making program...this is the
easy part). On a PO there can be no more than 99 line
items and 26 items per line. So the traveller is a number
on a label that identifies and travels with a "unit" on
the production line. The PO is always a fixed number. Here
is an example of the output and what my code is doing:

PO: 123456
Line: 3
Qty: 4

Then the travellers for these items would be
12345603A
12345603B
12345603C
12345603D

The unit label has this info:
COM MODEL TRAVELLER#
Click to expand...

In that case, you might instead do what you want with the help of another
table (say, named "Item Codes") that looks something like this:

Item Number, Item Code
1,A
2,B
3,C
..
..
..
26,Z

and a revised query whose SQL looks something like this:

INSERT INTO
tblTraveller
(
COM,
Model,
Traveller
)
SELECT
myTable.Field1,
myTable.Field2,
myTable.Field4 & Format(myTable.Field3, "00") & [Item Codes].[Item Code]
FROM
myTable,
[Item Codes]
WHERE
[Item Codes].[Item Number] <= [myTable].[Field5]
How could I use the logic below for this because of the
conditional statement, if the line item is less than 10
then I have to concatenate a 0 to it?
Click to expand...

That's what

Format(Field3, "00")

was meant to do. This actually should have been

Format(myTable.Field3, "00")

since there were two tables (actually two instances of the same table) in
the FROM clause that have a Field3. In the new SQL statement I suggested
above this is no longer the case.
 
Thank man....much appreciated. I think I'm on my way now..
-----Original Message-----
My comments are embedded below.

quixote said:
Thanks Brian,
The Traveller table is temporary. Info is deleted when I
am done with it(I can access the info elsewhere if
needed). I have a query that hits our ERP that gathers
Open PO's(Purchase orders). I have another query that
gathers Model information....these combined make a table
with Open PO/Model info. This is the table I get info from
to make travellers. Someone here is manually making labels
for each unit on a PO. This is a lot of clerical work. My
task is to automate the label making(eventually dump info
into excel and use a label making program...this is the
easy part). On a PO there can be no more than 99 line
items and 26 items per line. So the traveller is a number
on a label that identifies and travels with a "unit" on
the production line. The PO is always a fixed number. Here
is an example of the output and what my code is doing:

PO: 123456
Line: 3
Qty: 4

Then the travellers for these items would be
12345603A
12345603B
12345603C
12345603D

The unit label has this info:
COM MODEL TRAVELLER#
Click to expand...

In that case, you might instead do what you want with the help of another
table (say, named "Item Codes") that looks something like this:

Item Number, Item Code
1,A
2,B
3,C
..
..
..
26,Z

and a revised query whose SQL looks something like this:

INSERT INTO
tblTraveller
(
COM,
Model,
Traveller
)
SELECT
myTable.Field1,
myTable.Field2,
myTable.Field4 & Format(myTable.Field3, "00") & [Item Codes].[Item Code]
FROM
myTable,
[Item Codes]
WHERE
[Item Codes].[Item Number] <= [myTable].[Field5]
How could I use the logic below for this because of the
conditional statement, if the line item is less than 10
then I have to concatenate a 0 to it?
Click to expand...

That's what

Format(Field3, "00")

was meant to do. This actually should have been

Format(myTable.Field3, "00")

since there were two tables (actually two instances of the same table) in
the FROM clause that have a Field3. In the new SQL statement I suggested
above this is no longer the case.
I'm lost a bit also
with the where clause.

Thanks
quixote












SQL
statement that combination
of the first four data,
and makes it to.
For example, how
Click to expand...


.
Click to expand...
 
Back
Top