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] ?