Set focus based on a condition

[bodmin]

Hey,

I need to be able to move the focus from one subform into a corresponding record on a second subform based on the ID number of the record in the first subform. Is this possible, if so how?

cheers
Andi

 

[KenReay]

Hi

"I need to be able to move the focus from one subform into a corresponding record on a second subform based on the ID number of the record in the first subform. Is this possible, if so how?"

In principle you need code like so

Me.SubFormControlname.FORM.recordsetclone.findfirst "Id = " & Id
If Me.SubFormControlname.FORM.recordsetclone.NoMatch Then
Else
Me.SubFormControlname.FORM.bookmark = Me.SubFormControlname.FORM.recordsetclone.bookmark
end If

this should allow you to position to the relevant record in the second subform

precisely where you place this code in the first subform, depends on what event you wish to cause the action, afterupdate event of the Id column perhaps?

Note the variable Id is assumed to the the primae key of both tables, and is assumed to be of type numeric in the example code above



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Take a look at the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the DAO.Recordset and Form objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

I have used the methods that you have suggested and they seem to be workin, so cheers for that cus I was searchin for something exactly like that. My only problem left is in the section where it doesnt find a matching record in the subform I want it to force the cursor in to the new record at the bottom of the datasheet, is there also a method to achieve this???

cheers Andi

 

[PHV]

Take a look at the DoCmd.GoToRecord method with the Record paramater set to acNewRec.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

I am now having some more probs with this code, here is the section of code that the problem is within.

If AnalyseApplicable = False Then 'check to see if any of the analysis codes can be applied to this account
Message = "The debit account has been successfully updated"
Display = MsgBox(Message, vbOKOnly, "Update Successful") 'display message box informing user that the account has been successfully updated
Forms![Main]![TCodeDescr].Form.RecordsetClone.findfirst ID = ID 'check to see if there was any analysis already applied to previous account code
If Forms![Main]![TCodeDescr].Form.RecordsetClone.NoMatch Then 'if there is no match then do nothing
Else
strSql = "delete * from TempAttributes where Debit_ID=" & ID & ";" 'if there is a match then delete all analysis with a debit_ID that matches the current debit
localConn.Execute strSql
End If
Else
Forms![Main]![TCodeDescr].Form.RecordsetClone.findfirst ID = ID 'check to see if there was any analysis already applied to previous account code
If Forms![Main]![TCodeDescr].Form.RecordsetClone.NoMatch Then ' if no previous analysis output a message informing user
Message = "There has currently been no analysis code applied to this debit, please input a new analysis code if required using the datasheet. Remember that you must reduce the amount of other debits before any currency amount can be assigned to the new debit."
Display = MsgBox(Message, vbOKOnly, "Currently No Analysis Applied")
DoCmd.GoToRecord acDataForm, "TCodeDescr", acNewRec 'move the cursor to a new input to allow entry of analysis

For i = 0 To 10 'loop through array
Applicable = AnalysisArray(i) 'colect array element and store in local string variable
If Applicable <> "No" Then 'If this element is not no then add the analysis code to the drop down list
Forms![Main]![TCodeDescr]!Analysis_Code.AddItem Item:=Applicable
End If
Next i
Else
Forms![Main]![TCodeDescr].Form.Bookmark = Forms![Main]![TCodeDescr].Form.RecordsetClone.Bookmark

For i = 0 To 10
Applicable = AnalysisArray(i)
If Applicable <> "No" Then
Forms![Main]![TCodeDescr]!Analysis_Code.AddItem Item:=Applicable
End If
Next i
End If

My problem is that if it does find an analysis record it is not executing the delete sql statement to remove it from the table. My other problem is that the focus is not shifting to the correct place when it has found an analysis record that matches the edited debit record. Any one got any ideas??

Cheers
Andi

 

[PHV]

Replace this:
.findfirst ID = ID
By this:
.FindFirst "ID =" & ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

When I replace that section with what u suggest I get the following error message:

Run Time Error '3070'
The microsoft Jet database engine does not recognize 'ID' as a valid field name or expression.

Yet I have declared ID as an integer field during the declaration section of this procedure.

Cheers for the help so far any ideas on this would be great
Andi

 

[PHV]

What is the name of the ID field in the RecordSource of TCodeDescr subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

The ID field in the recordsource of TCodeDescr is called Debit_ID

 

[PHV]

So, replace this:
.findfirst ID = ID
By this:
.FindFirst "Debit_ID=" & ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bodmin]

I have now tried that and the problem remained, the code is still not removing the analysis record if the new debit account is not applicable for analysis and the focus is staying on the debit account control not moving in to the TCodeDescr subform

cheers
Andi

 

[bodmin]

I have also found that the section of code that checks to see whether there is a previous analysis record for the debit ID is not operating correctly therefore it is leaving the other analysis details in place.