Insert new value to a table

[EMILYTAN via AccessMonster.com]

Hi All,
Imagine...you have the database with the following form:-
I want to code a Get Item button to get the item from other job where the
quantityKitted is more than 0 and user should key in what job they want "to"
get the item from and to what job.

After getting all the item, the item should be inserted to the same table
where it get from with the new job number stated where it is "to".


For example now, I want to get item in jobnumber AG1234567 (from) where
quantitykitted is >0 to insert the item to same table but set the jobnumber
to (AG222222) BUT THE INITIAL record still there


In short I want to insert item where qtykitted > 0 and as selected jobnumber..
..

any idea ?

Note : If you need a database, do tell me....

 

[Guest]

Hi

Try the following in the on click event for your button...

Dim SQLString as String

SQLString = "insert into tblJobs (JobNumber, OtherField1, OtherField2) " & _
"select '" & Me.JobNumberTo & "', " & _
"OtherField1, OtherField2 from tblJobs " & _
"where QtyKitted > 0 and JobNumber = '" & Me.JobNumberFrom & "'"
DoCmd.RunSQL

Obviously, use your own table name and field names. I didn't know what other
fields you were copying from 1 job to another (if any) so have written the
above to use 2 more as an example. I have also assumed your form has 2
textboxes named JobNumberFrom and JobNumberTo for the user to enter the job
numbers.

Regards

Andy Hull

 

[EMILYTAN via AccessMonster.com]

Hi Andy,
But I think this line got a bit error (or perhaps I figure wrongly)

"select"" & Me.txtJobNumberNew&"", "&_

because I want to get the value from the "Me.txtJobNumberNew" to be inserted
to the JobNumber field
Code:
------------------------------------------------------------------------------
--



Private Sub cmdInsertItem_Click()
Dim SQLString As String

SQLString = "insert into WIPRawDetails (JobNumber, PartNumber) " & _
"select '" & Me.txtJobNumberNew & "', " & _
"PartNumber from WIPRawDetails " & _
"where W_KittedQty > 0 and JobNumber = '" & Me.txtJobNumberOld & "'"
'DoCmd.RunSQL
End Sub


------------------------------------------------------------------------------
--



Well, if it is correct, how come it didnt show me any response, not even
error message...

Hi

Try the following in the on click event for your button...

Dim SQLString as String

SQLString = "insert into tblJobs (JobNumber, OtherField1, OtherField2) " & _
"select '" & Me.JobNumberTo & "', " & _
"OtherField1, OtherField2 from tblJobs " & _
"where QtyKitted > 0 and JobNumber = '" & Me.JobNumberFrom & "'"
DoCmd.RunSQL

Obviously, use your own table name and field names. I didn't know what other
fields you were copying from 1 job to another (if any) so have written the
above to use 2 more as an example. I have also assumed your form has 2
textboxes named JobNumberFrom and JobNumberTo for the user to enter the job
numbers.

Regards

Andy Hull

Hi All,
Imagine...you have the database with the following form:-

[quoted text clipped - 15 lines]

Note : If you need a database, do tell me....

 

[EMILYTAN via AccessMonster.com]

Hi Andy,
I can figure it out already...Thanks a lot

Hi

Try the following in the on click event for your button...

Dim SQLString as String

SQLString = "insert into tblJobs (JobNumber, OtherField1, OtherField2) " & _
"select '" & Me.JobNumberTo & "', " & _
"OtherField1, OtherField2 from tblJobs " & _
"where QtyKitted > 0 and JobNumber = '" & Me.JobNumberFrom & "'"
DoCmd.RunSQL

Obviously, use your own table name and field names. I didn't know what other
fields you were copying from 1 job to another (if any) so have written the
above to use 2 more as an example. I have also assumed your form has 2
textboxes named JobNumberFrom and JobNumberTo for the user to enter the job
numbers.

Regards

Andy Hull

Hi All,
Imagine...you have the database with the following form:-

[quoted text clipped - 15 lines]

Note : If you need a database, do tell me....