wellington.menezes
Member
Hi all,
First time poster.
I am having some issues getting some VBA code in FactoryTalk in hopes of to filter a acess data base. I have the code that works fine in RSView 32, but I am not sure what steps need to be taken in FactoryTalk. I will paste my current code at the bottom, and any insight on what steps need to be followed for this to work in FactoryTalk is much apprecitated.
Thanks in advance.
Private Sub Group43_Click()
Dim MyTagGroup As TagGroup
Dim wkMyWorkspace As Workspace
Dim dbMyDatabase As Database
Dim rsMyRecordset As Recordset
Dim sMyQuery As String
Dim sMyDBFile As String
Dim sMyPartData1 As String
Dim sMyPartData2 As String
Dim DataI, DataF As Date
Dim DiaII, MesII, AnoII, DiaFF, MesFF, AnoFF As String
Dim InicialP, FinalP As String
sMyDBFile = gProject.Path & "\ODBC\REPORT.mdb"
'Open specified database
Set wkMyWorkspace = Workspaces(0)
Set dbMyDatabase = wkMyWorkspace.OpenDatabase(sMyDBFile)
If MyTagGroup Is Nothing Then
'Create a tag group based on the Area that the Display is located in
Set MyTagGroup = Application.CreateTagGroup(Me.AreaName)
'Add tags to tag group
MyTagGroup.Add "REPORT\DATAINICIO"
MyTagGroup.Add "REPORT\DATAFINAL"
End If
sMyPartData1 = MyTagGroup.Item("REPORT\DATAINICIO").Value sMyPartData2 = MyTagGroup.Item("REPORT\DATAFINAL").Value
'The Day and the month must be changed due to the fact Microsoft Access (BETWEEN INSTRUCTION)
'works with them changed
DataI = DateValue(sMyPartData1)
DataF = DateValue(sMyPartData2)
DiaII = Day(DataI)
MesII = Month(DataI)
AnoII = Year(DataI)
DiaFF = Day(DataF)
MesFF = Month(DataF)
AnoFF = Year(DataF)
InicialP = MesII & "/" & DiaII & "/" & AnoII
FinalP = MesFF & "/" & DiaFF & "/" & AnoFF
'SQL Query
sMyQuery = "SELECT * FROM GERAL " & _ "WHERE (((GERAL.DATAINICIO) Between # " & InicialP & " # And # " &
FinalP & " #)) ORDER BY GERAL.CODIGO;"
'Query DB
Set rsMyRecordset = dbMyDatabase.OpenRecordset(sMyQuery)
'Clear the Combobox sMyCommand = "invoke REPORT.combobox1.clear()" ExecuteCommand ("sMyCommand")
Do While Not rsMyRecordset.EOF
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #1 - " & Trim(rsMyRecordset.Fields(109).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #2 - " & Trim(rsMyRecordset.Fields(110).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #3 - " & Trim(rsMyRecordset.Fields(111).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #5 - " & Trim(rsMyRecordset.Fields(113).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #6 - " & Trim(rsMyRecordset.Fields(159).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #7 - " & Trim(rsMyRecordset.Fields(160).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""---------------------------------------------------------------------------------------"")"
ExecuteCommand ("sMyCommand") rsMyRecordset.MoveNext
'Loop until EOF (End of File) of query
Loop
End Sub
First time poster.
I am having some issues getting some VBA code in FactoryTalk in hopes of to filter a acess data base. I have the code that works fine in RSView 32, but I am not sure what steps need to be taken in FactoryTalk. I will paste my current code at the bottom, and any insight on what steps need to be followed for this to work in FactoryTalk is much apprecitated.
Thanks in advance.
Private Sub Group43_Click()
Dim MyTagGroup As TagGroup
Dim wkMyWorkspace As Workspace
Dim dbMyDatabase As Database
Dim rsMyRecordset As Recordset
Dim sMyQuery As String
Dim sMyDBFile As String
Dim sMyPartData1 As String
Dim sMyPartData2 As String
Dim DataI, DataF As Date
Dim DiaII, MesII, AnoII, DiaFF, MesFF, AnoFF As String
Dim InicialP, FinalP As String
sMyDBFile = gProject.Path & "\ODBC\REPORT.mdb"
'Open specified database
Set wkMyWorkspace = Workspaces(0)
Set dbMyDatabase = wkMyWorkspace.OpenDatabase(sMyDBFile)
If MyTagGroup Is Nothing Then
'Create a tag group based on the Area that the Display is located in
Set MyTagGroup = Application.CreateTagGroup(Me.AreaName)
'Add tags to tag group
MyTagGroup.Add "REPORT\DATAINICIO"
MyTagGroup.Add "REPORT\DATAFINAL"
End If
sMyPartData1 = MyTagGroup.Item("REPORT\DATAINICIO").Value sMyPartData2 = MyTagGroup.Item("REPORT\DATAFINAL").Value
'The Day and the month must be changed due to the fact Microsoft Access (BETWEEN INSTRUCTION)
'works with them changed
DataI = DateValue(sMyPartData1)
DataF = DateValue(sMyPartData2)
DiaII = Day(DataI)
MesII = Month(DataI)
AnoII = Year(DataI)
DiaFF = Day(DataF)
MesFF = Month(DataF)
AnoFF = Year(DataF)
InicialP = MesII & "/" & DiaII & "/" & AnoII
FinalP = MesFF & "/" & DiaFF & "/" & AnoFF
'SQL Query
sMyQuery = "SELECT * FROM GERAL " & _ "WHERE (((GERAL.DATAINICIO) Between # " & InicialP & " # And # " &
FinalP & " #)) ORDER BY GERAL.CODIGO;"
'Query DB
Set rsMyRecordset = dbMyDatabase.OpenRecordset(sMyQuery)
'Clear the Combobox sMyCommand = "invoke REPORT.combobox1.clear()" ExecuteCommand ("sMyCommand")
Do While Not rsMyRecordset.EOF
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #1 - " & Trim(rsMyRecordset.Fields(109).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #2 - " & Trim(rsMyRecordset.Fields(110).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #3 - " & Trim(rsMyRecordset.Fields(111).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #5 - " & Trim(rsMyRecordset.Fields(113).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #6 - " & Trim(rsMyRecordset.Fields(159).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""DATE: " & Trim(rsMyRecordset.Fields(2).Value) & " - ID #7 - " & Trim(rsMyRecordset.Fields(160).Value) & " COD - " & Trim(rsMyRecordset.Fields(0).Value) & ")"
ExecuteCommand ("sMyCommand")
sMyCommand = "invoke REPORT.combobox1.additem(""---------------------------------------------------------------------------------------"")"
ExecuteCommand ("sMyCommand") rsMyRecordset.MoveNext
'Loop until EOF (End of File) of query
Loop
End Sub