UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> MS Access 2007 basic module use    
 
   
smaftei
post Sep 25 2008, 11:58 AM
Post #1

UtterAccess Member
Posts: 30



(IMG:http://www.utteraccess.com/forum/style_emoticons/default/cryhard.gif) I am new to Access and the books and info I find about modules is past the first steps. I need to know how to use a module, what to do with a module. From creating one to using it in the form or report or where ever it is used. For example, if I wanted to calculate asset depreciation and put the amount in the table.

Edited by: smaftei on Thu Sep 25 13:00:29 EDT 2008.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 12:45 PM
Post #2

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Welcome to UA!

You can create a module in the database window. To access the VBA editor, press ALT-F11. Here you can add code to your forms or modules.

A simple procedure in a module looks something like this:

Public Sub HelloWorld()
msgbox "Hello World"
End Sub

When you run that, you will see a popup that says "Hello World".

You could then put a button on your form, and in the onclick event, you could tell it to run HelloWorld. E.g.

Public Sub cmdMyButton_OnClick()
HelloWorld
End Sub

It's impossible to tell you everything you can do with modules in one post, it's a whole new world from Access "out of the box", but it's very powerful. Just keep poking around UA, you'll learn it quick!
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 01:01 PM
Post #3

UtterAccess Member
Posts: 30



(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thanks.gif) Now I understand the basic use of a module, is kind of like a macro. But my next question is how do I use a module with a textbox to do a calculation and save it in the table. I was able to figure out almost everything pretty quickly, but the modules.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 01:08 PM
Post #4

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Well, first of all keep mind that there are modules, and then there are these sort of pseudo-modules, called code-behind-forms. It's the same thing, it's just the code behind the form is bound that form and not available to other forms.

There are probably dozens, if not hundreds of ways to do data manipulation. Just as an example, let's say you had a form with a textbox named txtMyField and then you had a button, let's say cmdCalculateButton.

In the properties of cmdCalculateButton, go to th events tab and change "On Click" (in the drop down menu) to [Event Procedure]. Then click on the little "..." button. You will see now where you can add code.

So as a simple example, you could do:

CODE
Private Sub cmdCalculateButton_Click()
  dim tmp

  tmp = me.txtMyField.Value
  If Not IsNull(tmp) Then tmp = tmp x 2
  me.txtMyField.value = tmp

  Set tmp = Nothing
End Sub


What you would find after clicked on that button is the value that was in that textbox, is now double what it was.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 01:14 PM
Post #5

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Sorry, lost my train of thought, finishing my post.

If your form is bound to a table and your control (txtMyField) is bound to a field in the table, when your code manipulates the value of that textbox, it will write that value back to the table automatically. There are many ways to accomplish such a task, this is one of the easier ways.

And what I meant by the difference between module and code-behind-forms is in the above example, it works well because the calculation can stay "within the form".

Now if you wanted to have a "global" function that any form or query could call to run a calculation, that's when you'd put it in a module. Let's say you had a module called mdlGlobalFunctions, you could do something like:

CODE
Public Function MyCalculation(intValue)
  dim tmp
  tmp = intValue
  if not isnull(tmp) then tmp = tmp x 2  
  MyCalculation = tmp
  set tmp = nothing
End Function


Now you could still put that in your form, but for the cmdCalculateButton_Click() event, you would do:

CODE
Private Sub cmdCalculateButton_Click()
  me.txtMyField.value = mdlGlobalFunctions.MyCalculation(me.txtMyField.value)
End Sub
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 01:22 PM
Post #6

UtterAccess Member
Posts: 30



That is so cool. I have done VB programming before, but not with Access. You are so helpful. I am getting closer to what I really want to do and that is to calculate asset depreciation and to show the amount in annualDepreciation textbox, which you just showed me how to. Now how do I set the annualDepreciation textbox to auto calculate depreciation. annualDepreciation= CostOfAsset - ScrapValue / lifeSpan I am trying to make a report that show the asset value.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 01:29 PM
Post #7

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



For that, you may not even necessarily need a module (though you could).

If all of those controls exist on your form, create a new, unbound textbox. In the properties, for the Data->Control Source, do:

=[CostOfAsset] - [ScrapValue] / [LifeSpan]

If it doesn't work correctly, make sure your controls on the form have the right format (General Number?).

If you're next question is, "how do I write that back to the table?", my answer is "you DON'T". In this case, you're calculating the value "on the fly". Let's say you DID write this value back to a field in the table. What happens if ScrapValue or LifeSpan changes? You have a hard record of the OLD calculated value sitting in the table. I can tell you from first hand experience, it is a pain in the butt to try and keep that data in the table in sync.

By calculating it on the fly, if you change the ScrapValue or LifeSpan, it recalculates automatically, and it takes almost no time at all, so don't worry about performance.
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 01:56 PM
Post #8

UtterAccess Member
Posts: 30



I have placed =[CostOfAsset] - [ScrapValue] / [LifeSpan] in the expression builder and it doesn't anything, in a unbound textbox. Can I make it so, it executes when I initially run the form.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 01:59 PM
Post #9

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



It should run when you open the form, try it again, make sure you click OK in the expression builder and you save the form. Sometimes I close the Expression Builder with the red X and it doesn't save what I just did.
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 02:23 PM
Post #10

UtterAccess Member
Posts: 30



I added a textbox, unbound. Right-click the textbox > Build Event > Expression Builder > added =([tblAssets]![CostOfAsset])-[tblAssets]![ScrapValue]/[tblAssets]![LifeSpan] and clicked ok. Double-click the form and that textbox is empty. Format is General Number for fields user and textbox. Also, in the Property Sheet that code is in the Before Update too.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 03:05 PM
Post #11

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



No, don't do that. Put that code in the Control Source, it's in the Data tab of the property sheet.
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 03:17 PM
Post #12

UtterAccess Member
Posts: 30



Ok, now it shows the CostOfAsset instead of the depreciated value.
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 03:23 PM
Post #13

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Does it show a value or the text CostOfAsset? I'm thinking it's probably just a syntax thing, make sure your formula is right -- and make sure the controls (fields) you are referring to are the right names. E.g. if you call [CostOfAsset] in your formula/expression but the actual name on the form is [AssetCost] it obviously won't work.

And make sure you have the = sign there at the beginning.


Edited by: bnelson333 on Thu Sep 25 16:24:03 EDT 2008.
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 03:28 PM
Post #14

UtterAccess Member
Posts: 30



Actually my CostOfAsset is the lable and PurchasePrice is coming from tblAssets. Yes, it shows the value.

=[PurchsePrice]-[ScrapValue]/[LifeSpan]
Go to the top of the page
 
+
bnelson333
post Sep 25 2008, 03:34 PM
Post #15

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Ok, educate me on this formula because I wonder if we have an order problem.

If:
Purchase Price = 20
Scrap Value = 10
Life Span = 2

Would you expect the CostOfAsset to be 5, or 15?

If 5, the formula needs to be: =([PurchasePrice]-[ScrapValue])/[LifeSpan] (subtracting scrap value from purchase price BEFORE I divide by life span).

If 15, your formula is correct, as division happens before subtraction.
Go to the top of the page
 
+
smaftei
post Sep 25 2008, 03:58 PM
Post #16

UtterAccess Member
Posts: 30



(20-10)/2=5, and yes that solves the problem. I appreciate your help and your time. Now I must put this away for the day, thank you very much. I should continue working on it next week and I do have another question. Now that I have an unbound textbox, how do I write the value to the table, so I can show it in the report. That's my next step. I hope I can still get some help next week if you are not too busy. Thank you.
Go to the top of the page
 
+
bnelson333
post Sep 26 2008, 08:25 AM
Post #17

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



Glad it worked out for you.

On the storing of the value, read my post a few up, you DON'T!!! Storing calculated values is almost always evil.

I don't use reports, but I'm relatively confident you can add an unbound textbox to a report and do the same calculation, just like you did for your form. If that doesn't work, you can write a query, with a calculated column, and display that in the column. But you shouldn't store a calculated value, it's too difficult to keep the data in sync if one of the other values should change.
Go to the top of the page
 
+
smaftei
post Oct 1 2008, 08:08 AM
Post #18

UtterAccess Member
Posts: 30



That report worked out wonderfully, thank you. That was my practice before I did the real report, and of course now I have new questions (IMG:http://www.utteraccess.com/forum/style_emoticons/default/confused.gif) I have my table in SQL, my form and report in access. I am pretty much doing the same report but a little different. Before I had the LifeSpan calculated in the form by clicking the Calc. button. Now I need to calculate it in the report. I need the LifeSpan column to be calculated when the form loads or so. That's the code, but how do I use it; or is there a better way?

If Me.AssetType = "Disposal Asset" Then
Me.LifeSpan = 3
End If

If Me.AssetType = "Capital Asset" Then
Me.LifeSpan = 5
End If

I attached screen shoots in a word doc if you need to look at them.
Attached File(s)
Attached File  Report.zip ( 342.24K ) Number of downloads: 14
 
Go to the top of the page
 
+
bnelson333
post Oct 1 2008, 10:01 AM
Post #19

UtterAccess Veteran
Posts: 409
From: Minnesota, USA



I'm not positive on the reports (because I don't use them), but if they're anything like forms, there are many events, one being OnLoad -- which as you might guess, runs the code when the form loads (which is where you'd put your code).
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 17th April 2014 - 04:35 AM