Trouble writing Apend/Update query with VBA

[runningphan]

I'm faced with a requirement that is causing me some problems. What I need to do is a combination Update / Append query depending on whether or not the keyed value already exists in the database. I've never really used VBA so this is all new to me.

The user has an Excel file that they enter items into. We'll call it {Excel.Item}. One the same row they provide {Excel.Location}. The excel file is designed to update/add records to a table in acces with a key {Access.Item}.

One other tricky aspect is that if the item value they provide {Excel.Item} exists in {Access.Item}, I have to figure out which of the possible 10 (0-9) different location fields of the Access record to stick the {Excel.Location}. I will put {Excel.Location} in the first Null Location field I find (which I'm not quite sure how to do.

So, this is how I'm thinking the logic would work, but I'm not sure how it translates into code.

If {Excel.Item} = {Access.Item} Then
If IsNull({Access.Location0}) Then update Else
If IsNull({Access.Location1}) Then update Else
If IsNull({Access.Location2}) Then update Else
If IsNull({Access.Location3}) Then update Else
If IsNull({Access.Location4}) Then update Else
If IsNull({Access.Location5}) Then update Else
If IsNull({Access.Location6}) Then update Else
If IsNull({Access.Location7}) Then update Else
If IsNull({Access.Location8}) Then update Else
If IsNull({Access.Location9}) Then update Else
Else Append Query

Sorry if this is a bit confusing...and for any help you might be able to provide.

Thanks!

 

[PHV]

possible 10 (0-9) different location fields of the Access record
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886