Help with queries and record updates

[paulevans]

Ok I been at this most of the day and I have got no where fast.

I have a table with two fields [type] and [amount]

when populated I have something like this

hats 23
coats 33
shoes10
hats 18
coats 19
shoes 5

I need the individual sums of each of these records. for example shoes 15

I have created a query that does this for me. and I have put this information into a form. How do I get to the values to use else where. The form I created was a single form which shows coats 54 then by using the record navigation I get shoes 15. I think I need to pass these values into VBA but have not go a clue where to start.

Please help.

 

[wazz]

I have created a query that does this for me.

ok.

and I have put this information into a form.

ok.

How do I get to the values to use else where.

don't know what you want to do exactly.

The form I created was a single form which shows coats 54

i hope that's 52.

then by using the record navigation I get shoes 15. I think I need to pass these values into VBA but have not go a clue where to start.

same here. it's unclear what you are trying to do.

 

[paulevans]

Hi I have attached a sample file to show what I am trying to do.
This has one table, a query and a form. In the footer of the form I need the values calculated as to how many coats, shoes and hats there are.

I hope this answers your question

 

[wazz]

oic.

use a list box. put a list box on your form. make sure the column count is 2 and the column widths are > 0.

when you change a value in the main form, you have to requery the list box to see the sum change: Me.ListboxName.Requery.

 

[paulevans]

Hi Thanks for that help.

Only I'm reaaly new to this could you please tell me where I put the requry statement .

 

[wazz]

in the After Update event of the Form.

 

[paulevans]

Thanks Wazz

That is really just what I wanted. I have now been looking at ways to get the info from the list box and am struggling still with this. I would like to be able to take the vales of shoes and put this in a seperate text box any ideas?

Paul

 

[wazz]

hi. this is easily done. however, i am concerned about the overall structure. you may have only shown me part of your db, i'm not sure. but it's probably not wise for me to do much more without addressing the table design first, which could be faulty, depending on how much i've seen. can you post any more? is there more? can you post maybe a shot of the relationships? if we go further without checking the design we might have to do a lot over again...no point.

 

[paulevans]

hi cannot load main programme as file size is too large.

I have tried to compress it with winzip yet is still 1.4MB

If you take my sample programme I would just like to see how the data is taken from the listbox to one of the unbound text boxes.

Thanks again for all your help

 

[wazz]

actually, now i don't think it's possible. maybe someone else knows of a way??

i *think* what you have to do is use separate listboxes instead of textboxes. reformat them if necessary to make them look the way you want. create a separate query for each item using criteria and apply the results to separate listboxes. i don't think a textbox can automatically read a value from a listbox quite the way you want. you can't just leave the list? you can get values from a list by clicking the list...but the values are already there...

 

[paulevans]

Hi Wazz

Thanks for all your help. You can get info from list box by following method:
Private Sub Form_AfterUpdate()
Dim lstdata As Integer
Dim lst2data As Integer
Dim lst3data As Integer

Me.List10.Requery
lstdata = Me.List10.ItemData(0)
lst2data = Me.List10.ItemData(1)
lst3data = Me.List10.ItemData(2)

Me![Text4] = lstdata
Me![Text6] = lst2data
Me![Text8] = lst3data

end sub

Just incase you ever need to do this.

 

[wazz]

i forgot about itemdata. glad u got results.