Solved

VBA

Posted on 2004-03-23
36
1,218 Views
Last Modified: 2012-05-04
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
Comment
Question by:deanpollard2000
  • 17
  • 11
  • 7
  • +1
36 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 10659594
Can you post the code in question, please?  This is a little vague.

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10660328
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
 

Author Comment

by:deanpollard2000
ID: 10660724
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10660859
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
 

Author Comment

by:deanpollard2000
ID: 10661045
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10661480
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
 

Author Comment

by:deanpollard2000
ID: 10666680
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10668519
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
 

Author Comment

by:deanpollard2000
ID: 10668862
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10673186
Where does it return that error?  Have you locked the table?  Do you have permissions on the table?

0
 

Author Comment

by:deanpollard2000
ID: 10676209
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10677949
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
 

Author Comment

by:deanpollard2000
ID: 10678641
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10678736
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10678890
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
 

Author Comment

by:deanpollard2000
ID: 10682889
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10686665
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10686668
and if programming is simple, then why is there EE???
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:deanpollard2000
ID: 10688316
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
 

Author Comment

by:deanpollard2000
ID: 10708786
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10708999
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
 

Author Comment

by:deanpollard2000
ID: 10713691
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10713828
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
 

Author Comment

by:deanpollard2000
ID: 10714012
It says theres too few parameters now?

Thanks to you all
0
 

Author Comment

by:deanpollard2000
ID: 10714019
It says "Too few parameters expected 2"
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10714304
something (2 things actually) is not spelled correctly.
0
 

Author Comment

by:deanpollard2000
ID: 10716656
Checked that all spelt correctly anything else it could be?

Cheers
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10716816
??? spelt correctly ???

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

Author Comment

by:deanpollard2000
ID: 10716949
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
 
LVL 32

Expert Comment

by:jadedata
ID: 10717093
Actually if your field names had underscores instead of spaces, that would be better design form.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10717234
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
 

Author Comment

by:deanpollard2000
ID: 10717562
thanks for both ur help do you want me to award the points and who too?
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 125 total points
ID: 10718104
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
 

Author Comment

by:deanpollard2000
ID: 10723855
erm has that just been awarded 2 u?
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10724522
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
 

Author Comment

by:deanpollard2000
ID: 10862865
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now