Solved Copy filtered records to a temporary table

[NeilT123]

Good morning, I have a main form with a subform. The subform is a continuous form which is filtered from the main form. I then use the inbuilt Access Filters (right click in the text box) to narrow down the records shown in the subform.

Once the records are filtered down I would like to copy those records using a button on the main form (shown as Print Appln Charts on the screenshot) to a temporary table to use as a base for a report.

Is this possible?

I have tried using an append query but it copies the unfiltered records of the subform e.g. when I open the forms there may be 30 records showing in the subform I then filter this down to say 10 which I want to copy but the append query copies 30 records to the new table.

Screenshot attached for guidance

Thank you in advance for any help.

 

[MajP]

Get the forms filter and use that to build the append query.

 

[NeilT123]

Hi MajP, how would I do that please? This is my append query

INSERT INTO tblSLTrptFertApplnCharts ( FertApplnIndex )
SELECT qryUpdateFertApplns.ApplicationIndex
FROM qryUpdateFertApplns;

 

[MajP]

in code from your form maybe something like

dim strSql as string
strSql = "INSERT INTO tblSLTrptFertApplnCharts ( FertApplnIndex ) SELECT qryUpdateFertApplns.ApplicationIndex FROM qryUpdateFertAppln "
strSql = strSql & " WHERE " & me.Filter
debug.print strql
'if that does not work go into immediate window and post the results here from the debug.print
currentdb.execute Strsql, dbfailonerror

 

[NeilT123]

Using your code:

Dim strSql As String

strSql = "INSERT INTO tblSLTrptFertApplnCharts ( FertApplnIndex ) SELECT qryUpdateFertApplns.ApplicationIndex FROM qryUpdateFertApplns "

strSql = strSql & " WHERE " & Me.Filter

Debug.Print strql

'if that does not work go into immediate window and post the results here from the debug.print

CurrentDb.Execute strSql, dbFailOnError

gives
Run-time error 3145
Syntax error in WHERE clause

Because it is the subform that is filtered I also tried this. The subform is called frmIfUpdateFertApplns

strSql = strSql & " WHERE " & frmIfUpdateFertApplns.Form.Filter

and that gave
Run-time error 3061
Too few parameters. Expected 1

 

[Gasman]

Show uour Me.Filter value

 

[NeilT123]

Does the "receiving table" tblSLTrptFertApplnCharts have to have identical field names to the "sending query" qryUpdateFertApplns?

 

[MajP]

No. Please follow instructions if you want help.

 

[NeilT123]

How do I find Me.Filter value? Would it be in the Property sheet? If so here are a couple of examples.

(([Lookup_ProductName].[ProductName] Like "*N. 34.5*")) AND ([Lookup_ApplicationTiming].[ApplicationTiming] Like "*umn seed*")

([qryUpdateFertApplns].[FieldName] Like "*unt Plea*")

 

[mike60smart]

How do I find Me.Filter value? Would it be in the Property sheet? If so here are a couple of examples.

(([Lookup_ProductName].[ProductName] Like "*N. 34.5*")) AND ([Lookup_ApplicationTiming].[ApplicationTiming] Like "*umn seed*")

([qryUpdateFertApplns].[FieldName] Like "*unt Plea*")

MajP is asking for you to show what the Debug.Print command has produced.
Go into the Immediate window and copy and paste what is shown.

 

[NeilT123]

There was nothing in the Immediate window and I couldn't understand why. Then I noticed there was a typing error in the Debug.Print command.

Here is the text
INSERT INTO tblSLTrptFertApplnCharts ( FertApplnIndex ) SELECT qryUpdateFertApplns.ApplicationIndex FROM qryUpdateFertApplns WHERE

 

[MajP]

Sorry. I missed the subform part. Try this and report back since the filter is on the subform and you are running code from main form
strSql = strSql & " WHERE " & frmIfUpdateFertApplns.Form.Filter
Then we can see the sql string.

 

[NeilT123]

The error is too few parameters.

INSERT INTO tblSLTrptFertApplnCharts ( FertApplnIndex ) SELECT qryUpdateFertApplns.ApplicationIndex FROM qryUpdateFertApplns WHERE (([Lookup_ProductName].[ProductName] Like "*olysulpha*")) AND ([qryUpdateFertApplns].[Variety] Like "*ham Win*")

 

[NeilT123]

tblSLTrptFertApplnCharts has a single field FertApplnIndex

 

[ebs17]

SELECT qryUpdateFertApplns.ApplicationIndex FROM qryUpdateFertApplns WHERE (([Lookup_ProductName].[ProductName] Like "*olysulpha*")) AND ([qryUpdateFertApplns].[Variety] Like "*ham Win*")

Only fields from the tables specified in the FROM part are known in the query. Something in Lookup_ProductName is unknown and requires inquiry. Hence the error message after expected parameters.

 

[NeilT123]

Once the records are filtered down I would like to copy those records using a button on the main form (shown as Print Appln Charts on the screenshot) to a temporary table to use as a base for a report.

Just to finish off this thread I resolved this by adding a "select for printing" Yes/No button into the detail of the subform and then used that in the query behind the report.

 

[Gasman]

Please mark it solved then.