Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: Need a bit of help on access

    Hi, i have 2 problems i need help with.

    1st) im trying to design some sort of code where when a button is pressed it will show the field "notes" for that record only, so when you scroll to the next record its not visible until a button is pressed again for that record.

    2nd)once the above button is pressed i have a macro running where it sets a value for a particular field, in this case its the initials of the person who pressed the button. i have a button each for the people working with this system and every button sets different initals. what i want is once the field has the initials set it wont allow them to delete their initials anymore from the field anymore.

    any help appreciated, but whatever im doing is not working.
    thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    1st Problem:
    I'm not sure how the "notes" are stored, but sounds like you're going to need something in the OnClick event of the button, probably going to involve a dlookup() or something. Could you supply more information about how the "notes" are stored, how are they on the form, etc.? You'll probably also need something in the OnCurrent event of the form to wipe it clean for the next record, but we'll get to that later

    2nd Problem:
    i have a button each for the people working with this system and every button sets different initals.
    First off, I'd recommend creating a User Table and using fOSUserName() to get the UserID and lookup their initials rather than letting the user pick who they are, but that's just a suggestion. Otherwise, look into the Enable and Locked properties of the control to lock it down. Not sure how good your code skills are, but something like (untested):
    Code:
    me.ctlName.Enabled = false
    Me.Geek = True

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    The notes field is a memo type where customers have left notes about their query, now the reason im looking to do this is if everyone can see the notes before they take ownership of the record (i.e. initials are set in the owner field from the macro button thats pressed) they could potentially cherryp pick the customers with better notes.
    how would fosusername() work?
    will the users always have the initials stored with them while their logged in?and would it require a password for every user?

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    The beauty of fosusername() is that passwords aren't required, but it's still a fairly secure method of identifying who's using the database; it's not perfect, but no security method out there is, and this does pretty well considering the minimal amount of work involved IMO.

    If you want someone's initials, you first create a usertable with everyone's Windows Login, First Name, Last Name, Initials and anything else you may want to have (this can also be used as a rights management thing and other uses, but I'll leave that alone for now). Then when you want the initials of whomever is using the database, just do like:
    strInitials = nz(dlookup("fldInitials","tblUsers","fldLogin = '" & fosusername() & "'"),"Unregistered User")
    Just use a call like this whenever you want their initials (you may need to rename things based on however you name them).

    Back to your first problem, is there a reason you don't want to display the notes when you surf to that record? Why hide the notes first and then display only when a button is clicked?
    Me.Geek = True

  5. #5
    Join Date
    Sep 2008
    Posts
    101
    thanks a lot nckdryr, the fosudername() is really useful. im gonna give it a go....and i think ill give rights management a go aswell, because i want some forms to be accessed by staff and some restricted, is it difficult?

    the notes have information that might make the user cherry pick the ownership.....if the notes have something with an easy sale or its too difficult they might not want to take ownership and scroll to the next record
    cheers

  6. #6
    Join Date
    Sep 2008
    Posts
    101
    P.S how would a macro use the fosusername() function?
    or how would i get the initials to transfer into a specified field? at the moment i use a macro to setvalue "Intials of the person" depending on which button they press, and every user has their own button.....

  7. #7
    Join Date
    Sep 2008
    Posts
    101
    i've tried using this function but its not working for me?
    where would the strInitials be?is it in a form, table?
    would i have a button to run the code or would it just automatically load up from scrolling from record to record?

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    To limit access to forms, I usually have a boolean field in the user table, and then do a similar dlookup on the load event of the form. Or alternatively, enable/disable the button that opens the form in the first place.

    On the link I supplied earlier it details how to load the value into a textbox, do you need more help than that?
    Me.Geek = True

  9. #9
    Join Date
    Sep 2008
    Posts
    101
    appreciate your help nckdryr but im fairly new on access coding etc.....how could i get the button to lookup the username then enter the intials from the user field for the windows login they are using and enter the initials into the text box automatically after pressing it?
    and what about the notes?
    cheers again i know your helping me a lot but im a beginner.....thanks

  10. #10
    Join Date
    Sep 2008
    Posts
    101
    im using this in the text box to try it out as the control source:
    =nz(DLookUp("fldInitials","tblUsers","fldLogin = '" & fosusername() & "'"),"Unregistered User")
    if i use the strInitials = nz(dlookup("fldInitials","tblUsers","fldLogin = '" & fosusername() & "'"),"Unregistered User")
    it says it must contain a minus or equals sign at the beginning.....
    this is driving me mad.....

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    After you have the table set up, go to your button's properties, scroll till you find the OnClick property, then click the "..." button beside it and it should prompt you how to define the button's click event: Choose the Code option. When the module comes up (the code looking stuff), it should have the cursor between something like
    Code:
    Private Sub Command1_Click()
    
    End Sub
    These code is what Access "looks at" to figure out what to do when the button is clicked. If you've renamed your button, the name will be different but that's ok. Paste the following between the aforementioned lines:
    Code:
    On error resume next
    
    me.txtboxName = Nz(DLookUp("fldInitials","tblUsers","fldLogin = '" & fosusername() & "'"),"")
    You'll need to change "txtboxName" to be whatever the name of the textbox is where you want the initials to go. If you have a different name for your table/fields, you'll need to change those to.

    Let us know how you get along.
    Me.Geek = True

  12. #12
    Join Date
    Sep 2008
    Posts
    101
    it comes up with a message compile error, sub or function not defined.
    and then in VB this is highlighted fOSUserName?

  13. #13
    Join Date
    Sep 2008
    Posts
    101
    ive created a module now for fosusername but when i click the button nothing happens?

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    put an apostrophe in front of the on error statement, this will "comment" the code, so access effectively ignores it for now. Run the code again and make sure you're not getting any error. If you do, go into debug mode and try to figure out what it's complaining about. Did you change the names to match what is in your particular situation? Put your cursor in the function names and hit F1, do you understand what the function is doing? Remember, we're not here to do the work for you, only to point you in the right direction.
    Me.Geek = True

  15. #15
    Join Date
    Sep 2008
    Posts
    101
    theres no error anymore, i realised i didnt create the fosusername module.....
    but when i click the button now nothing happens?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •