check boxes to enter and delete records

[Thymus]

Hello, new to vba and can't seem to be able to solve the following problem myself.

I have a form to enter archaeological features in a feature table. Each feature is present in one or more archaeological strata, for which I have a stratum table. There are 41 strata in total named 1A, 1B, 2A etc. On the form I want a checkbox for each of these and then to make it add a record to the stratum table for each of the selected strata. The stratum table and feature table are connected by the feature_primary_ID (key in feature table), so when making a new record I need this primary ID and the respective strata to be entered in the fields.

At the moment I've got something like this:

Private Sub Check1_Click()
If Check1.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES ((feature_primary_ID of present record),Label2.Caption)"
Else
DoCmd.RunSQL "DELETE FROM tbl_FEAT_STRAT WHERE feature_primary_ID = (feature_primary_ID of present record)"
End If
End Sub

For starters, I dont know how to get the feature_primary_ID from the features table (also represented in the form as a field) and use it to make a new record in the strata table.

Second, when using this code I need a copy for each of the 41 strata checkboxes. Creating lots of redundant code. Easier would be a generic code that loops through all the checkboxes after they've been checked and then add new records for each of the checked boxes. Tried something with a command button, but couldn't get it to work.

 

[CJ_London]

Replace

feature_primary_ID of present record

with

Forms!MyForm!feature_primary_ID

Where Myform is the name of the form which contains feature_primary_ID and feature_primary_ID is the name of the control on that form.

If you are using the query builder you can use the build wizard to build this link by navigating to it

 

[Thymus]

Thanks, that worked!

Now I want to move one step further and add a command button to append the records of each of the checked strata at once. I created this code:

Private Sub cmdStrata_Click()

For Each Control In Me.Controls
If Control.ControlType = acCheckBox Then
If Control.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES (Forms!feature_form!ctl_prime_ID,Forms!feature_form!Screen.ActiveControl.Name)"
Else
DoCmd.RunSQL "DELETE FROM tbl_FEAT_STRAT WHERE feature_primary_ID = (Forms!feature_form!ctl_prime_ID) AND stratum_ID = (Forms!feature_form!Screen.ActiveControl.Name)"
End If
End If
Next
End Sub

It doesn't work, as it asks me to manually enter a value for each 'Forms!feature_form!Screen.ActiveControl.Caption' in stead of getting the value from the name of the control and use that to put in a field.
Is it at all possible to refer to the active control in a sql statement? Am I doing wrong something else?

 

[CJ_London]

As soon as you click the button, the button becomes the active control so what you have won't work.

is your button in the same form as both of these controls? It might be an idea to upload a screenshot of your form and indicate which fields you want to use

 

[Thymus]

Yeah, I just realized that too.

Attached a screenshot. The button is in the same form. The present two checkboxes are just for experimental purposes. When everything works, in stead of 2 checkboxes there will be in total 41 of these.

I tried to recode it using DAO but got stuck with the same problem: as soon as you hit the command button, it becomes the active control.

 

[way2bord]

Yeah, I just realized that too.

Attached a screenshot. The button is in the same form. The present two checkboxes are just for experimental purposes. When everything works, in stead of 2 checkboxes there will be in total 41 of these.

I tried to recode it using DAO but got stuck with the same problem: as soon as you hit the command button, it becomes the active control.

You're already referencing the control, why do you need:
Forms!feature_form!Screen.ActiveControl.Name

when you could use...

control.name

DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES (Forms!feature_form!ctl_prime_ID, " & control.name & ")"

 

[CJ_London]

indicate which fields you want to use

Which ones?

 

[Thymus]

You're already referencing the control, why do you need:
Forms!feature_form!Screen.ActiveControl.Name

when you could use...

control.name

DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES (Forms!feature_form!ctl_prime_ID, " & control.name & ")"

Mmm. Yeah, you're totally right. Thanks. The reason why it kept going wrong is that I was trying it with

Forms!feature_form!Control.Name

But then it couldnt find the specific control I mean and gave an error.

Just in the process of getting familiar with the conceptual side of how this VBA stuff works... Thanks again.

 

[Thymus]

Which ones?

Sorry, its the ID field, the 'add strata' command button and either the name of check box 1 and 2, but preferably the labels of check box 1 and 2. Found out that i could refer to the bound label with

Control.Controls(0).Caption

Although I still don't completely understand why this works... I don't see where in this line it is referring to the label and not to the control itself. Is it the (0)?

 

[CJ_London]

Captions are used in labels and buttons, all objects in a form or report are controls and held in a collection called controls.

So Control.Controls(0).Caption will refer to the caption of the first control in the collection (they start from 0). The problem you may find with your method is Control.Controls(1) may not have a caption so the code will fail with an error along the lines of 'property not found'.

All controls have a name so you would be better to use

Control.Controls("myLabel").Caption where myLabel is the name of your label.

 

[Thymus]

But the caption of MyLabel is a variable which depends on the corresponding checked checkbox. How can I refer to a variable caption?

 

[CJ_London]

Sorry, it is not clear to me what you are trying to achieve but I would make sure you have your control naming conventions agreed (lets say each checkbox is called CB1...CB41 and each related label is called CBL1..CBL41 and use some vba along the following lines.

Dim Ctrl as Control
 
For each Ctrl in me.controls
    if left(Ctrl.Name,2)="CB" and Ctrl.Type=acCheckBox then
        if Ctrl.Value=true then
            Currentdb.execute("INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES (" & ctl_PrimeID & ", " & me("CBL" & mid(ctrl.name,3)).caption & ")")
        Else
            Currentdb.Execute("DELETE * FROM tbl_FEAT_STRAT WHERE feature_primary_ID = " & ctl_PrimeID & " AND stratum_ID = " & me("CBL" & mid(ctrl.name,3)).caption)
        End if
    End if
Loop

 

[Thymus]

My main problem was that I didn't understand how the reference system works when trying to refer to a variable, such as the name of the check box (in my mind I didnt see how the code knew which of the check boxes I ticked and than only add the names of these - or their corresponding label - to the stratum field). But I think I'm worrying about something that's obvious to you people.

Now, I tried your code. First an error message told me that there was a loop without do. Then I removed loop and replaced it with next (as it told me then there was a for each without a next). Now it tells me 'object doesn't support this property or method' about the line
If Left(Ctrl.Name, 2) = "CB" And Ctrl.Type = acCheckBox Then

And you also use some functions I haven't seen before 'left' and 'mid'? Why?

 

[CJ_London]

My mistake - comes with writing code directly into the post

Ctrl.Type should be Ctrl.ControlType

Left and Mid are string functions which return (in this case) the 2 leftmost characters from Ctrl.Name for Left and Mid returns every character from the 3rd character to the end.

Mid(myString,3,2) would return the 3rd and 4th characters

Right(myString,2) returns the last two characters

The reason for using mid in this case is because we don't know how many characters to return - could be 1,2,3.. or 10,11,12...41.

The reason why I use them is to determine that the code is looking at the right control to run the rest of the code.

 

[Thymus]

Ok. Now I get 'Too few parameters. Expected 1.' when I try to add records. The same for unchecking/deleting records.

 

[CJ_London]

this error occurs because there is something wrong with the data referred to by the sql.

For now, change current.execute back to docmd.runsql and see if you get a more helpful error message.

I presume you have named and captioned your chkboxes in line with my suggestion? CB1...CB41 and CBL1...CBL41 or have adapted the code to suit your naming?

The other thing to do is debug.print the sql code and copy and paste it into a new query and try to run it - you can see the issue there.

 

[Thymus]

Yes, I named the checkboxes and labels as you suggested. When I change current.execute back to docmnd.runsql it adds the records, but only fills the primary_ID field not the stratum_ID field with the name of the checkbox label.

 

[CJ_London]

Just thought of something. The me("CBL" & mid(ctrl.name,3)).caption is text so needs to be surrounded with single quotatons so change

VALUES (" & ctl_PrimeID & ", " & me("CBL" & mid(ctrl.name,3)).caption & ")")

AND stratum_ID = " & me("CBL" & mid(ctrl.name,3)).caption

to

VALUES (" & ctl_PrimeID & ", [COLOR=red]'[/COLOR]" & me("CBL" & mid(ctrl.name,3)).caption & "[COLOR=red]'[/COLOR])")
 
AND stratum_ID = [COLOR=red]'[/COLOR]" & me("CBL" & mid(ctrl.name,3)).caption [COLOR=red]& "'"[/COLOR][COLOR=black])[/COLOR]

changes marked in red

 

[Thymus]

It works! Also when using currentdb.execute. Why did you replace docmd.sql with currentdb.execute in the first place? What's the difference between the two?

Many thanks for helping out!

 

[Thymus]

Another thing I don't yet understand is why there is an uneven number of parentheses. I assumed that parentheses always appear in pairs as they have to surround a function or string or something.