Running a Procedure

K

Kully121

New Member
Joined
Sep 21, 2004
Messages
44
Hi a friend has kindly written a procedure AppendDays() that will append a number of records to my master table for each day depending on how many entries are required in one day, my question is is how do i call/run this procedure?

See below

Function AppendDays(strDay As String)

Dim strData As String
strData = "SELECT [B - " & strDay & " Production Plan]." & strDay & ", [B - " & strDay & " Production Plan].PRODUCT, [A - Date Entry].Cycle, [B - " & strDay & " Production Plan].INGEST, [B - " & strDay & " Production Plan].EDIT, [B - " & strDay & " Production Plan].MDATA, [B - " & strDay & " Production Plan].[V/OVER], [B - " & strDay & " Production Plan].DELIV, [B - " & strDay & " Production Plan].TOTAL, [B - " & strDay & " Production Plan].OPERATOR, [B - " & strDay & " Production Plan].[OP CATGRY], [B - " & strDay & " Production Plan].[CAT ID], [B - " & strDay & " Production Plan].FOLDER, [B - " & strDay & " Production Plan].SOURCE1, [B - " & strDay & " Production Plan].SOURCE2, [B - " & strDay & " Production Plan].SOURCE3, [B - " & strDay & " Production Plan].SOURCE4, [B - " & strDay & " Production Plan].MON, [A - Date Entry].Date FROM [A - Date Entry] INNER JOIN [B - " & strDay & " Production Plan] ON [A - Date Entry].Day = [B - " & strDay & " Production Plan]." & strDay & ";"

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(strData)
Set rs2 = db.OpenRecordset("SELECT * FROM [Z - Final Production Plan]")

Dim i As Integer

Do While rs.EOF = False

i = rs.Fields(Left(strDay, 3))

For x = 1 To i
rs2.AddNew
rs2.Fields("Day Plan") = rs.Fields("Day Plan")
rs2.Fields("Product ID") = rs.Fields("Product ID")
rs2.Fields("Date") = rs.Fields("Date")
rs2.Fields("Cycle") = rs.Fields("Cycle")
rs2.Fields("Day") = rs.Fields("Day")
rs2.Fields("Ingest") = rs.Fields("Ingest")
rs2.Fields("Edit") = rs.Fields("Edit")
rs2.Fields("MData") = rs.Fields("MData")
rs2.Fields("V/OVER") = rs.Fields("V/OVER")
rs2.Fields("DELIV") = rs.Fields("DELIV")
rs2.Fields("TOTAL") = rs.Fields("TOTAL")
rs2.Fields("OPERATOR") = rs.Fields("OPERATOR")
rs2.Fields("OP CATGRY") = rs.Fields("OP CATGRY")
rs2.Fields("CAT ID") = rs.Fields("CAT ID")
rs2.Fields("FOLDER") = rs.Fields("FOLDER")
rs2.Fields("SOURCE1") = rs.Fields("SOURCE1")
rs2.Fields("SOURCE2") = rs.Fields("SOURCE2")
rs2.Fields("SOURCE3") = rs.Fields("SOURCE3")
rs2.Fields("SOURCE4") = rs.Fields("SOURCE4")
rs2.Fields("Monday") = rs.Fields("Monday")
rs2.Fields("Tuesday") = rs.Fields("Tuesday")
rs2.Fields("Wednesday") = rs.Fields("Wednesday")
rs2.Fields("Thursday") = rs.Fields("Thursday")
rs2.Fields("Friday") = rs.Fields("Friday")
rs2.Fields("Saturday") = rs.Fields("Saturday")
rs2.Fields("Sunday") = rs.Fields("Sunday")
rs2.Fields("Notes") = rs.Fields("Notes")
rs2.Fields("Qty") = rs.Fields("Qty")
rs2.Update
Next
rs.MoveNext
Loop

rs.Close
db.Close



'Debug.Print strData

End Function


Cheers
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi i now know how to run it but i am getting a compile error stating an 'Invalid Outside Procedure' on

Set db = CurrentDb() above, any ideas?

Cheers
 
Upvote 0
How are you running it?

I can't see anything in the posted code that would cause that error.
 
Upvote 0
Hi i am running the following procedure via a macro;

Option Compare Database

Function AppendDays(strDay As String)

Dim strData As String
strData = "SELECT [B - " & strDay & " Production Plan]." & strDay & ", [B - " & strDay & " Production Plan].PRODUCT, [A - Date Entry].Cycle, [B - " & strDay & " Production Plan].INGEST, [B - " & strDay & " Production Plan].EDIT, [B - " & strDay & " Production Plan].MDATA, [B - " & strDay & " Production Plan].[V/OVER], [B - " & strDay & " Production Plan].DELIV, [B - " & strDay & " Production Plan].TOTAL, [B - " & strDay & " Production Plan].OPERATOR, [B - " & strDay & " Production Plan].[OP CATGRY], [B - " & strDay & " Production Plan].[CAT ID], [B - " & strDay & " Production Plan].FOLDER, [B - " & strDay & " Production Plan].SOURCE1, [B - " & strDay & " Production Plan].SOURCE2, [B - " & strDay & " Production Plan].SOURCE3, [B - " & strDay & " Production Plan].SOURCE4, [B - " & strDay & " Production Plan].MON, [A - Date Entry].Date FROM [A - Date Entry] INNER JOIN [B - " & strDay & " Production Plan] ON [A - Date Entry].Day = [B - " & strDay & " Production Plan]." & strDay & ";"

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(strData)
Set rs2 = db.OpenRecordset("SELECT * FROM [Z - Final Production Plan]")

Dim i As Integer

Do While rs.EOF = False

i = rs.Fields(Left(strDay, 3))

For x = 1 To i
rs2.AddNew
rs2.Fields("Day Plan") = rs.Fields("Day Plan")
rs2.Fields("Product ID") = rs.Fields("Product ID")
rs2.Fields("Date") = rs.Fields("Date")
rs2.Fields("Cycle") = rs.Fields("Cycle")
rs2.Fields("Day") = rs.Fields("Day")
rs2.Fields("Ingest") = rs.Fields("Ingest")
rs2.Fields("Edit") = rs.Fields("Edit")
rs2.Fields("MData") = rs.Fields("MData")
rs2.Fields("V/OVER") = rs.Fields("V/OVER")
rs2.Fields("DELIV") = rs.Fields("DELIV")
rs2.Fields("TOTAL") = rs.Fields("TOTAL")
rs2.Fields("OPERATOR") = rs.Fields("OPERATOR")
rs2.Fields("OP CATGRY") = rs.Fields("OP CATGRY")
rs2.Fields("CAT ID") = rs.Fields("CAT ID")
rs2.Fields("FOLDER") = rs.Fields("FOLDER")
rs2.Fields("SOURCE1") = rs.Fields("SOURCE1")
rs2.Fields("SOURCE2") = rs.Fields("SOURCE2")
rs2.Fields("SOURCE3") = rs.Fields("SOURCE3")
rs2.Fields("SOURCE4") = rs.Fields("SOURCE4")
rs2.Fields("Monday") = rs.Fields("Monday")
rs2.Fields("Tuesday") = rs.Fields("Tuesday")
rs2.Fields("Wednesday") = rs.Fields("Wednesday")
rs2.Fields("Thursday") = rs.Fields("Thursday")
rs2.Fields("Friday") = rs.Fields("Friday")
rs2.Fields("Saturday") = rs.Fields("Saturday")
rs2.Fields("Sunday") = rs.Fields("Sunday")
rs2.Fields("Notes") = rs.Fields("Notes")
rs2.Fields("Qty") = rs.Fields("Qty")
rs2.Update
Next
rs.MoveNext
Loop

rs.Close
db.Close



'Debug.Print strData

End Function

it is coming up with a 'can't find the expression <strday>'

Any ideas, it think i may not specified the day of the week correctly in the procedure above?
 
Upvote 0
Sorry I still can't see how you are running this code.:)
 
Upvote 0
Hi,

i am running it from within a macro using the 'run code function'

it is coming up with a 'cant find <strday>'

This i guess should be the day of the week Monday,Tuesday etc

Any ideas?

Cheers,
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
run time error 3615 - type mismatch
Replies
2
Views
3K
Kully121
K

Forum statistics

Threads
1,215,461
Messages
6,124,952
Members
449,198
Latest member
MhammadishaqKhan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top