searching for match in recordset

[tania20]

Hi, I am trying to search the records in a table "air to see if there is a match with general.HN (a field in the current form). If there is I want to do nothing, else add a new record with general.HN. I just cant seem to get it right though! any help appreciated!Cheers Tania

Set db = CurrentDb()
Set rs = db.OpenRecordset("air", dbOpenDynaset)

If Not rs.EOF then
If rs![HN] = Me![general.HN] Then

Else
rs.AddNew
rs![HN] = Me![general_info.HN]
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End If
End If

 

[Crowley16]

what you're doing is testing if the first record in the recordset matches hn, if not then add a new record...

if you want to test the whole recordset then you will have to loop through the entire recordset and test each record individually...

--------------------
Procrastinate Now!

 

[TheAceMan1]

How are ya tania20 . . .

Have a look at the [blue]FindFirst[/blue] method and the [blue]NoMatch[/blue] property for a recordset in VBE help . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tania20]

i tried using a loop with

do while not rs.eof
rs.movenext
loop

but that didnt work.is findfirst and nomatch avaiable in A2k?

 

[TheAceMan1]

tania20 . . .

In A2k:

[blue]   Dim db As DAO.Recordset, rs As DAO.Recordset
   
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("air", dbOpenDynaset)
   
   rs.[purple][b]FindFirst[/b][/purple] "[HN] = [red][b]'[/b][/red]" & Me![general.HN] & "[red][b]'[/b][/red]"
      
   If rs.[purple][b]NoMatch[/b][/purple] Then
      rs.AddNew
      rs![HN] = Me![general_info.HN]
      rs.Update
   End If
   
   Set rs = Nothing
   Set db = Nothing[/blue]

Note: if [blue]HN[/blue] is numeric, remove the two single quotes in [red]red[/red] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tania20]

thanks for everyones input, ive got it working how i need now..thanks!

 

[TheAceMan1]

tania20 . . .

. . . and your final results for those who were following this thread?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tania20]

i used aceman's method with findfirst and nomatch.for some silly reason i had thught it wasnt available in A2k so hadnt tried it!