Results 1 to 8 of 8
  1. #1
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286

    Refer To Selected Record on a FORM in Append/Delete Query

    Hello, i have a form with a subform that will display rows of data



    i want it so that the user can select the particular row, and click a button and be able to either delete that row or append that row to another table

    how do i refer to any selected record in that subform? for example in a delete query, it would normally be delete where [frmExample].[subform].[IDcolumn]

    ^however that normally applies only if its a textbox on the form. how would i do it for a selected record. thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    Can delete record with the keyboard Delete key.
    Do you want to prevent record deletion with the keyboard Delete key?

    I would use VBA to run sql action.

    If delete button on the subform, try:

    CurrentDb.Execute "DELETE FROM tablename WHERE ID=" & Me.ID

    Need more info about the append action, but something like:
    CurrentDB.Execute "INSERT INTO tablename(fieldname) VALUES(" & Me.ID & ")"
    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.

  3. #3
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    june, thx for the help. for the append you used:

    CurrentDB.Execute "INSERT INTO tablename(fieldname) VALUES(" & Me.ID & ")"

    however, wont that just append only the ID to the table i want to append to in the ID field? How would I append the whole row? or will that code append whole row?

    the way my form looks is i have several radio buttons, depending on what radio button is clicked, a different subform appears. however, i just have ONE append button. what i want is the 1 append button to append records of any subform so how would i refer to any subform is more of what im wondering? Thanks!!!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    Include however many fields and values in the INSERT you need. This example shows syntax for number, text, date datatypes.

    CurrentDB.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES(" & Me.ID & ", '" & Me.textbox2 & "', #" & Me.textbox3 & "#)"

    Never set up an SQL with dynamic subform reference. Will have to think about that.
    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.

  5. #5
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    this is a bit unrelated but how do i make a messagebox appear on a button click so that the user can type in the reason of why he wants to delete a record. and i also want to be able to refer to this reason so I can append it to a table to keep track. How would i refer to the value in this message box? thanks!

  6. #6
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    in the code above, what does the "#" refer to? thanks


    "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES(" & Me.ID & ", '" & Me.textbox2 & "', #" & Me.textbox3 & "#)"

  7. #7
    taimysho0 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Nov 2011
    Posts
    286
    another thing is, when i try to do it that way (above), the code cant find the variables on the form for example if im selecting a record in subformA with a bounded textbox to "Record" for example, it wont find it just by simply typing in me.record. if i type in me.subformA.record, it doesnt find "record" either. It finds the subform but code stops at the "record" field...

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,123
    Can use an intrinsic Input Box or build a custom form. The InputBox is easier to retain the value. Passing data between forms is trickier.
    Review http://office.microsoft.com/en-us/ac...001228856.aspx

    The # character is delimiter for date value, text uses apostrophe (') delimiter, nothing for number.

    Post your exact VBA code for analysis. The code is behind the main form and not the subform?
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-03-2012, 03:54 PM
  2. Replies: 9
    Last Post: 10-12-2011, 02:34 PM
  3. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 02:30 AM
  4. #Deleted issue with Delete/Append Query
    By WendyCha in forum Access
    Replies: 4
    Last Post: 08-05-2011, 08:34 AM
  5. Loading the form with selected record
    By emilylu3 in forum Access
    Replies: 1
    Last Post: 12-09-2005, 07:49 AM

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 - Senior Forums