How to use the form combo# in a VBA table query?

[hjnash]

I am experimenting with VBA doing calculations of a filed in my database table. I have a string that is working


but the last element in the command is what is being searched for (hard coded). Instead of doing this I want to
use the value after I select from my combo box the selected value so I use the build in comvo21 like a variable.
So If I select medical from my combobox "Combo21" and I have selected from the event triger for that combobox
after update I can do MsgBox Combo21 and the selected value from the combobox will be displayed in a message box
I need to use combo21 variable in my string I can't figure out how to change my command.
How do I replace Medical with combo21 variable? Disregard the [ ] below

[Private Sub Combo21_AfterUpdate()]
[Dim stotal As Currency]
[stotal = DSum("Spending", "Spending", "Category = 'Medical'")]
[MsgBox stotal]




[End Sub

[pbaldy]

See if this helps:

http://access.mvps.org/access/general/gen0018.htm

this may also be appropriate:

http://www.baldyweb.com/Autofill.htm

[hjnash]

Thank you for the link I have some success; but, an error is generated if the database table has no entry for the selection.
The error photo below was generated because I selected in my form combobox21 medical as the spending category, in my
table there are no entries yet for medical so I get and error.

[ Private Sub Combo21_AfterUpdate()][Dim stotal As Currency]
['stotal = DSum("Spending", "Spending", "Category = 'Medical'")]
[stotal = DSum("Spending", "Spending", "Category ='" & Forms!SpendingTest!Combo21 & "'")]
[MsgBox stotal]




[End Sub]

[Bradonnmorgan]

HjNash,

How are you today? Sorry to hear about your issue. May I sujest trying:

[ Private Sub Combo21_AfterUpdate()][Dim stotal As Currency]
['stotal = DSum("Spending", "Spending", "Category = 'Medical'")]
NZ([stotal = DSum("Spending", "Spending", "Category ='" & Forms!SpendingTest!Combo21 & "'")])
[MsgBox stotal]

The Nz function just makes Null a 0. Not 100% sure if that will give you the same end result or not but it should ^_^ hope this helps.

Regards,

Bradon Morgan

[hjnash]

Thanks for your help I have looked into the Nz and I think it should work BUT!
I tried the Nz function But I always get an error now, perhaps I am not ding it correctly! Please see image below
it shows the error and the code change.

[Bradonnmorgan]

hjnash,

Would you mind uploading a copy of your database as long as it contains no confidential information so that I may mess with the code without having to competly recreate the database?
I feel that this my be something as little as removing the [ ] from your last line of code.

Thanks,

Bradon Morgan

[Bradonnmorgan]

hjnash,

Here is the proper syntax. very sorry I should have tested the code before I posted it.

Dim stotal As Currency
'stotal = DSum("Spending", "Spending", "Category = 'Medical'")]
Nz (stotal = DSum("Spending", "Spending", "Category ='" & Forms!SpendingTest!Combo21 & "'"))
MsgBox (stotal)



Regards,

Bradon Morgan

[hjnash]

Thanks for the code update. The good news is that this somewhat works; that is, If I am
entering the first record for category medical now I do get back a 0 and I can continue working.
The bad news is that if I enter a record for groceries where I have a lot of records I still get a 0
not the amount of money spent under groceries. I looked this up but did not see anything wrong
so I am overlooking something. If you have some time I still would like to finish this.
Thanks Harry Nash

[Bradonnmorgan]

Harry,

I would love to continue assisting you with this. If you could upload a copy of your database I will take a look and see if there isnt anything that sticks out to me.\
Two is always better than one ^_^

Regards,

Bradon Morgan

[hjnash]

I saved my access 2013 file as a 2003 MDB I hope you can open it OK.
I am also searching for some idea what I can do.

[Bradonnmorgan]

Harry,

Sorry for the delay very busy yesterday and today. I will try and get back with you on a fix around 5pmest.


Thanks,

Bradon Morgan

[hjnash]

Thank you so much, I am also trying to learn more about this command and how it may be failing.
Thanks Harry Nash