Loop question (brain fog) (1 Viewer)

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 don't think you need the parentheses

strFind = "SiteIdentifier=" & rst!CentreID

This assumes that CentreID is a number and not text.

Thanks Minty, unfortunately, the field name is misleading, it is text... just can't seem to be able to get the syntax right

In that case you need to delimit the string - this is the simplest way assuming you don't have any single quotes in your CentreID;

strFind = "SiteIdentifier='" & rst!CentreID & "'"

You are a star!!! That did it... These double-quotes and ampersands and where/how they are used will be the end of me. Need to read up on these things again. Thank you so much

Have a lovely weekend