Update table with calculation from form

[jaxflorida]

Hi,
I am a only an occasional user to programming in Access so I really don't know much about the code area. I have been banging my head against the wall for some time now. This code was modified from a totally different database.

I have a form that has a calcuation in its code shown here:

Private Sub CurrentWorkforce_AfterUpdate()
Function EMFCalc(currentworkforce As Integer) As Single


Select Case currentworkforce

Case 1 To 50
EMFCalc = 1
Case 51 To 100
EMFCalc = 1.25
.
.
.
Case Is >= 1001
EMFCalc = 999

End Select

End Function

This form is based on a table named SER1. I need the calucation to update a field called EMF in my table after someone enters data in my form field called currentworkforce. I have seen reference to something similar in the FAQ, but they were doing a simple calculation within the expression builder.

I would really appreciate any help that anyone could offer.

Thanks

 

[jbehrne]

Is the above code not working? From what I am seeing it is only trying to set EMFCalc to a value... If your case statement is not working try this (Note - you don't have to have the goto Line1 code, I didn't know if you had other code that would be repeated throught the rest of the if then statment):

Dim CWF

CWF = Me!currentworkforce

If (CWF <= 1) AND (CWF >=50) Then
Me!EMFCalc = 1
GoTo Line1
ElseIf (CWF < 50) AND (CWF >= 100) Then
Me!EMFCalc = 1.25
GoTo Line1
.
.
.
Else
Me!EMFCalc = 999
GoTo Line1
End If

Line1
Anyother repeated code
Exit Sub

HTH,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jaxflorida]

jbehrne,

Thanks for responding. No, the code works just fine and it gives me the result in a text field. However, I need to put this calculation into the EMF field in my table. Any thoughts?

J

 

[jbehrne]

Before we go any further I need to know one quick thing. Is your table housing the EMF associated with the form or is it an entirely different table?

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jaxflorida]

Yes, the form is attached to the table.

 

[jbehrne]

Would you mind posting your EFMCalc function? You may be able to set the value of the field in the function...

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jaxflorida]

I am not sure what it is you are asking for. (Like I said, I don't use access or VBA for that matter much.)So here is what is in the text box.

=EMFCalc([CurrentWorkforce])

and as I understood it, the code up at the top of this post was the function. If it isn't, let me know and let me know what it is called so next time I won't have as much problems communicating. I greatly appreciate you looking at this for me.

J

 

[jbehrne]

Hi,

If your form is pulling the data from the table then all you have to do is put this in your code: (for example: in your textbox's &quot;After update&quot; event - this is the textbox that is showing the calculated info)

Me!EMFfield = Me!TextboxName

- EMFfield would be the name of the field in the table that is storing the calculated data
- TextboxName is the name of the textbox on your form that is showing the calculated info

HTH,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jaxflorida]

Thanks. However, nothing seems to happen. When I change the current workforce number, the textbox field changes to the correct number, but when I look at the table, it hasn't put anything in the EMF field.

J

 

[jbehrne]

Hmmm...

Is your form pulling its data from a query or directly from the table? If it is pulling from a query make sure that the field is being pulled along with the rest of your data.

Another option would be to use an append query. Your code look something like this:

DoCmd.Setwarnings False
DoCmd.OpenQuery &quot;AppendQuery&quot;
DoCmd.Close acQuery, &quot;AppendQuery&quot;
DoCmd.Setwarnings True


AppendQuery's sql statement would look like this:

INSERT INTO tablename (EMFfield) SELECT Forms!FormName.TextboxName;

Of course, if you don't want to do that you can send me the db (zipped) w/o any sensitive info to: jbehrne@hotmail.com
I'll be glad to take a look at it (if you do that please include instructions on what you need done)...



If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[jbehrne]

Sorry,

that query should look something like this:

INSERT INTO tablename (EMFfield) SELECT Forms!FormName.TextboxName WHERE ([Tablename].[KeyField] = Forms![FormName].[KeyField]);

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations