passing controlname to a function

[fedum]

I have problems passing a controlname to a function in this code.


Private Sub chkAlleBestellen_Click()
If chkAlleBestellen.Value = -1 Then
chkSelectAll -1, Forms!frmOverzichtProducten.Form, chkBestel.Name
Else
chkSelectAll 0, Forms!frmOverzichtProducten.Form, chkBestel.Name
End If

End Sub

Public Function chkSelectAll(waarde, formulierkeuze, ByRef objectnaam)
On Error GoTo Whoops

Dim frm As Form
Dim CNaaam As Control
Dim rst As dao.Recordset

Set frm = formulierkeuze
CNaaam = objectnaam

Set rst = frm.RecordsetClone
rst.MoveFirst

With rst
Do Until .EOF
.Edit
!cnaam = waarde
.Update
.MoveNext
Loop
End With
'Form.Refresh
OffRamp:
Exit Function
Whoops:
MsgBox "Error #" & Err & ": " & Err.Description
frm.objectnaam.Value = 1
Resume OffRamp
End Function

Thanks for your time and knowledge.

 

[MajP]

That is pretty sloppy code. You need to define the data types. You pass in a string, but then set it equal to a control.
I assume your controls and fields have the same name. I do not do it that. I give my controls different names than the fields

Public Function chkSelectAll(waarde as integer, formulierkeuze string, ByRef ctrlName as string) 
  On Error GoTo Whoops
  Dim frm As Form
    
    'you are passing a string but defined it as a control?
    'and below not even needed   
    'Dim CNaaam As Control
    
    Dim rst As dao.Recordset
    Set frm = formulierkeuze
    
    'below not needed 
    'CNaaam = objectnaam
   
    Set rst = frm.RecordsetClone
    rst.MoveFirst
    With rst
        Do Until .EOF
            .Edit
            [b].fields(CtrlName) = waarde[/b]
            .Update
            .MoveNext
        Loop
   End With

    'Form.Refresh
OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    frm.controls(ctrlName).Value = 1   
    Resume OffRamp
End Function

As I said my controls are using something like
txtBxBestel
and the bound field would be
Bestel

In that case I could modify the code something much cleaner

Public Function chkSelectAll(waarde as integer, frm as access.form, ByRef Ctrl as access.control ) 
   
  On Error GoTo Whoops
        
    Dim rst As dao.Recordset
    Set rst = frm.RecordsetClone
    rst.MoveFirst
    with rst
    Do while not .EOF
      .Edit
      .fields(Ctrl.controlsource) = waarde
      .Update
      .MoveNext
    Loop
   End With
OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    frm.controls(ctrlName).Value = 1   
    Resume OffRamp
End Function

Then call it like
chkSelectAll 0, me, me.chkBestel

 

[fedum]

Thanks!

I used your code but still get an error type 13: types don't match. I think it is the control. The Ctrl is a bound field. I used you call chkSelectAll 0, me, me.Bestel and didn't changed anything in your code.

 

[MajP]

Did you use the first or second example. I see in the second example at least one error in that I mixed some code in from the first

In the second example
frm.controls(ctrlName).Value = 1
should simply be
ctrl = 1
but I assume you mean -1, if it is really a yes no field

could simplify more

Public Function chkSelectAll(waarde as integer, rst as dao.recordset, ByRef Ctrl as access.control ) 
   
  On Error GoTo Whoops
    rst.MoveFirst
    with rst
    Do while not .EOF
      .Edit
      .fields(Ctrl.controlsource) = waarde
      .Update
      .MoveNext
    Loop
   End With
OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    ctrl.Value = -1   
    Resume OffRamp
End Function

chkSelectAll -1, me.recorsetclone, me.chkBestel

 

[fedum]

I tryed the last code.
Still get an error 3164 field cannot be updated.
Is it possible because there is a calculated field in the query?

 

[TheAceMan1]

How are ya fedum . . .

Try:

[blue]rst(cnaam) = waarde[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[fedum]

Hi,
I solved the problem with this
.Fields(Ctrl) = waarde

So, thanks for youre time and hope to meet again!