Add new records from a filtered query (1 Viewer)

[Dumferling]

I am not sure how I go about this. I have contracts which are listed in one table (Contracts). A contract can be affected by different regulations, depending on the industry (lets say Insurance) So, I have a related table (Regulations) on which I want to list the different regulations for each Insurance contract. Every time a new contract is added, I need to go to a third table (RegulationSource), filter the list by Insurance and add each regulation to the Regulations table.

How do I get Access to look at a filtered set of records, pick up each one and add them to the table? The adding I can do (AddNew) but how do I get to look at a filtered set of records and scroll through each one until the end of the recordset? It's not something I have ever done before.

 

[Gasman]

If it is in form use the recordsetclone property.
If in a listbox, walk through it's recordsource.
Then probably use an insert query to add the records?

arnelgp has just posted an example using recordsetclone.

Try searching for that.

Here it is, as I do not think the search looks in code tags

Select record from Subform

Hi, I have a subform with a user list in the main form I want 3 controls 1 first control to show the first user in the list 2nd control to show the second user in the list 3rd control to show the 3rd user in the list how would i code this please?

www.access-programmers.co.uk

 

[Pat Hartman]

If your tables are correctly defined, there is no need to copy anything. Please post a database with some data so we can see how you are trying to tie things together. The Contract table needs an IndustryCD field and the Regulation table also needs an IndustryCD. A third table is used to define valid Industries. Contract is joined to Regulation on IndustryCD to return all the regulations that apply to that contract.

Regulations should not be changed. They should be marked with an expiration date and then be replaced with a new version. The query that joins Contracts and Regulations can bring back only active regulations or all regulations including expired or future regulations depending on how you want to use the query's recordset.