VB Code for INSERT INTO

[StephenVW]

Ok, I'm fairly new to Access. What I'm attempting to accomplish is using a command button to insert short text to a specific field within a table. So for example, in my Master form I'd like to have a command button, that when pushed, inserts the text "A1" into the Location field in my Master Inventory table. I tried accomplishing this by using the INSERT INTO statement, but I had no luck. Is this the best way to do it, or is there a simpler way? If it's the best way, then what am I doing wrong? Here is the code I used:

'add data to Master Inventory Table


CurrentDb.Execute "INSERT INTO MasterInventoryTable (Location) " & _
" VALUES ('A1') "

[June7]

That SQL should create a new record in table. Don't see anything wrong with code. Why doesn't it work - error message, wrong results, nothing happens? Why do you need to do this? What are you really trying to accomplish?

[StephenVW]

Nothing happens, there's no error or anything. I need to do this to upload location on inventory, but I'd like to just create several command buttons to input location and not a combo box, list box, or anything like that.

EDIT: It seems I get this error -- Runtime error '3134': Syntax error in INSERT INTO statement.

[June7]

Do you want to create a new record?

Or do you want to enter data into current record displayed on form?

Your code would create new record. Are you sure nothing happens? Not seeing new record in table? Step debug. Refer to link at bottom of my post for debugging guidelines.

[StephenVW]

Ok, I got it to work. Here's what I did.

CurrentDb.Execute "INSERT INTO MasterInventoryTable (Location, Item)" & _
"VALUES ('A1', '')"

Only problem now is that I can only input A1 once, and when if I hit it again it won't input the new data.

[June7]

Why are you putting empty string in Item field?

Why would you enter another identical record?

[StephenVW]

For some reason, when I put in the next field and have it enter no information it worked. I figured this out by trying to update multiple fields instead of just one.

The record is not identical, because I could have a certain item in the same location. Each record has Location, Item, Color, Qty, Qty by Post, and Stage. I'm simply only inserting the location.

[June7]

But you are not updating data in existing record, the INSERT is creating a new record in table.

If you want to populate field of current record on form, can simply:

Me!Location = "A1"

[StephenVW]

Ok, I understand. Here's the code I was using to update the record, however, it would update all records in that field.

CurrentDb.Execute "UPDATE MasterInventoryTable" & _
"SET Item = ('" & Me.ItemID & "')"

So here's what I tried doing. But there's a syntax error due to the Where criteria.

CurrentDb.Execute "UPDATE MasterInventoryTable" & _
"SET Item = ('" & Me.ItemID & "')" & _
"WHERE Item = ('NULL')"

I only want to update blank records in the Item field, so if there's any data in them it will not update with new data.

[June7]

Null is never a string value so placing within quotes or apostrophes does not work, not if the field is truly null and isn't actually populated with the word NULL.

Can't use = Null in expressions http://allenbrowne.com/casu-12.html

Try:
WHERE Item Is Null

[StephenVW]

Awesome! Seems like that was the trick! Thank you for your help