Removing/Adding records according to checkboxes (1 Viewer)

[Elise_L]

OK, I've got a good 'un here...

In a form called RelatedMaterials, I have several checkboxes, either ticked or not according to the appropriate ID. (still not working properly yet, but anyway..)

I want to be able to add or delete records from these boxes. SO....

Ticking 'ceramics', (ChkCeramics)(previously unchecked)
It needs to lookup the ceramics ID in 'Materials' Table which has column headings:
ID (Autonumber) '4', Sortcode '400', Material 'Ceramics'.

The ID needs to be taken out and used as a reference in another table 'LnkMat', which has headings:
ID (autonumber), Contrib ID, LinkID

The ID gained in 'Materials' will be the LinkID value and the ContribID will be gained from the original form Related Materials 'ContribID'.

How can I add this record in automatically?? Is it possible? I also need to consider unchecking an option and deleting a record.

PLEASE PLEASE help!! I only have an Access 2 book to help me so am basically teaching myself!!

Elise
(email TimFa@cogapp.com)
4

 

[TiggR]

Elise,

I have read you postings and can say I have tackled a similar problem myself but what I did was create a temporary table which contained the information that was displayed on screen. This was populated whenever a certain form was selected(by use of simple queries). When the information was changed on the form it would then update the records in the temp and original tables. The temporary table can contain all the values that you need to either create a new record or delete one so there is no need to querie multiple tables every time an update has to be performed.

I hope this can be of use and if you would like any further info or assistance please email me @ d_mccolm@hotmail.com

 

[Elise_L]

Thanks for the advice, I can sort of see what you mean, I just have a couple of questions. (I'm new to Access)

Temporary table: to you mean the same as creating an ordinary table, just with ContribID and Materials in? Is it only open for the life of the form? How do I make it temp?

Query, do you mean do a query that will collate all the info from the different tables. How would I then insert it back to the original?

sorry to be dumb about this!!!
Elise

 

[TiggR]

The temporary table structure stays all the time but the data only needs to be inserted when the relevant form is selected. Then the data can be deleted when a new record is selected.
I included these three fields, the first contained the literal value ie Ceramics but this could contain the code for it if that is what is stored in your original table, second was the Record ID the third was the checkbox(Yes/No) to indicate if it is used or not. From this information I was able to create a new record without having to pull any information from any other tables. Obviously you may need to change these according to your needs.
In the ON ENTER event for the check box I first checked the value of the checkbox then depending on that created/deleted a record accordingly using the data in the temporary table. My code looked something like this

If Me.chkYes_No = False Then
Insert new record at this point either by SQL or run query
Else
Delete record record at this point
End If

If you have any other questions dont hesitate to keep posting Im only too happy to help. If I can!