Update Query not working on last record (1 Viewer)

J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Hi
I have this update query that is triggered by an after update event on a main form. The record being updated are in a continuous subform. It works well except from the last added/modified record. If I save and close the form and then open it again it works for all records but if modify or add a record, the update query will not work for that last modified/added record.

I have tried several things such as save record, use dirty = false for the on exit event of the subform control but nothing works. Here is the procedure:
Code: 
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim db As Database
    Dim strSql As String
    Dim lngID As Long
    Dim dblRate As Double
    Set db = CurrentDb
    DoCmd.RunCommand acCmdSaveRecord
    lngID = Me.txtID
    dblNCostR = DLookup("ExRate", "Currency", "ID = " & [cboPoCurrency])
    dblRate = dblNCostR / dblOCostR
    DoCmd.SetWarnings False
    strSql = "UPDATE QuoteLineItems SET QuoteLineItems.UnitCost = [UnitCost] * " & dblRate & _
                " WHERE QuoteLineItems.QuoteID = " & Me.txtID & ";"
    DoCmd.RunSQL strSql
    Me.txtPOExchRate.Value = dblNCostR
    Set db = Nothing
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub

Hope someone can help!
I have posted this on other forums without results.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
A couple of things to try...

Instead of DoCmd.RunCommand acCmdSaveRecord try Application.RunCommand acCmdSaveRecord

OR you could try a DoEvents right after the save command to give it time to save

OR move the save line right above the strSQL line.
 
Last edited:
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Hi Gina,
Just gave it a try but no luck. Still the same problem.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Okay, so let's understand the sequence...

1. You make a change on the subform of an existing quote
2. You go back to the main form to update the currency and that triggers the routine to run
3. (and this is what is not happening) all the records in the subform except the last one get updated.

Is that right?
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Yes it is right. All the records except the "last one that has been modified". Meaning that if I add a new record in the subform, and then trigger the event, all but that record will be updated. Now if I decide to change something on one of the previous records, the last added will work but the one I have just modified will be the one that doesn't work.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Okay, so any record that gets modified in the subform does not get updated... that is strange. What is the RecordSOurce of the Main Form and the Subform? And is there any overlap of Tables in either Record Source?
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Form has the tblQuote as a recordsource and Subform has qryQuoteItemExtended as a record source. qryQuoteItems links 2 tables together: tblQuoteItem and tblItemNames. There is a Master-Child relationship: tblQuote.ID and qryQuoteItemExtended.QuoteID.
I don't understand what you mean by "overlap".
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Overlap would be if tblQuote is also included in the RecordSource query for the Subform but I see that is not the case but I think I see what is happening... thinking...
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Okay, so in qryQuoteItemExtended there are two tables... when you update the Currency per chance is the field that is update in tblItemNames?
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
The Exchange Rate is in tblItemNames, it is picked up and stored in the double variable dblNCostR. But the field that gets updated is in table QuoteLineItems.

(sorry I made a mistake in the table name. What I have described as tblQuoteItem is actually the table QuoteLineItems that you can see in the in my initial post's SQL statement)
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Would it make a difference to run the SQL on the query (that is used as a record source) rather than on the table?
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
No, you need to run it against the tabe what I am thinking is is there a way to remove tblItemsNames because I think the problem may have to do with the fact that there are two tables as the Record Source. Why is tblItemNames included?

EDIT: I knew that about the table names, no worries...
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Because each item belongs to a category. So there is another table called tblCategory with a relationship ID -> CategoryID.
This is being used in the subform to filter items (cascading combo boxes)
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
But now thinking about it... I don't think it is necessary. just using the table as a record source should be enough. Let me try.
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
Yes but...
Just changed it. Obviously it was not necessary but it did not solve the problem. The last modified record still does not get updated :(
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Well, <not able to use that language here>...

Okay, so now what is the Record Source of the Subform?
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 11:41
Joined
Jun 21, 2011
Messages
5,899
Have you tried the DoEvents? Now, it should update as the record should save once you go back to the Main Form.
 
J

jerem

Registered User.
Local time
Today, 23:41
Joined
May 1, 2012
Messages
118
No luck...
Code: 
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
    Dim db As Database
    Dim strSql As String
    Dim lngID As Long
    Dim dblRate As Double
    Set db = CurrentDb
    lngID = Me.txtID
    dblNCostR = DLookup("ExRate", "Currency", "ID = " & [cboPoCurrency])
    dblRate = dblNCostR / dblOCostR
    DoCmd.SetWarnings False
    Application.RunCommand acCmdSaveRecord
    DoEvents
    strSql = "UPDATE QuoteLineItems SET QuoteLineItems.UnitCost = [UnitCost] * " & dblRate & _
                " WHERE QuoteLineItems.QuoteID = " & Me.txtID & ";"
    DoCmd.RunSQL strSql
    Me.txtPOExchRate.Value = dblNCostR
    Set db = Nothing
ExitSub:
    Exit Sub
ErrHandler:
    Call ErrorAlert
    Resume ExitSub
End Sub
 
You must log in or register to reply here.

Similar threads

C
Solved Update a record with a recordset
Replies
10
Views
374
cheekybuddha
cheekybuddha
M
Populate Sub Form with similar to last main form record data
Replies
6
Views
601
MHART08
M
N
Solved Record set too slow with SQL server Cloud & Parameters not working
Replies
15
Views
537
cheekybuddha
cheekybuddha
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
359
mloucel
mloucel
N
Dlookup Vs Record set query
Replies
1
Views
402
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom