Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2021
    Posts
    14

    Field not visible in dropdown list when typing Me (dot) in VBA

    I'm using Access 2003. Building on a very old software of mine with an old client, so have to stick to 2003.



    I have a table "Inv_Table" that's bound to a form named "Invoicing".

    In the "Activate" subroutine of the form I have the following code ...
    Me.txt_Bank = Me.Bank
    Me.txt_Branch = Me.Bank_Branch
    Me.txt_Account_No = Me.Account_No
    'Me.txt_Lease_No = Me.Lease_No

    On the last line (above) Me.Lease_No is the name of my field and should be visible in the dropdown when I type Me (dot). Somehow it's not visible. I tried changing the field name to Lease_Num in the table just to check if it becomes visible in the form but to no avail.

    I had a similar problem a few days back with the field name "Bank_Branch" above. I had used just "Branch" as the original name but it didn't drop down. Somehow when I changed the name to "Bank_Branch" it suddenly became visible. It's mystifying as none of these are reserved words.

    I'd appreciate some help.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Me dot refers to a CONTROL on the form. Is there a textbox named txt_Lease_No?

    It's also unclear what you are attempting to do; setting control values to each other on the same form?

    In fact, if the table fields are properly bound to the textboxes, none of that code would be necessary at all.

    At the top of the form's code module, the first two lines should always be

    Option Compare Database
    Option Explicit

    This option will catch undefined variables or variable name misspellings.
    Last edited by davegri; 07-17-2021 at 09:27 PM. Reason: more detail

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That sure looks odd - like there's a control on the form named txt_Bank and one named Bank, and so on. Eight controls that are similarly named, but why?
    Whatever the issue is, it likely has nothing to do with the version of Access being used.

    "Me dot refers to a CONTROL on the form." As well as a ton of other things that are properties and methods.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jul 2021
    Posts
    14
    Quote Originally Posted by davegri View Post
    Me dot refers to a CONTROL on the form. Is there a textbox named txt_Lease_No? ... YES

    It's also unclear what you are attempting to do; setting control values to each other on the same form?
    I'm reading values from a temporary TABLE into my variables when the form is being opened.

    In fact, if the table fields are properly bound to the textboxes, none of that code would be necessary at all.
    The recordsource for the form is my table "INV_TABLE" and I've checked it's correct.
    i USED
    MsgBox Me.RecordSource to confirm
    This is a Continuous form. In the header of the form I have some unbound text boxes and hence I'm trying to store the values of the table into the variables. In the lower part of the form I have text boxes which are bound and they all work perfectly.


    At the top of the form's code module, the first two lines should always be

    Option Compare Database ... These both are present
    Option Explicit

    This option will catch undefined variables or variable name misspellings.

    Thank you for your response ... Answers interspersed above ...

  5. #5
    Join Date
    Jul 2021
    Posts
    14
    Quote Originally Posted by Micron View Post
    That sure looks odd - like there's a control on the form named txt_Bank and one named Bank, and so on. Eight controls that are similarly named, but why?
    I'm trying to store the contents of fields to variables. Hence used similar names ...
    eg. Me.txt_Bank = Me.Bank stores the contents of a field in my table named "Bank", into my variable named "txt_Bank"



    Whatever the issue is, it likely has nothing to do with the version of Access being used.
    Thx for clearing this

    "Me dot refers to a CONTROL on the form." As well as a ton of other things that are properties and methods.
    I agree. And since the form is bound to a table ie the RECORDSET of the FORM is INV_TABLE ... when I type Me.(dot) on my form, I should be able to see a dropdown of all the fields in my table (including other properties and methods, etc.) However, "Lease_No" is a valid field in my table but isn't visible when i type Me.(dot). I tried renaming the filed from "Lease_No" to "Lease" but that too didn't work. It's perplexing ... why are some fields visible when I type Me.(dot), and some not visible?

  6. #6
    Join Date
    Jul 2021
    Posts
    14
    I just tried ...

    Me.txt_Lease_No = Me.RecordsetClone.Lease_No (this worked)

    Me.txt_Lease_No = Me.Lease_No (this doesn't work)

    When I type MsgBox Me.Lease_No it throws up the foll error:
    it says Method or Data Member not found


    Something so basic is driving me nuts.

    Thanks for your response everyone. I'd appreciate if you'll can fathom what I'm doing wrong.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I have never used Me with fields???
    In fact I use Me to identify the controls if the names are the same as per form wizard.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    When I type MsgBox Me.Lease_No it throws up the foll error:
    it says Method or Data Member not found
    That's because Lease_No is a field in the form's recordsource, not a control name on the form.
    Msgbox [Lease_No] with the brackets should work.
    That's how you refer to a field variable that is NOT bound to a form control. Your use of the Me dot syntax MUST refer to an element defined on the form, e.g. a textbox, and won't work for just a field that happens to be in the form recordsource.

    Me.RecordsetClone.Lease_No (this worked)
    This works because recordsourceclone is a property of Me (the form).

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree that you don't use Me to preface a variable because the variable is not a member of the form (it's not an object, property or method). That is what the message is telling you. Don't know why it would work on the other lines (if in fact it did) but it doesn't really matter as the approach really isn't correct. Suggest you use some other naming method. What you have will be universally interpreted as a control, not a variable.

    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html
    Last edited by Micron; 07-18-2021 at 09:12 AM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Quote Originally Posted by davegri View Post
    MUST refer to an element defined on the form, e.g. a textbox, and won't work for just a field that happens to be in the form recordsource.
    Well, I tested and referencing field only in recordsource with Me. structure works for me. There is no control bound to that field and no control with that field's name. However, I find does not work for report, nor does bang (!). For report, field must be bound to textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    My testing of unbound field in recordsource disclosed this:

    Debug.Print Me.Lease_No will error.
    Debug.Print Me.[Lease_No] will display properly.
    Debug.Print [Lease_No] will display properly.

    So the brackets are necessary for the unbound field.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not in my test.
    Code:
    Private Sub Form_Current()
    Debug.Print Me.Hol_Date
    Debug.Print Me.HolName
    Debug.Print HolID
    Debug.Print Hol_Date
    End Sub
    Works just fine.

    However, I changed field name Hol_Date to HolDate but VBA still expects Hol_Date. I have done C&R and closed/reopened file and still thinks field is Hol_Date. Very odd.
    I had to remove table from RecordSource and re-set it before field name change was acknowledged.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I seem to remember that there is a quirk with forms, where any field in the recordsource does have a mapped control, even if not used on the form.?
    I have used Me.Updated = Date() to update a record when changed, although no such control exists on the form?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Very odd that a particular syntax will error for one person and not another. My approach for debugging is to try a variety of solutions until one works. Once I get one to work, I don't worry much about why the others didn't work.

    Edit:

    After C/R and restart,

    Debug.Print Me.Lease_No
    Debug.Print Me.[Lease_No]
    Debug.Print [Lease_No]

    all display fine, jibes with June7 findings.
    Last edited by davegri; 07-18-2021 at 05:29 PM. Reason: More testing EDIT

  15. #15
    Join Date
    Jul 2021
    Posts
    14
    Quote Originally Posted by davegri View Post
    That's because Lease_No is a field in the form's recordsource, not a control name on the form.
    Msgbox [Lease_No] with the brackets should work.
    That's how you refer to a field variable that is NOT bound to a form control. Your use of the Me dot syntax MUST refer to an element defined on the form, e.g. a textbox, and won't work for just a field that happens to be in the form recordsource.



    This works because recordsourceclone is a property of Me (the form).
    Thank you so much for explaining & clarifying! This is clear now.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-16-2020, 03:14 PM
  2. Replies: 5
    Last Post: 08-14-2017, 02:19 AM
  3. Multivalue field / checklist dropdown list
    By greggue in forum Forms
    Replies: 2
    Last Post: 08-22-2013, 11:20 AM
  4. Replies: 2
    Last Post: 05-31-2013, 03:27 PM
  5. All text visible while I'm typing
    By viro in forum Access
    Replies: 4
    Last Post: 06-24-2012, 05:11 PM

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