requery subform data update problem

[tchaplin]

I have a procedure that makes a call to check to see if there is a record with a nz(ClientDateInvoice) <> 0 and nz(ClientInvoiceNumber) <> 0 it then sets a flag bolMultiInvoice.

The problem is that I'm on a subform continuous forms on the Form_AfterUpdate event i change data on a record and then want to check to see if there is a recored that contains both an invoice number and invoice date and set the flag to do something else - code below. If i requery the recordset i lose the record position. I seem to have to move the focus to two records before the flag kicks in. A timing data update issue any suggestions.

this works but i lose the record focus
'Dim strsql As String
'strsql = "qselSubMultiInvoice"
'Me.RecordSource = strsql

ive also tried
'lngPos = Me.Recordset.AbsolutePosition
'If lngPos > (Me.Recordset.RecordCount - 1) Then
' lngPos = Me.Recordset.RecordCount - 1
' End If
'Me.Recordset.AbsolutePosition = lngPos
but if I click on another record it goes back to the one that i was editing



The procedure
Call CheckJobInformation(bolJobEvent, bolRemedialWork, bolMultiInvoice, Me.Parent.Parent!txtEstimateNumber)


Calling ......
Call OpenDbConnection

strsql = "SELECT TJob.JobID, TEstimates.EstimateId, TWorkAttention.WorkAttentionId, TWorkAttention.JobId, TRemedialWork.RemedialId, TRemedialWork.RemedialJobId, TEstimateWorkType.ClientInvoiceNumber, TEstimateWorkType.ClientDateInvoice " _
& "FROM (((TEstimates INNER JOIN TJob ON TEstimates.EstimateId = TJob.EstimateJobID) LEFT JOIN TWorkAttention ON TJob.JobID = TWorkAttention.JobId) INNER JOIN TEstimateWorkType ON TEstimates.EstimateId = TEstimateWorkType.EstimateIdWorkTypeID) LEFT JOIN TRemedialWork ON TJob.JobID = TRemedialWork.RemedialJobId " _
& "WHERE (((TEstimates.EstimateId) = " & strEstimateNo & "))"

Set rst = New adodb.Recordset
Set rst.ActiveConnection = dbcon

rst.CursorLocation = adUseClient

rst.Open strsql, dbcon, adOpenDynamic


With rst
' checks to see if there are records - not eof or not bof
If (.EOF = True) Or (.BOF = True) Then
rst.Close
Set rst = Nothing
dbcon.Close
Set dbcon = Nothing
Exit Sub
End If


.MoveLast
.Requery
.MoveFirst

' iterate through the data to see what is active job event, multi invoice, remedials work
Do While Not .EOF
' bolMultiInvoice
If bolMultiInvoice = False Then
If Nz(rst!ClientDateInvoice) <> 0 And Nz(rst!ClientInvoiceNumber) <> 0 Then
bolMultiInvoice = True
End If
End If

' bolRemedialWork
If bolRemedialWork = False Then
If Nz(rst!RemedialId) <> 0 Then
bolRemedialWork = True
End If
End If

' bolJobEvent
If bolJobEvent = False Then
If Nz(rst!WorkAttentionId) <> 0 Then
bolJobEvent = True
End If
End If
.MoveNext
Loop

End With
rst.Close
Set rst = Nothing
dbcon.Close
Set dbcon = Nothing


Suggestions ......

 

[TheAceMan1]

How are ya tchaplin . . .

[blue] . . . on the Form_AfterUpdate event i change data on a record and then want to check to see if there is a recored that contains both an invoice number and invoice date and set the flag to do something else . . .[/blue]

Hard to say at this point, but your quote appears to be [blue]ambiguous[/blue]. You change data then hunt for a record with invoice number & date! [blue]Why am I thinking there will be other records that satisify invoice & date?[/blue]

[blue]Be more specific about this . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[tchaplin]

Thanks for your reply

Yep that is correct - there are two types of job - a multi invoice and a single invoice - both are treated very differently.

If both fields are cleared within the multi invoice section i want to be able to 'reset' the job for either a single invoice or multi depending on the selection. Alternatively if for the first time an invoice and date are entered for multi-invoice i want to flag it so a single invoice selection cannot be made.

So on the forms after update im seeing it the fields have been cleared or entered to then flag the answer

Thanks once again

 

[TheAceMan1]

tchaplin . . .

Sorry to get back so late. I've been going over this thread quite a bit and keep coming up with areas I can't resolve.

[blue]The problem is that I'm on a subform continuous forms on the Form_AfterUpdate event [red]i change data on a record[/red] . . [/blue]

[ol][li]How are you saving the record? . . .[/li]
[li]I can't tell if its the currently edited record or some other record that your changing in the Form_AfterUpdate event. Be more specific here![/li]
[li]You can get back to the origional edited record with:

[blue]   Dim hldID, Cri As String
   
   hldID = Me![purple][B][I]PrimaryKeyName[/I][/B][/purple]
   Me.RecordSource = "qselSubMultiInvoice"
   
   Cri = "[[purple][B][I]PrimaryKeyName[/I][/B][/purple]] = [red][b]'[/b][/red]" & hldID & "[red][b]'[/b][/red]"
   Me.Recordset.FindFirst Cri[/blue]

If the [blue]primarykey is numeric[/blue], remove the two single quotes in [red]red[/red].[/li]
[li]In the SQL of your recordset is [blue]strEstimateNo[/blue] a field on the subform or what? . . . If its a field on the form/subform you'll have to instantiate proper form referencing.[/li]
[li]Where do agruements [blue]bolJobEvent[/blue], [blue]bolRemedialWork[/blue], and [blue]bolMultiInvoice[/blue] reside/come from?[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886