fluffyozzy
Registered User.
- Local time
- Today, 16:54
- Joined
- May 29, 2004
- Messages
- 63
Hi everyone,
I'm having a bit of a brain-freeze here and hope someone can help me. This loop does not seem to be working, could you please cast your experienced eyes over it and see why? It is getting stuck at rs.FindFirst strFind line. It's likely to be a really rookie mistake as I'm kinda rusty not having done this sort of work for a while. Basically what I'm trying to do is to loop through the master file (import from an Excel sheet, not normalised) and see if those values already exists in tblSites (normalised table) and insert if not.
Private Sub Command42_Click()
Dim rst As DAO.Recordset 'recordset for master file
Dim rs As DAO.Recordset 'recordset for normalised table
Dim strFind As String
Set rst = CurrentDb.OpenRecordset("tblMasterBaseline")
Set rs = CurrentDb.OpenRecordset("tblSites")
Do While Not rst.EOF
rs.MoveFirst
Do While Not rs.EOF
strFind = "(SiteIdentifier=" & rst!CentreID & ")"
rs.FindFirst strFind
If rs.NoMatch Then
rs.AddNew
rs!SiteIdentifier = rst!CentreID
rs.Update
End If
rs.MoveNext
Loop
rst.MoveNext
Loop
'Cleanup
Set rst = Nothing
Set rs = Nothing
End Sub
Many thanks in advance
I'm having a bit of a brain-freeze here and hope someone can help me. This loop does not seem to be working, could you please cast your experienced eyes over it and see why? It is getting stuck at rs.FindFirst strFind line. It's likely to be a really rookie mistake as I'm kinda rusty not having done this sort of work for a while. Basically what I'm trying to do is to loop through the master file (import from an Excel sheet, not normalised) and see if those values already exists in tblSites (normalised table) and insert if not.
Private Sub Command42_Click()
Dim rst As DAO.Recordset 'recordset for master file
Dim rs As DAO.Recordset 'recordset for normalised table
Dim strFind As String
Set rst = CurrentDb.OpenRecordset("tblMasterBaseline")
Set rs = CurrentDb.OpenRecordset("tblSites")
Do While Not rst.EOF
rs.MoveFirst
Do While Not rs.EOF
strFind = "(SiteIdentifier=" & rst!CentreID & ")"
rs.FindFirst strFind
If rs.NoMatch Then
rs.AddNew
rs!SiteIdentifier = rst!CentreID
rs.Update
End If
rs.MoveNext
Loop
rst.MoveNext
Loop
'Cleanup
Set rst = Nothing
Set rs = Nothing
End Sub
Many thanks in advance