If excist.....?

[JokeCoat.nl]

How can i look in a table (tblTotaal) to see if a record excists
(Inschrijving_ID, Ttotaal).
If excists then update the value of Ttotaal. If not excists then create new
record in tblTotaal.

 

[Dirk Goldgar]

How can i look in a table (tblTotaal) to see if a record excists
(Inschrijving_ID, Ttotaal).
If excists then update the value of Ttotaal. If not excists then
create new record in tblTotaal.

Here's one way, requiring a reference set to the DAO object library:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT Inschrijving_ID, Ttotaal FROM tblTotaal " & _
"WHERE Inschrijving_ID = " & Me!Inschrijving_ID)

With rs
If .EOF Then
.AddNew
!Inschrijving_ID = Me!Inschrijving_ID
Else
.Edit
End If
!Ttotaal = Me!Ttotaal
.Update
.Close
End With

Set rs = Nothing

That's assuming you have controls on the form named "Inschrijving_ID"
and "Ttotaal", but of course your actual control names may be different.
It also assumes that Inschrijving_ID is not an autonumber field, but
rather one with a field that must be set to a specific value, which in
the example will be picked up from the control on the form.

 

[JokeCoat.nl]

Hi,

I used:
Set rs = CurrentDb.OpenRecordset("SELECT Inschrijving_ID, Ttotaal FROM
tblTotaal WHERE Inschrijving_ID = Me!Tekst69")

But I get an error 3061 during execution: To little parameters. Expected:2
(It's my translation so i hope you'll understand it ;-)

Greetz,
Renze

 

[Dirk Goldgar]

Hi,

I used:
Set rs = CurrentDb.OpenRecordset("SELECT Inschrijving_ID, Ttotaal FROM
tblTotaal WHERE Inschrijving_ID = Me!Tekst69")

But I get an error 3061 during execution: To little parameters.
Expected:2 (It's my translation so i hope you'll understand it ;-)

The message means that there are 2 names in the SQL string that the
query engine doesn't recognize. One would be "Me!Telst69", which must
go outside of the quoted string, like this (as I originally posted it):

Set rs = CurrentDb.OpenRecordset( _
"SELECT Inschrijving_ID, Ttotaal FROM tblTotaal " & _
"WHERE Inschrijving_ID = " & Me!Tekst69)

I don't know what the other unrecognized name would be, but my guess is
that you misspelled the name of one of the fields, Inschrijving_ID or
Ttotaal. Check the fields in the table.

 

[JokeCoat.nl]

Thanks!!!!!!!

you were right, i misspelled something, i'm sorry (shame)

Now it works: great thanks again!