Append Query in a Form

[dancer99]

I've read the other posts, but still can't see how to do this.

I have a form and opens up a datasheet to a table. I then use the date picker to limit the recordset showing in the form. Once I review the records for the day, I now want to use a button to do the following:

Append 3 of the fields [SC, TFN, EP] to another table with today's timestamp.

Example: The recordset in the form shows 90 records. However, 30 of them have not been timestamped showing they have been reviewed. The timestamp field is in the table I am appending to. I need to append ONLY THOSE 30 to another table with todays' timestamp. The 30 I need to append to the other table have a common criteria of EP is Null.

Basically, need to copy the records as they are to a new table with timestamp, but only copy the records with EP is Null that are showing in the form filter.

Is this possible?

 

[dancer99]

Ok...I've got it now so that the data in the Form is only the data I need for the Append query.

So how do I write the query that will execute in the form as a button to only append the records that show in the form? I think I need a WHERE statement that points to the Open FORM of records, but I do not know what it should say.

Here's what I have so far...

INSERT INTO ScriptValidated ( SC, Checked, EP, TFN )
SELECT q_DIG_CurrentEP_RouteIT_Combined_Validated.[DIG Code], Format(Now(),"mm/dd/yy hh:nn") AS t, q_DIG_CurrentEP_RouteIT_Combined_Validated.[EP ID], q_DIG_CurrentEP_RouteIT_Combined_Validated.TFN
FROM q_DIG_CurrentEP_RouteIT_Combined_Validated
WHERE ?????

 

[dancer99]

Is this something Access can't do then?

The table contains over 11K records...the user is asked to enter the Number of Days to Review, and the form opens showing only the records for the next X days [example 120 records out of 11K].
I review them, and now I click the APPEND Query button to append only 4 fields of those 120 records that show in the form to another table.

Anyone have any ideas please? I am desperate and stuck on this for days now!

 

[boblarson]

You can build a query in code like this below. I am assuming that Dig Code is the PK field in the form. But if not, then change it to the right field in the code below.

[B][COLOR=red]Dim strSQL As String[/COLOR][/B]
[B][COLOR=red]Dim strHold As String[/COLOR][/B]
[B][COLOR=red]Dim rst As DAO.Recordset[/COLOR][/B]
 
[B][COLOR=red]Set rst = Me.RecordsetClone[/COLOR][/B]
 
[B][COLOR=red]With rst[/COLOR][/B]
[B][COLOR=red]  If .RecordCount > 0 Then[/COLOR][/B]
[B][COLOR=red]     Do Until .EOF[/COLOR][/B]
[B][COLOR=red]         strHold = strHold & Chr(34) & rst("DIG Code") & Chr(34) & ","[/COLOR][/B]
[B][COLOR=red]         .MoveNext[/COLOR][/B]
[B][COLOR=red]     Loop[/COLOR][/B]
[B][COLOR=red]     If Right(strHold, 1) = "," Then[/COLOR][/B]
[B][COLOR=red]        strHold = Left(strHold, Len(strHold)-1)[/COLOR][/B]
[B][COLOR=red]     End If[/COLOR][/B]
 
      strSQL = "INSERT INTO ScriptValidated([SC], [Checked], [EP], [TFN]) " & _  
"SELECT q_DIG_CurrentEP_RouteIT_Combined_Validated.[DIG Code], Format(Now(),"mm/dd/yy hh:nn") AS t, " & _
"q_DIG_CurrentEP_RouteIT_Combined_Validated.[EP ID], q_DIG_CurrentEP_RouteIT_Combined_Validated.TFN " & _
"FROM q_DIG_CurrentEP_RouteIT_Combined_Validated " & _
"WHERE [B][COLOR=red][DIG Code] In (" & strHold & ")"[/COLOR][/B]
   End If
 
CurrentDb.Execute strSQL, dbFailOnError
.Close
End With
 
Set rst = Nothing

 

[mrapoza]

Can't you just write a regular append query and when you click a button have it do:

DoCmd.OpenQuery QueryName, acNormal, acEdit 'where QueryName is the name of your query

It would prompt the user to Append, but if you don't mind that, it 's a simple and fast way to do it.