Using SQL UPDATE with a concantated string containing numbers & letters

jcrozier

New member
Local time
Today, 11:19
Joined
Jun 19, 2010
Messages
4
I'm implementing a stock transfer system into the dbase for a new branch interstate. I need to assign new jobnumbers to the items, and in order to avoid duplicates I've decided to use a prefix (In this case ADL for adeliade, and TX for transfer).
I've written it all up, and everything functions, but as soon as I insert the letters before the job number, it dies midstride! I've spent 4 hours trying to make this work now, I really need some help!

Regarding the code below, yes, it's messy, there are no-longer accurate comments in it, etc etc. I tend to tidy up/optimise afterwards, forgive me.

Code:
Private Sub TransferBTN_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'our temp record set for getting the transfer status
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
Dim JobNoStr As String 'Holds the new job number
Dim dbs As Database
Set dbs = CurrentProject.Application.CurrentDb
If IsNull(Me.ListTransfer.Column(1)) Then
   MsgBox "Cannot transfer nothing. Add some items to the list first."
   GoTo Exit_TransferBTN_Click
End If
'get all data from ADL stock on hand that has been put in the list
rst.Open "(SELECT * from tblstockonhand where PackNoId = " & Me.ListTransfer.Column(1) & ";)"
rst![Transfer] = "Y" 'Give it transfer status
rst.Update
rst.Close
'need to add checks to avoid job number dupes
If txtJobNo.Value = " " Or txtJobNo.Value = "" Then
JobNoStr = Trim(InputBox("Enter the Job Number you wish to assign to this item.", "Job Number Request"))
Else
JobNoStr = "ADLTX" & Trim(txtJobNo.Value)
'JobNoStr = Trim(txtJobNo.Value)
End If
'Add the item to Transfer Stock
dbs.Execute ("INSERT INTO tblstockontransfer SELECT ParentPackId,ByWhom,LastModified,Transfer,CreditRecord,InvNo,UnitSellPrice,FISDelivery,HowDelivered,FreightInvoiced,Freight,FreightCharged,FreightCost,SalesPerson,PurchaseOrdNo,DateSold,Location,AttIntComments,IntCategory,IntComments,DateIntendedFor,IntendedFor,Company,Packaging,ProcessingCost,ProcessingFreight,ProcessingTime,DateProcessingCompleted,ProcessingOrdNo,DateProcessingOrdered,Machine,ProcessedBy,Quantity,Cost,ArticleNo,AttComments1,AttComments2,ExtComments,ID,Comments,OD,Length,Finish,Coating,Grade,ProductCode,Category,SubType,Type,ManufacturingStandard,Status,Origin,UltimateParent,DateOrdered,DatePromised,PromisedBy,DateReceived,OpportunityBuy,Thickness,Width FROM tblstockonhand WHERE PackNoId = " & Me.ListTransfer.Column(1) & ";")
MsgBox JobNoStr
dbs.Execute ("UPDATE tblstockontransfer SET [JobNo] = " & JobNoStr & " WHERE [Transfer] = 'Y';") ' assign new job number
MsgBox "part2"
rst.Open "(SELECT * from tblstockontransfer where Transfer = 'Y';)" 'grab all items being transfered
MsgBox "part3"
rst![Transfer] = "N" 'and close their transfer status
rst.Update
rst.Close
MsgBox "part4"
dbs.Execute ("UPDATE tblstockonhand SET [InvNo] = " & JobNoStr & " WHERE [PackNoId] = " & Me.ListTransfer.Column(1) & ";")
rst.Open "(SELECT PackNoId,Weight  from tbl_tmp_PacksSold where PackNoId = " & Me.ListTransfer.Column(1) & ";)"
 ' delete record
   rst.Delete
rst.Close
MsgBox "part5"
rst.Open "tblTransferJobs", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rst.AddNew
MsgBox "part6"
rst![JobNo] = JobNoStr
MsgBox "part7"
rst.Update
rst.Close
MsgBox "part8"
Set rst = Nothing
Me.Refresh
DoCmd.OpenReport "rptPickSlip", acViewDesign
Reports("rptPickSlip").RecordSource = "(SELECT * FROM tblstockontransfer WHERE JobNo = " & JobNoStr & ";)"
DoCmd.Save
DoCmd.OpenReport "rptPickSlip", acPreview
MsgBox "Items are in the Transfer area."
Form_Load
Exit_TransferBTN_Click:
     Exit Sub
End Sub
 
I should add, importantly, that it halts on this line:
dbs.Execute ("UPDATE tblstockontransfer SET [JobNo] = " & JobNoStr & " WHERE [Transfer] = 'Y';") ' assign new job number
 
It sounds like the JobNo field is text, in which case the code needs to be:

dbs.Execute ("UPDATE tblstockontransfer SET [JobNo] = '" & JobNoStr & "' WHERE [Transfer] = 'Y';") ' assign new job number
 
It sounds like the JobNo field is text, in which case the code needs to be:

dbs.Execute ("UPDATE tblstockontransfer SET [JobNo] = '" & JobNoStr & "' WHERE [Transfer] = 'Y';") ' assign new job number

Yes! That was it! Thankyou very much for that mate, those two button strokes cost me 4-5 hours!
 
Happy to help, and welcome to the site by the way!
 
Thanks :)

EDIT: Nevermind! I was missing '-1' on the end of each FOR. Ahwell, it might help the next bloke!

While I'm at it, this part is giving more stoppages, and it looks fine to me :S Maybe it's grumpy about the paranthesis in the SQL statement?


Code:
For lngI = 1 To Me.ListTransfer.ListCount
'get all data from ADL stock on hand that has been put in the list
rst.Open "(SELECT * from tblstockonhand where PackNoId = " & Me.ListTransfer.Column(1, lngI) & ";)"
rst![Transfer] = "Y" 'Give it transfer status
rst.Update
rst.Close
Next lngI
lngI is just a; Dim lngI as Long
 

Users who are viewing this thread

Back
Top Bottom