 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Copy date from a form to a different table
Good Morning to all. I have a database that i use. i have a table "stock" and a table "materials subform" The form i use to write the data to the subform extracts its data from the "stock" table. I do a simple cculation and then i need to write 1 field back to the "stock" table. The names used in the "stock" table are "partnumber" "quantityinstock" "costprice" and "sellprice". In the subform the fields are "partno" "tempqty" "buyprice" and "myprice" . What i want to do is in the VBcode in after update i want to write the field "tempqty" back to the "quantityinstock" but but in the right "partnumber" I knowi am probalble doing it wrong, buti am to far down track now and this will fix me up "I HOPE ANYWAY" Many Thanks Kevin > Good Morning to all. > I have a database that i use. i have a table "stock" and a table [quoted text clipped - 18 lines] > Many Thanks > Kevin In your AfterUpdate code, put something like this: DoCmd.RunSQL "UPDATE [stock] SET [quantityinstock]=" & Me.tempqty & " WHERE [partnumber]=" & Me.partno If partno is a text field, you'll need to put quotes around the value: DoCmd.RunSQL "UPDATE [stock] SET [quantityinstock]=" & Me.tempqty & " WHERE [partnumber]='" & Me.partno & "'" Carl Rapson Hi Carl; I am getting closer, i can get the code to write back the updated quantity in stock, but i get an error when i do the code to tie it to the stock number. Here is the code i wrote DoCmd.RunSQL "UPDATE [stock] SET [quantityinstock]=" & Me.tempqty & "where [stocknumber]=" & Me.PartNo And i get he follwoing error Run_time erroe'3075' syntax error (missing operator) in query expression '-10where [stocknumber] =04721-001'. 04721-001 is the stco number i was entering as a trial. I really appreciate your ehlp i am picking up what i need to do to fix my problems. lol after this one i do have a nother problem, but i will address that later >> Good Morning to all. >> I have a database that i use. i have a table "stock" and a table [quoted text clipped - 13 lines] > >Carl Rapson >Hi Carl; >I am getting closer, i can get the code to write back the updated quantity in [quoted text clipped - 10 lines] > >04721-001 is the stco number i was entering as a trial. You're missing a blank. If tempqty is -10, your concatenation will produce UPDATE [stock] SET [quantityinstock]=-10where [stocknumber] = Just put a blank between the " and the word where: DoCmd.RunSQL "UPDATE [stock] SET [quantityinstock]=" & Me.tempqty & " where [stocknumber]=" & Me.PartNo John W. Vinson [MVP] The code is still not working, it updates the quantity if i leave the "stocknumber part of the code, it updates every "quantityinstock" which is cool, but when i add the extra part of the code is says that "You are about to updtae (0) lines, I have pasted the code i am really lost now. Me.tempqty = Me.Quantityinstock - Me.QTY DoCmd.RunSQL "UPDATE [stock] SET [quantityinstock]=" & Me.tempqty & " where [stocknumber]= "" & Me.PartNo &""" Me.tempqty = 0 End Sub >>Hi Carl; >>I am getting closer, i can get the code to write back the updated quantity in [quoted text clipped - 12 lines] > > John W. Vinson [MVP] >The code is still not working, it updates the quantity if i leave the >"stocknumber part of the code, it updates every "quantityinstock" which is [quoted text clipped - 6 lines] > Me.tempqty = 0 >End Sub What's in the form control named PartNo? Is it in fact the part number that you want to update? It would appear that the Stock table does not contain any records for the value of PartNo that is being passed in this routine. Another possibility: what's the datatype of Partno? Is it - <yuck> - a Lookup field in your table definition? That could explain the problem! John W. Vinson [MVP] Yes it is a lookup field, is there a way around this or do i create a query table to look the part number up >>The code is still not working, it updates the quantity if i leave the >>"stocknumber part of the code, it updates every "quantityinstock" which is [quoted text clipped - 10 lines] > > John W. Vinson [MVP] >Yes it is a lookup field, is there a way around this or do i create a query >table to look the part number up The problem with Lookup fields is that they do not contain what they appear to contain. The field LOOKS like it contains a text PartNo, but it actually contains (I'd guess, I can't see your database) a numeric PartNoID, linked to the autonumber primary key of the Lookup Table created by the lookup wizard. What you can do is to put a Combo Box based on the lookup table on your form, with this numeric ID as its bound column, and use that combo as the criterion. John W. Vinson [MVP] Thanks John, I will create a combo box and try that i really do appreciate everyone's help on this Kevin >>Yes it is a lookup field, is there a way around this or do i create a query >>table to look the part number up [quoted text clipped - 8 lines] > > John W. Vinson [MVP] I got it working many thanks too everyone who hleped me >>Yes it is a lookup field, is there a way around this or do i create a query >>table to look the part number up [quoted text clipped - 8 lines] > > John W. Vinson [MVP] >I got it working many thanks too everyone who hleped me You're very welcome from all of me... <g> John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.