Results 1 to 9 of 9
  1. #1
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123

    Update table from form

    In the Edit button on my form I am coming up the this error for this code.
    could someone please show me where I have gone wrong and how to fix it?

    Click image for larger version. 

Name:	debug.png 
Views:	12 
Size:	26.7 KB 
ID:	14775

  2. #2
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    I had the following for the add button and it works fine

    CurrentDb.Execute "INSERT INTO tblEmpDetails(EmpId, Name, EmpNo, Roster, Shift, PermFctn)" & _
    "VALUES(" & Me.txtEmpIDAdd & ", '" & Me.txtName & "', '" & _
    Me.txtEmpNo & "', '" & Me.cboRoster & "', '" & Me.txtShift & "', '" & Me.cboPermFctn & "')"

    I tried to exchange "INSERT INTO" for "UPDATE" but I got an error.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Missing quote marks and apostrophes on each line:

    "SET EmpID=" &

    ",Name='" &

    ...


    Why do you need sql actions to insert/update record? Why not data entry/edit to record on bound form?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    The reason I am not doing the bound form way is the only video I could find on how to
    update, edit and delete records from a form was the one I am following. (i am a newbie)

    I am now getting the this error but the whole thing highlights yellow when I click debug now.
    I think it may have something to do with my last line


    Click image for larger version. 

Name:	debug.png 
Views:	12 
Size:	16.0 KB 
ID:	14776

    CurrentDb.Execute "UPDATE tblEmpDetails " & _
    "SET EmpID=" & Me.cboSearchName.Column(0) & _
    ",Name='" & Me.txtName & "'" & _
    ",EmpNo='" & Me.txtEmpNo & "'" & _
    ",Roster='" & Me.cboRoster & "'" & _
    ",Shift='" & Me.txtShift & "'" & _
    ",PermFctn='" & Me.cboPermFctn & "'" & _
    "WHERE EmpID=" & Me.cboSearchName.Column(0)

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    I don't see anything wrong with the syntax. However, don't understand why allowing edit of EmpID field. Is this an autonumber type?

    I suggest you search some more. Bound form for data entry/edit is basic Access functionality. What you are attempting is not fully utilizing Access capabilities and is making your life harder.

    Access Help has guidelines on creating bound forms.

    Access has built-in methods for finding existing records or can build code to do search/filter of form records.

    An Access database can be built entirely without any code by simply using intrinsic features.

    You will need to learn how to build reports and the process for binding a report object to data is essentially the same as for form. There are even wizards to do most of the work for you.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    I will have to look into it,

    and about the EmpId, this ID is a number but it is and needs to be a text field

  7. #7
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    is it possible that the last line is causing it to fail as the EmpID is as text

    WHERE EmpID=" & Me.cboSearchName.Column(0)

  8. #8
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    I sorted that part out I just needed to put the ='" instead of just "

    I now have a new problem.
    this is the delete part of it

    I have the following code Private Sub cmdDelete_Click()

    If Not (Me.frmEmpDetailsSub.Form.Recordset.EOF And Me.frmEmpDetailsSub.Form.Recordset.BOF) Then
    'confirm Delete msg
    If MsgBox("Are You Sure You Want to Remove this Person From Existance?") = vbYes Then
    'Delete Now
    CurrentDb.Execute "DELETE FROM tblEmpDetails " & _
    " WHERE EmpID='" & Me.cboSearchName.Column(0) & "'"
    'refresh list
    Me.frmEmpDetailsSub.Form.Requery
    End If
    End If
    End Sub

    when I click the delete button the msgbox comes up as it should and when I press yes absolutly nothing happens.
    no error, just nothing?!?

  9. #9
    WickidWe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    123
    never mind i forgot to put vbyesno in the msgbox
    MsgBox("Are You Sure You Want to Remove this Person From Existance?") = vbYes Then

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Table from Form
    By NISMOJim in forum Forms
    Replies: 12
    Last Post: 10-28-2013, 01:30 PM
  2. Replies: 7
    Last Post: 09-21-2012, 11:09 AM
  3. Use a form to Update a table
    By j2curtis64 in forum Forms
    Replies: 0
    Last Post: 04-05-2011, 10:18 AM
  4. Replies: 2
    Last Post: 12-22-2010, 01:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums