1) As documented in Help, you must call a function from RunCode. It is true that a function *can* return a value, but RunCode will ignore it. That's just one of the weaknesses of macros. If you need to test the return value, you must call the function from an expression in the Condition column.
2) Your code can't possibly run. First, the SQL is invalid. You cannot mix an Append and Make Table query - INSERT INTO ... SELECT ... INTO ... FROM. You are running the loop until the variable f (not declared) is greater than 5270, but you're not incrementing f, and I don't see where you set its initial value. You're using R inside the loop, but that isn't set anywhere, either. I would think you want to execute each SQL statement inside the loop right after you build it. What is the business problem you're trying to solve? It seems very strange to pre-load some table with a series of region number records. And your table is clearly not normalized if it contains a repeating group of account numbers. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of pinar_akturk Sent: Wednesday, December 21, 2005 1:14 AM To: John Viescas Subject: Using Sub Procedure in A Macro Hi, Thank you for the quick answer. (: 1) Actually, I' ve done what you suggest. Change Sub to function, call it in the macro as a function. It seems it is working but in the Access help files, it says funtion is used to return values, where sub is used for procedures. I just wanted to make sure if the values that returns would be correct. By the way, in these codes within my Sub, tables are created, updated, new records added to several tables. 2)The values are the region numbers. I will be assigning these region numbers in sequence to a Dim R as Integer Dim SQL as String Do While f < 5271 SQL = ""INSERT INTO TGMIZAN ( KOD, ACCOUNTNO1, ACCOUNTNO2, ACCOUNTNO3, ACCOUNTNO4, ACCOUNT1,ACCOUNT2, ACCOUNT3, ACCOUNT4, ACCOUNTNO, ACCOUNTNAME)" "SELECT " & R & " AS KOD, [K-" & R & "].ACCOUNTNO1,[K-" & R & "].ACCOUNTNO2,[K-" & R & "].ACCOUNTNO3, [K-" & R & "].ACCOUNTNO4, [K-" & R & "].ACCOUNT1, [K-" & R & "].ACCOUNT2, [K-" & R & "].ACCOUNT3, [K-" & R & "].ACCOUNT4, [K-" & R & "].ACCOUNTNO, [K-" & R & "].ACCOUNTNAME INTO TGMIZAN FROM [K-" & R & "];" 'HERE, I NEED TO BE CHANGING THE REGION NUMBER... OR I NEED TO CALL THE REGION NUMBERS OUTSIDE THE DO WHILE LOOP?' Loop DoCmd.RunSQL SQL This is a simplified version of a part of my sub. Here as the region number changes, new values from the relevant table are appended to a new table.(table name: K-region number. e.g. region number: 5200, then table name: K-5200) As the region numbers are not like 5200, 5201, 5202, 5203 etc., I need to get them from the region table. From: "John Viescas" <[EMAIL PROTECTED]> Date: Tue Dec 20, 2005 6:26 am Subject: RE: [ms_access] Using Sub Procedure in A Macro Any reason the code you have already needs to be a Sub? You could simply change it from Sub to Function and then you can call it from a macro. If it must remain a Sub (because lots of other code already calls it), then create a "wrapper" function: Public Function CallMySub() Call MySub End Function .. where "MySub" is the name of your public Sub procedure. Need more details about the table name and field names and how you want to fetch a "variable" to do calculations. What are the calculations? What are you doing with the calculated value(s)? John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of pinar_akturk Sent: Tuesday, December 20, 2005 7:34 AM To: [email protected] Subject: [ms_access] Using Sub Procedure in A Macro Hello guys, I hope everyone is OK. I have two questions for you if you can help. 1) I' ve been trying to run some "Sub" procedures in a Macro. I searched through the help files, I found out that we can run a "function" procedure with "RunCode" action in a macro. And there also it says to run a "Sub" procedure in a macro, call it in a function. But I couldn' t figure out how to do that. 2) I want to use the values in a tables (in my database) to use as a variable. I have to call these values in the procedure. I figured I can do that with "OpenRecordSet", but don' t know how to do it in detail. Let say that I put these tables values to a variable called. How can I change this variable in a "Do While .... Loop" ? I would appreciate any help. Thanks a lot in advance! ( : Pýnar ------------------------ Yahoo! Groups Sponsor --------------------~--> AIDS in India: A "lurking bomb." Click and help stop AIDS now. http://us.click.yahoo.com/9QUssC/lzNLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
