E2203 SetValueNow issue - Microsoft Access Expert 22


	Back to Access Expert 22 Comments List

Upload Images @Reply Bookmark Link Email

E2203 SetValueNow issue

Benjamin Heacox

12 months ago

Good morning all

I am building a database for inventory management (Thanks for teaching me Rick!). I have tried to build a similar system as shown in this video where an inventory asset is picked , then a button opens an inventory input window where there is an unbound box to input the count into. On a button click event in that window I have a setvalue for the inventory count and a setvalue for the time stamp ( now() ).

My trouble is when I add a count, it overwrites the timestamp for every record in the asset table with now(). It is puzzling because it keeps the asset's inventory count, but resets the timestamp for every record, so I have blocks of records (every asset) with the exact same time stamp.

Can someone help me figure out what happening?

	Kevin Robertson @Reply 12 months ago
Sounds like you need some sort of WHERE condition so it only sets the value in the current record. Please post some screenshots (Form, Macro for example).

	Benjamin Heacox OP @Reply 12 months ago

	Benjamin Heacox OP @Reply 12 months ago
I agree. I am not sure how to incorporate that into the field. I have now made a field that is the InvDateTimeCurrent that is just now() because I thought maybe if I was setvalue'ing from a blank input box and it was working fine, that maybe I just needed the date field to be pulled from a field as well. It did not work.

	Benjamin Heacox OP @Reply 12 months ago
Oh, I should also say for context that the 'unbound' box is named 'CountToInput' and is used to set the value of the 'InvCountInput' field on the button press (either new entry or overwrite last entry depending on the button)

	Adam Schwanz @Reply 12 months ago
I thought SetValue had a where line, but I don't use macros anymore so not 100%. Do you have a restriction where you can't use VBA? Edit: nevermind I see now this is only expert 22, you probably haven't seen much VBA yet

	Benjamin Heacox OP @Reply 12 months ago
I am up to Dev 7, but am still terrified of VBA code. Macros seem so much easier for me still - but you're right I need to bite the bullet and fumble around in the dark with some VBA in order to fix this problem and ultimately get better.

	Adam Schwanz @Reply 12 months ago
You might even be able to do that with just a Update Queries. But yea, VBA is much easier :). You can just make a currentdb.execute statement

	Benjamin Heacox OP @Reply 12 months ago

Benjamin Heacox OP @Reply

12 months ago

This is my new code I have written converting this from the macro. After much strife it functions as the only one has, and still has the same problem.

It will take the value from the 'CountToInput' box and paste it into the table field 'InvCountInput' and puts 'Now()" into the 'InvDateTime' field

It then saves the table and updates the form, but when I generate the query by unique timestamp it seems to save the count (not the date) over all the previous counts with unique InvDateTime stamps..

If I am misunderstanding the process I have built please let me know.

Also big shoutout to ChatGPT for helping me with syntax👍

	Benjamin Heacox OP @Reply 12 months ago

Adam Schwanz @Reply

12 months ago

Yea I would use a currentdb statement with a criteria instead of trying to gotocontrols, gotocontrols is probably what is setting all your fields, if I'm understanding what you're doing.

what fields are in the table that you are trying to set with this button? and what are the names of them on the form? I can try to help with the syntax

and just to clarify, are we creating a NEW record (insert) or are we trying to change the value of an existing record (update)?

	Adam Schwanz @Reply 12 months ago
something like this should create one instance of a new record and change nothing else `Currentdb.execute "Insert into InvInvputT (InvCountInput,InvDateTime) VALUES (" & CountToInput & ",#" & Now() & "#)"`

	Benjamin Heacox OP @Reply 12 months ago
Well, one button (the one we are currently addressing) creates a new inventory input, and a separate button overwrites the most recent entry. I will play with using the currentdb statement, I think I tried doing it this way first, but may have grown since then..😬

	Adam Schwanz @Reply 12 months ago
Yea if you needed to add the assetID and stuff as well, which I'm assuming you do, you would just add those fields into the statement as well. The update version of it for your other button would be similar but with a where criteria of I'm guessing the assetID.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 22.