Results 1 to 10 of 10
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Wildcard in filename determines form's background color (via CASE statement)

    Hello:

    I would like to get some help with a FormLoad routine which checks for a *wildcard value* based on the file's filename.

    Background:


    - I have attached 2 sample DBs (form only). Their filenames are "011_Filename_ABC_01" and "011_Filename_XYZ_01".

    Envisioned Process:
    - If file "011_Filename_ABC_01" is opened, the routine will recognize wildcard = "*ABC*"
    - Thus, the Form_Load's CASE statement will apply background color #F9CDAA (sand'ish) to the form's header as well as to textbox "txt_FormName".
    - Alternatively, if file "011_Filename_XYZ_01" is opened it will identify wildcard = "*XYZ*" and then apply background color #CCFF99 (lime green'ish) instead.
    - If neither wildcard equal (ABC or XYZ) is found in the filename, background color #DBDBDB (gray) should be applied.

    Please see attached JPG for illustration purposes.

    Code:
    Private Sub Form_Load()
    
        'Pseudo Code...
    
        Select Case FilenameWildcard
            
            Case "ABC":     'Based on filename = "011_Filename_ABC_01"           
                Me.BackColor = F9CDAA
                Me.txt_FormName.BackColor = F9CDAA        
            
            Case "XYZ":     'Based on filename = "011_Filename_XYZ_01"            
                Me.BackColor = CCFF99
                Me.txt_FormName.BackColor = CCFF99
    
            Case Else       'Any other filename not including *ABC* or *XYZ*
                Me.BackColor = DBDBDB
                Me.txt_FormName.BackColor = DBDBDB
                
        End Select
        
    End Sub
    My question: How can this the above routine be accomplished?

    Thank you,
    EEH
    Attached Thumbnails Attached Thumbnails Filename Wildcard.jpg  
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why are there 2 db files?

    Can pull the database filename with Application.CurrentProject.Name which should return something like:

    011_Filename_XYZ_01.accdb

    Assuming the filename structure is always as you show:

    SELECT Case Mid(Application.CurrentProject.Name, 14, 3)

    If there is more variation in filename structure, will have to get more creative with code.
    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.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7:

    Thank you for the feedback. I will have other files with longer/shorter file names. Also, the wildcards will vary between 4-6 characters. I used these 2 only for demo purposes.

    Is there a way where length of file names AND length of wild characters won't make a difference? That is, ideally a method using the asterisk would be preferred and would allow greatest flexibility.

    Recommendations?

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7:

    I got this to work... thank you!

    Final question... how to I convert the HEX value into the 8-digit number using in VBA? I tried the HEX but that did work. I then tried some generic number (e.g., 15325906) and it did work.

    Naturally, I need to be able to identify the corresponding 8-digit numbers for any color I choose.
    Attached Files Attached Files

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Quick follow-up... changing to RGB works great.

    Me.FormHeader.BackColor = RGB(255, 237, 65)
    Me.txt_FormName.BackColor = RGB(44, 62, 162)

    Thank you for the assistance, June7. Much appreciated.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    You might also want to look at using the Instr() function as a substitute for working with wild characters (like in
    Code:
    If Instr(sFileName,"ABC") >0 Then
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- thank you... I'll look into it! Appreciate the feedback.

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Thank you... your recommendation works like a charm... it's extremely flexible.

    Code:
        SourceSystem = (Application.CurrentProject.Name)
        
        'Define form name
        FormName = "Name of Form"
        SourceName1 = "ABC"
        SourceName2 = "DEF"
        SourceName3 = "GHI"
        SourceName4 = "KLM"
        SourceName5 = "XYZ"
        
        'Apply color codes (form header and textbox)
        If InStr(SourceSystem, SourceName1) > 0 Then
            
                Me.FormHeader.BackColor = RGB(232, 27, 43)
                Me.txt_FormName.BackColor = RGB(232, 27, 43)
                Me.txt_FormName.ForeColor = RGB(255, 255, 255)
                Me.txt_FormName.Caption = FormName & " (" & SourceName1 & ")"

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Are you putting this code in each forms module? I ask as thats what it appears to be in your initial post.
    I changed the naming convention for simplicity for the example (ie.MyDatabaseC1.accdb, MyDatabaseC2.accdb,MyDatabaseC3.accdb,etc.)
    Use a sub in a standard module and pass the form to it.

    Code:
    Public Sub sSetMyColors(frm As Form)
    
    
        Dim fil As String
        Dim x As String
    
    
        fil = Application.CurrentProject.Name
    
    
        x = Mid(fil, InStrRev(fil, ".accdb") - 2, 2)
    
    
        Select Case x
    
    
        Case "C1"
            frm.FormHeader.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.ForeColor = RGB(255, 255, 255)
            frm.txt_FormName.Caption = frm.Name & " (C1)"
            
        Case "C2"
            frm.FormHeader.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.ForeColor = RGB(255, 255, 255)
            frm.txt_FormName.Caption = frm.Name & " (C2)"
            
        Case "C3"
            frm.FormHeader.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.BackColor = RGB(232, 27, 43)
            frm.txt_FormName.ForeColor = RGB(255, 255, 255)
            frm.txt_FormName.Caption = frm.Name & " (C3)"
    
        End Select
    
    
    End Sub
    You can then call it in each form with

    Code:
    Private Sub Form_Load()
    
     sSetMyColors Me
     
    End Sub
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 20
    Last Post: 07-22-2021, 04:26 AM
  2. Replies: 5
    Last Post: 08-25-2018, 04:55 PM
  3. Background Color on Form
    By PATRICKPBME in forum Forms
    Replies: 2
    Last Post: 01-25-2017, 02:43 PM
  4. Replies: 3
    Last Post: 01-01-2014, 11:21 PM
  5. background color in a form
    By taggart in forum Forms
    Replies: 1
    Last Post: 05-07-2009, 10:37 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