UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Simplify Macro (module), Access 2010    
 
   
dzoker
post Aug 8 2018, 12:11 PM
Post#1



Posts: 388
Joined: 12-February 14



Hi All

I'm working on an old database that has the following module:

CODE
    ' 1
    DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[1] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=1));"
    ' 2
    DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[2] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=2));"
    ' 3
    DoCmd.RunSQL "UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[3] = [a]WHERE (((tblEmployeeInfo.Platoon)=""a"") AND ((a.autonumber)=3));"


Up to 31.

There are 6 of these modules that run one after another (UPDATE a, UPDATE b, c, d, e & f)

Is there a way to simplify this? As it is now it takes some time to run through all 6 of the modules with 600+ records!

Thank you in advance!
This post has been edited by dzoker: Aug 8 2018, 12:11 PM
Go to the top of the page
 
AlbertKallal
post Aug 8 2018, 12:37 PM
Post#2


UtterAccess VIP
Posts: 2,696
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ignoring that the existing code is designed wrong (it not normalized), you can do this:

CODE
   Dim strSQL       As String
   Dim strWhere     As String
   Dim strUpdate    As String
  
   Dim i As Integer

   dim strSQLU  as string
  
   strSQL = " UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID "
   strWhere = " WHERE (tblEmployeeInfo.Platoon)=""a"") AND (a.autonumber = @MyNum) "
   strUpate = " SET Employees.[@MyNum] = [a] "
    
  For i = 1 To 6
   strSQLU = strSQL & Replace(strWhere, "@MyNum", i) & Replace(strUpdate, "@MyNum", i)
   DoCmd.RunSQL strSQLU
  Next i


edit:
Above is only for "a". It not clear how many "[a]" updates you have. But above is for 1 to 6. If you have letters a to d, then you can add a new outer loop such as:

CODE
   Dim strSQL       As String
   Dim strWhere     As String
   Dim strUpdate    As String
  
   Dim i As Integer
  
   strSQL = " UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID "
   strWhere = " WHERE (tblEmployeeInfo.Platoon)= '@MyLetter' ) AND (a.autonumber = @MyNum) "
   strUpdate = " SET Employees.[@MyNum] = [@MyLetter] "
    
    
   Dim letLoop  As Variant
   Dim L As Variant
   letLoop = Split("a,b,c,d,e", ",")
  
    
  For Each L In letLoop
  For i = 1 To 6
   strUpdate = strSQL & Replace(Replace(strWhere, "@MyNum", i), "@MyLetter", L) & _
              Replace(Replace(strUpdate, "@MyNum", i), "@MyLetter", L)
   DoCmd.RunSQL strUpdate
  Next i


So above would do "a", and 1 to 6, and then "b" (1 to 6) all the way to e.



Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
dzoker
post Aug 8 2018, 01:14 PM
Post#3



Posts: 388
Joined: 12-February 14



AlbertKallal,

Thank you very much for your help. I'm getting an Run-time error 3144 on DoCmd.RunSQL strUpdate
Go to the top of the page
 
dzoker
post Aug 8 2018, 01:51 PM
Post#4



Posts: 388
Joined: 12-February 14



I think I need to explain a bit more. I converted the module to SQL and this is what I got:

CODE
UPDATE a, tblEmployeeInfo INNER JOIN Employees ON tblEmployeeInfo.ID = Employees.EMDID SET Employees.[1] = [a], Employees.[2] = [a], Employees.[3] = [a], Employees.[4] = [a], Employees.[5] = [a], Employees.[6] = [a], Employees.[7] = [a], Employees.[8] = [a], Employees.[9] = [a], Employees.[10] = [a], Employees.[11] = [a], Employees.[12] = [a], Employees.[13] = [a], Employees.[14] = [a], Employees.[15] = [a], Employees.[16] = [a], Employees.[17] = [a], Employees.[18] = [a], Employees.[19] = [a], Employees.[20] = [a], Employees.[21] = [a], Employees.[22] = [a], Employees.[23] = [a], Employees.[24] = [a], Employees.[25] = [a], Employees.[26] = [a], Employees.[27] = [a], Employees.[28] = [a], Employees.[29] = [a], Employees.[30] = [a], Employees.[31] = [a]
WHERE (((tblEmployeeInfo.Platoon)="a") AND (([a.autonumber])=31));

The a is a field in table named a, consisting (Date, a, autonumber)

Example:
Date a autonumber

8/22/2018 X 22
8/23/2018 X 23
8/24/2018 X 24
8/25/2018 25
8/26/2018 26
8/27/2018 27
8/28/2018 28
8/29/2018 29
8/30/2018 X 30
8/31/2018 X 31
This post has been edited by dzoker: Aug 8 2018, 01:54 PM
Go to the top of the page
 
BruceM
post Aug 9 2018, 07:22 AM
Post#5


UtterAccess VIP
Posts: 7,727
Joined: 24-May 10
From: Downeast Maine


QUOTE
The a is a field in table named a, consisting (Date, a, autonumber)

Is a the table name too?

I don't understand what you have between UPDATE and INNER JOIN. What is the "a," doing in there? Are you trying to set [1], [2], etc. to the value of the field [a], or to a literal value "a". If the former, in what table is [a]?
Go to the top of the page
 
dzoker
post Aug 13 2018, 10:07 AM
Post#6



Posts: 388
Joined: 12-February 14



BruceM,

QUOTE
Is a the table name too?

I don't understand what you have between UPDATE and INNER JOIN. What is the "a," doing in there? Are you trying to set [1], [2], etc. to the value of the field [a], or to a literal value "a". If the former, in what table is [a]?

Yes the name of the table is a and I'm trying to set [1], [2], etc. to the value of the field [a]

ty
Go to the top of the page
 
BruceM
post Aug 13 2018, 10:13 AM
Post#7


UtterAccess VIP
Posts: 7,727
Joined: 24-May 10
From: Downeast Maine


You have not joined any table to the table named a, so unless the table a has only one record there is no way to extract a specific value.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2018 - 06:53 AM