Results 1 to 2 of 2
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Making SQL Query code into VBA

    I am trying to put an query SQL into a VBA so I can do a for next count and not have to repeat 15 same queries with 1 Field difference.

    This:
    Code:
    SELECT Table1.[Role / FE / Node ID], IIf([Subnets]<>"",[Equip HB Name] & " [" & [Networks] & "]",[Equip HB Name]) AS EquipHB, IIf([materiel_text_coloring]<>""," ;;" & [materiel_text_coloring] & ";"," ;;;") AS MatTextColor, [EquipHB] & [MatTextColor] AS EquipHBName
    FROM Table1
    WHERE (((Table1.DisEquipSeq)=1));

    AND This
    Code:
    UPDATE qryDismounted1 LEFT JOIN Table2 ON qryDismounted1.RoleID = Table2.Visio_ID SET Table2.Equip_1 = [qryDismounted1].[EquipHBName];
    to something like this?:



    Code:
     Dim rS As DAO.Recordset, HB As DAO.Recordset
    Dim db As DAO.Database
    Dim Last_Col_Num As Long
    Dim C As Integer
    Dim strEquipHB As String, strEquipHBName As String, strMatTextColor As String, strRole As String
    Set db = CurrentDb()
    Set HB = CurrentDb.OpenRecordset("Table2") ' Opens the table
    Set rS = CurrentDb.OpenRecordset("Table1") ' Opens the table
        DoCmd.SetWarnings False ' Alerts don't show
        Last_Col_Num = CurrentDb.TableDefs("Table1").Fields.Count 'Determines the last column by number
    C = 1
        For C = C To (Last_Col_Num - 3) 'Adds columns - Equip 1 to Equip N
    Do While Not rS.EOF And HB.EOF
        HB.Edit
        If Not IsNull(rS![DisEquipSeq]) Then
            If rS![DisEquipSeq] = C Then
                strRole = rS![Role / FE / Node ID]
                If rS![Subnets] <> "" Then ' Determines if there is a network of some type
                    strEquipHB = rS![Equip HB Name] & " [" & rS![Networks] & "]"
                        Else: strEquipHB = rS![Equip HB Name]
                    If rS![materiel_text_coloring] <> "" Then ' 
    Determines Text color
    strMatTextColor = " ;;" & [materiel_text_coloring] & ";" Else: strMatTextColor = " ;;;" End If End If End If End If strEquipHBName = strEquipHB & strMatTextColor HB!["Equip_" & C] = strEquipHBName HB.Update Loop Next C HB.Close rS.Close DoEvents Set rS = Nothing Set HB = Nothing



    How do I "JOIN" the
    strRole = rS![Role / FE / Node ID] to HB.[Visio_ID] ?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would use a loop in VBA from 1 to your equipment # (40) to manipulate two string variables (corresponding to your two queries - the first select and the second update one) and use Currentdb.execute sSQLUpdate, dbFailonError to run the update.

    Code:
    dim sSQLSelect as string,sSQLUpdate as string,iCount as integer
    
    for icount=1 to 40
    
    'set your two strings here
    
    
    Currentdb.execute sSQLUpdate, dbFailonError
    next iCount

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making code wait/pause until event
    By faythe1215 in forum Programming
    Replies: 3
    Last Post: 02-09-2015, 03:44 PM
  2. Replies: 2
    Last Post: 09-15-2014, 01:51 PM
  3. Replies: 1
    Last Post: 10-15-2013, 10:41 AM
  4. Making sure code runs regardless
    By nvrwrkn in forum Programming
    Replies: 2
    Last Post: 10-09-2012, 11:39 AM
  5. Need help making my code more efficient
    By themidnitereign in forum Programming
    Replies: 3
    Last Post: 08-10-2012, 11:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums