Simple Retrieval of Data from Multiple Forms

[Jaxson]

Hey guys,

Objective:
Create a chart comparing the weight and volume of two items, A and B.

Parameters:
I already have two subforms, each which calculate the weight and volume of A and B. I then created a bar chart which illustrates the weight and volume differences for a set of psuedo numbers.

Problem:
How can I import the values which I have in those forms into my table?

I have attached a pic of my table to add some visual reference.

Thanks in advance. You guys have been a great help in the past 3 months.

 

[dmonney]

try this

You can add this statement once the value is entered on form. It will update your table. Simply fill in the values in parenthises.

Dim sqlstatement as string
sqlstatement = "UPDATE tempcomparison SET tempcomparison.=(weight or volume) =(valuestored.value) WHERE (tempcomparison.Model= & (WhaterverModel) & ");"
DoCmd.RunSQL sqlstatement

 

[Jaxson]

hey dmoney,

Thanks for your help so far. Unfortunately I still got an error. It seems like theres a syntax error involving the perenthasis and the quotation makrs. Please let me know if this is right. Is this what you mean when you said I had to fill in the paranthesis.

Thanks

Dim sqlstatement as string
sqlstatement = "UPDATE tempcomparison SET tempcomparison.=(WEIGHT) =(valuestored.value) WHERE (tempcomparison.Model= & (A) & ");"
DoCmd.RunSQL sqlstatement

 

[Jaxson]

Anybody else have any suggestions?

 

[dmonney]

the parentasies are suppoed to be removed for example let's assume that the weight is stored on your form in a text box txtweight and model is stored in a textbox called txtmymodel
sqlstatement = "UPDATE tempcomparison SET tempcomparison.Weight = " & txtweight.value & " WHERE tempcomparison.Model = " & txtmymodel.value & ");"

 

[Jaxson]

dmonney,

Your the man!...Thanks.

But I have another problem, maybe you can help me also. The issue now is that the value sent to the table is 0 at first because the two subforms dont get a chance to calculate the volume and weights before the chart is created.

Is there an event procedure that makes the chart wait a few second or until the two subforms are done calculating before forming?

 

[dmonney]

Try putting the code in the "on update" procedure. If that doesn't work there must be some form of code or procedure that happens when your form updates. Try putting your update clause there.

 

[Jaxson]

Unfortunately, that still isnt working. I entered the code, which you help me with yesterday, into the form with the graph. But with the code u sent me, a message box pops up everytime to notify me that its updating the table. But by the time the table is updated, the chart is already up and has no values in it, until i reopen the forum a second time, since the numbers from the previous run is stored in the tempcomparison table.


I have spent literally hours trying to get it to work.....ahhh. So close yet so far.

 

[Jaxson]

I have the following code posted in the form with the chart. Other than it not updating the chart, it also requires the user to click the chart first before thiese lines of codes even run....I though the _enter() was an automatic type event procedure.

Private Sub ComparisonChart_Enter()
Dim sqlstatement1 As String
Dim sqlstatement2 As String
Dim sqlstatement3 As String
Dim sqlstatement4 As String
sqlstatement1 = "UPDATE TempComparison SET tempcomparison.Weight = Forms!ComparisonResults1!ComparisonResults_sub.Form!TotalWeight WHERE TempComparison.Model = 'A' "
sqlstatement2 = "UPDATE TempComparison SET tempcomparison.Volume = Forms!ComparisonResults1!ComparisonResults_sub.Form!TotalCube WHERE TempComparison.Model = 'A' "
sqlstatement3 = "UPDATE TempComparison SET tempcomparison.Weight = Forms!ComparisonResults2!ComparisonResults_sub.Form!TotalWeight WHERE TempComparison.Model = 'B' "
sqlstatement4 = "UPDATE TempComparison SET tempcomparison.Volume = Forms!ComparisonResults2!ComparisonResults_sub.Form!TotalCube WHERE TempComparison.Model = 'B' "

DoCmd.RunSQL sqlstatement1
DoCmd.RunSQL sqlstatement2
DoCmd.RunSQL sqlstatement3
DoCmd.RunSQL sqlstatement4

End Sub

 

[dmonney]

put this code directly before the update
Docmd.SetWarnings false 'turn off warning messeges
<-------------- YOUR UPDATE CODE HERE ---------------->
Docmd.SetWarnings True ' turn back on messeges
(Yourchartname).requery ' this will update the chart

 

[Jaxson]

Okay....but now when i exit the form, a message box saying "enter parameter value" for each of my sql statements appears.

If I just click "ok", it will clear my table since I am not entering anything.
But if I click "cancel" it will ask to debug my code and highlight the "DoCmd.RunSQL sqlstatement1"

I have attached some screen shots.

 

[dmonney]

From what you said it is working when you want it to and then when it closes it tries to go again. You need to find out what event prodedure is calling that code on you exiting the form.

 

[Jaxson]

I dont know what it could be. I had a refresh button that requery the graph when clicked, but that is what was causing the "enter parameter" message to appear when exiting the graph. So I removed it. If I dont click on it, the message does not appear when exiting.

So now I still need a way to update my graph. I have checked and the code that you helped me write does update my table with the values from two other forms, however again the chart displayed doesnt show those values until it is reopened.

I have tried entering the graphname.requery after the code that sends the values to the table, but it still doesnt work. I have also tried some delay functions, but that didnt work either. Any suggestions?

Here is my code so far for "graph2."

Private Sub Graph2_Updated(code As Integer)
Dim sqlstatement1 As String
Dim sqlstatement2 As String
Dim sqlstatement3 As String
Dim sqlstatement4 As String
sqlstatement1 = "UPDATE TempComparison SET tempcomparison.Weight = Forms!FootprintcomparisonResults!Footprint_sub.Form!ETotalWeight WHERE TempComparison.Model = 'A'"
sqlstatement2 = "UPDATE TempComparison SET tempcomparison.Volume = Forms!FootprintcomparisonResults!Footprint_sub.Form!ETotalCube WHERE TempComparison.Model = 'A' "
sqlstatement3 = "UPDATE TempComparison SET tempcomparison.Weight = Forms!FootprintcomparisonResults!Footprint_sub.Form!KTotalWeight WHERE TempComparison.Model = 'B' "
sqlstatement4 = "UPDATE TempComparison SET tempcomparison.Volume = Forms!FootprintcomparisonResults!Footprint_sub.Form!KTotalCube WHERE TempComparison.Model = 'B' "
DoCmd.SetWarnings False 'turn off warning messeges
DoCmd.RunSQL sqlstatement1
DoCmd.RunSQL sqlstatement2
DoCmd.RunSQL sqlstatement3
DoCmd.RunSQL sqlstatement4
DoCmd.SetWarnings True 'turn on warning messeges
me.requery

 

[dmonney]

There are twp directions you could go from here,
one is to se where you get your "enter parameter" messege from. When it pops up hit [CTRL] + [Pause/Break] and enter the debuger to see what line of code it is trying to follow. Once that is diagnosed you can possibly fix that.

The second is to totaly forget about refreshing that graph and make a new on in a seperate form. Take a look at how to use subforms(do a search on this site youwill get boatloads of info). They can be refreshed programatically without actually closing the form your in.

On second note you already put in your screenshot and it's dying on the sql statement.
Just try using the subform i guess

 

[Jaxson]

dmonney,

yea...I just moved the chart to its own individual form and created a button to link to it. Now everytime it opens, the chart is always updated with the new values opposed to when it was in a subform. Maybe because it was drawign values from other subforms in the same main form simply did not give it enough time to update by the time it was called upon.

thanks for the dedication to helping me.