Checkbox on form to add multiple values. (1 Viewer)

[travisdh]

Hello.

I am struggling a little bit with using a checkbox to generate multiple values in the same table.

Essentially I have a projects table, that is linked to a sample table by ProjectID. The sample table is essentially the follow fields:

SampleID, ProjectID, Sample Description, Sample Recieved, Sample Entered, ParentSample Attachments.

Basically normally we enter samples with all of the information, but leave the ParentSample field empty. When the sample is based on another sample, or is created as a result of recieving another sample the parent sample gets filled in with the initial ID of the sample recieved, and then the rest of the details get filled out.

What i want to do, is have checkboxes bound to the sampleID of each sample, so the user checks the boxes for samples they want to generate subsamples from, and when they hit a button, up pops a form that asks the testing period, and the tubes required (x hours, or 24 hours, 7 days and so on) and what tubes (Carbon, XAD-2, Silica and so on).

How can i get it so that it loops through the selected samples and creates subsamples in the following format?


SampleID, ProjectID, Sample Description, Sample Recieved, Sample Entered, ParentSample Attachments.

NEWID, FORM1.ID.value, (Tubetype,collection period, parentsample), date, date, initialsample, no attachements

and also create a new sample for every tube clicked. So for example if 3 x Carbon and 1 x XAD-2 were ticked for 7 days, for samples 72343. 72435

A total of eight new subsamples would be generated, four of which would reference 72343, four of which would reference 72435 in the parent field.

 

[Fuga]

When you want to create the subsamples, are yoiu registering a parentsample? Or is it the other way around?

If you´re registering a parent sample and you wish to create subs, then you would execute some sort of append query on a table.

As for the checkboxes, how many choices do you want to have availible? Is it just a few?

Depending on how your material will look over time, you might want to split the table into one for the parent samples and one for subsamples.

Fuga.

 

[travisdh]

When you want to create the subsamples, are yoiu registering a parentsample? Or is it the other way around?

If you´re registering a parent sample and you wish to create subs, then you would execute some sort of append query on a table.

As for the checkboxes, how many choices do you want to have availible? Is it just a few?

Depending on how your material will look over time, you might want to split the table into one for the parent samples and one for subsamples.

Fuga.

I thought that, however as often samples are analysed on the instrument which recieves both parent samples, and subsamples (parent samples from field sampling, subsamples when products are sent in for testing) i am very keen to keep it within the one table, which of course makes things much harder.

I have the new samples generated, which then reference the old samples they are new records, so an insert query should work fine, i am just not sure how to use the sampleID of the box that is checked to put that in the parentsample column, there would be a total of four checkboxes (Carbon, XAD-2, XAD-7, Silica and a dropdown box of 24 Hours, 7 Days, 14 Days or 28 Days)

So you check the box for which types of samples you want to be registered and select the timeperiod, and it should insert into the samplestable the jobID from the form, the sampleID of the selectedcheckbox and the description as a combination of the tube type, the parent sample and the testing period (for human readability)

 

[Fuga]

Maybe I´m wrong here, but I´m thinking you want an option group rather than checkboxes.

This is an example that creates a query. The sql must be your own insert query of course. Mine is an update query, but I leave it as it is so you can see how to use variables.

you put this code behind a button or whatever.

Dim icur as string, iprev as string
icur = me.textbox2.text 'here you reference your control, ie the option group which will give you a value of 1 to 4. this was just an example
CurrentDb.CreateQueryDef "prevupdate", "UPDATE 15_currentprevious SET [15_currentprevious].previous = [current]-" & isubtract & " where current between " & icur & " and " & icur + iprev & ";"
DoCmd.OpenQuery ("prevupdate")

You might also want to look at using master and subform and link the subforms parentfield to the sample id field.

Fuga