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 SkipVought 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
0
0
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
OK, we're getting somewhere now, I understand what you're after [smile]

Just one final question and I think we'll be there, when they click Categorize Charges, what code do you have behind that button?

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
Private Sub CategorizeCharges_Click()
DoCmd.OpenTable "AmexCurrent", , acEdit
DoCmd.Close acForm, "StartupForm"
End Sub
 
Upvote 0 Downvote
OK, we can't filter the results using OpenTable, how about you make a form (with a RecordSource of AmexCurrent) then from the list of field names that appears drag on all the fields you want to use (they will come on as controls), then open it from the click of your button:
Code: 
Private Sub CategorizeCharges_Click()
DoCmd.OpenForm "YourDataFormName", acNormal, , "UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
DoCmd.Close acForm, "StartupForm"
End Sub
Also for this to work easily you will need to add a module and in that module add the following line
Code: 
Public Tempvar_CCN as String
Then remove any other references to "Dim Tempvar_CCN as String" etc. in your code and we should be good to go.

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
i dont have Dim Tempvar_CCN as string anywhere. i have Dim TempSQL as string....

So i tried what you said and i didnt filter. it just gave me the first record on AmexCurrent

[sadeyes] Valgore
 
Upvote 0 Downvote
Does your new form have record selectors on it?

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
The RecordSource is AmexCurrent if thats what you are asking.

all i did to the form was make it, add the RecordSource, add the fields, and put the code it...

Valgore
 
Upvote 0 Downvote
Sorry, meant to say Navigation Buttons.

If you turn them on then it will tell you at the bottom if the form is filtered and what reocrd (of the filtered records) you're currently 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
oh. yes it is on. it says unfiltered
 
Upvote 0 Downvote
Did you create the module and put the:
Code: 
Public Tempvar_CCN as String
in it?

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
Option Compare Database

Public Tempvar_CCN As String


thats what is in the module
 
Upvote 0 Downvote
If you put a breakpoint when you open your new form, it there a value in Tempvar_CCN?

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
yup. its the person i logged in as, which is correct
 
Upvote 0 Downvote
And you open your new form like this?
Code: 
DoCmd.OpenForm "YourDataFormName", acNormal, , "UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
The form definitely isn't filtering? If it isn't it won't say anything on the bottom, if it is, it'll say (filtered). Which it should always say the way we're opening the form.

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. it does say its filtered, but i still gives me all the records...
 
Upvote 0 Downvote
WAIT!!!! i think i got it to filter. i went to Advanced and it said the criteria was Like '**'
so i changed ** to Tempvar_CCN and it filtered!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
[2thumbsup]

i have no idea what this means though [smile]

Valgore
 
Upvote 0 Downvote
So the values at the bottom of the form when it loads are:

Record 1 of X (filtered)

Where X is the exact same number as the number of records in 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
It means that Tempvar_CCN wasn't holding a vlue when it was passed to that form basically [smile]

That's great news though, glad we managed to get this working! I can leave work now and go to the pub, I was staying until we'd fixed this... [wink]

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. so now how to i apply that to AmexCurrent table?
 
Upvote 0 Downvote
OK, go through to the form as normal, when it loads, right click on it and select properties. Make sure it's the properties for the Form. Then go to the data tab and see what is set for Filter.

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
UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, JANE*'
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
211
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
194
Andrzejek
Andrzejek
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
224
strongm
strongm
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
179
Andrzejek
Andrzejek
aldi07
  • Locked
  • Question
update a front-end database with a new release using vba
Replies
5
Views
95
aldi07
aldi07

Part and Inventory Search

Sponsor

Back
Top