Help with Adding/Removing selected Item from Table

[nxqviet]

I have a form which contain a subform that show a list of item. I want
to be able to select an item from this subfom and have a deletequery to
take it out.

Also, I have a second subfom in this same form, I want to select an
item in the second subform and append it (using append query) to the
first subform above.

I understand that there are way of doing it with coding, but im not
very familiar with coding and would much rather minimize code and use
queries instead.


Thank you for your time,


V

 

[John Vinson]

I have a form which contain a subform that show a list of item. I want
to be able to select an item from this subfom and have a deletequery to
take it out.

If the Form has Allow Deletes and Record Selectors properties set to
Yes, just click the little grey box on the left of the subform (it
should darken) and press the Delete key. Or, to make it more obvious
but take more screen space, you can put a command button on the form;
use the button wizard to create a button that deletes the record.

Also, I have a second subfom in this same form, I want to select an
item in the second subform and append it (using append query) to the
first subform above.

A button which runs the append query (using the VBA Execute method)
would be the best bet. A button with a Macro using the RunQuery action
would do, but may give the user unwanted prompts like "You are about
to run an action query which will append 1 record, OK?" unless you put
a line to SetWarnings False first. If you do so, be sure to
SetWarnings True after the line that runs the query or you'll turn off
*all* warning messages.

I understand that there are way of doing it with coding, but im not
very familiar with coding and would much rather minimize code and use
queries instead.

Queries can't put commands or buttons on the screen for you. That's
not their function.

John W. Vinson[MVP]

 

[nxqviet]

Thanks John for your comment.
I do know what you are telling me to do, but i want my form to be more
interactive...because I'm designing it for non-access users.

I know how to use append queries and macros, but I want to append a
specific selected item from one form to the next, not the entire list
nor a group of item that match a defined criteria. Simply, select an
item, then click "add" to add it to a form above.

I appreciate your help, thanks,



V

 

[John Vinson]

Thanks John for your comment.
I do know what you are telling me to do, but i want my form to be more
interactive...because I'm designing it for non-access users.

I know how to use append queries and macros, but I want to append a
specific selected item from one form to the next, not the entire list
nor a group of item that match a defined criteria. Simply, select an
item, then click "add" to add it to a form above.

I appreciate your help, thanks,

I don't see any way of getting around VBA code to do this, then. Of
course you're not adding it *to the form* - you're adding it to a
table, using an append query, and then displaying that record on a
form.

Try a command button with a click event like

Private Sub CopyRecord_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
On Error GoTo Proc_Error ' set up an error trap
Set db = CurrentDb ' "handle" for the database

strSQL = "INSERT INTO ThatTable (fielda, fieldb, fieldc)" _
& " SELECT FieldA, FieldB, FieldC FROM ThisTable" _
& " WHERE KeyField = " & Me!txtKeyField
Set db = CurrentDb ' get reference to the current database
Set qd = db.CreateQuerydef("", strSQL) ' create a nameless
' Querydef object with the constructed SQL
qd.Execute, dbFailOnError ' run the action query
Proc_Exit:
Exit Sub ' if no error, exit normally
Proc_Error: ' display error message if one occurs
MsgBox "Error " & Err.Number & " in CopyRecord:" & vbCrLf _
& Err.Description
Resume Proc_Exit
End Sub


John W. Vinson[MVP]

 

[nxqviet]

John,

Thanks. I'm trying your code right now. It ganna take me while to make
sence out of the code since I'm not very familiar with coding.

Thanks again.

V