INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Code for Access reports using variables

VBA Code for Access reports using variables

(OP)
I've tried looking through the threads and found a few that were close. I have a query that contain multiple records that have multiple expense field such as AdminFee, LegalFee, MgmtFee etc. (there are 56 total). When I run the report if the user chooses just to see the AdminFee that's all I want to see on the report. I know it's a variable and I've used AdminFee.value, Adminfee.controlsource, AdminFee = varAdminFee. I've gotten so many error message I don't even remember half of them.

Any help would be greatly appreciated.

Thanks

RE: VBA Code for Access reports using variables

Hi,

What is the field name of the "multiple expense field?"

Typically...

CODE

WHERE ...[YOUR MULTI EXP FIELD] = 'AdminFee' ... 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code for Access reports using variables

(OP)
Those actually are my field names. AdminFee, LegalFee, MgmtFee. If you think of a operating budget for businesses, in this case multiple apartment complexes.

So:

Property Name AdminFee LegalFee MgmtFee
Skips Apt $30 $50 $20
Daves Apt $25 $60 $30
Franks Apt $40 $60 $30

The user just wants a report showing Admin Fee for each of the properties.

It has to be something simple but I can't seem to link the value of the field from the query to a unbound textbox.

Thanks

RE: VBA Code for Access reports using variables

The .ControlSource of a control binds the control to a data source. Not a value.

RE: VBA Code for Access reports using variables

In your VBA code that has the SQL code, make a selection for ONE of these fields, like...

CODE

Dim sSQL As String, bAdminFee As Boolean....

sSQL = sSQL & "SELECT [PROPERTY NAME]
If bAdminFee Then
sSQL = sSQL & ", ADMINFEE
End If 

Of course, somewhere else you need a user selection that would assign bAdminFee TRUE and all others FALSE.

BTW, I'd be apt to use a Select Case...End Select, rather than If...Then...ElseIf...End If

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code for Access reports using variables

Skip seems to be guessing that you need help with the S of SQL.

Whereas I guessed that you have more result than you need, and you are trying to display only some of it.

I guess that you shouldn't make the people willing to offer help make so many guesses.

RE: VBA Code for Access reports using variables

(OP)
in my report I have an unbound textbox = txtExp
In a case statement I have if Case = 1 then txtexp = AdminFee

What am I missing? I get an error message Run-time error 2427 You entered an expression that has no value

RE: VBA Code for Access reports using variables

(OP)
Mintjulep, I don't think the issue is in the SQL or query but is in the report. You are correct I am trying just to display just the AdminFee.

RE: VBA Code for Access reports using variables

(OP)
Mintjulep, our financial guy just wants a list of all the projects along with the admin fee listed. Next time he may want the Mgmtfee. I thought it would be easier to write just one report and use an unbound textbox to display his selection. I really didn't want to write 56 reports all the same.

RE: VBA Code for Access reports using variables

Something along the lines of

textExp.ControlSource = "AdminFee"

You need a bound control. Do the binding programatically.

I guess.

RE: VBA Code for Access reports using variables

(OP)
Thanks, sorry for being so dense, but how to I find it programatically? BTW just using the TextExp.controlsource = "AdminFee" I get a Duplication Output Alias '_Agg434'. error. I'm back to where i started. LOL

Thanks again

RE: VBA Code for Access reports using variables

Cool.

So my syntax was actually OK, and now you have a specific bug to smash.

RE: VBA Code for Access reports using variables

Sounds like a horrible table design. You have 56 fields that hold different fees? You should have 3 fields. PropertyID,FeeType, FeeAmount. Then you simply query by fee type.

RE: VBA Code for Access reports using variables

Yes, but he isn't asking about that.

RE: VBA Code for Access reports using variables

Quote:

Yes, but he isn't asking about that
Actually in a way he is. The reason why he needs a cludged workaround is because of bad table design. If properly designed this would be simple along with future queries and fee additions. I try not to facilitate bad designs because the farther you go down the road the trivial becomes more complicated.

Select * from someQuery where feeType = "AdminFee"
and you are done

RE: VBA Code for Access reports using variables

Amen!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code for Access reports using variables

(OP)
LOL, it probably could be attributed to bad table design; however, these database are over 40 years old, for you young guys its a lifetime.

Unfortunately, I have to deal with what I have.

In a previous comment, think of a Operating Budget. There are multiple items in a budget, in our case 56. The are all in the same table.

You have a record with a key and 56 separate fields.

So our financial guy wants to see a report showing just the administration fee all the properties (keys)

Thank you everyone for your help. I really do appreciate it and there have been some very good suggestions.

RE: VBA Code for Access reports using variables

Sounds like the simple solution is just to make a report called Admin fee and make a query that just returns that fee. Do not know why you would need any code to do this.

RE: VBA Code for Access reports using variables

(OP)
MajP, as is typical of all users I'm under a deadline so that's what I'm going to do. Good thing is while I need to create 56 different reports, they all are the same except for the category.

RE: VBA Code for Access reports using variables

Then you just create a single report. Have a form with a combo box where you can select the category. You open the report. In the reports onopen event you open the form and pick your category from the combox. Then you do what Skip said on 5 Jun. You then assign the reports recordsource. I will post a quick demo.

RE: VBA Code for Access reports using variables

Quote (Fbobak )

Unfortunately, I have to deal with what I have.

True, but...
You could make your life a lot easier by creating a few queries to get your data normalized and use MajP's advise from the post on 5 Jun 17 22:19

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Code for Access reports using variables

(OP)
Thanks MajP, I'd appreciate it.

MajP and Andrezejek, I'll try both suggestions.

RE: VBA Code for Access reports using variables

This is how you do it.
Have a form with a comobbox that has all 56 fees in it. For the demo I used a field list, but you could put the names into a table.

The form passes the fee name to the report

CODE -->

Private Sub cmdOk_Click()
  DoCmd.OpenReport "RptFees", acViewPreview, , , acDialog, Me.cmboFee
End Sub 

When the report opens it sets the controlsource and label caption of the fee field

CODE -->

Private Sub Report_Open(Cancel As Integer)
  On Error GoTo errlbl
  If Not Trim(Me.OpenArgs & " ") = "" Then
    Me.txtFee.ControlSource = Me.OpenArgs
    Me.lblFee.Caption = Me.OpenArgs
  End If
  Exit Sub
errlbl:
  MsgBox Err.Number & ": " & Err.Description
End Sub 

see demo.

RE: VBA Code for Access reports using variables

(OP)
MajP, thanks for the code. I'm going to give that a try. Since I'm under deadline I went another route, very messy but it's gets the job done for now.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close