Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

error setting a tempvar 2

Status
Not open for further replies.
valgore

valgore

Technical User
Nov 12, 2008
180
US
So i have two temp vars. one called tempRecordSet and the other is called tempRecordSet2. tempRecordSet2 doesn't work. i know why, but i dont know how to fix it.
Code: 
Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent!Cardholder Name!)) = '" & Tempvar_CCN)

it is erroring out on UCase(trim(AmexCurrent!Cardholder Name!))
it doesnt like the space between Cardholder and Name. what do you use to tell Access that thats actually one field? i have tried using "Cardholder Name"! and [Cardholder Name]! but those dont work.

any help would be appreciated

Valgore
 
Sort by date Sort by votes
That looks good, did the recordset populate?

This might also sound a stupid question, but there is record in AmexCurrent that matches the CCN you're searching on?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
to answer your first question, it still didnt filter. i added your code above and it looks like this

Code: 
Dim tempRecordSet As Recordset, Password As String
    Dim TempSQL As String

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN

TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL

Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)
    'this sets the variable
Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN
Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, JANE*'")

and that didn't work.
i then changed it to only include your code:

Code: 
Dim TempSQL As String

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN

TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL

Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)
    'this sets the variable

and THAT didn't work.. its still not filtering

as for your second question, i think i know what your asking :)

the field in AmexCurrent has a template like this:

LASTNAME, FIRSTNAME - CARD#

the field in UNP that is called CCN has LASTNAME, FIRSTNAME - CARD#

I made it identical to each other.
 
Upvote 0 Downvote
i'm wondering if Access only keeps track of who logs in for the login screen because i have some button disabled depending on who logs in. that works, but if i close the form with the disabled buttons and re-open it, that are enabled again.

just another thought

Valgore
 
Upvote 0 Downvote
What happens when you take the value from the Debug.Print and try and run it in a new query (not created using code)?

What I meant by my question was, if for example the value of TempVar_CCN was "Quinn," or "Quinn, Harley" there would be a record in AmexCurrent with a cardholder name value of "Quinn, Harley - ######" so it actually returned a record?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
yes. the code returned the record that was imported. but it wont filter on it.

when i put what i got from the Debug.Print into a query, it works perfectly

SO CONFUSED lol

Valgore
 
Upvote 0 Downvote
Hmm, that is strange.

What are you doing with tempRecorsdSet2 once you've set it's value?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
uh.... nothing. there is my problem... im such an idiot :)
after i set the temprecordset2 i dont do anything with it. so how do i tell it to filter Amexcurrent?
im going to show all of my code so you can look at it and tell me what i need and dont need and where i need to put a filter thingy....

Code: 
Private Sub Command12_Click()
 
   'checks to see if User Name is null
    If IsNull(Trim(Forms!LogonScreen!User)) Then
        MsgBox "User Name required.", vbExclamation
        Forms!LogonScreen!User.SetFocus
        Exit Sub
    End If
    
    'checks to see if Password is null
    If IsNull(Trim(Forms!LogonScreen!Password)) Then
        MsgBox "Password required.", vbExclamation
        Forms!LogonScreen!Password.SetFocus
        Exit Sub
    End If
    'declares temporary variable
   Dim tempRecordSet As Recordset, Password As String
    Dim TempSQL As String

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN

TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL

Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)
    'this sets the variable
Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN
Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, JANE*'")
    'this sets the variable
    'this compares the password you enter to the one in the table. if they match, then access granted. then it closes
    'the logonscreen form and opens the startupform
    If tempRecordSet.RecordCount <> 0 Then
        Password = UCase(Trim(tempRecordSet("Password")))
        If Password = UCase(Trim(Forms!LogonScreen!Password)) Then
            MsgBox "Access Granted", vbExclamation
            DoCmd.Close
            DoCmd.OpenForm "StartupForm"
            'this asks if the security code field in the the table UNP is a certain number, then enable/disable buttons
            If tempRecordSet("SecurityCode") = 1 Then
                Forms!StartUpForm!QuitApplication.SetFocus
                Forms!StartUpForm!Command0.Enabled = False
                Forms!StartUpForm!Archive.Enabled = False
                Forms!StartUpForm!AddorEdit.Enabled = False
        End If
            If tempRecordSet("SecurityCode") = 2 Then
                Forms!StartUpForm!Archive.Enabled = False
         End If
         
         Exit Sub
    End If
    
     tempRecordSet.Close
    Set tempRecordSet = Nothing
    
   MsgBox "Incorrect password", vbExclamation
    
   txtPassword = Empty
   End If
End Sub

i knew i was missing something

Valgore
 
Upvote 0 Downvote
OK, I think we can do away with tempRecordSet2 altogether [smile]

Try something like (untested):
Code: 
Private Sub Command12_Click()
 
   'checks to see if User Name is null
    If IsNull(Trim(Forms!LogonScreen!User)) Then
        MsgBox "User Name required.", vbExclamation
        Forms!LogonScreen!User.SetFocus
        Exit Sub
    End If
    
    'checks to see if Password is null
    If IsNull(Trim(Forms!LogonScreen!Password)) Then
        MsgBox "Password required.", vbExclamation
        Forms!LogonScreen!Password.SetFocus
        Exit Sub
    End If
    'declares temporary variable
   Dim tempRecordSet As Recordset, Password As String
    Dim TempSQL As String

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN

    'this sets the variable
    'this compares the password you enter to the one in the table. if they match, then access granted. then it closes
    'the logonscreen form and opens the startupform
    If tempRecordSet.RecordCount <> 0 Then
        Password = UCase(Trim(tempRecordSet("Password")))
        If Password = UCase(Trim(Forms!LogonScreen!Password)) Then
            MsgBox "Access Granted", vbExclamation
            DoCmd.Close
            DoCmd.OpenForm "StartupForm", acNormal, , "UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
            'this asks if the security code field in the the table UNP is a certain number, then enable/disable buttons
            If tempRecordSet("SecurityCode") = 1 Then
                Forms!StartUpForm!QuitApplication.SetFocus
                Forms!StartUpForm!Command0.Enabled = False
                Forms!StartUpForm!Archive.Enabled = False
                Forms!StartUpForm!AddorEdit.Enabled = False
        End If
            If tempRecordSet("SecurityCode") = 2 Then
                Forms!StartUpForm!Archive.Enabled = False
         End If
         
         Exit Sub
    End If
    
    tempRecordSet.Close
    Set tempRecordSet = Nothing
    
   MsgBox "Incorrect password", vbExclamation
    
   txtPassword = Empty
   End If
End Sub
Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
nope.... i put some breakpoints to see what it gives me. here is what i got

Set Tempvar_CCN = tempRecordSet!CCN
this gives me DOE, JANE which is correct.

but the
DoCmd.OpenForm "StartupForm", acNormal, , "UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"

the Tempvar_CCN is empty

hope this helps...
Valgore
 
Upvote 0 Downvote
Try taking out the DoCmd.Close just to see what happens, from looking at the code I can't see any other reason that the variable would empty itself.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Anyway, I'd replace this:
Set Tempvar_CCN = tempRecordSet!CCN
with this:
Tempvar_CCN = tempRecordSet!CCN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
i tried taking out the DoCmd.Close and it still didnt filter. i also replace what PHV said and it still doesn't filter....

So we know that the Query works, so could we possibly make a query for each user and have it AutoRun after they login? or would that not work because the query wouldn't know who logs in?

Just a thought

Valgore
 
Upvote 0 Downvote
Is the StartupForm bound to the AmexCurrent table?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 
Upvote 0 Downvote
I mean is the RecordSource of that form set to your AmexCurrent table?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 
Upvote 0 Downvote
What controls do you have on your StartupForm to display the filtered records?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 
Upvote 0 Downvote
Well i have a button that takes you to AmexCurrent, but i dont have a button that pops up a seperate window that has the filtered records. so should the startup form have AmexCurrent in its RecordSource?
 
Upvote 0 Downvote
If you wanted to display the filtered version of AmexCurrent actually on StartupForm using the methods we've talked about previously then yes.

If you don't want to display the data on StartupForm then no.

It might be a good idea for us both to take a step back from filtering etc. and for you to tell me exactly what you want your application to do, right from the login screen.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 
Upvote 0 Downvote
ok. when a user runs the application, a login form pops up from an AutoExec macro. they choose their username from a dropdown and enters their password. if their username and password match, they gain access. then the Startup form displays and the login form closes. on the startup form, there are several buttons (Import Bill, Add/Edit Category Codes, Categorize Charges, Achive, and Quit Application)
if they click on Categorized Charges, AmexCurrent table should display and THIS is where the filtering should happen. when they click Categorize Charges, AmexCurrent should display with the table already filtered. Once every user has categorized their charges, the Admin (me) goes in and click the archive button that transfers everything from AmexCurrent that has something in the Category Code field in AmexCurrent into AmexHistorical.


Valgore
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
692
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
593
Andrzejek
Andrzejek
M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
534
SoftwareRT
SoftwareRT
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
621
strongm
strongm
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
380
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top