Results 1 to 10 of 10
  1. #1
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30

    Help with VBA or Macros please

    Hi Guys and Girls,



    I have created a Database which we call a filechecker, its to check users are doing there job correctly.
    Everything i have done on the databse is from the internet and a few videos so please be gentle with how its built

    I would like some help with VBA with the following please:

    I have attached the database here:Test.zip
    Here are step i hope can help understand what i need help with:

    1. On the HomePage_frm select the File Checker btn.
    2. this will open the FileChecker_frm (there is some VBA code for this form upon loading to Name the btns as you can see(these names are from the Users_tbl Authority=Supervisors)
    3. when you click one of the btns ( eg.Richard Bartlett) it will open the FileCheckSelection_frm, this form has hidden btns ( you can see these in design view).
    4. what i want to do between the FileChecker_frm and the FileCheckSelection_frm is open the FileCheckSelection_frm but only display the btns that the user has a yes against in the Users_tbl.
    5 Also when the FileCheckSelection_frm loads with there buttons they can access, when they click the btns of choice it will open the form (eg. BrokerFileCheck_frm)
    6. when the eg.BrokerFileCheck_frm opens then it only open for the supervisor that was selected from the FileChecker_frm.

    i hope it all makes sense and any help would greatly apprechated.

    Thanks
    Simon

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I made some changes, see if it works for you, and take it from there.
    Attached Files Attached Files

  3. #3
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Hi Aytee, thanks for the effort but i have to have buttons and it can only show the supervisors not all users will have access to the database thank you.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I didn't do all your work for you! I gave some ideas of how to go about solving your problems. Change the row source to select only supervisors. BTW, you need a field to denote whether users are active or inactive. Not sure why you HAVE to have buttons.

  5. #5
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    I know they were example and i thank you, Buttons are for the users as that is what is wanted by the Director to make it more user friendly.

  6. #6
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    So far i have wrote some VBA but its not displaying as i want it, please see picture below and also the code, if anyone can help it would be great.
    im getting closer to my goal but need to be able to select for that specific user i select and open the file for that user only.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	10.1 KB 
ID:	28338

    Private Sub Form_Load()
    Dim SQL As String
    SQL = "Select * " & _
    "FROM Users_tbl " & _
    "WHERE 'Broker = -1 AND Executive = -1 AND Arborisk = -1 AND Claims = -1 AND Private_Client = -1 AND Client_Direct= -1'"


    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(SQL)


    Dim strText As String


    Dim myNum As Integer
    myNum = 4


    Do While Not rs.EOF


    strText = rs.Fields("Broker")
    strText1 = rs.Fields("Executive")
    strText2 = rs.Fields("Arborisk")
    strText3 = rs.Fields("Claims")
    strText4 = rs.Fields("Private_Client")
    strText5 = rs.Fields("Client Direct")


    'Make Buttons here
    'Set newButton = CreateControl(Me.Name, acCommandButton)


    Dim buttonName As String
    Dim actualButton As Control


    buttonName = "Command" & myNum


    UpdateButton buttonName, strText

    myNum = myNum + 1


    rs.MoveNext


    Loop


    End Sub


    Public Function MyOpenForm(FormName As String)
    DoCmd.OpenForm FormName
    End Function


    Private Sub UpdateButton(buttonName As String, strText As String)


    Dim cCont As Control


    For Each cCont In Me.Controls


    If TypeName(cCont) = "CommandButton" Then


    If cCont.Name = buttonName Then

    'cCont.Name = "gbtn_" & strText
    cCont.Visible = True
    cCont.Enabled = True
    cCont.Caption = strText
    cCont.ControlTipText = strText

    End If


    End If


    Next cCont
    End Sub

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is there a reason why my solution did you work for you?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I couldn't stand it any longer.....

    Here are some problem areas I see:

    ** You are using two text fields to link two tables in a 1 to many relationship. Employee Name is a poor choice to link tables.
    Click image for larger version. 

Name:	SimonhtcImg1.jpg 
Views:	17 
Size:	28.7 KB 
ID:	28365


    Better would be the PK field (Autonumber) from the One table to a FK field in the many table ( a Long Integer).
    Click image for larger version. 

Name:	SimonhtcImg2.jpg 
Views:	17 
Size:	44.0 KB 
ID:	28368

    ***** Making this change changes a lot of the forms and queries


    ** Read this carefully several times
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp


    ** Next: field names:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.
    ------------------
    Bad field name: "Grade?" (has the question mark")
    Good/better field names: "Grade", "IsGrade"

    Bad field name: "Renewal/New Business Report changes?" (has space, question mark and slash")
    Good/better field names: "RenewalNewBusinessReportChanges", "Renewal_New_Business_Report_changes"
    ------------------


    ** You have a field name "Form": this is a reserved word and shouldn't be used for object names.

    ** In code, you have a variable named "SQL". This is also a reserved word.




    I made lots of changes to the dB (but I didn't change the field names - that is your job) and probably messed up the import function.
    Use what you want or throw it all away...

    Good luck with your project.
    Attached Files Attached Files

  9. #9
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Thanks for the input, ill take your points on board and hopefully learn the language of Code through my error, as i said i am NEW to this and still learning.

    Thanks for the advise and ill update you once i finished the final product.

  10. #10
    Simonhtc4 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    30
    Hi Guys,

    just wanted update you on my progress, i have got my end goal and got it all to work with some VBA code.

    Here it is in 2 parts, FileCHeckers_frm and FileCheckSelection_frm.

    I hope someone is able use this for themselves one day.

    FileCheckers_frm
    Private Sub Form_Load()
    Dim SQL As String
    SQL = "Select * " & _
    "FROM Users_tbl " & _
    "WHERE Authority = 'Supervisor'"


    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(SQL)


    Dim strText As String
    Dim userID As String


    Dim myNum As Integer
    myNum = 4


    Do While Not rs.EOF


    strText = rs.Fields("Employee_Name")
    userID = rs.Fields("UserID")


    'Make Buttons here
    'Set newButton = CreateControl(Me.Name, acCommandButton)


    Dim buttonName As String
    Dim actualButton As Control


    buttonName = "Command" & myNum


    UpdateButton buttonName, strText, userID

    myNum = myNum + 1


    rs.MoveNext


    Loop


    End Sub


    Public Function MyOpenForm(FormName As String, userID As String)
    DoCmd.OpenForm FormName, , , , , , userID
    End Function


    Private Sub UpdateButton(buttonName As String, strText As String, userID As String)


    Dim cCont As Control


    For Each cCont In Me.Controls


    If TypeName(cCont) = "CommandButton" Then


    If cCont.Name = buttonName Then

    'cCont.Name = "gbtn_" & strText
    cCont.Visible = True
    cCont.Enabled = True
    cCont.Caption = strText
    cCont.ControlTipText = strText
    cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = userID
    '.Width = 100
    '.Height = 30
    '.Top = 12 + (curRow * 1584)
    '.Left = 12 + (curCol * 1584)
    '.BackThemeColorIndex = 1
    '.HoverThemeColorIndex = 4 'Accent 1
    '.HoverShade = 0
    '.HoverTint = 40 '60% Lighter
    '.PressedThemeColorIndex = 4 'Accent 1
    '.PressedShade = 0
    '.PressedTint = 20 '80% Lighter
    End If


    End If


    Next cCont


    End Sub


    FileCheckSelection_frm
    Private Sub Command12_Click()
    Dim userName As String
    userName = Command12.Tag


    DoCmd.OpenForm "ClaimsFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Command8_Click()
    Dim userName As String
    userName = Command8.Tag

    DoCmd.OpenForm "ArboriskFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Command14_Click()
    Dim userName As String
    userName = Command14.Tag


    DoCmd.OpenForm "ExecutiveFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Command13_Click()
    Dim userName As String
    userName = Command13.Tag


    DoCmd.OpenForm "ClientDirectFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Command15_Click()
    Dim userName As String
    userName = Command15.Tag


    DoCmd.OpenForm "PrivateClientFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Command9_Click()
    Dim userName As String
    userName = Command9.Tag

    DoCmd.OpenForm "BrokerFileCheck_frm", , , , , , userName
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub HomePage_Click()
    DoCmd.OpenForm "HomePage_frm"
    DoCmd.Close acForm, "FileCheckersSelection_frm"
    End Sub


    Private Sub Form_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs()) Then


    Dim userID As String
    userID = Me.OpenArgs()


    Dim SQL As String
    SQL = "Select * " & _
    "FROM Users_tbl " & _
    "WHERE UserID = " & userID


    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(SQL)


    Dim isBroker As Boolean
    Dim isExecutive As Boolean
    Dim isArborisk As Boolean
    Dim isClaims As Boolean
    Dim isPrivateClient As Boolean
    Dim isClientDirect As Boolean


    Do While Not rs.EOF


    strText = rs.Fields("Employee_Name")
    userID = rs.Fields("UserID")
    isBroker = rs.Fields("Broker")
    isExecutive = rs.Fields("Executive")
    isArborisk = rs.Fields("Arborisk")
    isClaims = rs.Fields("Claims")
    isPrivateClient = rs.Fields("Private_Client")
    isClientDirect = rs.Fields("Client Direct")


    'Make Buttons here


    For Each cCont In Me.Controls
    If TypeName(cCont) = "CommandButton" Then
    ' Broker
    If cCont.Name = "Command9" Then
    If isBroker Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    ' Executive
    If cCont.Name = "Command14" Then
    If isExecutive Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    'Arborisk
    If cCont.Name = "Command8" Then
    If isArborisk Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    'Claims
    If cCont.Name = "Command12" Then
    If isClaims Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    'Private Client
    If cCont.Name = "Command15" Then
    If isPrivateClient Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    ' Client Direct
    If cCont.Name = "Command13" Then
    If isClientDirect Then
    cCont.Visible = True
    cCont.Enabled = True
    'cCont.Caption = strText
    'cCont.ControlTipText = strText
    'cCont.OnClick = "=MyOpenForm(""FileCheckSelection_frm""," & userID & ")"
    cCont.Tag = strText
    Else
    cCont.Visible = False
    cCont.Enabled = False
    End If
    End If
    End If
    Next cCont


    'Set newButton = CreateControl(Me.Name, acCommandButton)


    'Dim buttonName As String
    'Dim actualButton As Control


    'buttonName = "Command" & myNum






    'UpdateButton buttonName, strText, userID

    'myNum = myNum + 1


    rs.MoveNext


    Loop
    End If
    End Sub

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

Similar Threads

  1. Macros
    By Highlander in forum Access
    Replies: 1
    Last Post: 05-23-2012, 02:18 PM
  2. Macros
    By kstyles in forum Programming
    Replies: 2
    Last Post: 07-12-2011, 01:38 PM
  3. Macros??
    By Rosier75 in forum Programming
    Replies: 3
    Last Post: 02-01-2011, 01:55 PM
  4. vba vs macros
    By RedGoneWILD in forum Access
    Replies: 4
    Last Post: 08-06-2010, 12:48 AM
  5. VBA vs Macros
    By Evgeny in forum Programming
    Replies: 3
    Last Post: 05-11-2010, 02:15 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