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 2 years 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 HeacoxOP
@Reply 2 years ago
Benjamin HeacoxOP
@Reply 2 years 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 HeacoxOP
@Reply 2 years 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 2 years 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 HeacoxOP
@Reply 2 years 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 2 years 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 HeacoxOP
@Reply 2 years ago
Benjamin HeacoxOP
@Reply 2 years 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 HeacoxOP
@Reply 2 years ago
Adam Schwanz
@Reply 2 years 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 2 years 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 HeacoxOP
@Reply 2 years 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 2 years 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.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Expert 22.
