UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Getting Runtime Error On Recordset, Access 2016    
 
   
jabm
post May 30 2020, 03:06 PM
Post#1



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Hello again
Brief context for my question: The database I am creating is for a local maternity home and she may have multiple born children not all of which she has custody or will live with her.

On my subform (continuous form - one record for each child) to list the children of the potential resident I have a checkbox to show whether the child will be residing with the mom. When the checkbox is checked I would like the underlying VBA code to run a query checking how many of the children listed will be residing and if the the answer = the limit set then to lock(make read-only) the checkbox (I believe the limit for each resident is 2 previously born children, but the limit number is not the question - it is the checking of such a limit)

With my current code I am getting runtime error 3061: Too few parameters, Expected 1 and the line it is getting caught at is:
Set rst = CurrentDb.OpenRecordset(SQLQry)

My code is below - what am I doing wrong to get this runtime error -- is there a better way to do this?
In my 'Googling" I saw something about using DCount on the recordset but now I can't find the link I saw it in

CODE
Private Sub ThisChildToReside_AfterUpdate()
'if query (query: count(ThisChildToReside) where mom=current mom) result = 2 then
'lock (make readonly) ThisChildToReside
    Dim SQLQry As String
    SQLQry = "SELECT T_SGP_CallLog_ResChildInfo.[ResChildName]"
    SQLQry = SQLQry + " FROM T_SGP_CallLog_ResChildInfo"
    SQLQry = SQLQry + " WHERE Mom = [Form].[SF_SGP_CallLog_ResChildInfo].[Mom] and ThisChildToReside = True"
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset(SQLQry)
    If rst.RecordCount >= 2 Then
        ThisChildToReside.Locked = True
    End If
End Sub

Go to the top of the page
 
MadPiet
post May 30 2020, 03:34 PM
Post#2



Posts: 3,780
Joined: 27-February 09



Maybe this?

CODE
    SQLQry = "SELECT T_SGP_CallLog_ResChildInfo.[ResChildName]"
    SQLQry = SQLQry + " FROM T_SGP_CallLog_ResChildInfo"
    SQLQry = SQLQry + " WHERE Mom = [Form].[SF_SGP_CallLog_ResChildInfo].[Mom] and ThisChildToReside = True"
    SQLQry = SQLQry + " WHERE ThisChildToReside = True AND Mom = " & [Forms]![SF_SGP_CallLog_ResChildInfo]![Mom]
Go to the top of the page
 
jabm
post May 30 2020, 03:50 PM
Post#3



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Hmmm -- not quite -- may be getting closer though.

Now getting runtime error '2465' Microsoft Access can't find the field 'SF_SGP_CallLog_ResChildInfo' referred to in your expression

Go to the top of the page
 
jabm
post May 30 2020, 04:04 PM
Post#4



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Got it past the runtime errors this is what I needed
-- Forms("F_SGP_CallLog").SF_SGP_CallLog_ResChildInfo.Form!mom
but now it seems to be locking the checkbox for all children of all callers.

Hmm -- because it is a continuous subform - it is setting it for all rows not just for those for this caller...

So I guess my problem now is how to selectively affect subform controls based on row contents....
A separate problem, for a separate thread...

Go to the top of the page
 
MadPiet
post May 30 2020, 04:21 PM
Post#5



Posts: 3,780
Joined: 27-February 09



Check around here on the locking subform controls. I'm not sure if you can - I remember reading that subform controls are like an array or something so you can't reference ones on individual records. But then I could be completely wrong. <g>
Go to the top of the page
 
cheekybuddha
post May 30 2020, 04:34 PM
Post#6


UtterAccess Moderator
Posts: 13,007
Joined: 6-December 03
From: Telegraph Hill


Why bother with a recordset? DCount() would do.

CODE
  ThisChildToReside.Locked = DCount("*", "T_SGP_CallLog_ResChildInfo", "Mom = Forms("F_SGP_CallLog").SF_SGP_CallLog_ResChildInfo.Form!mom AND ThisChildToReside = True") >2


For your issue with a continuous form, you might be able to use conditional formatting and he the DCount() expression to enable/disable the control.

--------------------


Regards,

David Marten
Go to the top of the page
 
jabm
post May 31 2020, 06:40 PM
Post#7



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thank you all -- will get back to this in the morning to try these suggestions.
Have a Blessed Sunday!
Go to the top of the page
 
tina t
post May 31 2020, 07:06 PM
Post#8



Posts: 6,675
Joined: 11-November 10
From: SoCal, USA


QUOTE
Hmm -- because it is a continuous subform - it is setting it for all rows not just for those for this caller...

well, the issue wouldn't be that the subform's form view is ContinuousForms. the issue is that you can't just set the Locked property once and forget it.

i'm assuming that you have a mainform, probably SingleForm view (though that doesn't really matter either), that displays the "mom" records, and a subform that displays the child records for the Current mom record in the mainform.

i'd probably run the code in the mainform's Current event procedure, to lock/unlock the control in the subform as you move through the mom records in the mainform. if you want to dynamically count the child records in the subform, to "stop" after the target number has been reached during data entry in the subform, then also run the code in the subform's ...hmm, Current event would do it, i think.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
jabm
post Jun 3 2020, 12:10 AM
Post#9



Posts: 73
Joined: 29-January 20
From: Londonderry, NH. USA


Thank you all -- between the help here, a little more research and some trial and error, I got this to work with the code below

The hint of the conditional formatting worked for some other issues I was running into, but not for this as conditional formatting is not available for check boxes (and to my chagrin, neither is it for command buttons)

CODE
Private Sub ThisChildToReside_AfterUpdate()
'basic premise if the number of True values for ThisChildToReside for the current mom exceeds the set limit
'(currently set as 2 [global constant]) then the current value is set to false and an message is given
'
'Note for self:  Checkbox values are -1 for yes/true and 0 for no/false
'see https://superuser.com/questions/694919/why-do-yes-no-values-in-microsoft-access-map-to-1-and-0/710356

    Dim CntResChildrenPreSave As Integer
    Dim CntResChildrenPostSave As Integer
    
    If Me.ActiveControl.Value = -1 Then  'adding resident child
        CntResChildrenPreSave = DCount("ThisChildToReside", "T_SGP_CallLog_ResChildInfo", "[ThisChildToReside] = True and Mom = " & Me.Mom.Value)
        'this dcount just queries the table, but the change of state that triggers this has yet to be saved
        'forcing save if not already at limit
        If CntResChildrenPreSave < ResChildLimit Then
            DoCmd.RunCommand acCmdSaveRecord
        Else
            'At (or past?) limit do not let this save reset to 0
            MsgBox "Limit of " & ResChildLimit & " has been reached, setting to no"
            ThisChildToReside.Value = 0
        End If
    Else 'removing resident child
        'forcing save (not forcing the save and user goes back to re-adding the count returned by the DCount is off)
        DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub


Thank you all again!
Have a blessed night (its just past 1am here in NH, USA as type this)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 12:02 PM