check boxes to enter and delete records

[CJ_London]

re parentheses there are an even number - not sure where to are looking but if you count them (in a working query) there will be the same number of lefts and rights.

There are a number of differences between docmd.runsql and currentdb.execute, however the main ones are:

• The former will prompt you that it is about to update x number of records etc whilst the latter doesn't.
• To get over this you would use docmd.setwarnings = true or false to turn these messages off
• if you specify a variable as a db and assign currentdb to it then you can make use of the recordsaffected value to advise how many records have been affected which is useful for progress reporting e.g.

Dim Mydb as database
 
set mydb=currentdb
mydb.execute("Update myTable....")
msgbox mydb.recordsaffected & " records have been updated"
set mydb=nothing

Note that currentdb.recordsaffected will always return 0 - for a full explanation - this link will explain a bit more http://stackoverflow.com/questions/3004153/currentdb-recordsaffected-returns-0-why

 

[Thymus]

Okay.

next problem. When displaying a record on the form I'd like to have only the checkboxes checked of the strata the feature belongs to. So, I want to set the value of the checkbox on true when a feature is present and on false when its not.

I tried to put this query

"SELECT * FROM tbl_FEAT_STRAT WHERE feature_primary_ID = " & ctl_prime_ID & " AND stratum_ID = '" & Me("CBL" & Mid(Ctrl.Name, 3)).Caption & "'"

In a variable called strSQL and then compare the result with the name of the checkbox ctrl.Name. It doesn't seem to work.

 

[CJ_London]

You need to take a slightly different approach to this and use vba code along the following lines - it should be put in the form current event and you should ensure all checkbox controls have a default=false

Private Sub Form_Current()
Dim Rst as Recordset
Dim Ctrl as Control
 
On Error Resume Next 'to ignore errors which will be generated when a control does not have a caption property   
Set Rst=Currentdb.OpenRecordset("SELECT Stratum_ID FROM tbl_FEAT_STRAT WHERE feature_primary_ID = " & ctl_prime_ID)
While Not Rst.EOF
    For each ctrl in me.controls
        if ctrl.caption=rst.fields(0) then me("CB" & mid(ctrl.name,4))=True        
    Next Ctrl
    Rst.MoveNext
Wend
Set Rst=Nothing
End Sub

edit: correction to mid function

 

[Thymus]

I set all checkbox defaults on false as you suggested, but It doesn't work. I don't get an error message either.

 

[CJ_London]

Did you pick up my edited version changing the mid function parameters?

Otherwise you have not given enough information for me to go on - suggest you debug the code.

To do this, in the vba window place the cursor on the first line of code (not the dim statements) and hit the F9 button, a maroon dot will appear to the left.

Open the form and the code will stop at this line and highlighting it in yellow. Use the F8 button to step through the code and by hovering over the variable names you can see what their values are.

you may have to step through a few times until you get to a control of the right type - i.e. a label caption which = the rst.field(0)

You can also use debug.print as well

Taking out the On Error line will guarantee a fail when the vba looks at a control without a caption so not recommended

 

[Thymus]

Yea, I used the edited version.

Debugging gives me this:
Rst.EOF = false
Rst.Fields(0) = "CBLx" (so, the correct caption of the label)
Ctrl.Name = "Auto_header()"

I was wondering, the checkboxes themselves have no caption, only their associated labels do. Something going awry here maybe?

 

[CJ_London]

Rst.Fields(0) = "CBLx" (so, the correct caption of the label)
Ctrl.Name = "Auto_header()"

From my previous posts I would expect your label control properties to be something like

Name=CBL1
Caption="Some description"

And your Stratum_ID to also be "Some Description"

 

[Thymus]

Checked it again today and it does work. Don't understand what was going on yesterday. Probably my brain didnt cooperate.
Anyway, it works only in part. As soon as the script checks the checkboxes it doesnt uncheck them when the criteria are not met. I tried with an else statement after

If Ctrl.Caption = Rst.Fields(0) Then Me("CB" & Mid(Ctrl.Name, 4)) = True

But then I get the error 'else without if', so apparently this is not allowed here.

And what does the EOF function exactly do and why is it necessary? I checked it on the microsoft website, but don't completely get it.

 

[CJ_London]

If you are only saving the checkbox label captions that are ticked and all checkboxes are set to default = false initially (i.e.unticked), when they are returned they will only be changing those that have been saved to ticked so I'm not sure why you are having a problem.

If you are saving all checkbox label captions together with whether the releated checkbox is ticked or not then you will need slightly different code.

Which are you trying to do?

 

[Thymus]

When I'm viewing a page in my form I want to have only those checkboxes ticked of the strata a feature belongs to (so the saved label captions).

What happens now is the following:
I view a page in my form of feature number 5. It has pre-ticked the checkbox CB1, corresponding to the saved CB label.

I move to the next page in my form, feature number 6. Feature number 6 has two saved CB labels (archaeological strata it belongs to): the label related to CB1 and the one to CB2. Both are ticked correspondingly. Everything fine until now.

I move back to the previous page, feature 5. What should happen is that only CB1 is ticked. In stead, both checkboxes are ticked, as in the previous page.

 

[CJ_London]

OK, You'll need to modify your form current event as follows - the bit in red, I've included the lines before and after so you know where to put it:

On Error Resume Next 'to ignore errors which will be generated when a control does not have a caption property   

[COLOR=red]For each ctrl in me.controls
        if ctrl.ControlType=acCheckBox and left(ctrl.name,2)="CB" then ctrl.value=false[/COLOR]
[COLOR=red]Next Ctrl
[/COLOR]Set Rst=Currentdb.OpenRecordset("SELECT Stratum_ID FROM tbl_FEAT_STRAT WHERE feature_primary_ID = " & ctl_prime_ID)

 

[Thymus]

Great! Thank you so much.

Now, I really hope this is the last problem I ask you to solve: the database crashes when I move to a new record in the form.

 

[CJ_London]

You'll need to explain 'crashes' in a bit more detail - but try compacting the db first.

Also try debugging as previous described to see if you can identify the line of code causing the problem or whether you have got into a loop

 

[Thymus]

It doesn't get out of the While Not rst.EOF loop when making a new record.

 

[CJ_London]

While Not Rst.EOF
    For each ctrl in me.controls
        if ctrl.caption=rst.fields(0) then me("CB" & mid(ctrl.name,4))=True        
    Next Ctrl
    [COLOR=red]Rst.MoveNext
[/COLOR]Wend

Have you got the bit in red per the original post?

 

[Thymus]

Yes, I didnt change anything to the code.

Could it be caused by the fact that it tries to get the primary_ID from the ctl_prime_ID field on the form, which however is not yet generated? It is only generated when I start filling the other fields in the form.

 

[Thymus]

Yes, that seems to have been the problem. I added:

If Not IsNull(ctl_prime_ID) Then

Works like a charm.

 

[PhonLing]

First off, thanks to both of you for this very useful thread. I'm trying to do pretty much the same thing, and have based my code on this discussion. Nevertheless, I can't seem to get it to work entirely.

I have a main form ("frmVFricTabbedForm") with several tabs on it, into which I'd like to enter language information, including what sounds (segments) exist in the language. The most intuitive user interface involves checkboxes for each segment. The underlying data structure is to have a table of possible segments (tblSegments), which has a many-to-many relationship with the main table (tblLangInfo). I have a junction table set up (tblSegmentLangJoin) with fields LangID, SegmentID.

Right now I have an 'Inventory' tab with checkboxes corresponding to each sound, and an 'Update Inventory' button that writes any changes to the junction table -- this works perfectly. (Attached for illustration)

I cannot get the second part to work, where the checkboxes are ticked only if the corresponding entries exist in the junction table. Right now the boxes are cleared whenever I move from record to record. My code is as follows:

Private Sub Form_Current()

'Declare variables
Dim Dbs As Database
Dim Rst As Recordset
Dim Ctrl As Control

'Set current database
Set Dbs = CurrentDb

'Initialize checkboxes to 'false'
For Each Ctrl In Me("TabInventory").Pages("PageInventory").Controls
   If Ctrl.ControlType = acCheckBox And Left(Ctrl.Name, 8) = "CheckInv" Then Ctrl.Value = False
Next Ctrl

On Error Resume Next

Set Rst = CurrentDb.OpenRecordset("SELECT * FROM tblSegmentLangJoin WHERE tblSegmentLangJoin.LangID = " & Forms!frmVFricTabbedForm!LangID)

If Rst.RecordCount <> 0 Then
    While Not Rst.EOF
       For Each Ctrl In Me("TabInventory").Pages("PageInventory").Controls
            If Mid(Ctrl.Name, 9, 2) = Rst.Fields(1) Then Me(Ctrl).Value = -1
            Next Ctrl
        Rst.MoveNext
    Wend
End If

Rst.Close
Set Rst = Nothing
End Sub

Note that because my checkbox captions involve symbols, I'm referencing the name of the checkbox, not the caption. The checkboxes involved are all named "CheckInv#", where # corresponds to the SegmentID.

I hope this is clear. I've been googling other approaches for 2 days now, and nothing seems to work, so any advice would be much appreciated. Thank you!