By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Conditional Form Opening & Report Filtering

P: 77
Hey Rabbit,

Thanks for all of your help. I really feel like this thing is starting to come together. It's very exciting.

There are still a few things that I would like the form to do that I don't know how to structure. If you could give me some help, I would appreciate it.

When I select one specific employee type, I want that to open another subform. I'm assuming that this will be something under the event tab, maybe something like "on click"?

The other thing that I want is to design a report that structures everything like a budget. I assume that this would be tied to the [Budget ID]. Is there a way that I can create a report only for data from a specific budget? Here's what I imagine: you open the form, fill in all the fields, for your project personnel, for your supplies, and for your equipment. Then you click a cmd button that says create budget, and your budget comes up as a report broken down into expense categories, and looks fairly similar to an excel sheet sans lines. What do you think, is this doable?

Thanks
Mar 16 '07 #1
Share this Question
Share on Google+
18 Replies


Rabbit
Expert Mod 10K+
P: 12,347

Rabbit
Expert Mod 10K+
P: 12,347
Hey Rabbit,

Thanks for all of your help. I really feel like this thing is starting to come together. It's very exciting.

There are still a few things that I would like the form to do that I don't know how to structure. If you could give me some help, I would appreciate it.

When I select one specific employee type, I want that to open another subform. I'm assuming that this will be something under the event tab, maybe something like "on click"?

The other thing that I want is to design a report that structures everything like a budget. I assume that this would be tied to the [Budget ID]. Is there a way that I can create a report only for data from a specific budget? Here's what I imagine: you open the form, fill in all the fields, for your project personnel, for your supplies, and for your equipment. Then you click a cmd button that says create budget, and your budget comes up as a report broken down into expense categories, and looks fairly similar to an excel sheet sans lines. What do you think, is this doable?

Thanks
The report question is the easiest. You just make a query that gathers the information you need for the report. From the query you can filter for a specific budget.

Then you make a report based on the query and design it how you want it to look.

The button will open this report which will open the query that selects records based on what was entered into the form.

As for the second question. It depends on if the form is different for each employee type, in which case you would use something along the lines of a select case to open the right form depending on their choice.

You also have to decide how you want to do this. Will the list be in a listbox or a combobox or some other representation. And how do you want to open it, automatically once they make a choice or do you want them to click a button first.
Mar 16 '07 #3

P: 77
Hi Rabbit,

I think I understand what you are saying about the query that gathers information; I have created most of them at this point. What I'm not quite sure I understand is how I filter for a specific budget. Can you tell me a little more about that? Also, for the report, can I pull from more then one query? Right now I have created multiple queries, because that seemed the easiest way to create a criteria. For example, I used the criteria of employee type to determine salary because within a type, salary is always determined the same way.

With regards to the form, I think that I would want it to be automatic, but I wonder what would happen if you selected two people of this particular employee type. Call it type 7, if you selected 1,2,4,7,5,8,7 what would happen? This would only apply to type 7 employees. Currently it is a combo box that the record enterer selects for the first field.

Thanks.

The report question is the easiest. You just make a query that gathers the information you need for the report. From the query you can filter for a specific budget.

Then you make a report based on the query and design it how you want it to look.

The button will open this report which will open the query that selects records based on what was entered into the form.

As for the second question. It depends on if the form is different for each employee type, in which case you would use something along the lines of a select case to open the right form depending on their choice.

You also have to decide how you want to do this. Will the list be in a listbox or a combobox or some other representation. And how do you want to open it, automatically once they make a choice or do you want them to click a button first.
Mar 16 '07 #4

P: 77
Hello Rabbit,

I have been trying to come up with a solution for how to open a form when a certain item in the combo box is selected. I wrote a private sub, but it doesn't work:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo12_Click()
  3. If [Combo12] = "GRA" Then
  4. DoCmd.OpenForm "Class7", acNormal, , , acFormAdd
  5. End If
  6. End Sub
  7.  
  8.  
Can you help me determine what I have done wrong?
I'm always grateful for your help.
Thanks.
Mar 23 '07 #5

Rabbit
Expert Mod 10K+
P: 12,347
Hi Rabbit,

I think I understand what you are saying about the query that gathers information; I have created most of them at this point. What I'm not quite sure I understand is how I filter for a specific budget. Can you tell me a little more about that? Also, for the report, can I pull from more then one query? Right now I have created multiple queries, because that seemed the easiest way to create a criteria. For example, I used the criteria of employee type to determine salary because within a type, salary is always determined the same way.

With regards to the form, I think that I would want it to be automatic, but I wonder what would happen if you selected two people of this particular employee type. Call it type 7, if you selected 1,2,4,7,5,8,7 what would happen? This would only apply to type 7 employees. Currently it is a combo box that the record enterer selects for the first field.

Thanks.
Sorry I haven't responded to this. It must have gotten buried under all the other threads. I didn't even realize this hadn't been responded to yet.

To filter for a specific budget, in the criteria for the budget ID you can just filter for that budget. If this is going to be different depending on what the user wants, then you can use either a parameter box or call the value from a form. To call a value from a form use Forms![FormName]![Control Name].

A report can use multiple queries if you can combine the queries into one or you can use subreports on a main report.

I don't understand what you mean by the last question, can you provide me with more information?
Mar 23 '07 #6

Rabbit
Expert Mod 10K+
P: 12,347
Hello Rabbit,

I have been trying to come up with a solution for how to open a form when a certain item in the combo box is selected. I wrote a private sub, but it doesn't work:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo12_Click()
  3. If [Combo12] = "GRA" Then
  4. DoCmd.OpenForm "Class7", acNormal, , , acFormAdd
  5. End If
  6. End Sub
  7.  
  8.  
What you want is to do is trigger after they have made a choice. The After Update event triggers after a control has been updated and is what you're looking for in this case.
Can you help me determine what I have done wrong?
I'm always grateful for your help.
Thanks.
The On Click event triggers right when they click on the control. So this happens before they make a choice.
Mar 23 '07 #7

P: 77
Hi Rabbit,

Not a problem. You have no obligation to respond to anything I or anyone else posts, and while I am always grateful, (very grateful) for the help you provide, I completely understand that.

I did try to combine all my queries into one, but I must have done something wrong, because it created an entry for every record. What I did was to take the expressions from the queries and add them into this new query where their table was the original query they were designed in. Is there a better way to do this?

I'm kind of surprised that I can't use more than one query on a report. I originally thought that I would be able to create the queries I needed, and then create some sort of command button on my form that would automatically filter for the current [budget ID] and display a report with all the data formatted as I had previously been created (sort of a template of a report that could be filled in with the relevant data). It seems that this is not possible, since I would need to pull from multiple queries. So now I'm wondering if there is a better way to create the query. Is there a way that I could create conditional criteria in the query? What I mean is that I would still use [Name ID #] as my criteria but have different calculations depending on the answer.

In my head I think of it as something like this:

Expand|Select|Wrap|Line Numbers
  1. IF [Name ID #] = 1 OR 3 Then
  2. Salary1: [Salary]/9*Val(Mid([Spread],1,1))*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  3.  
  4. ElseIF [Name ID #] = 2 Or (4 Or 5 Or 6) Then 
  5. Salary2: [Salary]/12*Val(Mid([Spread],1,1))*[AY Percent]+[Salary]/12*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/12*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/12*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/12*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/12*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  6.  
  7. ElseIF[Name ID #] = 7
  8. etc...
  9.  
I think of this as being part of the SQL code, something done with expression builder perhaps? Is this possible?

When you talk about calling a value by using Forms![FormName]![Control Name]., can you explain what object I would use that with?

My last question relates to the next post, trying to open a form with a combo box selection.

Thanks.



Sorry I haven't responded to this. It must have gotten buried under all the other threads. I didn't even realize this hadn't been responded to yet.

To filter for a specific budget, in the criteria for the budget ID you can just filter for that budget. If this is going to be different depending on what the user wants, then you can use either a parameter box or call the value from a form. To call a value from a form use Forms![FormName]![Control Name].

A report can use multiple queries if you can combine the queries into one or you can use subreports on a main report.

I don't understand what you mean by the last question, can you provide me with more information?
Mar 23 '07 #8

Rabbit
Expert Mod 10K+
P: 12,347
Hi Rabbit,

Not a problem. You have no obligation to respond to anything I or anyone else posts, and while I am always grateful, (very grateful) for the help you provide, I completely understand that.

I did try to combine all my queries into one, but I must have done something wrong, because it created an entry for every record. What I did was to take the expressions from the queries and add them into this new query where their table was the original query they were designed in. Is there a better way to do this?

I'm kind of surprised that I can't use more than one query on a report. I originally thought that I would be able to create the queries I needed, and then create some sort of command button on my form that would automatically filter for the current [budget ID] and display a report with all the data formatted as I had previously been created (sort of a template of a report that could be filled in with the relevant data). It seems that this is not possible, since I would need to pull from multiple queries. So now I'm wondering if there is a better way to create the query. Is there a way that I could create conditional criteria in the query? What I mean is that I would still use [Name ID #] as my criteria but have different calculations depending on the answer.

In my head I think of it as something like this:

Expand|Select|Wrap|Line Numbers
  1. IF [Name ID #] = 1 OR 3 Then
  2. Salary1: [Salary]/9*Val(Mid([Spread],1,1))*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  3.  
  4. ElseIF [Name ID #] = 2 Or (4 Or 5 Or 6) Then 
  5. Salary2: [Salary]/12*Val(Mid([Spread],1,1))*[AY Percent]+[Salary]/12*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/12*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/12*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/12*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/12*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  6.  
  7. ElseIF[Name ID #] = 7
  8. etc...
  9.  
I think of this as being part of the SQL code, something done with expression builder perhaps? Is this possible?

When you talk about calling a value by using Forms![FormName]![Control Name]., can you explain what object I would use that with?

My last question relates to the next post, trying to open a form with a combo box selection.

Thanks.
You can use multiple queries in a report by using subreports.

However, if this is because you are trying to create a salary field and the salary field is calculated differently depending on the name ID, then you can do this all in one query and in one field, no need to seperate the salary fields into more than one column. There are two ways to do this, using a nested iif() statement or using VBA code much like how I calculated the spread. I suggest the latter

So here's your homework, while my CalcSpread function is nothing close what you need, it can give you an idea of how to pass values, return values, and basic structures in a program. What you'll want to do is create another function to calculate salary and call that function from the same query that calls CalcSpread but in a different field.

You'll most likely run into problems as you try to code the function (everyone runs into problems while writing code) but give it your best shot and post what code you did write, where you got stuck, and we'll guide you along in fixing the code.
Mar 23 '07 #9

P: 77
You can use multiple queries in a report by using subreports.

However, if this is because you are trying to create a salary field and the salary field is calculated differently depending on the name ID, then you can do this all in one query and in one field, no need to seperate the salary fields into more than one column. There are two ways to do this, using a nested iif() statement or using VBA code much like how I calculated the spread. I suggest the latter

So here's your homework, while my CalcSpread function is nothing close what you need, it can give you an idea of how to pass values, return values, and basic structures in a program. What you'll want to do is create another function to calculate salary and call that function from the same query that calls CalcSpread but in a different field.

You'll most likely run into problems as you try to code the function (everyone runs into problems while writing code) but give it your best shot and post what code you did write, where you got stuck, and we'll guide you along in fixing the code.
Thanks Rabbit,

I appreciate the help. I have started trying to write the code, but I realized that I don't know how to refer to the calculations done in the previous code. I tried using a modification of the query, but that wasn't acceptable (I get a compilation error "expected expression". Here is what I have so far:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Salary(Salary As Currency, Spread As String)
  3.  
  4. Dim Calcsalary As String
  5. Dim Spread As String
  6. Dim ESalary As Currency
  7. Dim Number As Integar
  8. Dim AY As Single
  9. Dim Summer As Single
  10.  
  11. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  12. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  13. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  14. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
  15. If Number = 1 Or 3 Then
  16.     [ESalary]/9*1*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  17.  
  18.  
  19.  
  20. End Sub
  21.  
It's the "Val(Mid([Spread" part that I think I need to modify. I thought about just using the variables that you previously defined, but it seems that there might be an easier way to refer to the result of the function.

Thanks again.
Mar 25 '07 #10

Rabbit
Expert Mod 10K+
P: 12,347
Thanks Rabbit,

I appreciate the help. I have started trying to write the code, but I realized that I don't know how to refer to the calculations done in the previous code. I tried using a modification of the query, but that wasn't acceptable (I get a compilation error "expected expression". Here is what I have so far:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Salary(Salary As Currency, Spread As String)
  3.  
  4. Dim Calcsalary As String
  5. Dim Spread As String
  6. Dim ESalary As Currency
  7. Dim Number As Integar
  8. Dim AY As Single
  9. Dim Summer As Single
  10.  
  11. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  12. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  13. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  14. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
  15. If Number = 1 Or 3 Then
  16.     [ESalary]/9*1*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  17.  
  18.  
  19.  
  20. End Sub
  21.  
It's the "Val(Mid([Spread" part that I think I need to modify. I thought about just using the variables that you previously defined, but it seems that there might be an easier way to refer to the result of the function.

Thanks again.
There's a lot to change here.

1) You refer to MainID, BudgetID, and NameID but from the scope of a function, these variables are undefined. Basically they don't know what they refer to. You either have to define the variable (using Dim and setting the variable in the code) or pass the variable through the function declaration (the stuff in the parentheses of the function).

2) You set spread to call the function CalcSalary which returns the spread. However, you also declare spread as a variable to be passed to the function. This means that you end up calculating spread twice. Just pass spread through the function.

3) When you use an If/Then statement that spans multiple lines, you have to close it off with an End If at the end.
Mar 27 '07 #11

P: 77
Hello Rabbit,

I guess that I thought that since it had been defined as a variable in your previous code, I wouldn't need to define MainID, BudgetID or NameID.

Can I call CalcSalary from the previous code? Can I call Spread from the query?

Thanks.

There's a lot to change here.

1) You refer to MainID, BudgetID, and NameID but from the scope of a function, these variables are undefined. Basically they don't know what they refer to. You either have to define the variable (using Dim and setting the variable in the code) or pass the variable through the function declaration (the stuff in the parentheses of the function).

2) You set spread to call the function CalcSalary which returns the spread. However, you also declare spread as a variable to be passed to the function. This means that you end up calculating spread twice. Just pass spread through the function.

3) When you use an If/Then statement that spans multiple lines, you have to close it off with an End If at the end.
Mar 27 '07 #12

Rabbit
Expert Mod 10K+
P: 12,347
Hello Rabbit,

I guess that I thought that since it had been defined as a variable in your previous code, I wouldn't need to define MainID, BudgetID or NameID.

Can I call CalcSalary from the previous code? Can I call Spread from the query?

Thanks.
The variables declared in a function are temporary.

You can call CalcSalary from that code but you'll have to pass it all the variables in it's declaration. But there's no need since you call the function in the query.

You can just pass the Spread from the query right into your new function.
Mar 28 '07 #13

P: 77
The variables declared in a function are temporary.

You can call CalcSalary from that code but you'll have to pass it all the variables in it's declaration. But there's no need since you call the function in the query.

You can just pass the Spread from the query right into your new function.
Hello Rabbit,

I think that I have made the appropriate changes to the variables and function. However, I am still getting the same compile error expected expression message. It highlights the divisor mark after [ESalary]; is that not how division should be expressed?

Expand|Select|Wrap|Line Numbers
  1. If Number = 1 Or 3 Then
  2.     [ESalary]/9*1*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  3.  
Thanks.
Mar 28 '07 #14

Rabbit
Expert Mod 10K+
P: 12,347
Hello Rabbit,

I think that I have made the appropriate changes to the variables and function. However, I am still getting the same compile error expected expression message. It highlights the divisor mark after [ESalary]; is that not how division should be expressed?

Expand|Select|Wrap|Line Numbers
  1. If Number = 1 Or 3 Then
  2.     [ESalary]/9*1*[AY Percent]+[Salary]/9*Val(Mid([Spread],4,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],10,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],13,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],16,1))*1.034*1.034*[Summer Percent]
  3.  
Thanks.
Using [] designates that it is a field. As far as I know, this function can't see the fields from the query so you can't use the field names. This is why you either have to pass the variables through the function declaration or lookup the values from within the function.

You also can't have floating calculations like that. Yes it calculates a number but it is not assign it to anything. So what you want is for the function to return a number which will be the salary. So you can use FunctionName = Calculation And when the function finishes running, it will return the salary.
Mar 28 '07 #15

P: 77
Using [] designates that it is a field. As far as I know, this function can't see the fields from the query so you can't use the field names. This is why you either have to pass the variables through the function declaration or lookup the values from within the function.

You also can't have floating calculations like that. Yes it calculates a number but it is not assigning it to anything. So what you want is for the function to return a number which will be the salary. So you can use FunctionName = Calculation And when the function finishes running, it will return the salary.
Hello Rabbit,

I'm not sure I understand; if the function can't see the fields of the query, how do I refer to the calculations done by the previous function?

I'm also unclear about what the structure of FunctionName = Calculation

Here is what I have:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Salary(Salary As Currency, BudgetID As Integer, MainID As Integer, Spread As String)= Calculation
  3.  
  4. Dim ESalary As Currency
  5. Dim Number As Integar
  6. Dim AY As Single
  7. Dim Summer As Single
  8.  
  9. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  10. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  11. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  12. Summer = DLookup("[Summer Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  13. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
  14. If Number = 1 Or 3 Then
  15.     ESalary/9*1*AY +ESalary/9*Val(Mid([Spread],4,1))*Summer +ESalary/9*Val(Mid([Spread],7,1))*1.034*AY +ESalary/9*Val(Mid([Spread],10,1))*1.034*Summer +ESalary/9*Val(Mid([Spread],13,1))*1.034*1.034*AY +ESalary/9*Val(Mid([Spread],16,1))*1.034*1.034*Summer
  16.  
  17. End If
  18.  
  19. End Sub
  20.  
  21.  
Thanks
Apr 2 '07 #16

Rabbit
Expert Mod 10K+
P: 12,347
Hello Rabbit,

I'm not sure I understand; if the function can't see the fields of the query, how do I refer to the calculations done by the previous function?

I'm also unclear about what the structure of FunctionName = Calculation

Here is what I have:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub Salary(Salary As Currency, BudgetID As Integer, MainID As Integer, Spread As String)= Calculation
  3.  
  4. Dim ESalary As Currency
  5. Dim Number As Integar
  6. Dim AY As Single
  7. Dim Summer As Single
  8.  
  9. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  10. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  11. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  12. Summer = DLookup("[Summer Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  13. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
  14.  
  15. If Number = 1 Or 3 Then
  16.     ESalary/9*1*AY +ESalary/9*Val(Mid([Spread],4,1))*Summer +ESalary/9*Val(Mid([Spread],7,1))*1.034*AY +ESalary/9*Val(Mid([Spread],10,1))*1.034*Summer +ESalary/9*Val(Mid([Spread],13,1))*1.034*1.034*AY +ESalary/9*Val(Mid([Spread],16,1))*1.034*1.034*Summer
  17.  
  18. End If
  19.  
  20. End Sub
  21.  
  22.  
Thanks
In the function declaration, to return the result of the function, you should use As Double rather than = Calculation. Basically it will return a variable called Salary as data type double.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Salary(Salary As Currency, BudgetID As Integer, MainID As Integer, Spread As String)= Calculation
Expand|Select|Wrap|Line Numbers
  1. Dim ESalary As Currency
  2. Dim Number As Integar
  3. Dim AY As Single
  4. Dim Summer As Single
In your query you already called CalcSalary, there is no need to call it again.
Plus you are passing Spread as a variable into your function, there is no need to do it all over again.
Expand|Select|Wrap|Line Numbers
  1. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  2. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  3. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  4. Summer = DLookup("[Summer Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  5. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
This code here does nothing because you are making a calculation that is not assigned to anything. You have a variable Salary (Which is the function name) that is returned when the function finishes.

So you have to assign the calculation to Salary. This is what I meant by FunctionName = Calculation. So Salary = 8*4.

Just 8*4 by itself does nothing, you have to assign it to a variable. In this case Salary.
Expand|Select|Wrap|Line Numbers
  1. If Number = 1 Or 3 Then
  2.     ESalary/9*1*AY +ESalary/9*Val(Mid([Spread],4,1))*Summer +ESalary/9*Val(Mid([Spread],7,1))*1.034*AY +ESalary/9*Val(Mid([Spread],10,1))*1.034*Summer +ESalary/9*Val(Mid([Spread],13,1))*1.034*1.034*AY +ESalary/9*Val(Mid([Spread],16,1))*1.034*1.034*Summer
  3.  
  4. End If
  5.  
  6. End Sub
  7.  
  8.  
A function declaration comes down to this:
Expand|Select|Wrap|Line Numbers
  1. Public/Private FunctionName(VariableName As Datatype, ...) As Datatype
Whatever is in the parentheses MUST have a value passed in unless it is declared as Optional.

Let's use a simple example.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddNum(Num1 As Integer, Num2 As Integer) As Integer
  2.    AddNum = Num1 + Num2
  3. End Function
  4.  
This function requires 2 integers and will return an integer. So, when you call AddNum(), you have to provide the values. Something like AddNum(1,3) or AddNum(1, [X]) where X is some defined variable.

It then adds the 2 variables that are created as a result of the function call, Num1 and Num2. Then it assigns the result to the variable created because this function returns a result, AddNum.
Apr 2 '07 #17

P: 77
Hey Rabbit,

Thanks for your help. I don't want you to think I am ignoring your response. I am currently trying to learn more about VBA, because I just don't know enough. I have picked up the basics, or at least some of them, but it isn't enough. Do you have any advice about what a good book to learn VBA is?

Thanks

In the function declaration, to return the result of the function, you should use As Double rather than = Calculation. Basically it will return a variable called Salary as data type double.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Salary(Salary As Currency, BudgetID As Integer, MainID As Integer, Spread As String)= Calculation
Expand|Select|Wrap|Line Numbers
  1. Dim ESalary As Currency
  2. Dim Number As Integar
  3. Dim AY As Single
  4. Dim Summer As Single
In your query you already called CalcSalary, there is no need to call it again.
Plus you are passing Spread as a variable into your function, there is no need to do it all over again.
Expand|Select|Wrap|Line Numbers
  1. ESalary = DLookup("[Salary]", "TblEntry", "[Entry ID #] = " & MainID)
  2. Number = DLookup("[Name ID #]", "TblEntry", "[Entry ID #] = " & MainID)
  3. AY = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  4. Summer = DLookup("[Summer Percent]", "TblEntry", "[Entry ID #] = " & MainID)
  5. Spread = Calcsalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Name ID #])
This code here does nothing because you are making a calculation that is not assigned to anything. You have a variable Salary (Which is the function name) that is returned when the function finishes.

So you have to assign the calculation to Salary. This is what I meant by FunctionName = Calculation. So Salary = 8*4.

Just 8*4 by itself does nothing, you have to assign it to a variable. In this case Salary.
Expand|Select|Wrap|Line Numbers
  1. If Number = 1 Or 3 Then
  2.     ESalary/9*1*AY +ESalary/9*Val(Mid([Spread],4,1))*Summer +ESalary/9*Val(Mid([Spread],7,1))*1.034*AY +ESalary/9*Val(Mid([Spread],10,1))*1.034*Summer +ESalary/9*Val(Mid([Spread],13,1))*1.034*1.034*AY +ESalary/9*Val(Mid([Spread],16,1))*1.034*1.034*Summer
  3.  
  4. End If
  5.  
  6. End Sub
  7.  
  8.  
A function declaration comes down to this:
Expand|Select|Wrap|Line Numbers
  1. Public/Private FunctionName(VariableName As Datatype, ...) As Datatype
Whatever is in the parentheses MUST have a value passed in unless it is declared as Optional.

Let's use a simple example.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddNum(Num1 As Integer, Num2 As Integer) As Integer
  2.    AddNum = Num1 + Num2
  3. End Function
  4.  
This function requires 2 integers and will return an integer. So, when you call AddNum(), you have to provide the values. Something like AddNum(1,3) or AddNum(1, [X]) where X is some defined variable.

It then adds the 2 variables that are created as a result of the function call, Num1 and Num2. Then it assigns the result to the variable created because this function returns a result, AddNum.
Apr 9 '07 #18

Rabbit
Expert Mod 10K+
P: 12,347
Hey Rabbit,

Thanks for your help. I don't want you to think I am ignoring your response. I am currently trying to learn more about VBA, because I just don't know enough. I have picked up the basics, or at least some of them, but it isn't enough. Do you have any advice about what a good book to learn VBA is?

Thanks
Not a problem.

I haven't read a VBA book so I don't know what to reccommend. You might want to create a new thread for that and ask the wider community. I learned the syntax by breaking down an older database and using trial and error.

I had learned C/C++ a few years back and that gave me the mentality required for programming. So when I went to VBA I only had to learn the syntax.

I may be considered an expert but I've only created 2 and a half databases. Half because a lot of it was a copy of my first database but with a lot of improvements and for a different unit. The other database was something I did for... fun. LOL.
Apr 9 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.