update fields in table based on form input

[ginaburg]

I have a form that a user will enter budget information. They will enter Quantity and unit cost based for each line item listed. That is all they see on the form. This part is done.

This may be very easy, but my programing skills are a little rusty.
My question is I want to be able to update other fields within the same table based on what they entered.
example
They enter Item = 11200001
Quantity = 1
Unit Cost = 500
For that same item,I need:
(these are other fields in the same table)
total cost = Quantity x Unit Cost
FQuantity = (Quantity from above)
BQuantity = (Quantity from above)
FUint Cost = (Unit Cost from above)
BUnit Cost = (Unit Cost from above)

Thanks
Gina

 

[PaulF]

since they are all in the same table, why are you duplicating data.. These can all be obtained with the Quantity and Unit Cost fields regardless of what you want to call them.

PaulF

 

[ginaburg]

These are all existing fields in a table that a software program uses for different purposes. If I don't update these fields the the program will give me error messages. This form is a way for an end user to enter data quickly.

 

[SimonDavis]

You've almost written it yourself . . .!

Each field must of course be in the table, not just on the form.

Simple way - on the unit cost field, put this code on the afterupdate event (where something is folled by .value, I am assuming thats the name of the filed in the table);

totalcost.value = quantity.value * [unit cost].value
fquantity.value = quantity.value

etc etc.

You might want to add some code to check if all the fields have values first though - which would use an isnull kind of query - either check help, or let me know if you want an example.

Watch field names with spaces in them though, they can be a problem. Its better to use an underscore than a space.

 

[PaulF]

on the form create textboxes bound to the fields and set their visible property to false. Then in the AfterUpdate event of the textbox for the Quantity data set the value of the other textboxes to the value just entered.

Private Sub txtQuantity_AfterUpdate()
If Val(Nz(txtUnitCost,0))>0 And Val(nz(txtQuantity,0))>0 Then
Me.txtTotalPrice = Val(nz(txtUnitCost,0)) * Val(Nz(txtQuantity,0))
End If
Me.txtFQuantity = Me.txtQuantity
Me.txtBQuantity = Me.txtQuantity
End Sub
Private Sub txtUnitCost_AfterUpdate()
If Val(Nz(txtUnitCost,0))>0 And Val(nz(txtQuantity,0))>0 Then
Me.txtTotalPrice = Val(nz(txtUnitCost,0)) * Val(Nz(txtQuantity,0))
End If
Me.txtFUnitCost = Me.txtUnitCost
Me.txtBUnitCost = Me.txtUnitCost
End Sub

PaulF

 

[ginaburg]

Here is the code that I have but nothing is working. I tried to print some results to the immediate window to see why it is not working.
The PACOSTCATID prints ok(which is the item) but the PABQunatity and PAFQuantity show 0.
I am entering data in PABQuantity and PABUnitCost.

What am I doing wrong?

Private Sub PABUnitCost_AfterUpdate()
Dim PABQuantity As Integer
Dim PABUnitCost As Integer
Dim PABTotalCost As Integer
Dim PAForecastBaseQty As Integer
Dim PAFQuantity As Integer
Dim PAFUnitCost As Integer

Debug.Print PABQuantity
Debug.Print PACOSTCATID

If PABUnitCost > 0 And PABQuantity > 0 Then
PABTotalCost = PABUnitCost * PABQuantity
End If
PAForecastBaseQty = PABQuantity
PAFQuantity = PABQuantity
PAFUnitCost = PABUnitCost
Debug.Print PAFQuantity
End Sub

Thanks
Gina

 

[PaulF]

you're dimming a variable
Dim PABUnitCost As Integer

which is the same name as the AfterUpdate event

Private Sub PABUnitCost_AfterUpdate()

which is the same as setting it to zero.

PaulF

 

[ginaburg]

Thanks, that worked for the immediate window, but it is still not updating my table.
These are linked tables to SQL and I have the script that is supposed to work.
Here it is. Anything that you can see incorrect?
I am not getting any errors, how do I test to see if it is actually connecting?

Option Compare Database
Option Explicit
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstupdate As New ADODB.Recordset
Dim cmd As New ADODB.Command

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=Dynamics;User ID=sa;Password=;Initial Catalog=VCC"
.Open
End With
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
End Sub

 

[PaulF]

your code only sets the values of variables, doesn't mention anything about actual fields in a recordset. If you've linked the SQL table to your database, and you're using it in your recordset (table only or as part of a query) then you should create textboxes on your form bound to the fields you want to update, and use the AfterUpdate event for the Quantity and UnitCost fields like I stated previously. If you're not, then you're have to do more coding to find the correct record in the table and perform an update.

PaulF

 

[ginaburg]

This is my full code below. I have it linked to the After Update on the PABUnitCost field.
I enter the PABQuantity and then the PABUnitCost. All of the other fields listed below are on the subform with visible set to No.
The record source for the subform, is a query with all of these fields on it. The SQL tables are linked to the database. Am I missing anything?

Option Compare Database
Option Explicit
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstupdate As New ADODB.Recordset
Dim cmd As New ADODB.Command

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=Dynamics;User ID=sa;Password=;Initial Catalog=VCC"
.Open
End With
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
End Sub

Private Sub PABUnitCost_AfterUpdate()

Dim PABTotalCost As Integer
Dim PAForecastBaseQty As Integer
Dim PAFQuantity As Integer
Dim PAFUnitCost As Integer

Debug.Print PABQuantity
Debug.Print PACOSTCATID
Debug.Print PABUnitCost

If PABUnitCost > 0 And PABQuantity > 0 Then
PABTotalCost = PABUnitCost * PABQuantity
End If
PAForecastBaseQty = PABQuantity
PAFQuantity = PABQuantity
PAFUnitCost = PABUnitCost
Debug.Print PAFQuantity
Debug.Print PABTotalCost
Debug.Print PAForecastBaseQty
Debug.Print PAFUnitCost
End Sub

 

[ginaburg]

Part of the lightbulb just went on.
I revised the AfterUpdate for the Quantity and UnitCost as follows, but it still doesn't work.

Private Sub PABQuantity_AfterUpdate()

Dim PAForecastBaseQty As Integer
Dim PAFQuantity As Integer

If PABUnitCost > 0 And PABQuantity > 0 Then
PABTotalCost = PABUnitCost * PABQuantity
End If
PAForecastBaseQty = PABQuantity
PAFQuantity = PABQuantity


End Sub

Private Sub PABUnitCost_AfterUpdate()

Dim PABTotalCost As Integer
Dim PAFUnitCost As Integer

Debug.Print PABQuantity
Debug.Print PACOSTCATID
Debug.Print PABUnitCost

If PABUnitCost > 0 And PABQuantity > 0 Then
PABTotalCost = PABUnitCost * PABQuantity
End If
PAFUnitCost = PABUnitCost
Debug.Print PAFQuantity
Debug.Print PABTotalCost
Debug.Print PAForecastBaseQty
Debug.Print PAFUnitCost
End Sub

 

[PaulF]

you are still only setting variables, not the value for the controls bound to the fields you want to update.

if you name the controls in your form as follows

txtQuantity : bound to Quantity Field
txtUnitCost : bound to UnitCost Field
txtTotalPrice: bound to total cost field
txtFQuantity: bound to FQuantity field
txtBQuantity: bound to BQuantity field
txtFUnitCost: bound to FUint Cost field
txtBUnitCost: bound to BUnit Cost field

Then this code should work

Private Sub txtQuantity_AfterUpdate()
If Val(Nz(txtUnitCost,0))>0 And Val(nz(txtQuantity,0))>0 Then
Me.txtTotalPrice = Val(nz(txtUnitCost,0)) * Val(Nz(txtQuantity,0))
End If
Me.txtFQuantity = Me.txtQuantity
Me.txtBQuantity = Me.txtQuantity
End Sub
Private Sub txtUnitCost_AfterUpdate()
If Val(Nz(txtUnitCost,0))>0 And Val(nz(txtQuantity,0))>0 Then
Me.txtTotalPrice = Val(nz(txtUnitCost,0)) * Val(Nz(txtQuantity,0))
End If
Me.txtFUnitCost = Me.txtUnitCost
Me.txtBUnitCost = Me.txtUnitCost
End Sub


PaulF

 

[ginaburg]

I'm going to play dumb here. I'm not sure I understand. Is this actual code I have to put in or where does this go?

txtQuantity : bound to Quantity Field
txtUnitCost : bound to UnitCost Field
txtTotalPrice: bound to total cost field
txtFQuantity: bound to FQuantity field
txtBQuantity: bound to BQuantity field
txtFUnitCost: bound to FUint Cost field
txtBUnitCost: bound to BUnit Cost field

 

[PaulF]

no it isn't code.. I'm assuming (and we all know what happens when we do that).. that you're using a recordsource for your form that contains all of the fields you need to update. If that's the case then you need to create a textbox for each of these fields and use the fields as the controlsource for the textboxes. These can have their visible property set to False so that they don't show (except for the Unit Cost and Quantity textboxes that you enter data into). Then the code in the Afterupdate event will automatically update the other textboxes... If you name them like I have them in the code.

If I'm incorrect in what you are using for a recordsource for your form, then this has to be approached differently.

PaulF

 

[ginaburg]

I am using a record source will all of the fields on it. My record source is a select query (is this correct).
I have textboxes setup for each field that I want to update with the control source set to the same names as I have in my code. They are one the form as invisible.

But I am still not able to get it to work.
I have still got to be missing something. I am total confused.

Gina

 

[PaulF]

what is the name of the textbox control that is bound to the field
Quantity ? is it txtQuantity
UnitCost ? is it txtUnitCost
FQuantity ? is it txtFQuantity
BQuantity ? is it txtBQuantity
FUnitCost ? is it txtFUnitCost
BUnitCost ? is it txtBUnitCost
Total Cost ? is it txtTotalPrice

If no then, this is why the code doesn't work.. if yes, then check to ensure that you have [Event Procedure] in the box next to the AfterUpdate Event for txtQuantity and txtUnitCost.

If yes, then ensure that there isn't a message in the status bar stating that this recordset is not updatable.

if no, then I'm not sure what is happening.

PaulF

 

[ginaburg]

My field names are a little differnt than what you have as you can see from my coding.

The fields in my code above are identical to the textbox control source on these fields.
PABTotalCost : bound to PABTotalCost field
PAFQuantity : bound to PAFQuantity field
PAFUnitCost : bound to PAFUnitCost field
etc...

Is there some significance to the "txt" other than consitancy that I am missing?

I have the AfterUpdate event on both the PABQuantity and PABUnitCost fields.

The word calculating shows up in the status bar when I enter data.

I have to misunderstanding something that you are saying.

 

[PaulF]

your original post stated this
total cost = Quantity x Unit Cost
FQuantity = (Quantity from above)
BQuantity = (Quantity from above)
FUint Cost = (Unit Cost from above)
BUnit Cost = (Unit Cost from above)

so I thought your fields names are total cost, FQuantity, BQuantity, FUnit Cost and BUnit Cost.... not PABTotalCost, PAFQauntity or PFUnitCost

PABTotalCost and PAFUnitCost are variables in your code, not field names

Dim PABTotalCost As Integer
Dim PAFUnitCost As Integer

you need to tell me which is correct... if PABTotalCost is the actual field name, then you can't use that as a variable name nor would you want to.


if PABTotalCost is the actual field name and also the name of the control, you need to remove the dim statements that make them variables, and the code you have would probably work.

The Leszynski/Reddick Guidelines for Access is the most commonly used naming convention for Access objects, that's were "txt" comes from....
see

http://infocom.cqu.edu.au/Courses/2001/T5/COIT13143/Resources/lrnamstd.rtf

for one source of the data.

If I've confused you along the way.. sorry about that.. I was really only trying to help.

PaulF

 

[ginaburg]

It's working now, I am soooo sorry.
I didn't have the fields on my form in the beginning and that is why I setup the DIM's up because it was giving me variable errors.

Thank you so much for all of your help.
I apologize, it has been a long time since I have done any programming.

Thanks
Gina

 

[PaulF]

I'm just glad we finally got on the same page. Have a nice weekend.

PaulF