Total of specific selection not showing in textbox

[Ben Saeed]

Hi guys
Greetings from Geneva,

I am new in MS Access and I have a situation, seeking your kind help.
I have 2 table as follow.

Productmaster
-Productname
-Opening date

Transaction
-Productname (linked with master productname)
-qtyin
-qtyout

I am working on Form transaction1 linked with table(transaction) on which I have combobox therein I have name of Products which are coming from PRODUCTMASTER.PRODUCTNAME. everything going well all data commiting accurately in Transaction table.

Now, What I want when I select comboxbox value i.e. productname then sum of qtyin+qtyout from table(transaction) should come into textbox(total) as according to the productname selected

Looking forward to your kind response.
Thanks in advance

BEN

 

[CJ_London]

I think what you want is a Dsum in the control source of your total box

=dsum("[QtyIn]+[QtyOut]","Transaction","ProductName = '" ComboName & "'")

You may need to put the following into the afterupdate event of the combo box

total.requery 'where total is the name of your total control

 

[Ben Saeed]

Thanks for the reply.

No, its not working, I typed this =DSum("[entree]+[sorti]","Transaction","ProductName = '"[forms]![transaction]![productname] & "'") its given me error "The expression you entered contain invalid syntax, you may have entered an operand without an operator"

Please advise
Ben

 

[CJ_London]

Firstly, you are missing an ampersand (my mistake)

"ProductName = '" & [forms]![transaction]![productname] & "'"

Also, just to check - if your productname control on your transaction form is a combobox then check its rowsource

If it is something like "SELECT ID, Productname from Products" and the bound column is 1 then the product name value is numeric because the bound column is the first column which is ID.

So you would need to change your where part of the dsum to

","ID = " & [forms]![transaction]![productname]"

i.e. remove the single quotes

 

[Ben Saeed]

Well done...
Thanks alot.


I have just 2 last problems,


Problem 1

On my form transaction there is field "Namepick" where the names coming through query "workers query" from table "workers".


I also added command button with the namepick combobox, incase of name does'nt exist then press Add new name button, onclick addnew button there is the another form called "worker" open and I can put new worker name.


Problem is when I coming back to combobox, query is not updating in the combobox whatever the new workername I am entering in, in this case I have to close & open form again.


Problem 2

Similar to the above I have list box which is not getting update query, even in VBA I used me.refresh and list.requery commands.

Please help.

Ben

 

[CJ_London]

Problem 1

You need to requery the namepickcombo box after a new name has been added. I would do this in the form "Worker " afterupdate event>

Forms!transaction!namepick.requery

Problem2

Check you are using the correct naming as above and have the command in the correct place

 

[Ben Saeed]

Thanks a million

 

[Ben Saeed]

Hi,

I would much appreciate if I could get some advise regarding following query.

I am making some attendance sheet on access as follows

Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00
------!-----!------!-----!------!-----!------!
Test1 !Prsnt! ! ! ! ! !
test2 !Absnt! ! ! ! ! !
test3 !Vction! ! ! ! ! !

I have tables like
workersmaster(workerid,workername,joiningdate,status)
Workerdetail (Workerid,workername,entreetime,attendance)
timing(timing(0900to1500)

Now I wanted to make report as mentioned above, do you have any idea how can i do this?

Thanks
Ben

 

[CJ_London]

thanks for the thanks!

With regards your next issue - better to post this as a new thread but as a quick answer, it looks like using a crosstab query will be the solution (note if you want to display in a form or report you must predefine the column headers)

 

[Ben Saeed]

Thanks alot for whatever your help.
Take care and have a nice day