Start Free Trial

asked on

Acccess 2010 Login routine issues with SQL, Works in Access 2010 but not with SQL

I have an Access 2010 Ftontend and be that will run this login routine with no problem.

But after I migrated he Backend to SQL, the Login will not work just sits there.
In the attached file,I highlighted the Spot where it stops.

Can anyone see what might be causing the issue and how to resolve it.

Thanks

If I run the login in Access 2010 works fine with the Tables local
************************************************************************************
Atfet migrating the Access Tables to SQl, get the screen below and nothing works

This is the VB routine that works in Access 2010 but not with SQL?
***********************************************************************
Private Sub Cmd_Log_Click()

DoCmd.SetWarnings False

Dim dbMyDB As Database
Dim rsMyRS As Recordset
Dim rsMyRSP As Recordset
Dim updatelgn As String
Dim role As Long
Dim User As String
Dim acc As String
Dim Active As Boolean
Dim UserID As Long
Dim HMFID As Long

Set dbMyDB = CurrentDb

This is where it hangs up:
Set rsMyRS = dbMyDB.OpenRecordset("Users", dbOpenDynaset)

rsMyRS.FindFirst "[HMF_ID] = '" & ([Forms]![Frm_Login]![Txt_User]) & "'"

'temp variables
role = rsMyRS!User_Role_ID
User = rsMyRS!User_Name
UserID = rsMyRS!User_ID
Active = rsMyRS!Is_Active
Txt_User = rsMyRS!HMF_ID

If IsNull([Forms]![Frm_Login]![Txt_User]) Then

MsgBox "Enter User ID", vbInformation
DoCmd.GoToControl ("Txt_User")
[Forms]![Frm_Login]![Txt_User] = ""
Exit Sub

ElseIf rsMyRS.NoMatch Then

MsgBox "Invalid User ID", vbInformation

DoCmd.GoToControl ("Txt_User")
[Forms]![Frm_Login]![Txt_User] = ""
Exit Sub

ElseIf Active = False Then

MsgBox "User ID Account Deactivated", vbInformation

[Forms]![Frm_Login]![Txt_User] = ""
DoCmd.GoToControl ("Txt_User")

Exit Sub

Else
End If

TempVars.Add "User", User
TempVars.Add "Role", role
TempVars.Add "Access", acc
TempVars.Add "UserID", UserID
TempVars.Add "HmfID", HMFID

Set dbMyDB = Nothing
Set rsMyRS = Nothing

DoCmd.RunMacro "MKHMFTBL"

End Sub
If-I-run-the-login-in-Access-201.docx

Jeffrey Coachman

It is not clear from your word doc, what line the code stops on...

Can you highlight the line in Yellow?

Or post a screenshot of the code window when the app crashes...

ASKER

Set rsMyRS = dbMyDB.OpenRecordset("Users", dbOpenDynaset)

This is the line it stops at

Scott McDaniel (EE MVE )

Are you sure your table is named "Users"? SQL Server migration will sometimes result in tables named "dbo_Users", and you'd need to change the name of the linked table to match your expectations (i.e. remove the "dbo_" portion).

Your code is a bit of a mess. You should always check for a NoMatch when using FindFirst, for example - in fact, you really don't even need to use FindFirst at all. You can instead write this:

Set rsMyRS = Currentdb.OpenRecordset("SELECT * FROM Users WHERE [HMF_ID]= '" & ([Forms]![Frm_Login]![Txt_User]) & "'" )

You'd then check to see if there are any records:

If Not (rsMyRS.EOF and rsMyRS.BOF) Then
'/ you found a record, so do your comparison here
Else
'/ no record found
End If

ASKER

This was not my Code, this was done by a so called certified MS Access programmer.

I am just trying to clean up the mess.

I'll Give your suggestion a try this morning.

Thanks

Jeffrey Coachman

<This was not my Code, this was done by a so called certified MS Access programmer.>
Perhaps they were, I cannot see anything wrong with the code

<I am just trying to clean up the mess.>
If it worked before, we just have to figure out why it is not working now, I don't see anything that is particularly "messy".

ASKER

It works with a Access BE, but with SQL it does not work?
Tried your code but just sits and does nothing?

ASKER

I have the login routine working with theis code: But when I compile the Access Frontend I get the attached error message. See Attached file. Any sggestions

******************************************************

Private Sub Cmd_Log_Click()

DoCmd.SetWarnings False

Dim dbMyDB As Database
Dim rsMyRS As Recordset
Dim rsMyRSP As Recordset
Dim updatelgn As String
Dim role As Long
Dim User As String
Dim acc As String
Dim Active As Boolean
Dim UserID As Long
Dim HMFID As Long

Set dbMyDB = CurrentDb

Set rsMyRS = dbMyDB.OpenRecordset("Users", dbOpenDynaset)

rsMyRS.FindFirst "[HMF_ID] = '" & [Forms]![Frm_login]![Txt_User] & "'"

'temp variables
role = rsMyRS!User_Role_ID
User = rsMyRS!User_Name
UserID = rsMyRS!User_ID
Active = rsMyRS!Is_Active
Txt_User = rsMyRS!HMF_ID

If IsNull([Forms]![Frm_login]![Txt_User]) Then
MsgBox "Enter User Name", vbCritical, "Access Denied"
[Forms]![Frm_login]![lbl_name].Visible = True
DoCmd.GoToControl ("Txt_User")
[Forms]![Frm_login]![Txt_User] = ""
Exit Sub

ElseIf rsMyRS.NoMatch Then
MsgBox "Invalid User Name", vbCritical, "Access Denied"
DoCmd.GoToControl ("Txt_User")
[Forms]![Frm_login]![Txt_User] = ""
Exit Sub

ElseIf Active = False Then
MsgBox "Account Deactivated", vbCritical, "Access Denied"
[Forms]![Frm_login]![lbl_name].Visible = True
DoCmd.GoToControl ("Txt_User")
[Forms]![Frm_login]![Txt_User] = ""
Exit Sub
Else
[Forms]![Frm_login]![lbl_name].Visible = False
End If

TempVars.Add "User", User
TempVars.Add "Role", role
TempVars.Add "Access", acc
TempVars.Add "UserID", UserID
TempVars.Add "HmfID", HMFID

Set dbMyDB = Nothing
Set rsMyRS = Nothing

DoCmd.Minimize
DoCmd.RunMacro "MKHMFTBL"

End Sub
Compile-runtime-error.docx

ASKER

I still having issues with my login form which is supposed to check if the user ID is valid or not.
The backend is SQL DB and the Frontend is Access 2010. For some reason the Login form comes up and you can enter your ID number but when you hit the Enter Key it just sits there and does nothing. Like it neve gets to the IF condition statement to check.

Tried using LSMconsulting suggestion, but can not get it to work with SQL?

I have attached the my vb code. I think it has something to do with talking with SQL?

Need help!!
Thanks,
expnote.txt

Scott McDaniel (EE MVE )

Can you set a Breakpoint in the code and see exactly what line is failing? to do that, click on the Set MyDB line and press the F9 key, and then run the code. You should be taken into the VBA Editor in Debug mode. You an use the Debug menu to step through your code and determine exactly which line is throwing the error.

You should also remove the SetWarnings line, since this can hide the errors you're getting back.

By the way, your code needs to be changed so that you look at the Form controls FIRST - before you pass those values into the WHERE clause for the recordset. Try the code below, and if it doesn't work, do the Breakpoint method as described above:

Private Sub Cmd_Log_Click()

Dim rsMyRS As DAO.Recordset
Dim updatelgn As String
Dim role As Long
Dim User As String
Dim acc As String
Dim Active As Boolean
Dim UserID As Long
Dim HMFID As Long
	
		If IsNull([Forms]![Frm_login]![Txt_User]) Then
			MsgBox "Enter User Name", vbCritical, "Access Denied"
			DoCmd.GoToControl ("Txt_User")
		        [Forms]![Frm_login]![Txt_User] = ""
			Exit Sub
		ElseIf rsMyRS.NoMatch Then
            		MsgBox "Invalid User Name", vbCritical, "Access Denied"
            		DoCmd.GoToControl ("Txt_User")
		        [Forms]![Frm_login]![Txt_User] = ""
            		Exit Sub
      
        ElseIf Active = False Then
             		MsgBox "Account Deactivated", vbCritical, "Access Denied"
            		[Forms]![Frm_login]![lbl_name].Visible = True
            		DoCmd.GoToControl ("Txt_User")
            		[Forms]![Frm_login]![Txt_User] = ""
         		Exit Sub
        Else
           		
        End If

 
  Set rsMyRS = Currentdb.OpenRecordset("SELECT * FROM Users WHERE [HMF_ID]= '" & ([Forms]![Frm_Login]![Txt_User]) & "'" )

If Not (rsMyRS.EOF and rsMyRS.BOF) Then	
 	TempVars.Add "User", User
    TempVars.Add "Role", role
    TempVars.Add "Access", acc
    TempVars.Add "UserID", UserID
    TempVars.Add "HmfID", HMFID
  
    DoCmd.RunMacro "MKHMFTBL"
End If

Set rsMyRS = Nothing    
    
End Sub

Open in new window

ASKER

Thanks,
I try this when I get into my office .

ASKER

Ok I tried what you gave me but still did not work. I have been working on this all day and this is where I have it working except the match park.:

The section between the **** is what it is not seeing?
the Match of the ID number with TXT_User control value.

Let me know if you see why it is not doing the Match and running the Macro?

Thanks for your help

current code:

Private Sub CMDenter_Click()
Dim dba As DAO.Database
Dim rst As DAO.Recordset
Dim rsthmf As String
Set dba = CurrentDb

Set rst = dba.OpenRecordset("users")

rsthmf = "[hmf_id]=" & Me!Txt_User

rst.FindFirst "[hmf_id] LIKE '*rsthmf*'"

If Not (rst.EOF And rst.BOF) Then

If IsNull(Me.Txt_User) Then
MsgBox "Enter User Name", vbCritical, "Access Denied"
DoCmd.GoToControl ("Txt_User")
Me.Txt_User.Undo
'[Forms]![Frm_Login]![Txt_User] = ""
Set rst = Nothing
Exit Sub
************************************************************************
‘IF Id is matched to the txt_user then it should run the Macro, but it does not find the Match just goes to the next if for nomatch

ElseIf rsthmf = [Forms]![Frm_Login]![Txt_User] Then
DoCmd.RunMacro "MKHMFTBL"
'End If
****************************************************************************
ElseIf rst.NoMatch Then
MsgBox "Invalid User Name", vbCritical, "Access Denied"
DoCmd.GoToControl ("Txt_User")
Me.Undo
[Forms]![Frm_Login]![Txt_User] = ""

Set rst = Nothing
Exit Sub
End If
End If

Set rst = Nothing
Set dba = Nothing

'TempVars.Add "isactive", Is_Active
'TempVars.Add "hmfid", HMF_ID
'TempVars.Add "Access", acc
'TempVars.Add "UserID", UserID

End Sub
Start-up-errors-screens.docx

ASKER

The other thing I notice that after it displays the enter user name or invalild user id it does not reset it to blank, it just brings of the invalid message everytime.

If I can get these two items working, I'll be stress free again.

Thanks again for your help so far.

Scott McDaniel (EE MVE )

Ok I tried what you gave me but still did not work.

What do you mean by "did not work"? Did it error out? Or did it seem to run, but did nothing?

Did you add a breakpoint to your code as I suggested earlier? You really, really need to do this in order to track down the issue.

ASKER

Just sat there and did nothing.

ASKER

I have managed to get it to run and it will work with the isnull and no match. But have not been able to get it to find the ID and them execute the Macro?

Plus when it return back to the TXT_user it hold the value and goes back to the nnomatch IF?
Seems somewhere in this code is the issue:
******************************************
Set rst = dba.OpenRecordset("users")

rsthmf = "[hmf_id]=" & Me!Txt_User

rst.FindFirst "[hmf_id] LIKE '*rsthmf*'"

If Not (rst.EOF And rst.BOF) Then
*****************************************************
The Attached file has the Error messages that I was getting at the Start. Something about dbseechanges or =field messages?

ASKER

I am wondering if SQL security client has something to do with this issue?

Scott McDaniel (EE MVE )

I am wondering if SQL security client has something to do with this issue?

I don't think so, but you can check this by trying to open SQL Server Management Studio, and then run a query against your table.

However, I think your code is just wrong. You've mixed different methods together, and they won't work like that. Try this:

Private Sub CMDenter_Click()
    Dim rst    As DAO.Recordset
    Dim rsthmf As String

    '/ did the user enter anything in the username and password field?
   On Error GoTo CMDenter_Click_Error

    If IsNull(Me.txt_User) Then
        MsgBox "Enter User Name", vbCritical, "Access Denied"
        Me.txt_User.SetFocus
        Me.txt_User.Undo
    Else
        '/ user entered a name in txt_user, so validate it
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.txt_User & "'")

        If (rst.EOF And rst.BOF) Then
            '/ no match found
            MsgBox "Invalid User Name", vbCritical, "Access Denied"
            DoCmd.GoToControl ("Txt_User")
            Me.Undo
            Me.[txt_User] = ""
        Else
          '/ found a match, so run your macro
            DoCmd.RunMacro "MKHMFTBL"
        End If

        Set rst = Nothing
    End If

CMDenter_Click_Exit:
   On Error Resume Next
   Exit Sub

CMDenter_Click_Error:
  Select Case Err.Number
    'Case 0
    'Case 1
    Case Else
      MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
             & "Occured in Module1:CMDenter_Click", vbOKOnly + vbExclamation, "Error"
      Resume CMDenter_Click_Exit
  End Select

End Sub

Open in new window

ASKER

I'll Giv it a try,
Thanks

ASKER

OK I tired your code but get the attached error messages about dbseechanges?

Tried it with a blank field and entering a non valid number and get the attached error mesages?
8302013-error-messages.docx

Scott McDaniel (EE MVE )

Change this line:

'/ user entered a name in txt_user, so validate it
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.txt_User & "'", dbOpenDynaset, dbSeeChanges)

See this article:

http://support.microsoft.com/kb/190620

I'm surprised you didn't find that article when you searched for the text of that error - you did search for that error yourself, right?

ASKER

Your Code works good now,
I made one change to this line to call the Field out directly and now I get no errors messages.
and it works good.

Set rst = CurrentDb.OpenRecordset("SELECT HMF_ID FROM Users WHERE hmf_id='" & Me.Txt_User & "'")
***********************************************************************
I just need to add one more IF condition for non active accounts:

Tried by entering this part for non-active: (the is_active field is a yes/no field)

Dim rst As DAO.Recordset
Dim rsta As DAO.Recordset
Dim rsthmf As String

Set rsta = CurrentDb.OpenRecordset("SELECT HMF_id, is_active FROM Users WHERE hmf_id='" & Me.Txt_User And "is_active=" & False & "'")

If (rsta.EOF And rsta.BOF) Then
'/ Account Deactivated
MsgBox "Account Deactivated", vbCritical, "Access Denied"
DoCmd.GoToControl ("Txt_User")
Me.Undo
Me.[Txt_User] = ""
Else

But it get hung in a loop or something, so even if active is True and keeps coming back to the same IF for Account Deactivated and not to the IF for a valid HMF_ID number?
I must be missing something to let it go to the next IF to check for Valid HMF_ID number?
**********************************************************************************

If I can have it check for two conditions like TXT_user and Active are true then run macro else mesasge "invalid or deactivated".
Been trying to get this to work but if the Both conditions ar enot met it will return the Message lilke it should.
But then if I try it again and both conditions are met. It just return the same message invalid aot deactivated and not run the Macro?
I know I am missing something in this line of code?

Set rst = CurrentDb.OpenRecordset("SELECT HMF_ID, IS_active FROM Users WHERE hmf_id='" & Me.Txt_User & " is_active=" & "Yes" & "'")

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks,
I'll Give it a go at on Tuesday and let you know. I sure it will work.

ASKER

Thanks for all your help with this. If I could give you a 1000 point I would.
This helped me in many ways.

Thanks Again