Insert new value to a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EMILYTAN - 29 May 2007 07:57 GMT

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

Reply to this Message

Andy Hull - 29 May 2007 11:29 GMT

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

Reply to this Message

EMILYTAN - 31 May 2007 08:14 GMT

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
>
[quoted text clipped - 23 lines]
>>
>> Note : If you need a database, do tell me....

Reply to this Message

EMILYTAN - 01 Jun 2007 01:28 GMT

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

>Hi
>
[quoted text clipped - 23 lines]
>>
>> Note : If you need a database, do tell me....

Reply to this Message