sql update problem

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


-- 
RzaXL
0
Utf
2/13/2010 1:33:21 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
785 Views

Similar Articles

[PageSpeed] 57

You will need to show some code.

RBS


"rzaxl" <rzaxl@discussions.microsoft.com> wrote in message 
news:354DA14D-B750-40C3-8EE9-C86FAE00E912@microsoft.com...
> 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
>
>
> -- 
> RzaXL 

0
RB
2/13/2010 8:17:57 AM
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" wrote:

> You will need to show some code.
> 
> RBS
> 
> 
> "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message 
> news:354DA14D-B750-40C3-8EE9-C86FAE00E912@microsoft.com...
> > 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
> >
> >
> > -- 
> > RzaXL 
> 
> .
> 
0
Utf
2/14/2010 3:47:01 AM
Didn't look deeply into it, but just a few thing for starters:

> Dim sSql, sFile As Variant
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
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" <rzaxl@discussions.microsoft.com> wrote in message 
news:941C9A6D-7D85-4ED2-8F11-088E8F920AE8@microsoft.com...
> 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" wrote:
>
>> You will need to show some code.
>>
>> RBS
>>
>>
>> "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message
>> news:354DA14D-B750-40C3-8EE9-C86FAE00E912@microsoft.com...
>> > 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
>> >
>> >
>> > -- 
>> > RzaXL
>>
>> .
>> 

0
RB
2/14/2010 10:12:26 AM
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" wrote:

> Didn't look deeply into it, but just a few thing for starters:
> 
> > Dim sSql, sFile As Variant
> 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
> 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" <rzaxl@discussions.microsoft.com> wrote in message 
> news:941C9A6D-7D85-4ED2-8F11-088E8F920AE8@microsoft.com...
> > 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" wrote:
> >
> >> You will need to show some code.
> >>
> >> RBS
> >>
> >>
> >> "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message
> >> news:354DA14D-B750-40C3-8EE9-C86FAE00E912@microsoft.com...
> >> > 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
0
Utf
2/14/2010 4:07:01 PM
If you could zip the .xls and .mdb files and mail that to me I will have a 
look.

RBS


"rzaxl" <rzaxl@discussions.microsoft.com> wrote in message 
news:DD2D4F23-CAC9-4627-B198-F651BDE16943@microsoft.com...
> 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" wrote:
>
>> Didn't look deeply into it, but just a few thing for starters:
>>
>> > Dim sSql, sFile As Variant
>> 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
>> 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" <rzaxl@discussions.microsoft.com> wrote in message
>> news:941C9A6D-7D85-4ED2-8F11-088E8F920AE8@microsoft.com...
>> > 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" wrote:
>> >
>> >> You will need to show some code.
>> >>
>> >> RBS
>> >>
>> >>
>> >> "rzaxl" <rzaxl@discussions.microsoft.com> wrote in message
>> >> news:354DA14D-B750-40C3-8EE9-C86FAE00E912@microsoft.com...
>> >> > 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 

0
RB
2/17/2010 5:15:29 PM
Reply:

Similar Artilces:

SQL Server Training at your door step
We at www.sqlServer-Training.com, are trying to educate people on Microsoft SQL Server. We are providing daily tutorials, news and articles via our Blog. In case you are interested in exploring this new Database Administration skill set, please subscribe to our newsletters at http://feedburner.google.com/fb/a/mailverify?uri=Sqlserver-training We are also offering SQL Server 2005, 2008 DBA training in online mode, where you can join us for web conferences. Certifactions Training ========================= MCITP - SQL Server 2008, Implementation and Maintenance MCITP - SQL Server 20...

PA Problem
I am using PA extensively to record nearly verything that goes through our 'P & L'. I have it set up so that each cost category within PA will actually post to its 'mirror' in the GL. e.g. Project 'FINANCE_06' category '6000' (salaries) will post in the GL to account code 6000-0-01 (being salaries, finance). There are reasons I have set it up this way, too numerous to go into here. My question is....if I record an invoice through PA, into payables etc. How do a reverse all or part of this invoice if I choice to? We have an invoice from a vendor ...

Problems with opening a few attachments
Hi Everyone I have had a few users experiencing problems with opening attachments in Outlook. When they try and do so, it comes up with a "Can't Open" error message. It is only occurring on a few attachments. I have checked the event log and there doesn't appear to be any issues on the Exchange server (2003). I have checked the hard disks on the server and there a re no issues with them. It is a single server Exchange 2003 environment. I am just wondering what the next course of action to take is. If you could get back to me on this as soon as possible it would be gr...

Problems Printing a Linked Word document
I am having trouble printing a word document that has linked excel tables in it. When I print the document, some of the linked excel tables get skewed and distorted. However, they look normal on screen. What causes this? How can I fix it? -- jc ...

Problem opening Excel 2003 in a Small Business edition
When i open excel ,it appears a window "installing:function of Excel" Its seems like installing a new function of Excel , it last a few seconds and then we can work wiht it. How can i eliminate this Hi, You could try looking into your tools>Add-ins to determine what function packs are being loaded when you start Excel. If this is poiting to an add-in on a network drive, it could be what is causing the delay. You can try this by unchecking each option in turn and closing and re-opening Excel. Hope this helps Sunil Jayakumar ""installing:function of Excel"...

Money 2005
Hello, I use Microsoft Money 2005 now for a while, and everytime I exit the app, it connects and uploads the changes to MSN Money. Is there a way to disable this setting? I would like to delete all of my financial data on MSN Money and not have MS Money upload it there again. If there is a way to stop it, please let me know. Thanks, Brett No problem: Tools: Settings: Access your Money data on the Web. Then you'll be able to disable it. -- "Brett" <baisley@h o t ma i .com> wrote in message news:O9vVj9ZqFHA.1336@TK2MSFTNGP11.phx.gbl... > Hello, > >...

Report from Access 2003 to 2007 problem
I have converted a database designed in 2003 to 2007. One of the reports unfortunately does not work the same as it did in 2003. The report generates invoices and on the form I had put in a text box with the Control Source property set to [Enter Invoice Date] which brings up a dialog box when the report is run asking the user to put in a date. This works ok in 2003 but comes up as #Error in 2007 in the report generated. In 2007 report design, the text box has a green triangle on it indicating an error "Invalid Control Property: Control Source" & "No such field in t...

Problem restoring backup file from money 98 to money 04
Just upgraded to a new computer with Money 2004 --- Have transferred my money back-up file to the new computer --- Everytime I try to open using Money 2004 it begins the process of converting the file and then times out (stops responding) Any ideas??? Extremely frustrating as I don't want to lose 6 years of financial data ---- Thanks How long have you waited? It could take a long time--like tens of minutes. While it is in this state, it will be "not responding." This doesn't mean it's dead, just hat it's busy. "jh227" <anonymous@discussions.micr...

Problems with licensing
Hello, i have got a problem with how to tell exchange how many CALs exists. In the normal way I put it under Verwaltung/Lizensierung - there appears under products the Exchange Server 2003. But in this case there is no record with Exchange Server 2003. I can't find any licensing information for the Exchange Server. I think something got wrong during installation. Background: 1 x Domaincontroller width Server 2003 Standard 1 x old Mailserver with Server 2000 and Exchange 5.5 as member of domain. 1 x 2003 Server R2 as Member of Domain with Exchange 2003 (newly installed - will repla...

Exchange 2000/2003 Public Folder item Permissions Problem
Hi all, I've been having an issue with Public Folder permissions for some time now. The folder contains contacts for all company employees (most of which aren't on the company network), and is maintained by HR. Brief history is: Had Exchange 2000 on Win Server 2000, and a previous administrator modified public folder permissions on the M drive in explorer (a no-no). This resulted in the ESM "Invalid window handle" error. Managed fix these by following KB 270905 (permissions nuke), and deleting and re-creating the public folder. The latter was a problem, as most users were ...

"create mscrm.sql" error on 3rd (re-)install
I have installed CRM 1.2 on SBS2K3 twice as "advetureworks cycle" and "msdn subscriber" with only 90% success because of conflicts on the web site. I have removed the conflicts but now CRM installer errors trying to create the new database as (default) local service. My login has SQLServer db_creator permissions and I can create a DB, so I'm totally confused. Can I workaround this somehow ? What login needs to be given db_creator rights so Install can run te SQL scripts ? Hi Marcus, have you deleate the complete the old date from AWC? Have you delete the ...

How do I update Excel 2000 macros to work in Excel 2002?
Working in Excel 2002/XP on an Excel worksheet which was created in Excel 2000, I get an error message "Subscript out of range". How do I update the macro to work in Excel 2002? Hi normally the macro should work without a problem. Best to post the part of your code which generates this error -- Regards Frank Kabel Frankfurt, Germany "BobPetrich" <BobPetrich@discussions.microsoft.com> schrieb im Newsbeitrag news:9CF34907-0A45-4392-8F04-556EFB1F89E4@microsoft.com... > Working in Excel 2002/XP on an Excel worksheet which was created in Excel > 2000, I get a...

Import/Export sql tables to an XML file?
Hi, How can I export/import data from SQL server tables to/from an XML file? Note: I want to tdo this from a desktop application written in c# and uses sql server tables for storing data. Is there a way to do this? Also, are there libraries that already do this? Thanx in advance... ...

Budget account problems with transfers
Transfers out of budget accounts are distorting my budget reports, yet I can't add the relevant accounts (e.g account created for a loan to my business) to the list of budget accounts. Using Money 2005 UK. Thanks for help! ...

Calculation Problem 01-19-10
All though the formula is correct ( a simple math addition between 2 cells) ..There are a few of these cells in my 2003 XL Spread Sheet that consistently makes a 1 cent error in the addition - that is, it's adds the cell less 1 cent (formatted in currency) How do I fix this? Thanks http://www.mcgimpsey.com/excel/pennyoff.html -- Kind regards, Niek Otten Microsoft MVP - Excel "djm123" <djm123@discussions.microsoft.com> wrote in message news:6411803B-537F-43A2-921F-049D576FD19F@microsoft.com... > All though the formula is correct ( a simple math...

data entry problem
I am trying to make a detailed sheet of some inventory data whic changes each month. The first sheet just has general data, but ther are more columns on the second sheet with varying expanded details Column A - product number Column B - color Column C - available Column D - Misc notes (may or may not be filled in) [SHEET 1] Month 1 A B C D 2310 blue yes 2348 red M/R must sell soon 2362 blue no (some text data in-between) 2405 red M/R need to be redone 2450 grn yes sell cheap 2477 wh yes Month 2 (new ones may be added, and sold item t...

MBNA, Yodlle, Money 2006 problem
Over10 days ago I had an account transferred to MBNA Credit Card. (I already have 2 other accounts with them.) Since that time I've been trying to setup my accounts at MBNA in Money 2006. No luck. Each time I try and do it, I receive either the error message "Account setup is temporarily unavailable. Please try again later." or "Microsoft Money could not set up your account because of technial problems at your bank or brokerage..." Last Monday I called Microsoft about this since the problem had been ongoing for several days. They verified the problem and said th...

Date problem #6
I have a column containing 4/21/2004 10/7/2003 5/5/2004 Which I need to convert to UK dates. because of the variables(4/, 10/) I am unable to use mid etc in seperate columns. also =TEXT(f2,"DD/MM/YY")fails to work. Hi are these values 'real' date values. If yes a simple format should do -- Regards Frank Kabel Frankfurt, Germany "Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag news:9a4601c486b0$01ac1f40$a501280a@phx.gbl... > I have a column containing > 4/21/2004 > 10/7/2003 > 5/5/2004 > Which I need to convert to UK dates. > bec...

Solver problem #3
I have a complex equation that is equal to zero with just one unknown that I have entered as "x". I need to know how to use solver to solve for the unknown variable. How do I do this? It seems like it should be simple, but I'm not sure how to enter the unknown into the equation. I keep getting the error, "Invalid target cell." Which makes me think that I should enter the unknown into a different cell, or something else. Please help!!! Assume some value for this x, and then enter this cell as 'by changing cell'. Your equation cell should be the target cell....

Mapi email problem (and Apology)
===================== First off my apology for multiposting. I posted to the wrong group by accident. This post was meant for this group and not the enterprise group I had posted to by accident. ========================================= I have automated VB6 application that sends email to to clients when prompted by a user. The app uses MAPI client (using the default mail client information to send the mail). After sending mail the email and attachments gets added to SENT Folder of the mail client. This app has worked well and still does for users using Outlook express. Howev...

Stop Chart Style (Format) Update
Hi TWIMC, I'm using Excel 2000 and I have a pivot table that aggregates data based on REGION, COUNTRY, ZONE etc etc. Once I have set up the chart they way that I would like it to look, if I then make a change to the selection using the pivot chart buttons, e.g. I select a country and the chart re-formats itself back to what appears to be the default style. I've set my chart style to the default but it still doesn't keep my chart style. What I want is that the chart style remains the same but the new values are just plotted onto the chart. I'm sure there is an option to turn ...

oe6 Problem
Am unable to open websites when they show up in Outlook Express 6. Anyone know why? When I click on an URL all I get is a BONG. Am running WinXP Home Edition w/SP3. "bandi" <mnkwms@charter.net> wrote in message news:Oh30Tg5%23KHA.3880@TK2MSFTNGP04.phx.gbl... > Am unable to open websites when they show up in Outlook Express 6. > Anyone know why? When I click on an URL all I get is a BONG. Am > running WinXP Home Edition w/SP3. Outlook Express General newsgroup: In your newsreader: news://msnews.microsoft.com/microsoft.public.outlookexpr...

Format Problem #2
Hi how can i save an excel file to text file with out chainging the format. ie Excel file: adf 12 a 1 when i change this to text file the alighment changes like adf 12 a 1 so how can i solve this problem. i tryied many wayes and nothing work out properly. so if someone know this , plzz do help me. manikandan Try saving it as a "Formatted Text (Space delimited) (*.prn)" file. You may have to widen columns to make it look right. (And I like to change the font to Courier New--a nice non-proportional font. Then I can see how it'll line up.) manikandan wrote: > >...

Debt reduction problem
When I work out a plan for debt reduction, it keeps adding $350 to the first month of the plan. So for example if I want to pay $263 per month it comes up with $613. On my old Money 2003 I had worked out a plan paying $350 per month, but I deleted the account so I don't understand why it seems to have that number locked in. Any thoughts? First, reset the DRP--Tools, Settings, Planner Options (or Tools, Options, Planner in 2004 and prior), and click Reset Plan. Then add the account back into the DRP. Since your DRP will begin 11/1, if you have any payments due prior to that date,...

Lookup problem #3
I have an excel file with Overall Hotel bookings in it on the firs worksheet. Here the rows are in order of arrival date and have the agency name reference number, hotel name, group name, number of PAX amongst othe things. I want to also have a sheet for each hotel, by looking up the hotel' name in the first sheet and copying the information across. I have tried with vlookup, but all I get is a repeated list of on booking per hotel instead of all the different bookings. Can anyone help ? Thanks in advanc -- jamesjoh --------------------------------------------------------------------...