Append Query help

[dkbrostyle]

Hi with my project I would need a primary key to be searched, and then copy that record.

I'm stuck because the search of the information would be on a form and i would like to create a button which my end-user would would click which would copy that record from the form to a table which i would like it to be copied. After that i would like that same macro to delete that would to avoid the smae search to be copied onto the assigned table again.

 

[mdmilner]

Ok try this - no error trapping or transactions which you might want to use to ensure you don't lose data.

Dim strSQL

strSQL = "INSERT INTO tblname (fld1, fld2, fld3) "
strSQL = strSQL & "SELECT afld1, afld2, afld3 FROM tblName2 "
strSQL = strSQL & "WHERE afld1 = '" & Me!TextBox1.Value & "'"
DoCmd.RunSQL strSQL

strSQL = "DELETE * FROM tblName2 "
strSQL = strSQL & "WHERE afld1 = '" & Me!TextBox1.Value & "'"
DoCmd.RunSQL strSQL

Good technique would be to use a variable and assign the text value of the Textbox to it and insert that into the SQL. You could also substitute the 'LIKE' operator in place of the = sign.

WHERE afld1 LIKE '*" & Me!TextBox1.Value & "*'"

Like works using wildcards to allow partial string matches. The above would be parsed by VB to be:

WHERE afld1 Like '*search_string*'

Allowing the search string to be anywhere within the field.
Something else you can consider is using transactions. Access and SQL tend to commit changes immediately when made and do not give you Excels' classis 'Undo' type command. Transactions allow you to store all the data and changes within Memory...execute the changes, and detect if there are any errors that prevent all commands from working. If all work, it 'commits' the changes and saves them (no more undo) but if anything fails, the whole transaction fails.

In the above worst case scenario, you could potentially write to the 2nd table but fail to delete or vice versa. One doubles your data, the other loses it. As long as you don't introduce potential problems, if you have the database locally (not on network share), you don't share access with multiple users, you probably will never see this.

Mike