test if record exists does not work

  • Thread starter Thread starter Hans
  • Start date Start date
H

Hans

Hi There,

I want to make a check in VBA whether a record exist but cannot get it to
work.

I'm using 2 tables with a 1-1 relation. Although I'm sure there is a "child
record" in tbl_lid my VBA code doesn't find the record.

Question: Whats the mistake in the code i'm using?

table tbl_relatie
fields:
r_id key, autonumber
.....

table tbl_lid
fields :
l_id autonumber
l_relatie_id_fk foreign key to r_id, numeric (long integer)
....

the code:

strSQL = "SELECT tbl_lid.l_id FROM tbl_lid" _
& " WHERE ('&tbl_lid.[l_relatie_id_fk]&' = '&Me![r_id]&');"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount = 0 Then
' er is geen lid record dus we voegen toe
Set rs = db.OpenRecordset("tbl_lid")
rs.AddNew
rs("l_dat_aanmelding_lid") = Date
rs("l_relatie_id_fk") = Me!r_id
rs.Update
Me.frm_sub_relatie_lid.Requery
End If



Any help will be appreciated,

Hans
 
Hans said:
Hi There,

I want to make a check in VBA whether a record exist but cannot get it to
work.

I'm using 2 tables with a 1-1 relation. Although I'm sure there is a "child
record" in tbl_lid my VBA code doesn't find the record.

Question: Whats the mistake in the code i'm using?

table tbl_relatie
fields:
r_id key, autonumber
....

table tbl_lid
fields :
l_id autonumber
l_relatie_id_fk foreign key to r_id, numeric (long integer)
...

the code:

strSQL = "SELECT tbl_lid.l_id FROM tbl_lid" _
& " WHERE ('&tbl_lid.[l_relatie_id_fk]&' = '&Me![r_id]&');"
Click to expand...

It looks like you have your quotes and stuff mixed up. Try
this:

strSQL = "SELECT tbl_lid.l_id FROM tbl_lid" _
& " WHERE tbl_lid.l_relatie_id_fk = " & Me![r_id]

(Note that Access does not require the semicolon at the end)

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.RecordCount = 0 Then
' er is geen lid record dus we voegen toe
Click to expand...

It may not be strictly required, but you should clean up
that recordset before you reuse the recordset variable.
rs.Close: Set Rs = nothing
 
Back
Top