Code help

[BlueJacket]

I'm trying to update a checkbox in a parent form based off a combobox in subform. Does this code make sense? It's giving me a 2465 run-time error: Microsoft Access can't find the field '|1' referred to in your expression. What am I missing?

Private Sub cboRevenueTypeCode_AfterUpdate()
    
    If Me.cboRevenueTypeCode = 1 Then
        [frmAccounting].Form![chkClosed] = True
    Else
        If Me.cboRevenueTypeCode = 2 Then
            [frmAccounting].Form![chkClosed] = True
        Else
            If Me.cboRevenueTypeCode = 4 Then
                [frmAccounting].Form![chkClosed] = True
            Else
                [frmAccounting].Form![chkClosed] = False
            End If
        End If
    End If
     
End Sub

Thanks in advance.

 

[NauticalGent]

Try using Me.Parent.Form![chkClosed]...

 

[CJ_London]

think your form references are wrong. try simpler code

Private Sub cboRevenueTypeCode_AfterUpdate()
    
    select Case Me.cboRevenueTypeCode
        case 1,2,4 
            parent.chkClosed = True
        Case Else
            parent.chkClosed = False
    End Select  
   
End Sub

 

[Ranman256]

if you want to update the PARENT form from the sub form then,
IF the parent form name is frmAccounting:

select case Me.cboRevenueTypeCode
   case 1,2,4
        forms!frmAccounting!chkClosed = True
   case  Else
        forms!frmAccounting!chkClosed = False
End select

 

[BlueJacket]

I haven't seen that case statement before, that's very cool. Instead of doing a whole bunch of if then else statements for a combo box, I can just use that.

 

[NauticalGent]

I haven't seen that case statement before, that's very cool. Instead of doing a whole bunch of if then else statements for a combo box, I can just use that.

Just recently found them myself. A much better alternative to If...Then...Else if you have more then two conditions. In my opinion anyway! Makes for much cleaner code too...

 

[CJ_London]

if your cboRevenueTypeCode is limited to 0...9 single character then another way is a single line


me.parent.chkClosed="124" like "*" & cboRevenueTypeCode & "*"

 

[BlueJacket]

The new code worked like a charm. Thanks everyone! Also good to see what I was doing wrong with my old code.

 

[NauticalGent]

if your cboRevenueTypeCode is limited to 0...9 single character then another way is a single line


me.parent.chkClosed="124" like "*" & cboRevenueTypeCode & "*"

CJ...can you explain this? I can't figure out what's going on with your code...

 

[BlueJacket]

if your cboRevenueTypeCode is limited to 0...9 single character then another way is a single line


me.parent.chkClosed="124" like "*" & cboRevenueTypeCode & "*"

I probably won't use this, as I can see additional revenue codes being used in the future, even if I'll always want just these three codes to trigger the Closed event. I still have a couple of questions though.

Your earlier code was just a simple "parent.chkClosed" to reference the check box, here you have "me.parent.chkClosed". Is there a reason for that? Is it because the other one was apart of a bigger piece of code?

I'm not sure about the interactions here or how this one line of code would get everything done without some sort of If Then statement attached. Is chkClosed looking for a 1, 2, or 4 from cboRevenueTypeCode? Then assuming I want it to equal true somehow, else false?

 

[CJ_London]

@Nautical

"124" like "*" & cboRevenueTypeCode & "*"

produces a Boolean value, either true or false, which is then applied to chkClosed

Applies to any comparison - either there is a match, or there isn't. There's no 'might be'

@BlueJacket

I can see additional revenue codes being used in the future

providing it is less than 10 (i.e. single digit codes) then it would still work - if say code 8 becomes one to set chkClosed to true, just change "124" to "1248". If you had a code 12 then that would also return true which you would not necessarily want which is why single digit codes only. I often use it where the codes are alpha - you get 26 rather than 10.

With regards parent and me.parent that was just me copy and pasting. Either will work since Me is the default and does not need to be used - just as value is the default for a control so

me.ctrl.value
ctrl.value
ctrl

all return the same thing - the value of the control in the current form.

 

[NauticalGent]

Good to know, thanks for the lesson!

 

[BlueJacket]

@CJ_London

So it can only be used with Yes/No fields. That's pretty cool, even if it's very situational.

 

[CJ_London]

So it can only be used with Yes/No fields

in terms of assignment yes (although it will work with numeric fields, but only returns 0 or -1), but can also be used for progam flow e.g.

if "124" like "*" & cboRevenueTypeCode & "*" then
   'do something
end if
 
or 
 
while "124" like "*" & cboRevenueTypeCode & "*"
    'do something
wend
 
etc

note that unlike sql where you can have

"124" not like "*" & cboRevenueTypeCode & "*"

in VBA you have to use

not ("124" like "*" & cboRevenueTypeCode & "*")