Opening a navigation form for a users from a search form.

[macftm]

I am trying to create a form which will allow users to enter responses (using combo boxes for responses) to a series of questions which is to be stored in a table.

In order to access this form (which is a navigation form), the users must enter their unique details into a search form first which will then run a query to check if the user is allowed access.

However, I am finding it difficult to create the code for the search button so that when the user enters the details correctly, the navigation form appears for that person only appears. At the moment, whatever user name I enter, first user listed in the user details table appears.

I have the following code attached to the search button located on the search button:

Private Sub Command12_Click()
If Nz(DCount("*", "Q_PSE_Pupil_Search_T1"), 0) = 0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "Navigation Form1"
End If
End Sub

[Toyman]

I am trying to create a form which will allow users to enter responses (using combo boxes for responses) to a series of questions which is to be stored in a table.

In order to access this form (which is a navigation form), the users must enter their unique details into a search form first which will then run a query to check if the user is allowed access.

However, I am finding it difficult to create the code for the search button so that when the user enters the details correctly, the navigation form appears for that person only appears. At the moment, whatever user name I enter, first user listed in the user details table appears.

I have the following code attached to the search button located on the search button:

Private Sub Command12_Click()
If Nz(DCount("*", "Q_PSE_Pupil_Search_T1"), 0) = 0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "Navigation Form1"
End If
End Sub

You need to put a criteria in the DCount Function. Your current DCount function has not criteria and as such it will return the number you have in the table each time. Lets say the table has a field for userID (number Field) and Password (text field). So the user enters the password and userID and click the buttong. Your code then should look like this:

Dim sUserID as Integer
Dim sPassword as String

sUserID = Me.YourUserIDField
sPassword = Me.YourPasswordField

If DCount("*", "Q_PSE_Pupil_Search_T1", "UserID = " & sUserID & " And Password = '" & sPassword & "'") =0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "Navigation Form1"
End If
End Sub

You don't need the NZ function in DCount because if the search nets nothing, it will return a zero

[macftm]

Thanks for your quick reply Toyman. I am very sorry but I am very new to VB code and am a bit lost already.

I have created a new database as when I entered the following code into my existing database it didn't work.

I have create the following in my new database:

Table - T_User_Details which contains the following fields:

Username (primary key)
Password

Form - F_User_Search - containg:

Textboxes
txtUserName (unbound)
txtPassword (unbound)
Button
btnSearch

Query - Q_User_Search - containing the following search criteria on the table T_User_Details:

Username - Criteria = [Forms]![F_User_Search ]![txtUserName]
Password - Criteria = [Forms]![F_User_Search ]![txtPassword]

Form F_User_Search - linked to the result from the Query search

I have tried to create the following code for btnSearch:

Private Sub btnSearch_Click()
Dim sUserID as Integer
Dim sPassword as String

sUserID = Me.txtUserName
sPassword = Me.txtPassWord

If DCount("*", "Q_User_Searcg", "Username = " & sUserID & " And Password = '" & sPassword & "'") =0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "F_User_Search"
End If
End Sub

Can you tell me how this code is incorrect.

Thank once again for your help.

Regards
macftm

[macftm]

Sorry the second form linked to the query is called F_User_Details

[Toyman]

Thanks for your quick reply Toyman. I am very sorry but I am very new to VB code and am a bit lost already.

I have created a new database as when I entered the following code into my existing database it didn't work.

I have create the following in my new database:

Table - T_User_Details which contains the following fields:

Username (primary key)
Password

Form - F_User_Search - containg:

Textboxes
txtUserName (unbound)
txtPassword (unbound)
Button
btnSearch

Query - Q_User_Search - containing the following search criteria on the table T_User_Details:

Username - Criteria = [Forms]![F_User_Search ]![txtUserName]
Password - Criteria = [Forms]![F_User_Search ]![txtPassword]

Form F_User_Search - linked to the result from the Query search

I have tried to create the following code for btnSearch:

Private Sub btnSearch_Click()
Dim sUserID as Integer
Dim sPassword as String

sUserID = Me.txtUserName
sPassword = Me.txtPassWord

If DCount("*", "Q_User_Searcg", "Username = " & sUserID & " And Password = '" & sPassword & "'") =0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "F_User_Search"
End If
End Sub

Can you tell me how this code is incorrect.

Thank once again for your help.

Regards
macftm

You don't need to create a query just to run the DCount function. The second part to the function should be the table name where the criteria is checking. The third part is the criteria. It is similar to a Where clause. You are just duplicating the search process if you past the value to a query and doing a DCount to find the count number. You can do it in one line of code via the table

[macftm]

Cheers for that. I've enter the following code but the button is doing nothing when I press it.

Private Sub btnSearch_Click()
Dim sUser As String
Dim sPassword As String
sUser = Me.txtUserName
sPassword = Me.txtPassword
If DCount("*", "T_User_Details", "Username = " & sUserID & " And Password = '" & sPassword & "'") = 0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "F_User_Details"
End If
End Sub

[Toyman]

Cheers for that. I've enter the following code but the button is doing nothing when I press it.

Private Sub btnSearch_Click()
Dim sUser As String
Dim sPassword As String
sUser = Me.txtUserName
sPassword = Me.txtPassword
If DCount("*", "T_User_Details", "Username = " & sUserID & " And Password = '" & sPassword & "'") = 0 Then
MsgBox "Incorrect User Details! Please re-enter your details", vbExclamation, ""
Else
DoCmd.OpenForm "F_User_Details"
End If
End Sub

Have you check to ensure the On Click event of the BtnSearch is indeed set?

Also, is "UserName" a text field or is it a number field. If it is a text field then you have to put single quotes around it:
"Username = '" & sUserID & "' And Password = '" & sPassword & "'"

[macftm]

That's worked now, thank you so much for your help.

Regards

macftm