Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1234
  • Last Modified:

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
0
deanpollard2000
Asked:
deanpollard2000
  • 17
  • 11
  • 7
  • +1
1 Solution
 
walterecookCommented:
Can you post the code in question, please?  This is a little vague.

0
 
Steve BinkCommented:
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...
0
 
deanpollard2000Author Commented:
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Steve BinkCommented:
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
0
 
deanpollard2000Author Commented:
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
0
 
Steve BinkCommented:
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)
0
 
deanpollard2000Author Commented:
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!
0
 
Steve BinkCommented:
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.
0
 
deanpollard2000Author Commented:
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
0
 
Steve BinkCommented:
Where does it return that error?  Have you locked the table?  Do you have permissions on the table?

0
 
deanpollard2000Author Commented:
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
0
 
Steve BinkCommented:
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.
0
 
deanpollard2000Author Commented:
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!
0
 
Steve BinkCommented:
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.
0
 
jadedataMS Access Systems CreatorCommented:
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-
0
 
deanpollard2000Author Commented:
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
0
 
jadedataMS Access Systems CreatorCommented:
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.




0
 
jadedataMS Access Systems CreatorCommented:
and if programming is simple, then why is there EE???
0
 
deanpollard2000Author Commented:
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!
0
 
deanpollard2000Author Commented:
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
0
 
Steve BinkCommented:
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
0
 
deanpollard2000Author Commented:
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
0
 
jadedataMS Access Systems CreatorCommented:
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-
0
 
deanpollard2000Author Commented:
It says theres too few parameters now?

Thanks to you all
0
 
deanpollard2000Author Commented:
It says "Too few parameters expected 2"
0
 
jadedataMS Access Systems CreatorCommented:
something (2 things actually) is not spelled correctly.
0
 
deanpollard2000Author Commented:
Checked that all spelt correctly anything else it could be?

Cheers
0
 
jadedataMS Access Systems CreatorCommented:
??? spelt correctly ???

should I be trusting you on that one ??  :))
0
 
deanpollard2000Author Commented:
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
0
 
jadedataMS Access Systems CreatorCommented:
Actually if your field names had underscores instead of spaces, that would be better design form.
0
 
Steve BinkCommented:
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.
0
 
deanpollard2000Author Commented:
thanks for both ur help do you want me to award the points and who too?
0
 
Steve BinkCommented:
Feel free to split 'em how you want.  I think jade has as much credit as me for the solve.  Good luck with the rest of your project!
0
 
deanpollard2000Author Commented:
erm has that just been awarded 2 u?
0
 
Steve BinkCommented:
Yup.  Don't worry...I'll do the split for you with a question for jade.

Jadedata:

http://www.experts-exchange.com/Databases/MS_Access/Q_20938780.html
0
 
deanpollard2000Author Commented:
Ok that code still works fine but how can i make it produce a msgbox if a product code is not found?

Thanks guys
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 17
  • 11
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now