Access 2007/2010 Validate Userid and password using ADO

Thank you all and Merry Christmas!


I have this code:

When a user selects continue button from a signOn screen:

Private Sub ContinToDisp_Click()
'******************************* Set paths to Template **********************
Path1 = CurrentProject.Path
Path2 = "\FleetMarineUser\"
Path3 = Me.cboTemplateID
Templat = "MainSCGTemplate112511"
Strtxt1 = SCGTitle
'SCGPassword = "SCGTemplate"

'Check to see if data is entered into the TemplateID combo box
    If IsNull(Me.cboTemplateID) Or Me.cboTemplateID = "" Then
      MsgBox "You must enter a SCG ID.", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If


'*******************Check database for valid SCG Number
Set rs = New ADODB.Recordset
Me.cboTemplateID.SetFocus
sSQL = "Select * from OCATempTable where SCGNumber = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"

 
rs.Open sSQL, CurrentProject.Connection
If Not rs.EOF Then
' Open Template to Copy
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Templat)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True
'************ Create WorkBook For user WorkBook Name = TemplateID
    objXLApp.ActiveWorkBook.SaveAs (Path1 & Path2 & Path3)

'Open and close the proper Screens
DoCmd.Close acForm, "SignOn", acSaveNo
DoCmd.OpenForm "MyActions"

'send to Main Table
CurrentDb.Execute "QrySCGTab", dbFailOnError
' Delete the record from the SCGTemporaryTable
CurrentDb.Execute "QryDelSCG", dbFailOnError

Exit Sub
End If

'Check if in the SCGTable
Set rsSCGTable = New ADODB.Recordset
sSQLSCGTable = "Select * from SCGTable where SCGNumber = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"
rsSCGTable.Open sSQLSCGTable, CurrentProject.Connection
If rsSCGTable.EOF Then
    MsgBox "Invalid Login, please try again", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
    Exit Sub
    Else
' Open Template that has already been created
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Path3)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True
    
'Open and close the proper Screens
DoCmd.Close acForm, "SignOn", acSaveNo
DoCmd.OpenForm "MyActions"

End If

  rs.Close
  Set rs = Nothing
  rsSCGTable.Close
  Set rsSCGTable = Nothing

    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub
Private Sub Form_Load()
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet As Object
'declare variables (tempRecordSet becomes an object variable)
Dim rs As New ADODB.Recordset
Dim rsSCGTable As New ADODB.Recordset
Dim sSQLSCGTable As String
Dim sSQL As String
Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Strtxt1 As String
Dim Templat As String
Dim SCGPassword As String

Me.cboTemplateID.SetFocus
Me.cboTemplateID = ""
Me.txtPassword = ""
End Sub

Open in new window


Right now the way the code is:
Checks what is typed into cboTemplateID and looks for validation in OCATempTable.SCGNumber

What I want is:
Checks what is typed into cboTemplateID and looks for validation in OCATempTable.SCGNumber And OCATempTable.TemplateID

Then checks the password


Thank You very much!
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can change SQL string:
sSQL = "Select * from OCATempTable where SCGNumber = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"
add to query OCATempTable and check TemplateID
I can recommend query designer for testing.
May be you can upload your DB with sample data?
0
Amour22015Author Commented:
als315

Thank you for helping!

What I want is:
Checks what is typed into cboTemplateID and looks for validation in OCATempTable.SCGNumber And OCATempTable.TemplateID


What I said in the last post let me clarify

Checks what is typed into cboTemplateID. If match found in OCATempTable.SCGNumber then checks If the Template (Excel workbook) already exist. If it does not exit copy the Template otherwise open the existing Template.

OtherWise:

Checks what is typed into cboTemplateID. If match found in OCATempTable.TemplateID then
checks If the Template (Excel workbook) already exist. If it does not exit copy the Template otherwise open the existing Template

So that would have to be something like:

sSQL = "Select * from OCATempTable where SCGNumber = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"

Or:

sSQL = "Select * from OCATempTable where TemplateID = '" & cboTemplateID & "' AND  PassList ='" & Me.txtPassword & "'"

Maybe?:

sSQL = "Select * from OCATempTable where SCGNumber = '" & cboTemplateID & "' OR TemplateID= '" & cboTemplateID & '" AND  PassList ='" & Me.txtPassword & "'"

 
The DB would be hard to upload it is considered Classified...

Thank you for helping me!
0
Amour22015Author Commented:
als315

This part of the code:
Path3 = Me.cboTemplateID

I would like:
Path3 = OCATempTable.SCGNumber

instead?

I will also keep trying.

Thank you!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Amour22015Author Commented:
als315

This part of the code:
Path3 = Me.cboTemplateID

I would like:Correction
Path3 = OCATempTable.TemplateID

instead?

I will also keep trying.

Thank you!
0
als315Commented:
You can upload only form and nesessary tables with dummy data. Create new DB, import involved tables, clear tables, fill with some strings of dummy data, compress DB. It is difficult to give proper answer without real tables and field types.
You can use dlookup for Path3, but I am sure there is another way.
0
Amour22015Author Commented:
als315

Thank you,

Path3
I changed it lastnight to Dlookup

I do that inside the:
If Not rs.EOF Then

but what if there is more than one record?
is it still going to find the correct one

Also,

I changed the
SQL
with an OR included and that seems to work.

one other question?

How can I test to see if the workbook already exist?

Thank you!
0
als315Commented:
"but what if there is more than one record?" - this is reason to look to tables structure. Id field should be unique.
You can check template presence with this code:
 If Dir(FullPathToWorkBook) <> "" Then ...
 
0
Amour22015Author Commented:
als315:

Thank You for helping me!
 
"but what if there is more than one record?" - this is reason to look to tables structure. Id field should be unique.

But this is not what I am asking. I asking if there is more than one record doesn't the DLookup only look at the first record or because I have it inside the:
If Not rs.EOF Then
Path3 = DLookup("TemplateID", "OCATempTable")
 
will it find the correct record?

You can check template presence with this code:
 If Dir(FullPathToWorkBook) <> "" Then ...

So what you are saying is this:
If Dir(Path1 & Path2 & Path3)<>"" Then???

I am going to check it out.

Thank you
 
 
0
Amour22015Author Commented:
aikimark


Why do you keep moving my postings to snippet?
0
Amour22015Author Commented:
Thank you all for helping me!

This is not working?

If Not rs.EOF Then
Path3 = DLookup("TemplateID", "OCATempTable")
If Dir(Path1 & Path2 & Path3) <> "" Then


Please help me!
0
aikimarkCommented:
the DLookUp() function has a third parameter that specifies a condition for which value gets returned.

Since this is happening in a recordset iteration loop, I suspect that you will want to construct a string expression that includes one or more fields in the rs fields collection.
0
als315Commented:
Can you show Path1, Path2, Path3?
0
Amour22015Author Commented:
als315

Thank You,

Path1 = CurrentProject.Path
Path2 = "\FleetMarineUser\"
Path3 = DLookup("TemplateID", "OCATempTable")

I did a messagebox

MessageBox " This is the path"    (Path1 & Path2 & Path3)


It came out like:
This is the path F:\SCGMS\FleetMarineUser\T02-02-0002


Thank you!
0
als315Commented:
Do you like to check file or folder?
F:\SCGMS\FleetMarineUser\T02-02-0002 - file or folder name?
0
Amour22015Author Commented:
als315

Thank you!

I don't understand you question:
Do you like to check file or folder?
F:\SCGMS\FleetMarineUser\T02-02-0002 - file or folder name?

To me this is the Folder:
F:\SCGMS\FleetMarineUser\

And this is the file name:
T02-02-0002

Maybe I have this backwards?:
rs.Open sSQL, CurrentProject.Connection
If Not rs.EOF Then
Path3 = DLookup("TemplateID", "OCATempTable")
MsgBox "This is a check " & (Path1 & Path2 & Path3)
If Dir(Path1 & Path2 & Path3) <> "" Then

' Open Template to Copy
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Templat)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True
'************ Create WorkBook For user WorkBook Name = TemplateID
    objXLApp.ActiveWorkBook.SaveAs (Path1 & Path2 & Path3)

'Open and close the proper Screens
DoCmd.Close acForm, "SignOn", acSaveNo
DoCmd.OpenForm "MyActions"
If SCGNumber <> "" Then
'send to Main Table
CurrentDb.Execute "QrySCGTab", dbFailOnError
' Delete the record from the OCATempTable
CurrentDb.Execute "QryDelSCG", dbFailOnError
End If
GoTo BeforeExit
Else
' Open Template that has already been created
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Path3)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True
   
'Open and close the proper Screens
DoCmd.Close acForm, "SignOn", acSaveNo
DoCmd.OpenForm "MyActions"

GoTo BeforeExit
End If
End If

In the message box above:
MsgBox "This is a check " & (Path1 & Path2 & Path3)

I get:
This is a check F:\SCGMS\FleetMarineUser\T02-02-0002


Thank You!
0
als315Commented:
You template file have no extension? May be it have xlt or xltx extension? In this case add after Path3: & ".xltx" (or xlt). Your code is saving user template as simple xlsx file. You should add proper extension for dir.
This is true, when file exists:
If Dir(Path1 & Path2 & Path3) <> "" Then
May be it should be:
If Dir(Path1 & Path2 & Path3) = "" Then
0
Amour22015Author Commented:
als315

Thank you,

I tried:
If Dir(Path1 & Path2 & Path3) = "" Then
 
It copied and did a SaveAs
This is what it should do and is correct.

I then tried to open the existing workbook
It copied and did a SaveAs
This is not correct?

I don't understand?
You template file have no extension? May be it have xlt or xltx extension? In this case add after Path3: & ".xltx" (or xlt). Your code is saving user template as simple xlsx file. You should add proper extension for dir.

Isn't .xlsx the correct extension?
Isn't .xlsx  extension for having VBA or Macro-Enabled worksheet?

where exactly should I put the ".xltx" extension in the code?

Path3: & ".xltx"

on what line of code?

do you mean during the SaveAs?

I tried changing:
objXLApp.ActiveWorkBook.SaveAs (Path1 & Path2 & Path3 & ".xltx")

And received an error:
Run-Time error '1004':  This extension can not be used with the selected file type.  Change the file extension in the file name text box or select a different file type be changing the Save as type.



Thank you for helping me!
0
als315Commented:
No only dir like to have extension. Xls objects can open files with default extensions.
If Dir(Path1 & Path2 & Path3 & ".xlsx") = "" Then
Dir can't find you file without extension and you always trying to create it. You can check it in VB editor window - set some breakpoints and run code.
xlsx is correct excel extension, but it is not an extension of excel template.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
als315

Thank you,

The extension:
xlsx
In this case was not correct

I used:
xlsm
And that seems to work.

What is the difference?

Thank you!
0
aikimarkCommented:
VBA macro code in an .xlsm workbook
0
Amour22015Author Commented:
aikimark

Thank you,

I am knew to creating a workbook in this manner and was wondering if I am doing the rightway?

also I am having problems on another posting. but is simular to this post only it is on the Excel side.  Maybe you all could help me out.:

Title:
Access 2007 To Excel 2007

Thank You for helping!
0
Amour22015Author Commented:
als315

Thank you that worked great!
0
Amour22015Author Commented:
Great Job and Happy New Year!
0
als315Commented:
You are welcome!
4 hours until New Year :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.