472,811 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,811 software developers and data experts.

Form Reference on Update Query Not Working

4 Nibble
I have a form that is used for creating orders of products on my database. My goal is to subtract the order.quantity field from the stock.stock_quantity field through the use of an update query. In order to target the specfic stock record, when the customer picks which stock they want in the order form, I use the order.stock_id field to pick which stock record I target.

The problem is that when I try and reference the form in my update query nothing happens, I simply get a prompt when I execute it saying "You are about to update 0 row(s)", I've tried taking out the "Where" clause from my query and it will subtract 1 from all the stock.stock_quantity records so I know its not a problem with the SET clause.

The full SQL for my UpdateStock query is as follows:
Expand|Select|Wrap|Line Numbers
  1. UPDATE stock SET stock.stock_quantity = [stock]![stock_quantity]-1
  2. WHERE (((stock.stock_id)=[Forms]![OrderForm]![stock_id]));
I've looked at multiple YT videos and even the offical microsoft access guides for this and so I think I have the right syntax. Any help is appreciated!
Feb 6 '21 #1

✓ answered by ADezii

I was wondering if it could be due to my Microsoft Access version?
In just loaded the DB into 2007 and here is what I came up with. The Click() Event of the Create Order Command Button is assigned to an Embedded Macro. Within that Macro, you are attempting to go to a New Record prior to executing the UpdateStock Query which will never work since the Criteria will never be satisfied (stock.stock_id)=[Forms]![OrderForm]![stock_id] or stock.stock_id)=[Forms]![OrderForm]![stock_combo]). Either Criteria will read the [stock_id], but in a New Record it would be meaningless, since it has yet to be initialized (it is an AutoNumber/Primary Key). Hopefully, this makes sense, or 2007 somehow corrupted the Embedded Macro.

10 2229
NeoPa
32,534 Expert Mod 16PB
I downloaded your database and, while the reference is different there from what you've posted, it does seem to be correct. My problem is that it worked first time. Did you post the question before fixing the reference and then forgot to test it?
Feb 6 '21 #2
Exiis
4 Nibble
Hi NeoPa,

I'm not sure why it worked on your database but not mine that is very weird, I've included a gyazo screen shot of the error with the query in the background.

https://gyazo.com/27f6af55ef99668714ef39a7ec3c0880

Thank you
Feb 6 '21 #3
ADezii
8,834 Expert 8TB
Works fine on my end also.
  1. Is [stock_id] the Bound Column for [stock_combo]?
  2. If it is, and you are still not getting the desired results, try a different approach referencing [stock_id] directly in the Combo Box:
    Expand|Select|Wrap|Line Numbers
    1. CurrentDb.Execute "UPDATE stock SET stock.stock_quantity = [stock]![stock_quantity]-1 " & _
    2.                   "WHERE stock.stock_id=" & [Forms]![OrderForm]![stock_combo].Column(0), _
    3.                    dbFailOnError
Feb 6 '21 #4
NeoPa
32,534 Expert Mod 16PB
I really have no idea how a picture of it not working could help any of our understandings of the situation. I have no trouble believing what you say you're experiencing. The fact you have a different experience from ADezii & myself isn't a problem per se - it's information we can use to identify where the issue lies. The format is fine. It works in some circumstances which couldn't be the case were the format in error.

What could be different? Is the form open when you run it? If so, what value is set in that control when you trigger the query to run? Is the database acually exactly the same as we're using? Or may there be some very minor modifications you assume won't make any difference, that you tried to see if you could find the problem? I would suggest doing your testing exclusively with your own posted database downloaded again into a separate area and never making changes that aren't posted to the thread.

@ADezii.
I understand that sometimes the SQL is incorrectly formatted and this can be got around by redoing it using literal values. However it seems clear in this case that isn't the situation. To avoid complicating matters even beyond the original state I would suggest we leave any alternative approaches aside until we have identified the actual problem. Such side-discussions have a tendency to make identifying the real problem, and thus helping the OP where they really need it, much harder to achieve.

I'm sure there'll be an opportunity for more general advice once the real issue has been identified and there's no longer any danger of throwing that search off track.
Feb 6 '21 #5
ADezii
8,834 Expert 8TB
Points are all valid, just curiosity creeping in.
Feb 6 '21 #6
Exiis
4 Nibble
Hi NeoPa,

I took your advice and redownloaded the database onto my computer. Alas it still didn't work, as you might already know, the update query is set to trigger upon clicking the "Create Order" button on my Order Form, of which the value in the "Material Type" combo box is taken as the stock_id for the update query to use. I've included a screenshot of how I'd fill the Order form out when trying to get this update query to work.

My original goal was to take the "Quantity" field away from the "stock_quantity" field in the SET clause but as that didn't work (Due to the same issue with referencing forms) I decided to scale down my problem into simply trying to target a specific record with the form referencing.

I was wondering if it could be due to my Microsoft Access version? I'm using Access 2007 with the 2016 File Format, is there a chance that it could be acting strangely due to this? I feel like something like that might be what is distinguishing our two experiences and @ADezii's as well.

https://gyazo.com/3158dc4f4f5e2e161d076522e9ef4fe6

@ADezii

To answer your question the [stock_combo] is simply me looking up the [stock_id] and [stock_name] for the record and saving the [stock_id] so I guess [stock_id] could be considered the bound column
Feb 6 '21 #7
ADezii
8,834 Expert 8TB
I was wondering if it could be due to my Microsoft Access version?
In just loaded the DB into 2007 and here is what I came up with. The Click() Event of the Create Order Command Button is assigned to an Embedded Macro. Within that Macro, you are attempting to go to a New Record prior to executing the UpdateStock Query which will never work since the Criteria will never be satisfied (stock.stock_id)=[Forms]![OrderForm]![stock_id] or stock.stock_id)=[Forms]![OrderForm]![stock_combo]). Either Criteria will read the [stock_id], but in a New Record it would be meaningless, since it has yet to be initialized (it is an AutoNumber/Primary Key). Hopefully, this makes sense, or 2007 somehow corrupted the Embedded Macro.
Feb 6 '21 #8
Exiis
4 Nibble
I fixed the issue by moving the 3 macros (OpenQuery, RunMenuCommand and CloseWindow) from the very bottom to the very top, meaning they would be launched first (See Screenshot)

https://gyazo.com/8eb04dc080c612ebf4890ee473037324

Thank you both ADezii and NeoPa for the assistance!
Feb 6 '21 #9
ADezii
8,834 Expert 8TB
the update query is set to trigger upon clicking the "Create Order" button on my Order Form
This was the reason why we didn't find the problem until later on. I was executing the Query directly, once you stated where it was triggered, then the problem became obvious.

I'm sure that I can speak for NeoPa in stating, you are quite welcome.
Feb 6 '21 #10
NeoPa
32,534 Expert Mod 16PB
ADezii:
I'm sure that I can speak for NeoPa in stating, you are quite welcome.
Always. On both counts.

Exiis:
I'm not sure why it worked on your database but not mine that is very weird
That'll be because what you were doing and what you told us about were two different things. You were clicking on a button that had not been mentioned up to that point in the thread. We were executing the query - being very careful not to involve anything that wasn't directly involved with the issue.

When testing your project it's always advisable to keep strictly to the KISS principle. I'll let you Google that if it's not something you recognise.
Feb 6 '21 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: vjmehra | last post by:
I have a datalist within a datagrid and I am having trouble trying to get the update command working. I am only using the one for the datagrid, but this should also update the datalist, however...
2
by: nadmasl | last post by:
I have an MS Access form, where a user enters the date by selecting it from three combo boxes for day, month and year. I convert it to 11-Jan-2004 format and attempt to write to the table with an...
1
by: Stuart E. Wugalter | last post by:
Hello. No one in the queries newgroup answered this question. I hope someone here can. Thank you. Stuart The following Update Query allows my users to substitute a numerical value for the...
1
by: Michelle | last post by:
Hi all I am having problems creating an update query. I have 2 tables, tblPublishRoster and tblCCAgents_Changed_Shifts. I want to select all records from tblCCAgents_ChangedShifts where...
5
by: Andrew | last post by:
I've got a list box that selects a record on a subform, and in that subform are a few text fiels and a button that runs an update query. How do I have the update query button run and only update...
2
by: justapawn | last post by:
Can someone assist me in using the correct syntax or method of assigning a variable to the Set portion of a DoCmd.RunSQL Update Query? The help file and most sites, including ms, seem to think the...
12
by: praveenkrg | last post by:
my update query is not working properly! $mgroupname=$_GET; //echo $mgroupname; ///query for checking userg_id $result=mysql_query("SELECT * FROM group WHERE userg_name ='$mgroupname'");...
5
by: dougmeece | last post by:
Hello experts, I have an update query that I want to modify records meeting a certain criteria in a table based on information on a form. I am having trouble recognizing the table for a...
4
by: nomeepk | last post by:
Hi, I want to make a form for Update Query to update order dates. As you know Query update requires: Criteria = Old Date Update to: New Date but i want it to get both old and new dates...
2
by: Arvinder Sahni | last post by:
UPDATE jt SET jt.Assigneduser = (SELECT u.userid FROM Users u where u.username ='Debbie Ross') FROM Jobstasks jt Users u on jt.assigneduser = u.userid Funds f on jt.fundid...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.