Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2015
    Posts
    17

    Unanswered: Opening forms connected via a foreign key with password protection

    Hi

    This topic may be covered elsewhere, so if it is, please redirect me.

    I am building a database of property information.

    The main form for the database contains information on property, such as the address etc. There is a subform connected to this
    main form via means of a foreign key, which contains the tenancy information for all the properties. There is a button on the main property form, that, when
    clicked, should display the details of the tenancy for that property. The button is password protected. At the moment the password protection part of
    the equation is working, but it is simply opening the form, and not showing the tenancy record connected to the specific property. I have pasted the VB code for the password protection below.

    Private Sub btnOpenWITPropertiesTable_Click()

    End Sub


    Private Sub Command72_Click()



    'Attached to On Click event of Command72_

    Dim strPasswd

    strPasswd = InputBox("Enter Password", "Restricted Form")

    'Check to see if there is any entry made to input box, or if
    'cancel button is pressed. If no entry made then exit sub.

    If strPasswd = "" Or strPasswd = Empty Then
    MsgBox "No Input Provided", vbInformation, "Required Data"
    Exit Sub
    End If

    'If correct password is entered open WIT Tenancy Details Form
    'If incorrect password entered give message and exit sub

    If strPasswd = "password" Then
    DoCmd.OpenForm "WIT Tenancies", acNormal

    Else
    MsgBox "Sorry, you do not have access to this form", _
    vbOKOnly, "Important Information"
    Exit Sub
    End If
    End Sub


    I want to be able to alter this code so that when you press the Tenancy Details button, and enter the password, it automatically displays the tenancy record connected to a particular property.

    If anyone needs more information, fire away. Hope someone can help.

    Thanks
    Karl

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want top open a form at a specific row then set that/those values in the wherecondition (the 4th parameter) parameter of the openform method.
    repalce the my_... with the name of your column
    ...when numeric
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_numeric_column = 12345"
    ..if string/text
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_string_column = '12345'"  'note the text value is delimted with ' (or "
    if date
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_date_column = #2105/06/08#"

    personbally I don't like hard coding passwords inside an Access applciation. Id rather use the network logon (google dev ashish api), and then use that logon to control what a user (through group permissions can or cant see). but that may be a step to far for you right now. bear in mind that when you come to deploy this application then you will have to deploy it as an MDE / ACDDE so the users cannot see the code behind the form, and you will need to make certain that users cannot just open the form. One fo the weaknesses in your current approach is that users could just open the form and circumvent any 'security' you think you've provided.

    'knowing' the network logon and designing a permissions table means you can
    1) decide to show the button on the form based on security settings. if a user cant even see the button, there is no risk they will press it 'accidentally'. Im a beliuever in the GUI concept that users whoudl be able to press what ever the heck they like , in what ever order they like. gray (disable/lock) out stuff they can't do right now, but hide if they can't EVER do...
    AND
    2) decide whether to open the form inside the form based on those same user permissions, that way round a user cannot circumvent security settings.
    bear in mind there may well be some legal requirement to demonstrate security around customers financial data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2015
    Posts
    17

    Question Not sure how this is going to help me - where does the foreign key figure in this?

    I don't see how getting the form to open to a specific column is going to help me. The whole idea is that the button allows me to open the subform displaying the record that is connected to the record displayed on the main form. The subform record that is opened is going to change depending on the record that is being displayed on the main form. Surely I need to include some code relating to foreign keys somewhere?

    Quote Originally Posted by healdem View Post
    if you want top open a form at a specific row then set that/those values in the wherecondition (the 4th parameter) parameter of the openform method.
    repalce the my_... with the name of your column
    ...when numeric
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_numeric_column = 12345"
    ..if string/text
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_string_column = '12345'"  'note the text value is delimted with ' (or "
    if date
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_date_column = #2105/06/08#"

    personbally I don't like hard coding passwords inside an Access applciation. Id rather use the network logon (google dev ashish api), and then use that logon to control what a user (through group permissions can or cant see). but that may be a step to far for you right now. bear in mind that when you come to deploy this application then you will have to deploy it as an MDE / ACDDE so the users cannot see the code behind the form, and you will need to make certain that users cannot just open the form. One fo the weaknesses in your current approach is that users could just open the form and circumvent any 'security' you think you've provided.

    'knowing' the network logon and designing a permissions table means you can
    1) decide to show the button on the form based on security settings. if a user cant even see the button, there is no risk they will press it 'accidentally'. Im a beliuever in the GUI concept that users whoudl be able to press what ever the heck they like , in what ever order they like. gray (disable/lock) out stuff they can't do right now, but hide if they can't EVER do...
    AND
    2) decide whether to open the form inside the form based on those same user permissions, that way round a user cannot circumvent security settings.
    bear in mind there may well be some legal requirement to demonstrate security around customers financial data

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope, its terminology

    I don't know your design, but lets assume
    the control containing the primary key is called my_control
    the actual column in the table is called my_primary_key, and its also numeric, so we don't need to delimit the supplied value.


    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_primary_key = " & my_control.value
    or possibly
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_primary_key = " & my_control
    or even
    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,,"my_primary_key = " & my_control.text
    whether you use a specific value or a value form a control or elsewhere is irrelevant in terms of the overall operation.

    what the above is doing, assumign you read the reference to the openform macro is:-
    open a form called "WIT Tenancies"
    limit the data displayed in the form to row(s) with where the value of a column in that row is equal to a specified value.
    so if you set the specified value to the the variabale / control on your 'parent' form the nbew form will only show rows matching that value.


    ferisntance
    say you had a form containing customer details, and lets say the unique customer identifier is stored in a control called tb_customer_id.
    you have a table for orders which has a column in it called customer_ID (so you know what customer have placed each specific order

    Code:
    DoCmd.OpenForm "ShowCustomerOrders", acNormal,,"Customer_ID = " & tb_customer_id.value
    if the control called tb_customer_id happened to hold the value of 12345 then effectivley what that is saying is
    open the form ShowCustomerOrders and only show orders where the customer ID is 12345
    next time round tb_customer_id.value could contain 98765

    its a where clause without using the word 'where'
    so conceivably you could say show me any orders place in the last 28 days from customers whose custoemr ID is in the range 900 to 999

    Code:
    DoCmd.OpenForm "ShowCustomerOrders", acNormal,,"Customer_ID between 900 and 999 and order_placed_on >= dateadd(d, 28, date())
    as a where clause (if you were using SQL thats
    Code:
    where Customer_ID between 900 and 999 and order_placed_on >= dateadd(d, 28, date())
    if you wanted to abstract that based on controls in a form then
    Code:
    DoCmd.OpenForm "ShowCustomerOrders", acNormal,,"Customer_ID between " & tb_lower_limit & " and " & tb_upperlimit & "  and order_placed_on >= dateadd(d, " & tb_number_of_days & ", " & tb_date_from)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2015
    Posts
    17

    Foreign keys and password protection

    Ok, I think I am starting to understand what you are getting at.

    So what specifically do I need to write?

    In my situation, the main form is called WIT Housing, with primary key HousingID.

    The button I press is called Tenancy Information.

    The linked subform is called WIT Tenancies, with foreign key frgnHousingID.

    When I am looking at a specific page, eg record 32 on the WIT Housing form,
    I want a button press to activate password protection, then once the password has been
    entered successfully, I want the WIT Tenancies form to also open, filtered to record 32.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    see post #4
    try to understand what is being said
    try to understand the code
    read the openform method reference referred to in post #2

    replace
    ...my_primary_key with the name of the column in the tennancy agreement table that yuou want to see when the button is clcked
    ...my_control.value with the name of the control that holds that value in the tennant form (I'm assuming its going to be the tenannt or pehaps more likely the tennancy agreement) dunno which
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2015
    Posts
    17

    More help please

    Hi.

    I've been trying pretty hard to get the code to work, trying various combinations of values but nothing is producing the results that I am wanting. I'm getting a bit frustrated.

    To review, here are the parameters I want in my search.

    The main form is called WIT Property, with primary key HouseID.

    I have a button called Tenancy Details on the WIT Property Form.

    When I press the button on the WIT Property Form, it should open a related form called WIT Tenancies, with primary key TenancyID and connected to the WIT Property Form by foreign key frgnHouseID. The record shown should correspond to the related record on the main form ie if I am on record number 4 in Property, it should open up Tenancy Record Number 4 in the Tenancy Form.

    I have repasted the code I am using. I really don't understand what I am meant to write in the place I have highlighted.

    Option Compare Database



    Private Sub btnOpenWITPropertiesTable_Click()

    End Sub


    Private Sub Command72_Click()



    'Attached to On Click event of Command72_

    Dim strPasswd

    strPasswd = InputBox("Enter Password", "Restricted Form")

    'Check to see if there is any entry made to input box, or if
    'cancel button is pressed. If no entry made then exit sub.

    If strPasswd = "" Or strPasswd = Empty Then
    MsgBox "No Input Provided", vbInformation, "Required Data"
    Exit Sub
    End If

    'If correct password is entered open WIT Tenancy Details Form
    'If incorrect password entered give message and exit sub

    If strPasswd = "Graham" Then
    DoCmd.OpenForm "WIT Tenancies", acNormal, "HouseID=" & ???????


    Else
    MsgBox "Sorry, you do not have access to this form", _
    vbOKOnly, "Important Information"
    Exit Sub
    End If
    End Sub

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what control has the value you want to use as the filter on the subform
    ...i cant answer this for you as I dont'[ have your form design infront of me, however i think its proabbly

    Code:
    DoCmd.OpenForm "WIT Tenancies", acNormal,, "frgnHouseID = " & mycontrolid
    repalce the mycontrolid with the name of the control that has the housing ID (ie the value of the on the parent form that has the key infoirmation you want to display on the child / sub form)
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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