A
Abes
In my database I have this problem that I am trying to get my head around:
I have a field 'W1' in a table called 'Type1', and this field has 8 specific
records added (updated) at a time. I wish to update records in field 'W2' in
table 'Type2' with the values in [Type1]![W1].
Each of the records in may occur in table 'Type2' many times.
[RNo] [GNo] [Type1]![W1] [RNo] [GNO] [Type2]![W2]
1 1 Red 1 1 Red
1 1 Red
1 1 Red
1 2 Yellow 1 2 Yellow
1 2 Yellow
1 2 Yellow
1 3 Blue 1 3 Blue
1 3 Blue
1 3 Blue
and so on.
I have been trying some 'code' to collect the data from [tblType1]![W1],
[RNo] and [GNo] which is on the subform with the command button to update the
records, and then update [tblType2]![W2], where [RNo] and [GNo] are the same,
but can only seem to gather the first of the 8 records, and cannot seem to
loop both 'queries' together.
This is where I am at so far:
Private Sub cmdTest_Click()
Dim db As DAO.Database
Dim CountRst As DAO.Recordset
Dim AddRst As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim txtW1 As String
Dim txtRNo As String
Dim txtGNo As String
'Open the current database
Set db = CurrentDb
txtW1 = Me.txtW1.Value
txtRNo = Me.txtRNo.Value
txtGNo = Me.txtGNo.Value
Debug.Print txtW1
Debug.Print txtRNo
Debug.Print txtGNo
' Get a recordset listing the all the round records
strSQL = "SELECT tblType2.RNo, Count(tblType2.RNo) AS CountofRNo " & _
"FROM tblType2 " & _
"GROUP BY tblType2.RNo " & _
"HAVING (((Count(tblType2.RNo))>1));"
Debug.Print strSQL
Set CountRst = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Loop over each set one by one,
'While (Not CountRst.EOF)
Do Until CountRst.EOF
strSQL2 = "UPDATE tblType2 SET tblType2.W2 = '" & txtW1 & " '" & _
"WHERE (([tblType2]![RNo]= '" & txtRNo & " ' )) " & _
"AND (([tblType2]![GNo]= '" & txtGNo & "' ));"
Debug.Print strSQL2
CountRst.MoveNext
Loop
The code above seems to select the correct values for the first of the 8
unique records in TblType1, but then strSQL2 seems to want to 'update' 27
records in tblType2 where there are only actually 15 records (so far) to
update, and I am not sure what the difference is.
Then I need assistance to 'loop' through the remaining 7 unique records of
TblType1. The 8 unique records of TblType1 are displayed on this subform.
Any guidance would be greatly appreciated.
I have a field 'W1' in a table called 'Type1', and this field has 8 specific
records added (updated) at a time. I wish to update records in field 'W2' in
table 'Type2' with the values in [Type1]![W1].
Each of the records in may occur in table 'Type2' many times.
[RNo] [GNo] [Type1]![W1] [RNo] [GNO] [Type2]![W2]
1 1 Red 1 1 Red
1 1 Red
1 1 Red
1 2 Yellow 1 2 Yellow
1 2 Yellow
1 2 Yellow
1 3 Blue 1 3 Blue
1 3 Blue
1 3 Blue
and so on.
I have been trying some 'code' to collect the data from [tblType1]![W1],
[RNo] and [GNo] which is on the subform with the command button to update the
records, and then update [tblType2]![W2], where [RNo] and [GNo] are the same,
but can only seem to gather the first of the 8 records, and cannot seem to
loop both 'queries' together.
This is where I am at so far:
Private Sub cmdTest_Click()
Dim db As DAO.Database
Dim CountRst As DAO.Recordset
Dim AddRst As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim txtW1 As String
Dim txtRNo As String
Dim txtGNo As String
'Open the current database
Set db = CurrentDb
txtW1 = Me.txtW1.Value
txtRNo = Me.txtRNo.Value
txtGNo = Me.txtGNo.Value
Debug.Print txtW1
Debug.Print txtRNo
Debug.Print txtGNo
' Get a recordset listing the all the round records
strSQL = "SELECT tblType2.RNo, Count(tblType2.RNo) AS CountofRNo " & _
"FROM tblType2 " & _
"GROUP BY tblType2.RNo " & _
"HAVING (((Count(tblType2.RNo))>1));"
Debug.Print strSQL
Set CountRst = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Loop over each set one by one,
'While (Not CountRst.EOF)
Do Until CountRst.EOF
strSQL2 = "UPDATE tblType2 SET tblType2.W2 = '" & txtW1 & " '" & _
"WHERE (([tblType2]![RNo]= '" & txtRNo & " ' )) " & _
"AND (([tblType2]![GNo]= '" & txtGNo & "' ));"
Debug.Print strSQL2
CountRst.MoveNext
Loop
The code above seems to select the correct values for the first of the 8
unique records in TblType1, but then strSQL2 seems to want to 'update' 27
records in tblType2 where there are only actually 15 records (so far) to
update, and I am not sure what the difference is.
Then I need assistance to 'loop' through the remaining 7 unique records of
TblType1. The 8 unique records of TblType1 are displayed on this subform.
Any guidance would be greatly appreciated.