Delete a selected record in a subfrom by using a button

[dannyy1993]

Hello guys,
Can anybody help me with my problem.
For school I have to make a application in access but the problem is I can't figure out how to delete a selected record in the table in a subform by using a button.

The subform is in the main form and the button is also in the main form

 

[CJ_London]

Just to clarify, the user is required to select a particular record in the continuous or single? subform, then click on a button in the main form?

Assuming the subform recordsource is bringing through the uniqueID for the records then the code behind your button would be something like (for either single or continuous subform

currentdb.execute("Delete * FROM myTable WHERE ID=" & me.subformctrlname.form.id)

where subformctrlname is the name of your subform control, not the subform itself

You will probably need to add some additional coding to prevent accidental deletion or where there are not records in the subform

 

[dannyy1993]

I tried the code but it doesn't work.
I get a the message:
"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column"

 

[CJ_London]

It would have helped if you had mentioned you were accessing SQL Server

try this instead

currentdb.execute "Delete * FROM myTable WHERE ID=" & me.subformctrlname.form.id, dbSeeChanges

If this doesn't work you will need to explain much more clearly how you have everything set up

 

[dannyy1993]

Thank you for your help it works perfectly now

 

[dannyy1993]

I have a other question I tried to imply two WHERE clause but that didn't work, do you have a solution?

 

[David R]

Not sure I understand your question, but it sounds like you want

WHERE bar=3 [COLOR="Red"]AND[/COLOR] foo=True

 

[dannyy1993]

This is the code I'm using right now:

CurrentDb.Execute "Delete * FROM dbo_Prijzen WHERE Prijs = " & Me.subVoorstellingenPrijzen.Form.Prijs And Voorstellingnummer = " & me.subVoorstellingenPrijzen.Form.Voorstellingnummer, dbSeeChanges"

But when I hit the button "verwijderen" I get this Error 13 Type Mismatch

 

[CJ_London]

your quotation marks are not correct

CurrentDb.Execute "Delete * FROM dbo_Prijzen WHERE Prijs = " & Me.subVoorstellingenPrijzen.Form.Prijs[COLOR=red] & " And[/COLOR] Voorstellingnummer = " & me.subVoorstellingenPrijzen.Form.Voorstellingnummer, [COLOR=red]dbSeeChanges[/COLOR]

 

[dannyy1993]

Thank CJ_London

I have a other question about UPDATE

I tried this code but it gives me a syntax error:

CurrentDb.Execute "UPDATE dbo_Reservering.Betaald" & _
"SET Betaald = 'me.lijstKeuzeBetaaldVeranderen.Value' " & _
"WHERE Reserveringnummer = txtReserveringnummerAuto"

 

[CJ_London]

It would be helpful to say what the error is but it looks like you are missing spaces before SET and WHERE

 

[dannyy1993]

I have to change "Betaald" from Nee to Ja, but in the database "Betaald" is a bit.
me.lijstKeuzeBetaaldVeranderen is a combobox with Ja and Nee


I get the message syntax error

 

[David R]

No, look at the end of your first line,. There's no space after Betaald.

 

[dannyy1993]

I have now the code:
CurrentDb.Execute "UPDATE dbo_Reservering.Betaald " & _
"SET Betaald = '" & Me.lijstKeuzeBetaaldVeranderen.Value & _
"' WHERE Reserveringnummer = txtReserveringnummerAuto ", dbFailOnError



But now I get the Error 3024 --> cannot find the file C:\Users\Danny\Documents\dbo_Reservering.mdb

 

[David R]

Well, is that the filepath? Are you on the same machine where that C:\ is?