To all Guru of Access
I am totally green in VBA world and trying to learn therefore, need special help from Access’s Guru
What I am trying to learn, to filter the record based on combo box value and find the unmatched data between two tables, instead of using in build query, I am trying to use VBA
Table 1
Tbl(1)field1
Tbl(1)field2
Sample Data
CAD = Canadian Dollar
USD = American Dollar
AUD = Australian Dollar
GBP = British Pound
Eur = Euro
Table 2
Tbl(2)field1
Tbl(2)field2 = Yes/No
Tbl(2)field3 = Tbl(1)field1
Tbl(2)field4
Sample Data
2003 = Yes = CAD
2003 = No = USD
2003 = No = AUD
What I am trying to learn using VBA to see the unmatched currency codes (i.e. GBP and EUR ONLY), upon selection of “2003”
There’re 3 filters
1st combo box where I am selecting YEAR in this case “2003”
2nd list box where I am showing what I have already in Table 2 against 2003 (i.e. CAD/USD/AUD)
3rd list box where I want to show unmatched currency codes (i.e. GBP and EUR)
Hope I am able to explain the situation
Below is my attempt but No luck List box is show nothing nor I am getting any error
First Combo Box (working perfectly) – I learn from form (thanks again)
Private Sub Form_Open(Cancel As Integer)
Dim sql1 As String
sql1 = "SELECT Table2.field1, Table2.field2,Table2.field3, Table2.field4 " & _
"FROM Table2 " & _
"WHERE (((Table2.field2)=Yes));"
Me.listcurbasecurr.RowSource = sql1
Me.listcurbasecurr.Requery
End Sub
Second List Box (working perfectly), as only want to show record with “NO”
Private Sub listcurbasecurr_AfterUpdate()
Dim sql2 As String
sql2 = "SELECT Table2.field1, Table2.field2, Table2.field3 " & _
"FROM Table2 " & _
"WHERE (((Table2.field1])=[forms]![updatebudfycurr]![listcurbasecurr]) AND ((Table2.field2)=No));" ‘=first filter above
Me.listfycurr.RowSource = sql2
Me.listfycurr.Requery
Here’s where I am totally blind/lost – Need GURUs Help
Third List Box (nothing is happen), as I want to show unmatched record (regardless of Yes or No)
Dim sql3 As String
Dim sql4 As String
First I am generating so all record appear regarding less of Yes or NO (I am not am i doing right or wrong or there would be better ways)
sql3 = "SELECT Table2.field1, Table2.field3 (referring as field2 below)" & _
"FROM Table2 " & _
"WHERE (((Table2.field1)=[forms]![updatebudfycurr]![listcurbasecurr]));" ‘= first filter above
'*********
Here I am trying to link the SQL3 and Table 1
sql4 = "SELECT Table1.* " & _
"FROM Table1 LEFT JOIN [sql3] ON Table1.field1 = [sql3].field2 " & _
"WHERE ((([sql3].field2) Is Null));"
Me.listavailablecurr.RowSource = sql4
Me.listavailablecurr.Requery
Thanks in advance
I am totally green in VBA world and trying to learn therefore, need special help from Access’s Guru
What I am trying to learn, to filter the record based on combo box value and find the unmatched data between two tables, instead of using in build query, I am trying to use VBA
Table 1
Tbl(1)field1
Tbl(1)field2
Sample Data
CAD = Canadian Dollar
USD = American Dollar
AUD = Australian Dollar
GBP = British Pound
Eur = Euro
Table 2
Tbl(2)field1
Tbl(2)field2 = Yes/No
Tbl(2)field3 = Tbl(1)field1
Tbl(2)field4
Sample Data
2003 = Yes = CAD
2003 = No = USD
2003 = No = AUD
What I am trying to learn using VBA to see the unmatched currency codes (i.e. GBP and EUR ONLY), upon selection of “2003”
There’re 3 filters
1st combo box where I am selecting YEAR in this case “2003”
2nd list box where I am showing what I have already in Table 2 against 2003 (i.e. CAD/USD/AUD)
3rd list box where I want to show unmatched currency codes (i.e. GBP and EUR)
Hope I am able to explain the situation
Below is my attempt but No luck List box is show nothing nor I am getting any error
First Combo Box (working perfectly) – I learn from form (thanks again)
Private Sub Form_Open(Cancel As Integer)
Dim sql1 As String
sql1 = "SELECT Table2.field1, Table2.field2,Table2.field3, Table2.field4 " & _
"FROM Table2 " & _
"WHERE (((Table2.field2)=Yes));"
Me.listcurbasecurr.RowSource = sql1
Me.listcurbasecurr.Requery
End Sub
Second List Box (working perfectly), as only want to show record with “NO”
Private Sub listcurbasecurr_AfterUpdate()
Dim sql2 As String
sql2 = "SELECT Table2.field1, Table2.field2, Table2.field3 " & _
"FROM Table2 " & _
"WHERE (((Table2.field1])=[forms]![updatebudfycurr]![listcurbasecurr]) AND ((Table2.field2)=No));" ‘=first filter above
Me.listfycurr.RowSource = sql2
Me.listfycurr.Requery
Here’s where I am totally blind/lost – Need GURUs Help
Third List Box (nothing is happen), as I want to show unmatched record (regardless of Yes or No)
Dim sql3 As String
Dim sql4 As String
First I am generating so all record appear regarding less of Yes or NO (I am not am i doing right or wrong or there would be better ways)
sql3 = "SELECT Table2.field1, Table2.field3 (referring as field2 below)" & _
"FROM Table2 " & _
"WHERE (((Table2.field1)=[forms]![updatebudfycurr]![listcurbasecurr]));" ‘= first filter above
'*********
Here I am trying to link the SQL3 and Table 1
sql4 = "SELECT Table1.* " & _
"FROM Table1 LEFT JOIN [sql3] ON Table1.field1 = [sql3].field2 " & _
"WHERE ((([sql3].field2) Is Null));"
Me.listavailablecurr.RowSource = sql4
Me.listavailablecurr.Requery
Thanks in advance
