UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Question about Code To Delete Subform Record From Mainform , Any Version    
 
   
wolfe
post Feb 14 2018, 03:00 PM
Post#1



Posts: 2
Joined: 14-February 18



Hi, apologies if this is a very old thread to be replying to, but I've been trying to implement the code suggested by Doug in post #2 and can't get it to work.

I have a form (frmViewProjectDetails) that contains a subform (fsubViewProjectDetails) in datasheet view. The subform's record source is a select query (qryViewProjectDetails) with 5 fields taken either directly or as calculated fields from one main table (tblProjectDetails), and 1 not particularly important field calculated from both that main table and another one (tblOpenedData). I'm using Access 2010.

Similarly to Olga, I would like a button in the form to delete a record selected by the user in the subform. I've modified Doug's code as follows:
CODE
Private Sub cmdDeleteSelectedProjectViewProjectBasicDetails_Click()

Dim Msg As String, Style As Integer, Title As String, Response As Integer
Dim strSQL As String
  
  Msg = "You are about to delete this record."
  Style = vbOKCancel + vbQuestion + vbDefaultButton2
  Title = "Continue?"
  Response = MsgBox(Msg, Style, Title)
  If Response = vbOK Then
    strSQL = "DELETE FROM tblProjectDetails WHERE ProjectIDcode = " & Me!fsubViewProjectBasicDetails.Form!ProjectIDcode
    CurrentDb.Execute dbFailOnError
    Me!fsubViewProjectBasicDetails.Form.Requery
  Else
    MsgBox "No record deleted", vbOKOnly, "No changes made"
  End If

... but when I select a record in the subform, click the relevant 'delete' button and click 'Ok' to the initial message box warning from the code, I get a "Run-time error '3078': The Microsoft Access database engine cannot find the input table or query '128'. Make sure it exists and that its name is spelled correctly".
'Debug' highlights the line:
CODE
CurrentDb.Execute dbFailOnError

I'm still finding my way with VBA (especially for Access), and haven't been able to find anything helpful enough to fix this from googling the error message or various parts of the code. In case it's relevant, the subform has 'Yes' for the properties allowing additions, deletions and edits, but if I try to make changes to it (or the underlying query) in datasheet view I get a message in the status bar that "This Recordset is not updateable". When experimenting with edits to the query in datasheet view I think I previously had a message along the lines that it was in read-only mode, though now it also shows the message that the recordset is not updateable.

Can you see where I'm likely to be going wrong?
Go to the top of the page
 
DanielPineault
post Feb 14 2018, 03:23 PM
Post#2


UtterAccess VIP
Posts: 5,659
Joined: 30-June 11



welcome2UA.gif




You forgot to include the SQL statement variable in the execute command

change

CODE
CurrentDb.Execute dbFailOnError


to

CODE
CurrentDb.Execute strSQL, dbFailOnError




--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
wolfe
post Feb 15 2018, 07:39 AM
Post#3



Posts: 2
Joined: 14-February 18



Thanks for your help Daniel.

Making that change by itself just produced a different error at the same stage ("Run-time error '3061': Too few parameters. Expected 1"), with the same line of code highlighted by the debugger.

However, from searching for that next error message I saw that I also needed to add apostrophes/single quotes around the variable location - so the whole thing works now with these two lines changed:
CODE
    strSQL = "DELETE FROM tblProjectDetails WHERE ProjectIDcode = '" & Me!fsubViewProjectBasicDetails.Form!ProjectIDcode & "'"
    CurrentDb.Execute strSQL, dbFailOnError

Thanks again
Go to the top of the page
 
DanielPineault
post Feb 15 2018, 10:00 AM
Post#4


UtterAccess VIP
Posts: 5,659
Joined: 30-June 11



no apostrophe = number data type

apostrophe = text data type



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st February 2018 - 04:21 AM