A
Alex
Hi,
I'm having problems returning more than 1 record in a
recordset with Set rts = OpenRecordset(sql) code.
Private Sub AnimalGroupName_AfterUpdate()
Dim GroupNoRefID, FarmSpecificGroupNo, lngVisitRef As Long
Dim sqlGroupLocationMonthCheck, strQnID, strMonth,
sqlAppendNewMonths As String
Dim rstLocationGroupMonthCheck As Recordset
Dim d1, d2, d3 As Integer
MsgBox ("Now starting AfterUpdate events = APPEND Qry")
'Create GroupLocation data lines for this Animal Group
'Check what months' records are already in the table
strQnID = Me!QnID_AnimalGroup.Value
FarmSpecificGroupNo = Me!FarmSpecificAnimalGroup.Value
GroupNoRefID = Me!Text10.Value
lngVisitRef = Me!VisitRef_AnimalGroup.Value
'SQL for standard (add all 5 years worth in 1 go)
sqlGroupLocationMonthCheck = "SELECT
Key_StudyMonth.StudyMonth FROM Key_StudyMonth WHERE
(((Key_StudyMonth.StudyMonthID)>21));"
'Gives 51 months to paste records for - limited to
21 needed on 1st questionnaire at SubForm sql
'MsgBox ("find unmatching sql built successfully")
'Set rstLocationGroupMonthCheck =
CurrentDb.OpenRecordset(sqlGroupLocationMonthCheck)
Set rstLocationGroupMonthCheck =
CurrentDb.OpenRecordset(sqlGroupLocationMonthCheck)
d1 = rstLocationGroupMonthCheck.RecordCount
d2 = MsgBox(d1 & " months / periods need to be added
to that group's location summary", 4)
rstLocationGroupMonthCheck.MoveLast
d2 = MsgBox(d1 & " months / periods need to be added
to that group's location summary", 4)
If d2 = 7 Then
Exit Sub
End If
I've been looking through the messages - 1 came close to
the same problem (from another Alex not me) and suggested
solution was to use MoveLast to correct the RecordCount
function - this did not correct the problem, suggesting
that I'm missing some 'Options' in the OpenRecordset
method?
I'd really appreciate some help with this - I tried to
reply to a posting by Dirk Goldgar earlier, but computer
crashed so not sure if this will end up posted double -
apologies for that.
I'm having problems returning more than 1 record in a
recordset with Set rts = OpenRecordset(sql) code.
Private Sub AnimalGroupName_AfterUpdate()
Dim GroupNoRefID, FarmSpecificGroupNo, lngVisitRef As Long
Dim sqlGroupLocationMonthCheck, strQnID, strMonth,
sqlAppendNewMonths As String
Dim rstLocationGroupMonthCheck As Recordset
Dim d1, d2, d3 As Integer
MsgBox ("Now starting AfterUpdate events = APPEND Qry")
'Create GroupLocation data lines for this Animal Group
'Check what months' records are already in the table
strQnID = Me!QnID_AnimalGroup.Value
FarmSpecificGroupNo = Me!FarmSpecificAnimalGroup.Value
GroupNoRefID = Me!Text10.Value
lngVisitRef = Me!VisitRef_AnimalGroup.Value
'SQL for standard (add all 5 years worth in 1 go)
sqlGroupLocationMonthCheck = "SELECT
Key_StudyMonth.StudyMonth FROM Key_StudyMonth WHERE
(((Key_StudyMonth.StudyMonthID)>21));"
'Gives 51 months to paste records for - limited to
21 needed on 1st questionnaire at SubForm sql
'MsgBox ("find unmatching sql built successfully")
'Set rstLocationGroupMonthCheck =
CurrentDb.OpenRecordset(sqlGroupLocationMonthCheck)
Set rstLocationGroupMonthCheck =
CurrentDb.OpenRecordset(sqlGroupLocationMonthCheck)
d1 = rstLocationGroupMonthCheck.RecordCount
d2 = MsgBox(d1 & " months / periods need to be added
to that group's location summary", 4)
rstLocationGroupMonthCheck.MoveLast
d2 = MsgBox(d1 & " months / periods need to be added
to that group's location summary", 4)
If d2 = 7 Then
Exit Sub
End If
I've been looking through the messages - 1 came close to
the same problem (from another Alex not me) and suggested
solution was to use MoveLast to correct the RecordCount
function - this did not correct the problem, suggesting
that I'm missing some 'Options' in the OpenRecordset
method?
I'd really appreciate some help with this - I tried to
reply to a posting by Dirk Goldgar earlier, but computer
crashed so not sure if this will end up posted double -
apologies for that.