ACCESS FEED: Re: [MS_AccessPros] Referring control in a SubForm

Rabu, 23 Desember 2015

Re: [MS_AccessPros] Referring control in a SubForm

Hi Zjubias,

if the field is an attachment field, the information is not stored the same way. Here is code to count all attachments in a table and specific field. run this code by typing

? CountAllAttachments()
into the immediate window (Ctrl-G)

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function CountAllAttachments( _
    pTableName As String _
    , pFieldname As String
    ) as long
'strive4peace
   On Error GoTo Proc_Err

    Dim rs As DAO.Recordset _
    , rsAttach As DAO.Recordset

   dim sSQL as string _
    , nNumAttachments as long _
    , nNumRecords as long _
        ,nNum as long

   ssql = "SELECT t.[" & pFieldname & "]" _
    & " FROM [" & pTableName & "] as t"

    CountAllAttachments = 0
    nNumAttachments = 0
    nNumRecords =0


    Set rs = CurrentDb.OpenRecordset( _
        ssql _
        , dbOpenDynaset)

    If rs.EOF Then goto Proc_Exit

    with rs
       do while not .eof

           Set rsAttach = .Fields(pFieldname).Value

           If rsAttach.EOF Then goto NextRecord

           rsAttach.MoveLast
           rsAttach.MoveFirst
           nNum = 0
           nNum = rsAttach.RecordCount
           if nNum > 0 then
             nNumAttachments = nNumAttachments + nNum
             nNumRecords = nNumRecords + 1
           endif
NextRecord:
           .movenext
      loop
   end with 'rs

    CountAllAttachments = nNumAttachments
Proc_Exit:
   on error resume next
   Exit Function

Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   CountAllAttachments"

   Resume Proc_Exit
   Resume

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

code to count attachments in a specific record of a field would be a little different (I do have that too, but it is really tricky) ... but you get the idea of what has to happen?

warm regards,
crystal

Tip: Enforce Referential Integrity (RI) on Access Relationships (cc)
https://www.youtube.com/watch?v=_zxxc9jzWEg
- importance of integrity, how to fix data when RI can't be enforced

~ have an awesome day ~

On 12/22/2015 6:57 PM, Moi Ubias zjubias@gmail.com [MS_Access_Professionals] wrote:

Hello Liz, Crystal,

Solution is halfway working, I need to advance to new record, then go back inorder to take effect.. it seems it is not refreshing after putting a value in Attachment control.

How to refresh it, taking note that the button is sitting in he mainform and the control is in the subform?

By the way, data type of the control is text and it is a textbox control..

Thank you,
Zjubias

On Dec 22, 2015 12:29 AM, "crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Zjubias,

Liz is close (Hi Liz, good to see you helping others), but I think it would go more like this:

current event of the subform:

Me.parent.cmdButton_controlname.enabled = Not ( isnull(me.attachment) )

of course, you could expand the code out to this (still on current event of subform):

if isnull(me.attachment) then
   Me.parent.cmdButton_controlname.enabled = false
else
   Me.parent.cmdButton_controlname.enabled = true
end if

... and you'd also want to do this on the AfterUpdate event of the control this condition is dependent upon ...

~~~
However, the field name "Attachment" makes me wonder if the number of attachments have to be counted as opposed to seeing if there is simply a value ... what is the data type ? what is the control type?

warm regards,
crystal

Microsoft Access Basics
https://www.goskills.com/Course/Access-Basics
40 lessons to empower you to build an application with Access.
on sale till Dec 26 for 29 with a year to finish (less than 1/lesson) ... https://www.goskills.com/Gifts

~ have an awesome day ~

On 12/21/2015 11:14 AM, Liz Ravenwood liz_ravenwood@beaerospace.com [MS_Access_Professionals] wrote:

Something like this?

On current event of main form

If isnull(me.subformname.attachment) then

   Me.cmdButton.enabled = false

Else

   Me.cmdButton.enabled = true

End if

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, December 21, 2015 11:11 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Referring control in a SubForm

Dear All,

I have this situation, I have a mainform with a subform. On my mainform I have a command button that I would like to enable/disable, depending on the control in my subform..

I would like to disable my command button in my mainform if "Attachment" control in my subform is empty.. (I want to make sure that user will not be able to click the command button if attachment is null/empty)..

Thank you..

Zjubias