Results 1 to 13 of 13
  1. #1
    gblystone is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    5

    MACRO variable not recognizing data in a table

    Greetings,

    We use ACCESS to track data for our tenants.
    We use a three letter abbreviation for the tenant and a 5 number year/serial number as the primary reference within our database.
    The tenants are listed in a Table, it has relationships to other tables
    Forms are used to enter/change data for the clients.
    Recently, another tenant was added to the table of tenants. Since then, When I enter data for that tenant, the Macro(s) invoke the Case Else and displays the msg information.
    It does save the inputted information.
    All other functions for all other tenants have no issue.

    There are four Macros that use this data, the first two are functions (add and change) use the same script to validate the tenant information; the second two are Private Subs, they are similar with slightly different data fields.
    For brevity, I'm only providing what I think are the key statements.

    Private Function VALIDTENANT()


    Select Case TENANT.Value
    Case "ABC":
    Case "DEF":


    (Total of 89 Case statements)

    Else
    MsgBox "INVALID SHIPNAME"
    Me.SHIPANDCASNUM.SetFocus
    Exit Sub


    End If

    Case "": MsgBox "PLEASE FILL OUT THE TENANT FIELD"
    Case Else:
    MsgBox "INVALID TENANT NAME"
    Me.TENANT.SetFocus
    ' Me.TENANT.Text = ""
    ' Me.TENANT.SetFocus
    End Select
    End Function

    Private Sub DOTHIS()
    Dim statements...

    If Left(Me.TENANT, 3) = "ABC" Or Left(Me.TENANT, 3) = "DEF" Or (all 89 tenants are listed here) Then
    Else
    MsgBox "INVALID TENANT NAME"
    Me.TENANT.SetFocus
    Exit Sub
    End If



    (Various "If" statements to validate information in the data fields.)

    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec


    End Sub

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Why the Select Case? Is something done differently for each tenant, or is it just to verify the tenant code is valid?
    Consider a DCount instead of the CASE statements and also in the DOTHIS sub.
    Using this method, you won't need to change the code every time a tenant is added.

    Code:
    dim arg as string
    arg = left(me.Tenant,3)
    If DCount("*","tblTenants","Tenant='" & arg & "'") > 0 then     'valid tenant
    Another option would be to use a combo box to select the tenant. The combo box would get its list of tenants from your tenants table, so it would be impossible to select an invalid tenant code.
    Last edited by davegri; 08-05-2020 at 03:45 PM. Reason: changed DLookup to DCount, added comment

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Me thinks you cut out too much code....
    Maybe you would post your dB with 3 -5 tenant records - make up names: Mighty Mouse, Clark Kent, Daffy Duck,...


    Code:
    Private Sub DOTHIS()
        Dim statements...
    
        If Left(Me.TENANT, 3) = "ABC" Or Left(Me.TENANT, 3) = "DEF" Or (all 89 tenants are listed here) Then
    89 tenants in an "IF ..THEN" statement?? What a nightmare to maintain. (just sayin')

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I agree, this is a maintenance nightmare. There are better options than modifying code every time tenant changes. As davegri suggested, use DCount or even DLookup.
    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.

  5. #5
    gblystone is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    5
    Quote Originally Posted by davegri View Post
    Why the Select Case? Is something done differently for each tenant, or is it just to verify the tenant code is valid?
    Consider a DCount instead of the CASE statements and also in the DOTHIS sub.
    Using this method, you won't need to change the code every time a tenant is added.

    Code:
    dim arg as string
    arg = left(me.Tenant,3)
    If DCount("*","tblTenants","Tenant='" & arg & "'") > 0 then     'valid tenant
    Another option would be to use a combo box to select the tenant. The combo box would get its list of tenants from your tenants table, so it would be impossible to select an invalid tenant code.
    I'm not the author; he left a few months ago, I'm trying to fill in.

    I appreciate the abbreviated code. I did a test on the edit Macro; it works, but the problem persists. The odd thing is it's only the last tenant added, all others works fine.

    I'll try the add new later today.

    A combo box might be okay for a new entry, but the complication is that the tenant and number are combined to make a unique record. i.e. ABC20001, ABC20002 etc

    There are 89 tenants (and growing) with over 1100 active records.

    R/Greg

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    A combo box might be okay for a new entry, but the complication is that the tenant and number are combined to make a unique record. i.e. ABC20001, ABC20002 etc
    The combo box could show the whole string, all 8 characters instead of only the first three.

  7. #7
    gblystone is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    5
    The value in the cell is "STL", I deleted that record and modified one that was no longer being used. Same issue...

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As suggested, provide db for analysis.
    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.

  9. #9
    gblystone is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    5
    June7,

    The "tenants" I refer to are US Navy ships. The data isn't classified, but it is sensitive; I'd prefer to keep it off the web. Can we work one-on-one?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Make a copy of db, delete real/sensitive data.

    Sorry, no, I don't work one-on-one with members here.
    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,388
    Can we work one-on-one?
    As a Navy veteran, I can do that. (ET2) Here's a throwaway email address good for a week. Send me your email address there and we can hookup on regular email.

    fzqo6tsl90xyjgf@jetable.org
    Last edited by davegri; 08-08-2020 at 11:31 AM. Reason: had to change email address

  12. #12
    gblystone is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    5
    I figured it out!

    We use a front end/back end set up. It never occured to me to modify the front end. I added the value and it worked great.
    Unfortunately I had to use the listing of all 89 ships; the Dlookup didn't work.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy you got it working the way you want.
    But it would have been interesting to see your dB and help fix the design issues.
    Having to modify the code every time a ship is commissioned or de-commissioned would be tedious at best.

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

Similar Threads

  1. Replies: 3
    Last Post: 11-30-2018, 05:03 PM
  2. Split database not recognizing data validation rules
    By JoeCson in forum Database Design
    Replies: 5
    Last Post: 03-02-2017, 02:35 PM
  3. Year variable in data macro
    By lonesoac0 in forum Macros
    Replies: 1
    Last Post: 09-04-2014, 12:32 PM
  4. Table with variable data
    By kcollop in forum Database Design
    Replies: 14
    Last Post: 07-20-2012, 01:28 PM
  5. Macro not recognizing text box value
    By avarusbrightfyre in forum Access
    Replies: 1
    Last Post: 09-17-2011, 01:45 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