VBA

Can you post the code in question, please?  This is a little vague.

If you're just looking to be able to edit the records one by one, include navigation buttons on your form.  You can either go into the form properties and enable the property there, or create your own with command buttons.

If that's not it, you'll have to provide just a wee bit more information...

Ok heres the code :-

Private Sub Deduction_LostFocus()
NumUnique = 1
If Forms![StockControlSystem].Deduction = [Product_Code] Then
    Select Case Forms![StockControlSystem].Stock_Remaining
    Case Is = [Product_Code]
        Forms![StockControlSystem].Stock_Remaining = "-1"
    Case Else
        Forms![StockControlSystem].Stock_Remaining = [Stock_Remaining] - [NumUnique]
End Select
End If
End Sub

What it does is see if the product code entered matches the one on the record then takes one from the stock remaining but it will only do it to the record im looking at i need to be able to enter a product code and then it will take one away from the stock remaining on any of the records i have with the same product code. E.g. if im looking at record 1 and i type to prouct code thats on record 1 it takes one from the stock remaining. If i type a code thats not on record one but is on say record 3 it dooesn't do anything though i need it to take one from record 3 in this example and same for if the product code matches one on record 5,6,7 etc.....

Hope this is a bit clearer now?

Thanks

Dean Pollard

try this instead:

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=-1 WHERE [Deduction] = [Product_Code]"
DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining]-[Num_Unique] WHERE [Deduction]<>[Product_Code]"

You could even combine both into one:

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=iif([Deduction]=[Product_Code],-1,[Stock_Remaining]-[Num_Unique])"

I built those based on the logic in the code you posted.  Are you sure you want to compare the [Deduction] field to [Product_Code] to determine how a record will be updated?  Will those two match?  Chances are you need to look for records matching a product code entered by the user, yes?  In that case:

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining]-[Num_Unique] WHERE [Product_Code]=" & varUserProductCode

Yes i need it to look for records matching a product code entered by the user then take 1 from the stock remaining.

I entered the last bit of code you typed do i need to delete the code i have already?

It came up with this error "Run-time error '3075' syntax error (missing operator) in query expression '[Product_Code]='

Thanks as you can tell im really new to this thanks for you time

There's alot of unknowns about your project that limit how specific any code I give you can be.  The more information we have about your project, the more likely we are to provide something useful.  :)  Here's an example of how to take a user's input and run an update query.  At the very least, you will need to change the table name from "MyTable".  This example assumes [Product_Code] is a field of type string.  If [Product_Code] is a numeric field, remove the references to Chr(34).

Sub UpdateStock()
Dim x  ' a temp variable

x = InputBox("Enter a product code")

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining] - 1 WHERE [Product_Code]=" & Chr(34) & x & Chr(34)

End Sub


FYI:  Chr(34)="  (double-quote character)

Im getting really confused now!

I want the user to be able to type in a product code and if it matches any ones in all the records it will take one from the stock remaining, and should work wether you type it in looking at record 1,2,3,4,5,6,7,8...............etc etc etc

Do i need the code i have already written or am i meant to be replacing it with your code?????

Thanks for the help but as i said im getting really confused now!

Still need desperate help with this!

The sub I just posted will do exactly that.  You need a trigger of some sort (like a command button) to fire the sub.  The sub itself is very simple.  The first line brings up an input box asking the user to enter a product code.  When they do, that is stored in variable x.  Then next line:

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining] - 1 WHERE [Product_Code]=" & Chr(34) & x & Chr(34)

uses x to build a SQL UPDATE statement.  Say the user entered "1234" as the product code.  Once x is put in, the line will look this way at run-time:

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining] - 1 WHERE [Product_Code]="1234""

The SQL pretty much explains itself.  In plain english, update the table MyTable, setting the field [Stock_Remaining] to the value of ([Stock_Remaining]-1) in any record having a product code of "1234".

If you put the UpdateStock sub in a module, and point a command button on your form to run that sub on a mouse click, your job should be done.

Oh i see i have done this now though it says "Operation must use an updateable query"

What does this mean?

Thanks for all you help

Where does it return that error?  Have you locked the table?  Do you have permissions on the table?

Ok when i click the buttin it says enter product code like it shoulkd then i enter one and it comes up with that error?

I dont think i have locked it or anything!

I entered the table name correct i also tried entering the form name but it still caused an error!

I though it was all gonna work 2 then this came up :( will it ever work?

Cheers

When the error comes up, it should highlight a line of code that is causing the error.  If not, you can place a breakpoint in your code by positioning the cursor on a line and pressing F9.  Using F8 to trace through the code, you can see exactly which line is causing the error.

Post any of your relevent code here and we'll see what we can do with it.

DoCmd.RunSQL "UPDATE MyTable SET [Stock_Remaining]=[Stock_Remaining] - 1 WHERE [Product_Code]=" & Chr(34) & x & Chr(34)
 
Its this line thats causing the problem!!

Could it be to do with the Chr(34) part as you mentioned abouve?

And i have replaced the words MyTable with the name of the table i have and for some reason just dont like it! So i put the form name in and it didnt like that either?

Erm dunno wat else to do/say

apart from HELP!

Thanks for the help though more is still required!

Post the code exactly as you have it in the module with no edits.  In the meantime, I will ask other experts to direct their attention here as well.

You can try replacing DoCmd.RunSQL with CurrentDB.Execute.

you rang?
I'm just swoopin' in to drop off some suggestions and I'm gone...

dim ssql as string
ssql = "UPDATE MyTable SET [Stock_Remaining] = ([Stock_Remaining] - 1) WHERE ([Product_Code]='" & x & "');"
currentdb.execute ssql, dbfailonerror  '<--I agree with your other Experts here
 (.execute gives you far more control over the operation.)

If your ProductCode will never have special characters in them you do not need to try and comp for that with chr(34).
if the ProductCode field is a numeric datatype you need to eliminate delimeters.

You need to also be looking out for Null math in the SET expression and mindiing that you build the sql statement to the best possible syntax to assure good results and quick debugging when needed...

somethin' to think about...

-j-

Ok

routinet - this is how i have the code : -

Private Sub Command25_Click()
   
Dim x  ' a temp variable

x = InputBox("Enter a product code")

DoCmd.RunSQL "UPDATE Stock SET [Stock_Remaining]=[Stock_Remaining] - 1 WHERE [Product_Code]=" & Chr(34) & x & Chr(34)

End Sub

jadedata - Where am i meant to put that code? Do i do it as a command button still?

Why is this so complicated? I thought it was a simple thing!!

Thanks again really appreciating it just wish it wud work!

Dean

Who told you this was simple...??
(hope it wasn't someone close to you...)
If programming were simple then everyone would be doing it and I'd be out of work...

The above code can go on the OnClick event of a button.  But in it's current form there are NO check and balances for errors and abort conditions, and no checks to be sure the that operation went smoothly and well.  Of these, which are needed for this routine.

and if programming is simple, then why is there EE???

Oh ok then so it aint a simple thing thats good!!!!

So the code above what do i need to add to it? I put it in as is and it didnt work so wat do i need to add?

Thanks again!

Erm? Am i not gettin ne more help? i still really need this solved? wat if i send one of you the file via msn or sumfing?

Help please Thanks

here's the code again, changed a little bit to incorporate jade's suggestions.  What data type is the field [Product_Code]?

Private Sub Command25_Click()
   
Dim x  ' a temp variable

x = InputBox("Enter a product code")

CurrentDB.Execute "UPDATE Stock SET [Stock_Remaining]=([Stock_Remaining] - 1) WHERE ([Product_Code]='" & x & "'"

End Sub

Product Code is a number field!

Put the above code in nearly there i think came up with this error :-

Runtime error '3075':

Missing ), ] or item in query expression '([Product_Code]='11111".

Thanks

error caused by missing paren....
remove the delimiters from around the variable
CurrentDB.Execute "UPDATE Stock SET [Stock_Remaining]=([Stock_Remaining] - 1) WHERE ([Product_Code]= " & x & ");"

note:
always have your code build an sql that uses all the "balancing" markers like quotes and parens and brackets where you can.
It  make the sql much easier to read in debug and reduces in-line errors and is good practice.
-j-

It says theres too few parameters now?

Thanks to you all

It says "Too few parameters expected 2"

something (2 things actually) is not spelled correctly.

Checked that all spelt correctly anything else it could be?

Cheers

??? spelt correctly ???

should I be trusting you on that one ??  :))

Ive got it too work Horay!!!!!!!!!!

Had to take the underscores out of the names product_code etc and just have spaces!!!!!!!!!!!!!!

Ok now when it has done that can i make it start another bit of code from the same click of that button? if so wat do i put? then begin or something?

Cheers guys for all the help!!!!!!!!!!!!!

Dean Pollard

Actually if your field names had underscores instead of spaces, that would be better design form.

LOL!

I just came back to this question to post that very same comment, jade.  :)

To DeanPollard2000:

Keeping spaces in field/form/table/query names is a good way to up your stress level.  It forces you to pay attention to all kinds of extra punctuation in both SQL and VB code.  A good practice to get into is to use names that are both descriptive of the use of the object and as brief and concise as possible.

thanks for both ur help do you want me to award the points and who too?

erm has that just been awarded 2 u?

Yup.  Don't worry...I'll do the split for you with a question for jade.

Jadedata:

https://www.experts-exchange.com/questions/20938780/Points-for-Jadedata.html

Ok that code still works fine but how can i make it produce a msgbox if a product code is not found?

Thanks guys