VBA Code for Access reports using variables

[Fbobak]

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

 

[SkipVought]

Hi,

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

Typically...

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Fbobak]

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

 

[mintjulep]

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

 

[SkipVought]

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

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.

 

[Fbobak]

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

 

[Fbobak]

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.

 

[Fbobak]

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.

 

[mintjulep]

Something along the lines of

textExp.ControlSource = "AdminFee"

You need a bound control. Do the binding programatically.

I guess.

 

[Fbobak]

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

 

[mintjulep]

Cool.

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

 

[MajP]

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.

 

[mintjulep]

Yes, but he isn't asking about that.

 

[MajP]

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

 

[SkipVought]

Amen!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Fbobak]

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.

 

[MajP]

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.

 

[Fbobak]

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.

 

[MajP]

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.