Copy date from a form to a different table

[Kevin_melb]

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

 

[Carl Rapson]

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

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

 

[Kevin_melb via AccessMonster.com]

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

 

[John W. Vinson]

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.

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]

 

[Kevin_melb via AccessMonster.com]

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 - 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]

 

[John W. Vinson]

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

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]

 

[Kevin_melb via AccessMonster.com]

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 - 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]

 

[John W. Vinson]

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]

 

[Kevin_melb via AccessMonster.com]

Thanks John, I will create a combo box and try that i really do appreciate
everyone's help on this

Kevin

 

[Kevin_melb via AccessMonster.com]

I got it working many thanks too everyone who hleped me

 

[John W. Vinson]

I got it working many thanks too everyone who hleped me

You're very welcome from all of me... <g>

John W. Vinson [MVP]