Append or Update Query (picking which to use Via VB)

[ConfusedA]

Using: Access 2k3

What I have is an input form for inserting inventory (type, amount, price, etc.)

I use an append query to place each type in to another table. If the object is already in the table i'd like to be able to use an update query just to update the values.

The query would be set off by a command button.
I imagine the code is something like:
If [Forms]![Formiteminput]![flditemtype] = tblItemstock Then
stDocName = "qryupdateitem"
Else stDocName = "qryappenditem"
End If
DoCmd.OpenQuery stDocName, acNormal, acEdit

But I don't know quite how to turn this into appropriate visual basic code. Any help will be appreciated.
Thanks.
ConfusedA

 

[pbaldy]

One way would be to use a DCount and test for > 0. Info on syntax:

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

 

[ConfusedA]

Thanks for your thoughts Paul, I still haven't been able to figure out Dcount, but I created part of my solution, though it's still full of errors.

The problem I'm having that I do not understand, is figuring out how to append if nothing exists. Like: say I have 3 values in a base table, and then I have a list of numbers associated with them, so that I would have a table like:
# Value Number-type1 Number-type2 Number-type3
1. Value1
2. Value2
3. Value3


When a value occurs (IE if value2 is inputted in my form) I want to update the number-type fields. But my problem lies with the lack of value. So say I have an append query that adds a list of values to one table, but takes a number off of the above table to calculate.
If Value4 does not exist, I will not get a result in my update query because the form will only save to the one table, not to the ValueTable.

So what I have come up with is a system in which I need to check if a value does not exist, and if it doesn't to add a record to th value table that would contain the Value and numerical fields associated with it. I'm just not sue which procedure would let me do this. I know If Null works for an empty field, but can it work for an empty(nonexistant) line?


Thanks again.
ConfusedA