Avatar of deanpollard2000
deanpollard2000 asked on

VBA

Ok ihave written some code in VBA and it works fine but it only applies to the record im looking at in my form, how can i make it so it changes all the records in the form if neccessary?

Help needed asap

Thanks

Dean Pollard
Microsoft Access

Avatar of undefined
Last Comment
deanpollard2000

8/22/2022 - Mon
walterecook

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

Steve Bink

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...
ASKER
deanpollard2000

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steve Bink

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
ASKER
deanpollard2000

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
Steve Bink

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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
deanpollard2000

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!
Steve Bink

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.
ASKER
deanpollard2000

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Steve Bink

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

ASKER
deanpollard2000

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
Steve Bink

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
deanpollard2000

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!
Steve Bink

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.
jadedata

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-
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
deanpollard2000

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
jadedata

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.




jadedata

and if programming is simple, then why is there EE???
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
deanpollard2000

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!
ASKER
deanpollard2000

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
Steve Bink

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
deanpollard2000

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
jadedata

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-
ASKER
deanpollard2000

It says theres too few parameters now?

Thanks to you all
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
deanpollard2000

It says "Too few parameters expected 2"
jadedata

something (2 things actually) is not spelled correctly.
ASKER
deanpollard2000

Checked that all spelt correctly anything else it could be?

Cheers
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jadedata

??? spelt correctly ???

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

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
jadedata

Actually if your field names had underscores instead of spaces, that would be better design form.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Bink

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.
ASKER
deanpollard2000

thanks for both ur help do you want me to award the points and who too?
ASKER CERTIFIED SOLUTION
Steve Bink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
deanpollard2000

erm has that just been awarded 2 u?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Steve Bink

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

Jadedata:

https://www.experts-exchange.com/Databases/MS_Access/Q_20938780.html
ASKER
deanpollard2000

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

Thanks guys