border settings and an action button issue

[AnOddExperiment]

Hi everyone, I've only just started learning Access and am in the process of making my very first database. I have a couple questions that I couldn't find in the forums, probably because they are pretty easy.

1) I have a form comprised of four subforms of subforms. In order to make the form more visually appealing and easier to use, I have removed the unnecessary record selectors, however, I am having a very tough time figuring out how to make the borders of some of the forms completely dissapear. How do I do that?

2) I removed the record selectors as I mentioned above and opted instead to use the action button wizard to add a previous and next record button. On one of the parent forms, called "Tests" I do not want the user to be able to progress past the last record. IE, if a product has 3 tests associated with it, I do not want the user to be able to click the "next test" button past three times and open a new, 4th new record.

However, within tests, I do want the user to be able to specify what reagents he/she used within a subform, so there I DO want the user to be able to click the "next reagent" button until they open up a blank record.

How do I make them different?

Thanks!!

 

[boblarson]

Welcome to Access World Forums!

To answer your questions -

1. Open the main form in design view and then select the subform container which houses the subform. Open the properties dialog and find the properties SPECIAL EFFECT (and set it to FLAT) and BORDER STYLE (set it to TRANSPARENT).

2. In the code for the next button you should be able set the subform's ALLOW ADDITIONS property to NO when the record count is at 3 via code in the NEXT button.

 

[AnOddExperiment]

Thanks for the reply! Well, the second one worked like a charm, I can't believe it was that easy. The first one, however, still leaves a thin white border between the forms. I sadly don't have any url space to post a screenshot, but there are my settings:

SPECIAL EFFECT: flat
BORDER STYLE: transparent
BORDER WIDTH: hairline
BORDER COLOR: 215 (this is the same shade as my form background)

With those settings there is a very thin white line that appears along the left and top borders, and I'm not sure how to remove it. Thanks so much, and sorry about the late reply, I can only spend time on computer projects inbetween other responsibilities.

 

[boblarson]

Thanks for the reply! Well, the second one worked like a charm, I can't believe it was that easy. The first one, however, still leaves a thin white border between the forms. I sadly don't have any url space to post a screenshot, but there are my settings:

SPECIAL EFFECT: flat
BORDER STYLE: transparent
BORDER WIDTH: hairline
BORDER COLOR: 215 (this is the same shade as my form background)

With those settings there is a very thin white line that appears along the left and top borders, and I'm not sure how to remove it. Thanks so much, and sorry about the late reply, I can only spend time on computer projects inbetween other responsibilities.

You can actually upload a screenshot here if you don't have your own spot.

 

[AnOddExperiment]

Not the best screen-shot I've ever taken, but effective none-the-less So what do you think?

 

[boblarson]

Make sure Scrollbars on subform and main form are set to NONE and Dividing lines are set to NO on the subform and main form.

If that doesn't help, feel free to post the db so we can play around with it. If you haven't posted one yet, take a look here for instructions:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[AnOddExperiment]

Here it is, thanks so much for your help! It's driving me nuts.

 

[boblarson]

Okay, got it. The detail section on frmTestResultEntry(Subform4) is set to SPECIAL EFFECT: RAISED and if you change it to FLAT the white lines will go away.

 

[AnOddExperiment]

Ah hah! Sooo sneaky. Thanks so much!

 

[boblarson]

Glad to be able to help.

 

[AnOddExperiment]

Actually, while I've got you on the line...

I've created an action button for a switchboard I'm making from scratch that opens a form I would like to have password protected. It opens the form just fine, but is there an easy way to add to the "on click" macro to prompt for a password? I can't find it in the help files.

 

[boblarson]

Here's a way I use. I create a table to store a username (or group name) and an encrypted password value, and their security level. Then I use the attached little database (see attached) to create the encrypted password. Then I store the user name and the encrypted password and then put the encryption code in my database and when someone enters the password into the login form, the code will encrypt the password they gave and compare to the password in the table and if it matches then it will let them do what they want based on their security level.

So the benefit of that is that you don't need to store the actual password, but just the encrypted value.

Here's the encryption code for putting in a standard module:

Option Compare Database
Option Explicit

Public MyPassword
Public Function CodeKey(sPassword As String) As Long
' This function will produce a unique key for the
' string that is passed in as the Password.
    Dim I As Integer
    Dim Hold As Long

    For I = 1 To Len(sPassword)
        Select Case (Asc(Left(sPassword, 1)) * I) Mod 4
        Case Is = 0
            Hold = Hold + (Asc(Mid(sPassword, I, 1)) * I)
        Case Is = 1
            Hold = Hold - (Asc(Mid(sPassword, I, 1)) * I)
        Case Is = 2
            Hold = Hold + (Asc(Mid(sPassword, I, 1)) * _
                           (I - Asc(Mid(sPassword, I, 1))))
        Case Is = 3
            Hold = Hold - (Asc(Mid(sPassword, I, 1)) * _
                           (I + Len(sPassword)))
        End Select
    Next I
    CodeKey = Hold
End Function

 

[AnOddExperiment]

Hey, thank you very much for sharing all that. This is actually much more complex, but also more useful than what I had originally thought. I've spent some time playing with it, but what I'm having trouble implementing it properly as I've never worked with a module before, and I'm not entirely sure what they do. Is there any chance you could break down the steps involved in plugging that module you attached into my database and causing an opened form to prompt the user for a password (via another form?) ? Thanks again!

 

[boblarson]

Create a table with these fields:

Table -tblUsers
UserID - Autonumber (PK)
UserName - Text
UserPwd - Long Integer
SecurityLevel - Long Integer (FK)

Table - tlkpSecurityLevels
SecLevelID - Autonumber (PK)
SecLevelDescription - Text



1. go to your database window and click on the MODULES category

2. Click NEW to create a new Standard Module

3. Go to the name which should say something like Module1 and change it to something like basPwd or modPwd

4. Copy the entire code I gave that is in the blue box into that module.

5. Use the encryter database I gave you to create the logins for the users and give them the passwords you use, but not the number generated and enter the login name, encrypted password, and security level into your users table.

6. When the user opens your database you can have a form that opens that has the username box and the password box.

7. After they fill in the information and click OK (a button you add to your form) have the click event of the OK button do this

If CodeKey(Me.YourPasswordTextBox) = DLookup("YourEncryptedPwdFieldInTable","YourUsersAndPasswordTableNameHere", "[UserName]='" & Me.YourUserNameTextBox & "'") Then
    DoCmd.OpenForm  "FormNameHere"
Else
    MsgBox "You entered the wrong information"
End If

Instead of just opening the form you can also use a Dlookup, if they authenticated, to check their security level and open different things based on that.

Hope that helps

 

[AnOddExperiment]

Wow, that helps a ton! So, I think I almost have it, but I haven't entered the on click event in correctly, what did I miss? This is what I have so far. (I've signed up to take a VBA class for Access so I can code independently)

Option Compare Database

Private Sub Command8_Click()
If CodeKey(Me.Text3) = DLookup("UserPassword", "tblUsers", "[UserName]='" & Me.Combo1 & "'") Then
DoCmd.OpenForm "frmSwitchboard2"
Else
MsgBox "You entered the wrong information"
End If
End Sub

Also, I don't know anything VBA specifically, but I did take some C++ and Pascal. It is safe to assume I could add the lines...

DoCmd.CloseForm "frmPwdEntry"
DoCmd.OpenForm "frmSwitchboard"

at the end of the else statement?

 

[boblarson]

Is the Combobox actually returning the text as the bound column or is it the ID? If it is the ID then you want to use Me.Combo1.Column(1) to select the appropriate column, assuming that the 2nd column is the text and the first is the ID.

 

[AnOddExperiment]

That still returns an error.

Basically all I did was make the exact same tabels you listed, verbatum, and created a form (frmPwdEntry) with a combo box (Combo1) that looks up the field "UserName" (Row Source = SELECT tblUsers.UserID, which was entered by the combo-box wizard) and a totally unbound text box (Text3) which I labeled password.

I also created an action button with the on click event equal to the code I entered below.

I've attached a pictureless version of my database if that helps.

Thanks again

 

[boblarson]

what password did you use?

I tried encrypting the password "test" (encrypted value 1127) and it worked just fine

 

[AnOddExperiment]

!!

I swear 5 seconds ago it was returning an error. Oh, well, you touched it, it works now.

What the proper syntax to close a form so I can add that at the end of the if statement? I want the frmPwdEntry to close after it accepts the password.

 

[boblarson]

DoCmd.Close acForm, Me.Name, acSaveNo

use that exactly as written and it will close the form, plus you can use that in any form without having to include the name so a quick copy and paste will do you