Results 1 to 11 of 11
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Find if the source of control allows ZLF and what the setting is?

    I'm at a loss again to find the syntax for:



    In a form, I'm doing carious checks and changing background colors in controls.

    Some of the sources for controls Allow Zero Length (Short Text Data Type in the table).
    I'm trying to figure out if the control (from its source) has the property of Allow Zero Length, and if so, if it's yes or no.

    Some of the things I've done in the past look like this:

    Code:
     FldNm = Me.ActiveControl.Name
     FldVal = Me(FldNm)
     FldValOld = Me(FldNm).OldValue
     FldCaptn = Me.Controls(FldNm).Controls(0).Caption
    but I can't find or figure out the allow ZLS part.
    I don't want to cruise through all the controls (too many iterations every time a field is entered), I want it for a specific control.
    Thanks

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    https://learn.microsoft.com/en-us/of...h-property-dao

    It's a table field data level property, not a control property as far as I can tell?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    @ Minty, #2. Access throws an error #3315 at form entry if there is a ZLS entered in a field with "no" for allow ZL. Which is fine.
    I just need to know that later when processing on the control, or others, in a LostFocus subroutine.
    I understand the article is for touching on tables, but that's not the case for my need.
    You would think there is a way to know a control's source properties.

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Well you know what field the control is bound, so could you not then look at the source table of the controls field?
    My understanding is that control doesn't inherently know what the detailed properties of the table field are, only the data type.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    [QUOTE=Minty;509432 My understanding is that control doesn't inherently know what the detailed properties of the table field are, only the data type.[/QUOTE]
    That would be a shame in a supposedly OO environment.
    I guess I could write a routine (that would have to run dozens of times in a form), but since speed is somewhat important, I guess I'll resort to hard-coding and passing arguments, something I was trying to avoid.

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You could set a tag property on the control during form build and check that in a form wide routine rather than individually per control.

    I have never considered Access as being particularly Object Oriented, it does have some of the traits, but it's not it's underlying design.
    You can build classes to to make it more OO, but in a lot of instances it adds a layer of obfuscationthat only confuses following the processes involved, and brings nothing to the party.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Minty View Post
    You could set a tag property on the control during form build and check that in a form wide routine rather than individually per control.

    I have never considered Access as being particularly Object Oriented.
    That brings up another interesting question. To do what I want, I need to have an _LostFocus procedure in the "form" VBA program. So, I end up with 30 or so calls, of one line, to a procedure in my Form Help module. Is there a way instead to say, all entry style controls (textbox, command button, list box, etc.) will call this procedure?

    OOP came out , in real terms, many years after I started my development of RAD tools in the 70s and 80s. I've read a few books and many articles on the subject. I always thought, I'm kinda already doing all that with simple subroutines. If you look at the code syntaxes in my post #1, I can't say for you, but for me my subroutines and arguments passed made a lot more sense than what Access has done. Of course I had a routine that could be called from anywhere to return any "property" of a table and it's field "definitions" (think of a powerful DLookup). Other routines could do things (create tables, fields, modify them, up to building an entire custom db for a client, kind of like what the old table wizard and templates from MS do), I guess the fancy term for that now is a method.

  8. #8
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    What kind of control are we talking here?

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if I wanted to know something about a controls related field properties, I would look at the object where those properties are stored - in this case the recordset

    Me.Recordset(myControlName.ControlSource).Properti es("allowzerolength").Value

    this would fail for any field that does not have this property such a numbers so you need to do a check as to the datatype

    Me.Recordset(D_Payee.ControlSource).Type

    generally speaking better to use a before update event rather than lost focus if you are doing data validation


    you can call a public function instead of having vba code in the form - instead of [Event Procedure] on the event property put =myPublicEvent()

    you can include fixed parameters or [Form] to pass the form object. =myPublicEvent([FORM])

    Code:
    function myPublicEvent(frm as form)
    
    with frm
    '.....loop through all the controls
    you also have screen.activeform, screen.activecontrol, and screen.PreviousControl you can use as well

    Code:
    function myPublicEvent()
    
    with screen.activeform
    '.....loop through all the controls
    but these won't work for the lost focus event since the form will no longer be the active one

  10. #10
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Anyway, here's a little sub to get the Zero Length thing from a module outside the form class:
    Code:
    Sub getZL()
    
        Dim frm As Form
        Set frm = Forms("SomeForm").Form
        
        Dim item  As Variant
        For Each item In frm.Recordset.Fields
            Debug.Print "at root", item.Name, item.AllowZeroLength
            Debug.Print "in properties", item.Name, item.Properties.item("AllowZeroLength").Value
        Next item
        
    End Sub
    Similarly, if you want to get that from the form itself, then:
    Code:
    Sub getZL()
        
        Dim item  As Variant
        For Each item In Me.Recordset.Fields
            Debug.Print "at root", item.Name, item.AllowZeroLength
            Debug.Print "in properties", item.Name, item.Properties.item("AllowZeroLength").Value
        Next item
        
    End Sub
    And I don't really know what's going on here, but if you also want to get the control types in the form:
    Code:
    Sub getAllControlTypes()
        Dim ctl As Control
        For Each ctl In Me.Controls
            Debug.Print ctl.Name, ctl.Properties.item("ControlType").Value
        Next
    End Sub
    Or just the type of the control with focus:
    Code:
    Sub getCurrentControlType()
        Debug.Print Me.ActiveControl.Properties.item("ControlType").Value
    End Sub
    It's all in the form variable.

  11. #11
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Additionally, if what you want is to know if the control's control source allows zero length, then something like this should work:
    Code:
    Sub checkZeroLengthOfCurrentControl()
        Dim currCtlName As String
        currCtlName = Me.ActiveControl.Name
        
        Dim ctlSourceOfCurrCtl As String
        ctlSourceOfCurrCtl = Me.Controls.item(currCtlName).ControlSource
        
        MsgBox "Allows Zero Length?" & vbCr & Me.Recordset.Fields(ctlSourceOfCurrCtl).AllowZeroLength
    End Sub
    And you can implement it like this on, say, the GotFocus event:
    Code:
    Private Sub txtSomeTextbox_GotFocus()
        checkZeroLengthOfCurrentControl
    End Sub
    This is, of course, from the form class.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-30-2022, 05:18 AM
  2. Replies: 19
    Last Post: 06-26-2018, 07:13 AM
  3. Setting Control Source Property Issue
    By Sarah11 in forum Forms
    Replies: 2
    Last Post: 09-23-2017, 10:37 PM
  4. Replies: 3
    Last Post: 04-27-2016, 01:25 PM
  5. Replies: 3
    Last Post: 05-03-2014, 03:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums