Check if a record is there in VBA

[Martin]

I want to check if a given item is already there in a table.

Table: Participants
definition primary key:
Name (characterfield)
Number

Before I insert the values I get from the form (it is independent of any tables) I want to check if de PK is already there in VBA.

I tried .Nomatch (this I don't get to work) and DoCmd.runSQL (I use a count(*) in a select, but won't get the results back to use further.

HELP!!

 

[garyd1234]

dim db as database
dim rs as recordset
set db=currentdb()
set rs=db.openrecordset("select * from participants where primary key var = field on the form you want to check")
if rs.recordcount =0 then
go ahead and insert it cause it isn't there
endif

 

[Martin]

I used your solution, but I get the notice I used a wrong 'user defined objecttype'. When I take out the dim of the database (dbs). The macro will run, but then the recordset won't work. HELP!!

Hereby my source (partly dutch).

Option Compare Database
Dim NaamI As String
Dim AvondI As Byte
Dim Ronde1I As Long
Dim Ronde2I As Long
Dim Ronde3I As Long
Dim Ronde4I As Long
Dim dbs As Database
Dim rst As Recordset

Private Sub Knop6_Click()

NaamI = [Forms]![Test]![Naam]
AvondI = [Forms]![Test]![Avond].Value
Ronde1I = [Forms]![Test]![Ronde1].Value
Ronde2I = [Forms]![Test]![Ronde2].Value
Ronde3I = [Forms]![Test]![Ronde3].Value
Ronde4I = [Forms]![Test]![Ronde4].Value
AvondscoreI = Ronde1I + Ronde2I + Ronde3I + Ronde4I

selSQL = "SELECT Naam FROM TblDeelnemers where Naam = """ & NaamI & """"

Set dbs = CurrentDb()
Set rst = dbs.openrecordset(selSQL)
If rst.RecordCount = 0 Then
rst.Close
dbs.Close
End If


insSQL = "Insert into TblScores (Naam, Speelavond, Ronde1, Ronde2, Ronde3, Ronde4, Avondscore)" & _
" values (""" & NaamI & """, " & AvondI & ", " & Ronde1I & ", " & Ronde2I & ", " & _
Ronde3I & ", " & Ronde4I & ", " & AvondscoreI & ");"

DoCmd.SetWarnings False
DoCmd.Hourglass True

DoCmd.RunSQL insSQL

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

 

[Dugantrain]

You need to set a Reference to the Microsoft DAO 3.6 Object Library.