I have the following code, which connects to a database, searchs postcodes, well the first part of it. But the code i have only returns the first one it finds, and I need it to carry on until there are no more matchs, not sure how do this, as I am very new, and shocked I have made what i have to work:
Code
Sub SearchAdd_test()
'The name of the database
Const STR_DB_NAME As String = "C:\Documents and Settings\Andrew\My Documents\CSOS_ADD1.mdb"
Dim objEngine As DBEngine
Dim objDb As Database
Dim objRs As Recordset
Dim strSQL As String
Dim recID As String
Dim formPost1 As String
'T to look for in the database
formPost1 = searchform.post1
' SQL statement to query the database with
strSQL = "SELECT * FROM [SouthWest] WHERE [post1] = '" & formPost1 & "' ;"
'Initiate new DAO session, connect to the given database
'and open the above SQL query results
'MS Access will NOT be visible
Set objEngine = New DBEngine
Set objDb = objEngine.OpenDatabase(STR_DB_NAME)
Set objRs = objDb.OpenRecordset(strSQL)
'Takes the first result from the query and prints the fields
'on to the XL spreadsheet
With objRs
.MoveFirst
Cells(1, 1).Value = ![ID]
'If there is a 2nd record .EOF will return False
'If Not .EOF Then
'MsgBox "More than 1 record has been found.", vbOKOnly
'End If
.MoveNext
End With
'Destroys the DAO/MS Access objects
Set objRs = Nothing
Set objDb = Nothing
Set objEngine = Nothing
End Sub
Display More
Any help would be great!!!