Update Query Peculiarity - Not All Fields Updated

[boostbyslinky]

Morning folks, hope Access is being kind to you today..

I'm mid build on a commitments tracking (pseudo Purchase Order system) project, but seem to have run into an issue with a loop I'm building to allow users to edit commitments.

The idea is that an edit form is launched, values amended and the submit button is clicked. A VB subroutine then validates the entries to ensure that the mandatory entries are included. It then writes a copy of the original values to the Archive table, before attempting to update the existing commitment with the new values..

This is working perfectly, apart from the fact that 3 fields that are "updated" are being updated to a blank value..

Code is thus :

Private Sub CommitSubmit_Click()
Dim SQLStr, LastID, DOwner, DHeading As String
Dim ErrState, Dtype, DProperty, DTCA, DITD, DSD, DED, DSP, DRetention, DRA, DRPD, DSupplier, DDOW, DStatus, DUser, DShD As String
Dim Authcheck, Complete, ErrMsg As Boolean
Dim QDF1, QDF2 As QueryDef

Dtype = Me.Type.Value
DHeading = Nz(Me.Heading.Value)
DProperty = Me.Property.Value
DTCA = Me.TCA.Value
DITD = Me.ITD.Value
DSD = Me.SD.Value
DED = Me.ED.Value
DSP = Me.SP.Value
DRetention = Me.Retention.Value
DRA = Me.RA.Value
DRPD = Me.RPD.Value
DSupplier = Me.Supplier.Value
DShD = Nz(Me.ShortDescription.Value)
DDOW = Me.DOW.Value
DStatus = Me.Status.Value
DUser = UCase(Environ("Username"))
DOwner = Nz(Me.Owner.Value)
Authcheck = Nz(DLookup("Authlimit", "tblstaff", "Userid='" & Environ("username") & "'") > DTCA)
ErrState = 0
If IsNull(Dtype) Then ErrState = ErrState + 1
If DHeading = "" Then ErrState = ErrState + 1
If IsNull(DProperty) Then ErrState = ErrState + 1
If IsNull(DTCA) Then ErrState = ErrState + 1
If IsNull(DSD) Then ErrState = ErrState + 1
If DRetention = -1 Then
    If IsNull(DRPD) Then ErrState = ErrState + 1
    If IsNull(DRA) Then ErrState = ErrState + 1
End If
If IsNull(DShD) Then ErrState = ErrState + 1
If IsNull(DSupplier) Then ErrState = ErrState + 1
If IsNull(DStatus) Then ErrState = ErrState + 1
If DOwner = "" Then ErrState = ErrState + 1
If IsNull(Dtype) Then Me.Type.BackColor = vbRed
If DHeading = "" Then Me.Heading.BackColor = vbRed
If IsNull(DProperty) Then Me.Property.BackColor = vbRed
If IsNull(DTCA) Then TCA.BackColor = vbRed
If IsNull(DSD) Then SD.BackColor = vbRed
If DRetention = -1 Then
    If IsNull(DRPD) Then RPD.BackColor = vbRed
    If IsNull(DRA) Then RA.BackColor = vbRed
End If
If IsNull(DShD) Then ShortDescription.BackColor = vbRed
If IsNull(DSupplier) Then Supplier.BackColor = vbRed
If IsNull(DStatus) Then Status.BackColor = vbRed
If DOwner = "" Then Owner.BackColor = vbRed
If Not ErrState <> 0 Then
Set db = CurrentDb
Set qdf = db.QueryDefs("qry-AppendArchive")
qdf.Parameters(0) = Eval(Forms![frmcommitmentedit]![CommID])
qdf.Execute dbFailOnError
Set qdf = db.QueryDefs("qry_UpdateCommitment")
qdf.Parameters(0) = Eval(Forms![frmcommitmentedit]![CommID])
qdf.Parameters(1) = DLookup("version", "tblcommitments", "ID=" & Forms![frmcommitmentedit]![CommID]) + 1
qdf.Parameters(2) = Forms![frmcommitmentedit]![Type]
qdf.Parameters(3) = Forms![frmcommitmentedit]![Property]
qdf.Parameters(4) = Eval(Forms![frmcommitmentedit]![TCA])
qdf.Parameters(5) = Nz(Forms![frmcommitmentedit]![ITD], 0)
qdf.Parameters(6) = Forms![frmcommitmentedit]![SD]
qdf.Parameters(7) = Forms![frmcommitmentedit]![ED]
qdf.Parameters(8) = Forms![frmcommitmentedit]![SP]
qdf.Parameters(9) = Eval(Forms![frmcommitmentedit]![Retention])
qdf.Parameters(10) = Forms![frmcommitmentedit]![RPD]
qdf.Parameters(11) = Forms![frmcommitmentedit]![RA]
qdf.Parameters(12) = Forms![frmcommitmentedit]![Supplier]
qdf.Parameters(13) = Forms![frmcommitmentedit]![ShortDescription]
qdf.Parameters(14) = Forms![frmcommitmentedit]![DOW]
qdf.Parameters(15) = Forms![frmcommitmentedit]![Status]
qdf.Parameters(16) = Forms![frmcommitmentedit]![Owner]
qdf.Parameters(17) = Forms![frmcommitmentedit]![Heading]
qdf.Parameters(18) = Forms![frmcommitmentedit]![pCommit]
qdf.Parameters(19) = UserName()
qdf.Execute dbFailOnError
Else
ErrMsg = MsgBox("Some required elements are incomplete, please ensure all necessary data is entered. " & ErrState & " items are missing.", vbInformation)
End If
End Sub

The Query being called is as follows..

PARAMETERS [Param-CommID] IEEEDouble, [Param-Version] IEEESingle, [Param-Type] Text ( 255 ), [Param-Property] Text ( 255 ), [Param-TotalContractAmount] Text ( 255 ), [Param-InvoicedToDate] Text ( 255 ), [Param-StartDate] DateTime, [Param-EndDate] DateTime, [Param-StagedPayments] Bit, [Param-Retention] Bit, [Param-RetentionPayableDate] DateTime, [Param-RetentionAmount] Text ( 255 ), [Param-Supplier] Text ( 255 ), [Param-ShortDescription] Text ( 255 ), [Param-DescriptionOfWorks] LongText, [Param-Status] Text ( 255 ), [Param-Owner] Text ( 255 ), [Param-Heading] Text ( 255 ), [Param-ParentCommitment] Text ( 255 ), [Param-User] Text ( 255 );
UPDATE tblCommitments SET tblCommitments.Version = [Param-Version], tblCommitments.Type = [Param-Type], tblCommitments.Property = [Param-Property], tblCommitments.TotalContractAmount = [Param-TotalContractAmount], tblCommitments.InvoicedtoDate = [Param-InvoicedtoDate], tblCommitments.StartDate = [Param-StartDate], tblCommitments.EndDate = [Param-EndDate], tblCommitments.StagedPayments = [Param-StagedPayments], tblCommitments.Retention = [Param-Retention], tblCommitments.RetentionPayableDate = [Param-RetentionPayableDate], tblCommitments.RetentionAmount = [Param-RetentionAmount], tblCommitments.Supplier = [Param-Supplier], tblCommitments.ShortDescription = [Param-ShortDescription], tblCommitments.DescriptionOfWorks = [Param-DescriptionOfWorks], tblCommitments.Status = [Param-Status], tblCommitments.[User] = [Param-User], tblCommitments.Owner = [Param-Owner], tblCommitments.Heading = [Param-Heading], tblCommitments.ParentCommitment = [Param-ParentCommitment]
WHERE (((tblCommitments.ID)=[Param-CommID]));

and the three fields which are not being updated/nulled out are tblcommitments.Type
tblcommitments.Owner
& tblcommitments.Heading


tl/dr - Some fields in a VB called update query are not updating...
Anyone have any thoughts on what I've done wrong here?
Feel free to shout up if I've missed any information..

Cheers..

slinky

 

[pr2-eugin]

boostbyslinky, is there any specific reason, you are checking for Null values to Increment the ErrState and then again check for the same condition to change the background? Could it not be as simple as..

If IsNull(Me.someControl) Then
    ErrState = ErrState + 1
    Me.someControl.BackColor = vbRed
End If

Also to point out.. IsNull will not work if it is a Zero Length String.. In technicality ZLS are not Null.. So IsNull("") will be False.. Try

If Len(Me.someControl & vbNullString) = 0 Then
    ErrState = ErrState + 1
    Me.someControl.BackColor = vbRed
End If

If it is a ComboBox, use the ListIndex property..

 

[boostbyslinky]

Thanks for the advice Paul.. I don't wish to seem ungrateful, but that's not the issue I'm focusing on right now..
Stepping through the routine and querying the values of each qdf.parameter(x) displays the correct values (or at least, it appears to), but they seem to disappear between adding them into the qdf.parameters array and qdf.Execute ..
If I can get that to work, I'll turn my focus to making things a little more efficient/proper..

 

[namliam]

Why do an update query in the first place and not open a query in a DAO.recordset and update the table there?

Are all 3 fields actually text fields?
Are you actually entering any data in your form?

Cant see any glaringly obvious faults, have you checked with debug.print what the values in your fields were/are?

 

[boostbyslinky]

Why do an update query in the first place and not open a query in a DAO.recordset and update the table there?

Erm.. Simply because I searched for a method to achieve my results and the one I'm using was the first I fell upon!

Are all 3 fields actually text fields?

Type, Owner & Heading are all text values, represented in individual combo boxes.. (Other combo box values on the form are being carried across without issue. Property for example)

Are you actually entering any data in your form?

Yup

Cant see any glaringly obvious faults, have you checked with debug.print what the values in your fields were/are?

Yup!
I've been through every single qdf.parameter value (0-19) and verified that the value stored against the parameter is correct...

hmmmmmmm

 

[pr2-eugin]

So is the ComboBox RowSource returning the required result? Sometimes comboxes will have the Bound Column as Number but display Text.. So check the properties.. Or See if the Field you are trying to update is Text to begin with..

 

[boostbyslinky]

If I've verified that qdf.parameters(17) (for example) contains the data that I'm interested in, is the rowsource verification still a requirement?

Table is configured as per the screengrab attached.

 

[namliam]

Are those fields inside your table defined as "Lookup" fields?

 

[boostbyslinky]

Nope, they're pure and "simple"..

 

[namliam]

Any chance of posting a sample database with just a few records, the offending form && Code && query?

 

[boostbyslinky]

The form in question is frmCommitmentEdit
Database is attached to this post..
Cheers.

 

[namliam]

Dunno what you are doing but it works just fine for me....

Try a full reboot of your machine and see if that helps

 

[boostbyslinky]

Dunno what you are doing but it works just fine for me....

Try a full reboot of your machine and see if that helps

If I wasn't working in a full blown Citrix environment........

 

[namliam]

There seems to be a problem with the order of things....
For me what I put into Parent Commitment (49 in my case) seems to be updated to User
And parentCommitment actually contains "Wil Perry" which I put in as "Heading"
Owner is "Cancelled" which is what I entered as Status... Owner should be "Closed" which is in Heading....

All this parameter stuff, its making it more complex than it needs to be... either
1) Edit stuff in VBA using a Recordset
2) Compile the update query in code and run it as a string

Both cases give much more control

 

[boostbyslinky]

I did wonder whether there was an ordering issue..

I think I've ended up chasing a method which, whilst initially was simple (the append query to add the row to the Archive table), is now not as simple as it could be!

So..
Any recommended reading on Option 1?
I'll tinker with option 2 in the background, I've written plenty of docmd.runsql style table inserts in the past..

 

[namliam]

Option 1 goes something along the lines of

'Do appendquery and checks and stuffs
dim rs as dao.recordset
set rs = Currentdb.Openrecordset("Select ... from ... where id = " & id)
rs.edit
rs.YourFieldname = Somevalue
rs.AnotherField = anothervalue
'Some other options you could use like:
'rs!Thatfield = ThatValue
'rs.Fields("Here") = Therevalue
rs.update
rs.close
set rs = nothing

 

[boostbyslinky]

Cheers Mailman..
I've done some reading, googling and testing and come to a method which I think I'm going to adopt..

In simple testing I arrived at this...

Sub test()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCommitments", dbOpenDynaset)
ID = 42
With rs
    .FindFirst "ID=" & ID
        .Edit
            .Fields("TotalContractAmount") = 99
        .Update
    .Close
Set rs = Nothing
End With
End Sub

All I need to do now is weave that into a useable form in the main code..
Thanks for your help folks..

 

[namliam]

friendly advice, dont use findfirst... instead do like I suggested earlier
Currentdb.Openrecordset("Select ... where id = "& ID)
Particularly if the table starts to get big, you will see the performance increase of this over findfirst...

 

[boostbyslinky]

Friendly advice always gratefully received..
At what point are we calling the table "big"?

 

[namliam]

a lot about big depends on the user experience, which is largely determined by db responce time which is largely dictated by the (network) drive the database is on....

Some say 10k others say 10m