sql update problem

  • Thread starter Thread starter rzaxl
  • Start date Start date
R

rzaxl

I'm using a variable of adodb.connection type to first insert a recoord, and
then update the records aditional fields using the sql update, inserting a
record is know problem, updating the additional fields is proving to be a
problem. when ever I run the program with breakpoints, the sql update
function updates the record know problem; but as i try to execute program
without know breakpoint; it executes succesfully, but it does not updste the
record. It's as if the program was to fast to stop & update the data
 
Private Sub Insert_Rail_Ancillaries(lRef As Long)
'Insert Rail Ancillaries into the Rail Ancillaries Database
' LSA/

Dim sValue As String, lCatid As Long, sMaint As String
Dim iQty As Integer, iProfile As Integer, dLength As Double
Dim rsRail As New ADODB.Recordset

'SetupDB ("V:\NE\YK04GROUPS\LSA\Database\Ancillaries\Ancillary_be.mdb")
SetupDB ("C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb")
'SetupDB ("h:\data\access\Ancillary_be.mdb")

lRef = lInsertNewRecord_Ancillary
sNumbers = lRef



'Cell D2 Work Type
sValue = sBookValue(sSheetName, "Sheet1", "D", "2")
If sValue = "Track Renewals" Then sValue = "RENEWALS"
sMaint = sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"MTCE?", True)
'Cell G2 Area/IMT
sValue = sBookValue(sSheetName, "Sheet1", "G", "2")
If sMaint = "RENEWALS" Then sValue = sValue & " IMT"
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"AREA", True)
'Cell D3 Order Originator
sValue = sBookValue(sSheetName, "Sheet1", "D", "3")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"ORIGINATOR", True)
'Cell I3 Your Ref
sValue = sBookValue(sSheetName, "Sheet1", "J", "3")
sValue = sValue & " : " & sBookValue(sSheetName, "Sheet1", "D", "24")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"JOB REF", True)
'Cell H4 = Depot/Contractor
sValue = sBookValue(sSheetName, "Sheet1", "H", "4")
If sMaint = "Maintenance" Then sValue = "NR " & sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"Contractor", True)
'Cell D5 Email
sValue = sBookValue(sSheetName, "Sheet1", "D", "5")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"email", True)
'Cell D7 Order Date
sValue = Now()
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"order date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"requisition received date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"requisition to supplier date")
'Cell D23 Worksite Name
sValue = sBookValue(sSheetName, "Sheet1", "D", "23")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"SITE", True)
'Cell D31 Delivery Date
sValue = sBookValue(sSheetName, "Sheet1", "D", "31")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"Date Required")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"Supplier promised date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book Data",
"Origsupplierpromiseddate")
'Cell D45 Cost Centre
sValue = sBookValue(sSheetName, "Sheet1", "D", "45")
Call UpdateRecord(lRef, "Record ID", sValue, "n", "Master Order Book Data",
"costcentre")
'Cell H46 Project Code
sValue = sBookValue(sSheetName, "Sheet1", "H", "46")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book Data",
"PMCS", True)
'Cell D10 Description Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "10")
lCatid = lFindCatalogue(sValue)
'Cell D11 Quantity
iQty = sBookValue(sSheetName, "Sheet1", "D", "11")
'Cell D12 Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "12")
iProfile = lFindCatalogue(sValue)
'Cell D14 Length
dLength = sBookValue(sSheetName, "Sheet1", "D", "14")
'Cell D15 Drilling
sValue = sBookValue(sSheetName, "Sheet1", "D", "15")

sSql = "Insert into tblAncillary
(recid,ancillary,profile,length,drilling,quantity) values (" & lRef
sSql = sSql & "," & lCatid & "," & iProfile & "," & dLength & ",'" & sValue
& "'," & iQty
sSql = sSql & ")"
adoDiane.Execute sSql

'Cell B19 Special Requirements
sValue = sBookValue(sSheetName, "Sheet1", "B", "19")
sMaint = GetWorkstationInfo

sSql = "Insert into tblMultiComments ([Record
id],comments,commentdate,txtnetworkusername) values ("
sSql = sSql & lRef & ",'" & sValue & "',#" & Format(Now(), "dd mmm yyyy") &
"#,'" & sMaint & "')"
If sValue > "" Then adoDiane.Execute sSql 'Only run if there is a comment

frm_ancillaries.lRefNumber = lRef
frm_ancillaries.Show

sSql = "Select * from [Master Order Book Data] where [record id] = " & lRef
rsRail.Open sSql, adoDiane, adOpenForwardOnly, adLockReadOnly

sNumbers = rsRail("Order No Pre") & rsRail("Order No Suffix")

rsRail.Close
Set rsRail = Nothing

If sNumbers <> "" Then
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (1)
delivaryflag = 1
End If

insertRailAncillaries_DelivaryDetails sNumbers

End Sub

Sub insertRailAncillaries_DelivaryDetails(ByVal get_sNumbers As String)
Dim delivarySQL, sFile As String
Dim getCellData, getCellDataX As Variant
Dim adoDelivaryDetails As New ADODB.Connection

sFile = "C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb"
adoDelivaryDetails.Provider = "Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" & sFile
adoDelivaryDetails.Open

MsgBox "DELIVARY FLAG CHECK = " & delivaryflag
If delivaryflag = 1 Then

delivarySQL = "insert into tblAncillaryDelivary(ndsref) values('" &
get_sNumbers & "')"
adoDiane.Execute delivarySQL

'get delivary contact cell d27
getCellData = sBookValue(sSheetName, "Sheet1", "d", "27")
Call UpdateAncillaryDelivaryRec("dContact", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ext phone cell d28
getCellData = sBookValue(sSheetName, "Sheet1", "d", "28")
Call UpdateAncillaryDelivaryRec("extTel", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary email cell d29
getCellData = sBookValue(sSheetName, "Sheet1", "d", "29")
Call UpdateAncillaryDelivaryRec("emailContact",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary date cell d31
getCellData = sBookValue(sSheetName, "Sheet1", "d", "31")
Call UpdateAncillaryDelivaryRec("delivaryDate",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ldc cell d34
getCellData = sBookValue(sSheetName, "Sheet1", "d", "34")
Call UpdateAncillaryDelivaryRec("location_LDCName",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary street/road cell d35
getCellData = sBookValue(sSheetName, "Sheet1", "d", "35")
Call UpdateAncillaryDelivaryRec("streetRoad",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary town/city d36
getCellData = sBookValue(sSheetName, "Sheet1", "d", "36")
getCellDataX = sBookValue(sSheetName, "Sheet1", "d", "37")
getCellData = getCellData & " " & getCellDataX
Call UpdateAncillaryDelivaryRec("Town", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary county dc37
'getCellData = sBookValue(sSheetName, "Sheet1", "d", "37")
'Call UpdateAncillaryDelivaryRec("county", "tblAncillaryDelivary",
getCellData, get_sNumbers)

'get delivary postcode d38
getCellData = sBookValue(sSheetName, "Sheet1", "d", "38")
Call UpdateAncillaryDelivaryRec("PostCode", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary HIAB g38
getCellData = sBookValue(sSheetName, "Sheet1", "g", "38")
Call UpdateAncillaryDelivaryRec("HIAB", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""

MsgBox "delivaryflag has been set to 1 (true)" & delivaryflag
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (0)
delivaryflag = 0
Else
MsgBox "delivaryflag has been set to 0 (false)" & delivaryflag
End If

adoDelivaryDetails.Close
Set adoDelivaryDetails = Nothing
End Sub


Sub UpdateAncillaryDelivaryRec(sField As String, sTable As String, sFieldVal
As Variant, getNdsRef As Variant, myAdo As Object)

Dim sSql, sFile As Variant



If IsDate(sFieldVal) Then
'Update record with correct data value
sSql = "Update " & sTable & " Set " & sField & " = " & sFieldVal & "
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

Else
sSql = "Update " & sTable & " Set " & sField & " = '" & sFieldVal & "'
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

End If

myAdo.Execute sSql


End Sub

Don't worry about the SBookValue, all it those is extract data from the
worksheet,
as i said the straing thing is if i place a break point on the MsgBox
"DELIVARY FLAG CHECK = " & delivaryflag and iterate through the code line by
line, it updates the databse perfectly; but as soon as i take off the
breakpoint and execute the code the only thing it executes is the adoDiane
recordset. Its almost as if it ignores it, could you help me pleeeease
 
Didn't look deeply into it, but just a few thing for starters:
Dim sSql, sFile As Variant
Click to expand...
Probably it doesn't matter, but to declare all variables properly:
Dim sSql As String
Dim sFile As String
"Select * from [Master Order Book Data] where [record id] = " & lRef
Click to expand...
Again, probably nothing wrong with it, but better to use parameter queries.

As to solve the problem, for starters put a DoEvents where you have your
message boxes now and see if that fixes it.

RBS


rzaxl said:
Private Sub Insert_Rail_Ancillaries(lRef As Long)
'Insert Rail Ancillaries into the Rail Ancillaries Database
' LSA/

Dim sValue As String, lCatid As Long, sMaint As String
Dim iQty As Integer, iProfile As Integer, dLength As Double
Dim rsRail As New ADODB.Recordset

'SetupDB ("V:\NE\YK04GROUPS\LSA\Database\Ancillaries\Ancillary_be.mdb")
SetupDB ("C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb")
'SetupDB ("h:\data\access\Ancillary_be.mdb")

lRef = lInsertNewRecord_Ancillary
sNumbers = lRef



'Cell D2 Work Type
sValue = sBookValue(sSheetName, "Sheet1", "D", "2")
If sValue = "Track Renewals" Then sValue = "RENEWALS"
sMaint = sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"MTCE?", True)
'Cell G2 Area/IMT
sValue = sBookValue(sSheetName, "Sheet1", "G", "2")
If sMaint = "RENEWALS" Then sValue = sValue & " IMT"
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"AREA", True)
'Cell D3 Order Originator
sValue = sBookValue(sSheetName, "Sheet1", "D", "3")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"ORIGINATOR", True)
'Cell I3 Your Ref
sValue = sBookValue(sSheetName, "Sheet1", "J", "3")
sValue = sValue & " : " & sBookValue(sSheetName, "Sheet1", "D", "24")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"JOB REF", True)
'Cell H4 = Depot/Contractor
sValue = sBookValue(sSheetName, "Sheet1", "H", "4")
If sMaint = "Maintenance" Then sValue = "NR " & sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"Contractor", True)
'Cell D5 Email
sValue = sBookValue(sSheetName, "Sheet1", "D", "5")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"email", True)
'Cell D7 Order Date
sValue = Now()
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"order date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition received date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition to supplier date")
'Cell D23 Worksite Name
sValue = sBookValue(sSheetName, "Sheet1", "D", "23")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"SITE", True)
'Cell D31 Delivery Date
sValue = sBookValue(sSheetName, "Sheet1", "D", "31")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Date Required")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Supplier promised date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Origsupplierpromiseddate")
'Cell D45 Cost Centre
sValue = sBookValue(sSheetName, "Sheet1", "D", "45")
Call UpdateRecord(lRef, "Record ID", sValue, "n", "Master Order Book
Data",
"costcentre")
'Cell H46 Project Code
sValue = sBookValue(sSheetName, "Sheet1", "H", "46")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"PMCS", True)
'Cell D10 Description Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "10")
lCatid = lFindCatalogue(sValue)
'Cell D11 Quantity
iQty = sBookValue(sSheetName, "Sheet1", "D", "11")
'Cell D12 Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "12")
iProfile = lFindCatalogue(sValue)
'Cell D14 Length
dLength = sBookValue(sSheetName, "Sheet1", "D", "14")
'Cell D15 Drilling
sValue = sBookValue(sSheetName, "Sheet1", "D", "15")

sSql = "Insert into tblAncillary
(recid,ancillary,profile,length,drilling,quantity) values (" & lRef
sSql = sSql & "," & lCatid & "," & iProfile & "," & dLength & ",'" &
sValue
& "'," & iQty
sSql = sSql & ")"
adoDiane.Execute sSql

'Cell B19 Special Requirements
sValue = sBookValue(sSheetName, "Sheet1", "B", "19")
sMaint = GetWorkstationInfo

sSql = "Insert into tblMultiComments ([Record
id],comments,commentdate,txtnetworkusername) values ("
sSql = sSql & lRef & ",'" & sValue & "',#" & Format(Now(), "dd mmm yyyy")
&
"#,'" & sMaint & "')"
If sValue > "" Then adoDiane.Execute sSql 'Only run if there is a comment

frm_ancillaries.lRefNumber = lRef
frm_ancillaries.Show

sSql = "Select * from [Master Order Book Data] where [record id] = " &
lRef
rsRail.Open sSql, adoDiane, adOpenForwardOnly, adLockReadOnly

sNumbers = rsRail("Order No Pre") & rsRail("Order No Suffix")

rsRail.Close
Set rsRail = Nothing

If sNumbers <> "" Then
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (1)
delivaryflag = 1
End If

insertRailAncillaries_DelivaryDetails sNumbers

End Sub

Sub insertRailAncillaries_DelivaryDetails(ByVal get_sNumbers As String)
Dim delivarySQL, sFile As String
Dim getCellData, getCellDataX As Variant
Dim adoDelivaryDetails As New ADODB.Connection

sFile = "C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb"
adoDelivaryDetails.Provider = "Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" & sFile
adoDelivaryDetails.Open

MsgBox "DELIVARY FLAG CHECK = " & delivaryflag
If delivaryflag = 1 Then

delivarySQL = "insert into tblAncillaryDelivary(ndsref) values('" &
get_sNumbers & "')"
adoDiane.Execute delivarySQL

'get delivary contact cell d27
getCellData = sBookValue(sSheetName, "Sheet1", "d", "27")
Call UpdateAncillaryDelivaryRec("dContact", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ext phone cell d28
getCellData = sBookValue(sSheetName, "Sheet1", "d", "28")
Call UpdateAncillaryDelivaryRec("extTel", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary email cell d29
getCellData = sBookValue(sSheetName, "Sheet1", "d", "29")
Call UpdateAncillaryDelivaryRec("emailContact",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary date cell d31
getCellData = sBookValue(sSheetName, "Sheet1", "d", "31")
Call UpdateAncillaryDelivaryRec("delivaryDate",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ldc cell d34
getCellData = sBookValue(sSheetName, "Sheet1", "d", "34")
Call UpdateAncillaryDelivaryRec("location_LDCName",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary street/road cell d35
getCellData = sBookValue(sSheetName, "Sheet1", "d", "35")
Call UpdateAncillaryDelivaryRec("streetRoad",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary town/city d36
getCellData = sBookValue(sSheetName, "Sheet1", "d", "36")
getCellDataX = sBookValue(sSheetName, "Sheet1", "d", "37")
getCellData = getCellData & " " & getCellDataX
Call UpdateAncillaryDelivaryRec("Town", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary county dc37
'getCellData = sBookValue(sSheetName, "Sheet1", "d", "37")
'Call UpdateAncillaryDelivaryRec("county", "tblAncillaryDelivary",
getCellData, get_sNumbers)

'get delivary postcode d38
getCellData = sBookValue(sSheetName, "Sheet1", "d", "38")
Call UpdateAncillaryDelivaryRec("PostCode", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary HIAB g38
getCellData = sBookValue(sSheetName, "Sheet1", "g", "38")
Call UpdateAncillaryDelivaryRec("HIAB", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""

MsgBox "delivaryflag has been set to 1 (true)" & delivaryflag
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (0)
delivaryflag = 0
Else
MsgBox "delivaryflag has been set to 0 (false)" & delivaryflag
End If

adoDelivaryDetails.Close
Set adoDelivaryDetails = Nothing
End Sub


Sub UpdateAncillaryDelivaryRec(sField As String, sTable As String,
sFieldVal
As Variant, getNdsRef As Variant, myAdo As Object)

Dim sSql, sFile As Variant



If IsDate(sFieldVal) Then
'Update record with correct data value
sSql = "Update " & sTable & " Set " & sField & " = " & sFieldVal & "
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

Else
sSql = "Update " & sTable & " Set " & sField & " = '" & sFieldVal & "'
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

End If

myAdo.Execute sSql


End Sub

Don't worry about the SBookValue, all it those is extract data from the
worksheet,
as i said the straing thing is if i place a break point on the MsgBox
"DELIVARY FLAG CHECK = " & delivaryflag and iterate through the code line
by
line, it updates the databse perfectly; but as soon as i take off the
breakpoint and execute the code the only thing it executes is the adoDiane
recordset. Its almost as if it ignores it, could you help me pleeeease
--
RzaXL


RB Smissaert said:
You will need to show some code.

RBS




.
Click to expand...
Click to expand...
 
Unfortunatly it still does not work, like i say when i place a break point,
and iterate through the code by pressing F button line by line, it updates
the database, but as soon as i take the breakpoint off the only thing that is
executed is the the first sql insert statement, it completly ignores the
other update statement; by the way a quick question am i allowed to run more
then 1 recordset or do i have to close the first recordset before i open a
new one
--
RzaXL


RB Smissaert said:
Didn't look deeply into it, but just a few thing for starters:
Dim sSql, sFile As Variant
Click to expand...
Probably it doesn't matter, but to declare all variables properly:
Dim sSql As String
Dim sFile As String
"Select * from [Master Order Book Data] where [record id] = " & lRef
Click to expand...
Again, probably nothing wrong with it, but better to use parameter queries.

As to solve the problem, for starters put a DoEvents where you have your
message boxes now and see if that fixes it.

RBS


rzaxl said:
Private Sub Insert_Rail_Ancillaries(lRef As Long)
'Insert Rail Ancillaries into the Rail Ancillaries Database
' LSA/

Dim sValue As String, lCatid As Long, sMaint As String
Dim iQty As Integer, iProfile As Integer, dLength As Double
Dim rsRail As New ADODB.Recordset

'SetupDB ("V:\NE\YK04GROUPS\LSA\Database\Ancillaries\Ancillary_be.mdb")
SetupDB ("C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb")
'SetupDB ("h:\data\access\Ancillary_be.mdb")

lRef = lInsertNewRecord_Ancillary
sNumbers = lRef



'Cell D2 Work Type
sValue = sBookValue(sSheetName, "Sheet1", "D", "2")
If sValue = "Track Renewals" Then sValue = "RENEWALS"
sMaint = sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"MTCE?", True)
'Cell G2 Area/IMT
sValue = sBookValue(sSheetName, "Sheet1", "G", "2")
If sMaint = "RENEWALS" Then sValue = sValue & " IMT"
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"AREA", True)
'Cell D3 Order Originator
sValue = sBookValue(sSheetName, "Sheet1", "D", "3")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"ORIGINATOR", True)
'Cell I3 Your Ref
sValue = sBookValue(sSheetName, "Sheet1", "J", "3")
sValue = sValue & " : " & sBookValue(sSheetName, "Sheet1", "D", "24")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"JOB REF", True)
'Cell H4 = Depot/Contractor
sValue = sBookValue(sSheetName, "Sheet1", "H", "4")
If sMaint = "Maintenance" Then sValue = "NR " & sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"Contractor", True)
'Cell D5 Email
sValue = sBookValue(sSheetName, "Sheet1", "D", "5")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"email", True)
'Cell D7 Order Date
sValue = Now()
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"order date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition received date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition to supplier date")
'Cell D23 Worksite Name
sValue = sBookValue(sSheetName, "Sheet1", "D", "23")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"SITE", True)
'Cell D31 Delivery Date
sValue = sBookValue(sSheetName, "Sheet1", "D", "31")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Date Required")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Supplier promised date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Origsupplierpromiseddate")
'Cell D45 Cost Centre
sValue = sBookValue(sSheetName, "Sheet1", "D", "45")
Call UpdateRecord(lRef, "Record ID", sValue, "n", "Master Order Book
Data",
"costcentre")
'Cell H46 Project Code
sValue = sBookValue(sSheetName, "Sheet1", "H", "46")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"PMCS", True)
'Cell D10 Description Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "10")
lCatid = lFindCatalogue(sValue)
'Cell D11 Quantity
iQty = sBookValue(sSheetName, "Sheet1", "D", "11")
'Cell D12 Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "12")
iProfile = lFindCatalogue(sValue)
'Cell D14 Length
dLength = sBookValue(sSheetName, "Sheet1", "D", "14")
'Cell D15 Drilling
sValue = sBookValue(sSheetName, "Sheet1", "D", "15")

sSql = "Insert into tblAncillary
(recid,ancillary,profile,length,drilling,quantity) values (" & lRef
sSql = sSql & "," & lCatid & "," & iProfile & "," & dLength & ",'" &
sValue
& "'," & iQty
sSql = sSql & ")"
adoDiane.Execute sSql

'Cell B19 Special Requirements
sValue = sBookValue(sSheetName, "Sheet1", "B", "19")
sMaint = GetWorkstationInfo

sSql = "Insert into tblMultiComments ([Record
id],comments,commentdate,txtnetworkusername) values ("
sSql = sSql & lRef & ",'" & sValue & "',#" & Format(Now(), "dd mmm yyyy")
&
"#,'" & sMaint & "')"
If sValue > "" Then adoDiane.Execute sSql 'Only run if there is a comment

frm_ancillaries.lRefNumber = lRef
frm_ancillaries.Show

sSql = "Select * from [Master Order Book Data] where [record id] = " &
lRef
rsRail.Open sSql, adoDiane, adOpenForwardOnly, adLockReadOnly

sNumbers = rsRail("Order No Pre") & rsRail("Order No Suffix")

rsRail.Close
Set rsRail = Nothing

If sNumbers <> "" Then
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (1)
delivaryflag = 1
End If

insertRailAncillaries_DelivaryDetails sNumbers

End Sub

Sub insertRailAncillaries_DelivaryDetails(ByVal get_sNumbers As String)
Dim delivarySQL, sFile As String
Dim getCellData, getCellDataX As Variant
Dim adoDelivaryDetails As New ADODB.Connection

sFile = "C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb"
adoDelivaryDetails.Provider = "Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" & sFile
adoDelivaryDetails.Open

MsgBox "DELIVARY FLAG CHECK = " & delivaryflag
If delivaryflag = 1 Then

delivarySQL = "insert into tblAncillaryDelivary(ndsref) values('" &
get_sNumbers & "')"
adoDiane.Execute delivarySQL

'get delivary contact cell d27
getCellData = sBookValue(sSheetName, "Sheet1", "d", "27")
Call UpdateAncillaryDelivaryRec("dContact", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ext phone cell d28
getCellData = sBookValue(sSheetName, "Sheet1", "d", "28")
Call UpdateAncillaryDelivaryRec("extTel", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary email cell d29
getCellData = sBookValue(sSheetName, "Sheet1", "d", "29")
Call UpdateAncillaryDelivaryRec("emailContact",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary date cell d31
getCellData = sBookValue(sSheetName, "Sheet1", "d", "31")
Call UpdateAncillaryDelivaryRec("delivaryDate",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ldc cell d34
getCellData = sBookValue(sSheetName, "Sheet1", "d", "34")
Call UpdateAncillaryDelivaryRec("location_LDCName",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary street/road cell d35
getCellData = sBookValue(sSheetName, "Sheet1", "d", "35")
Call UpdateAncillaryDelivaryRec("streetRoad",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary town/city d36
getCellData = sBookValue(sSheetName, "Sheet1", "d", "36")
getCellDataX = sBookValue(sSheetName, "Sheet1", "d", "37")
getCellData = getCellData & " " & getCellDataX
Call UpdateAncillaryDelivaryRec("Town", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary county dc37
'getCellData = sBookValue(sSheetName, "Sheet1", "d", "37")
'Call UpdateAncillaryDelivaryRec("county", "tblAncillaryDelivary",
getCellData, get_sNumbers)

'get delivary postcode d38
getCellData = sBookValue(sSheetName, "Sheet1", "d", "38")
Call UpdateAncillaryDelivaryRec("PostCode", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary HIAB g38
getCellData = sBookValue(sSheetName, "Sheet1", "g", "38")
Call UpdateAncillaryDelivaryRec("HIAB", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""

MsgBox "delivaryflag has been set to 1 (true)" & delivaryflag
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (0)
delivaryflag = 0
Else
MsgBox "delivaryflag has been set to 0 (false)" & delivaryflag
End If

adoDelivaryDetails.Close
Set adoDelivaryDetails = Nothing
End Sub


Sub UpdateAncillaryDelivaryRec(sField As String, sTable As String,
sFieldVal
As Variant, getNdsRef As Variant, myAdo As Object)

Dim sSql, sFile As Variant



If IsDate(sFieldVal) Then
'Update record with correct data value
sSql = "Update " & sTable & " Set " & sField & " = " & sFieldVal & "
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

Else
sSql = "Update " & sTable & " Set " & sField & " = '" & sFieldVal & "'
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

End If

myAdo.Execute sSql


End Sub

Don't worry about the SBookValue, all it those is extract data from the
worksheet,
as i said the straing thing is if i place a break point on the MsgBox
"DELIVARY FLAG CHECK = " & delivaryflag and iterate through the code line
by
line, it updates the databse perfectly; but as soon as i take off the
breakpoint and execute the code the only thing it executes is the adoDiane
recordset. Its almost as if it ignores it, could you help me pleeeease
--
RzaXL


RB Smissaert said:
You will need to show some code.

RBS


I'm using a variable of adodb.connection type to first insert a
recoord,
and
then update the records aditional fields using the sql update,
inserting a
record is know problem, updating the additional fields is proving to be
a
Click to expand...
Click to expand...
Click to expand...
 
If you could zip the .xls and .mdb files and mail that to me I will have a
look.

RBS


rzaxl said:
Unfortunatly it still does not work, like i say when i place a break
point,
and iterate through the code by pressing F button line by line, it updates
the database, but as soon as i take the breakpoint off the only thing that
is
executed is the the first sql insert statement, it completly ignores the
other update statement; by the way a quick question am i allowed to run
more
then 1 recordset or do i have to close the first recordset before i open a
new one
--
RzaXL


RB Smissaert said:
Didn't look deeply into it, but just a few thing for starters:
Dim sSql, sFile As Variant
Click to expand...
Probably it doesn't matter, but to declare all variables properly:
Dim sSql As String
Dim sFile As String
"Select * from [Master Order Book Data] where [record id] = " & lRef
Click to expand...
Again, probably nothing wrong with it, but better to use parameter
queries.

As to solve the problem, for starters put a DoEvents where you have your
message boxes now and see if that fixes it.

RBS


rzaxl said:
Private Sub Insert_Rail_Ancillaries(lRef As Long)
'Insert Rail Ancillaries into the Rail Ancillaries Database
' LSA/

Dim sValue As String, lCatid As Long, sMaint As String
Dim iQty As Integer, iProfile As Integer, dLength As Double
Dim rsRail As New ADODB.Recordset

'SetupDB ("V:\NE\YK04GROUPS\LSA\Database\Ancillaries\Ancillary_be.mdb")
SetupDB ("C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb")
'SetupDB ("h:\data\access\Ancillary_be.mdb")

lRef = lInsertNewRecord_Ancillary
sNumbers = lRef



'Cell D2 Work Type
sValue = sBookValue(sSheetName, "Sheet1", "D", "2")
If sValue = "Track Renewals" Then sValue = "RENEWALS"
sMaint = sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"MTCE?", True)
'Cell G2 Area/IMT
sValue = sBookValue(sSheetName, "Sheet1", "G", "2")
If sMaint = "RENEWALS" Then sValue = sValue & " IMT"
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"AREA", True)
'Cell D3 Order Originator
sValue = sBookValue(sSheetName, "Sheet1", "D", "3")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"ORIGINATOR", True)
'Cell I3 Your Ref
sValue = sBookValue(sSheetName, "Sheet1", "J", "3")
sValue = sValue & " : " & sBookValue(sSheetName, "Sheet1", "D", "24")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"JOB REF", True)
'Cell H4 = Depot/Contractor
sValue = sBookValue(sSheetName, "Sheet1", "H", "4")
If sMaint = "Maintenance" Then sValue = "NR " & sValue
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"Contractor", True)
'Cell D5 Email
sValue = sBookValue(sSheetName, "Sheet1", "D", "5")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"email", True)
'Cell D7 Order Date
sValue = Now()
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"order date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition received date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"requisition to supplier date")
'Cell D23 Worksite Name
sValue = sBookValue(sSheetName, "Sheet1", "D", "23")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"SITE", True)
'Cell D31 Delivery Date
sValue = sBookValue(sSheetName, "Sheet1", "D", "31")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Date Required")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Supplier promised date")
Call UpdateRecord(lRef, "Record ID", sValue, "d", "Master Order Book
Data",
"Origsupplierpromiseddate")
'Cell D45 Cost Centre
sValue = sBookValue(sSheetName, "Sheet1", "D", "45")
Call UpdateRecord(lRef, "Record ID", sValue, "n", "Master Order Book
Data",
"costcentre")
'Cell H46 Project Code
sValue = sBookValue(sSheetName, "Sheet1", "H", "46")
Call UpdateRecord(lRef, "Record ID", sValue, "s", "Master Order Book
Data",
"PMCS", True)
'Cell D10 Description Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "10")
lCatid = lFindCatalogue(sValue)
'Cell D11 Quantity
iQty = sBookValue(sSheetName, "Sheet1", "D", "11")
'Cell D12 Profile
sValue = sBookValue(sSheetName, "Sheet1", "D", "12")
iProfile = lFindCatalogue(sValue)
'Cell D14 Length
dLength = sBookValue(sSheetName, "Sheet1", "D", "14")
'Cell D15 Drilling
sValue = sBookValue(sSheetName, "Sheet1", "D", "15")

sSql = "Insert into tblAncillary
(recid,ancillary,profile,length,drilling,quantity) values (" & lRef
sSql = sSql & "," & lCatid & "," & iProfile & "," & dLength & ",'" &
sValue
& "'," & iQty
sSql = sSql & ")"
adoDiane.Execute sSql

'Cell B19 Special Requirements
sValue = sBookValue(sSheetName, "Sheet1", "B", "19")
sMaint = GetWorkstationInfo

sSql = "Insert into tblMultiComments ([Record
id],comments,commentdate,txtnetworkusername) values ("
sSql = sSql & lRef & ",'" & sValue & "',#" & Format(Now(), "dd mmm
yyyy")
&
"#,'" & sMaint & "')"
If sValue > "" Then adoDiane.Execute sSql 'Only run if there is a
comment

frm_ancillaries.lRefNumber = lRef
frm_ancillaries.Show

sSql = "Select * from [Master Order Book Data] where [record id] = " &
lRef
rsRail.Open sSql, adoDiane, adOpenForwardOnly, adLockReadOnly

sNumbers = rsRail("Order No Pre") & rsRail("Order No Suffix")

rsRail.Close
Set rsRail = Nothing

If sNumbers <> "" Then
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (1)
delivaryflag = 1
End If

insertRailAncillaries_DelivaryDetails sNumbers

End Sub

Sub insertRailAncillaries_DelivaryDetails(ByVal get_sNumbers As String)
Dim delivarySQL, sFile As String
Dim getCellData, getCellDataX As Variant
Dim adoDelivaryDetails As New ADODB.Connection

sFile = "C:\Documents and Settings\Msalami\My
Documents\tracybrowntest\ancillaryDevelopmentDB.mdb"
adoDelivaryDetails.Provider = "Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data
Source=" & sFile
adoDelivaryDetails.Open

MsgBox "DELIVARY FLAG CHECK = " & delivaryflag
If delivaryflag = 1 Then

delivarySQL = "insert into tblAncillaryDelivary(ndsref)
values('" &
get_sNumbers & "')"
adoDiane.Execute delivarySQL

'get delivary contact cell d27
getCellData = sBookValue(sSheetName, "Sheet1", "d", "27")
Call UpdateAncillaryDelivaryRec("dContact",
"tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ext phone cell d28
getCellData = sBookValue(sSheetName, "Sheet1", "d", "28")
Call UpdateAncillaryDelivaryRec("extTel",
"tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary email cell d29
getCellData = sBookValue(sSheetName, "Sheet1", "d", "29")
Call UpdateAncillaryDelivaryRec("emailContact",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary date cell d31
getCellData = sBookValue(sSheetName, "Sheet1", "d", "31")
Call UpdateAncillaryDelivaryRec("delivaryDate",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary ldc cell d34
getCellData = sBookValue(sSheetName, "Sheet1", "d", "34")
Call UpdateAncillaryDelivaryRec("location_LDCName",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary street/road cell d35
getCellData = sBookValue(sSheetName, "Sheet1", "d", "35")
Call UpdateAncillaryDelivaryRec("streetRoad",
"tblAncillaryDelivary", getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary town/city d36
getCellData = sBookValue(sSheetName, "Sheet1", "d", "36")
getCellDataX = sBookValue(sSheetName, "Sheet1", "d", "37")
getCellData = getCellData & " " & getCellDataX
Call UpdateAncillaryDelivaryRec("Town", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary county dc37
'getCellData = sBookValue(sSheetName, "Sheet1", "d", "37")
'Call UpdateAncillaryDelivaryRec("county",
"tblAncillaryDelivary",
getCellData, get_sNumbers)

'get delivary postcode d38
getCellData = sBookValue(sSheetName, "Sheet1", "d", "38")
Call UpdateAncillaryDelivaryRec("PostCode",
"tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""
'get delivary HIAB g38
getCellData = sBookValue(sSheetName, "Sheet1", "g", "38")
Call UpdateAncillaryDelivaryRec("HIAB", "tblAncillaryDelivary",
getCellData, get_sNumbers, adoDelivaryDetails)
getCellData = ""

MsgBox "delivaryflag has been set to 1 (true)" & delivaryflag
'new code inserted on 11/02/10 by m.salami
'set delivaryflag to true (0)
delivaryflag = 0
Else
MsgBox "delivaryflag has been set to 0 (false)" & delivaryflag
End If

adoDelivaryDetails.Close
Set adoDelivaryDetails = Nothing
End Sub


Sub UpdateAncillaryDelivaryRec(sField As String, sTable As String,
sFieldVal
As Variant, getNdsRef As Variant, myAdo As Object)

Dim sSql, sFile As Variant



If IsDate(sFieldVal) Then
'Update record with correct data value
sSql = "Update " & sTable & " Set " & sField & " = " & sFieldVal & "
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

Else
sSql = "Update " & sTable & " Set " & sField & " = '" & sFieldVal &
"'
where ndsref='" & getNdsRef & "'"
'MsgBox Err.Source & "-->" & Err.Description, , "Error"

End If

myAdo.Execute sSql


End Sub

Don't worry about the SBookValue, all it those is extract data from the
worksheet,
as i said the straing thing is if i place a break point on the MsgBox
"DELIVARY FLAG CHECK = " & delivaryflag and iterate through the code
line
by
line, it updates the databse perfectly; but as soon as i take off the
breakpoint and execute the code the only thing it executes is the
adoDiane
recordset. Its almost as if it ignores it, could you help me pleeeease
--
RzaXL


:

You will need to show some code.

RBS


I'm using a variable of adodb.connection type to first insert a
recoord,
and
then update the records aditional fields using the sql update,
inserting a
record is know problem, updating the additional fields is proving to
be
a
Click to expand...
Click to expand...
Click to expand...
 
Back
Top