Code executing without errors, but not working

[The Professor]

Hi,

The following code is being run from the AfterUpdate event of a combo box on the subform "frmInvoice". It is intended to first, move the master form "frmCustomer" to the correct Record based on "CustID", then return focus to the subform and then move it to the required Record, based on InvoiceNum. The first half of the code works a treat and does indeed set the Record in the master form to the required "CustID". However when the focus returns to the subform (from which the code is being executed) the code runs but doesn't seem to have any effect, and no errors are generated.

The problem lies in the second DoCmd.FindRecord command (in Red), this is intended to set the subform record to match the InvoiceNum field, stored in variable lngInvNum. The FindRecord command appears to execute, but has no effect.

The record in the subform does change however, but only because the Record in the Master form "frmCustomer" has changed. Because of the way in which the two tables are linked (One to Many) the subform updates automatically to match the first linked Record for the current Record in the Master form (there may be several or more records in the subform table linked to the current Record in Master form). My second DoCmd.FindRecord command is intended to display the precise record chosen by the user in the combo box, but this line of code is simply being ignored, for some reason I cannot fathom.


Private Sub cmbInvNo_AfterUpdate()

Dim lngInvNo As Long
Dim lngCustNo As Long

On Error Resume Next

'obtain search criteria "CustID" & "InvoiceNum"
lngInvNo = Me.cmbInvNo
lngCustNo = DLookup("CustID", "tblInvoice", "[BookingID] = " & lngInvNo)

'set frmCustomer to required 'CustID' record


Parent.txtCustID.SetFocus
DoCmd.FindRecord lngCustNo, acEntire, False, acSearchAll, False, acCurrent, True

'All the above code seems to work well.
'Return focus to subform "frmInvoice" and set to required Record for lngInvNo
Me.txtInvoiceNum.SetFocus
DoCmd.FindRecord lngInvNo, acEntire, False, acSearchAll, False, acCurrent, True
'it is this line of code, above, that is being ignored - executes without error, but has no effect.

'check there has been no error
If Err <> 0 Then
MsgBox "Error has ocurred, Err No : " & Err & ", " & Err.Description, vbCritical, "Error"
End If

On Error GoTo 0
End Sub


I've racked my brains to try and understand why that second DoCmd.FindRecord line (in Red) is having no effect, and refusing to change the Record based on the lngInvNum value. Prof.

[DarrenUD]

set up a break point and start the debugger. It could be a problem with getting the focus back to the sub form...I hope this helps...

[The Professor]

Hi Darren and thanks,

Appreciate your input, but been there and done that. I've F8'td my way through the code, line by line and the focus does indeed return to the subform, I've even had it change the colour of the text, just to make sure. Still, I'll have another go at debbuging, see if anything new strikes me. fact is I'm missing something here, but I just don't know what it is. Prof.

[Missinglinq]

Assuming that txtInvoiceNum is the name of the Subform Control, I think this this is all you're setting Focus to, with your line

Me.txtInvoiceNum.SetFocus

I think you then need to move the Focus to the Form the Subform is based on, by setting Focus to a Control on it, then running your search command.

Linq ;0)>

[The Professor]

Hi Linq,

Despite my earlier answer to Darren I think he is actually right - I don't think Focus is coming back to the SubForm. My test to change the text colour of a Control was flawed, I think.

Linq, the SubForm Control is called frmSubform.

txtInvoiceNum is the name of a Control on the Subform, so Focus should be coming back to it, but for some reason I don't think it is, which is why the second DoCmd.FindRecord line isn't working, I suppose.

Prof.

[Missinglinq]

...[B]the SubForm Control is called frmSubform...

...txtInvoiceNum is the name of a Control on the Subform...

Therein lies the problem! You have to first set Focus to the Subform Control and then set Focus to a Control on the Subform! So to set Focus to txtInvoiceNum you need this:

Me.frmSubform.SetFocus
Me.txtInvoiceNum.SetFocus

Linq ;0)>

[The Professor]

Hi Linq,

I think you're right, and I hope we nearly cracked this, but I can't find the right syntax to refernce the SubForm Control (which is actually called "frmInvoice" not frmSubform - my bad). But...

Me.frmInvoice.SetFocus

Produces the Compile Error: "Method or Data Member not Found".

frmInvoice doesn't appear in the intelli-list when I type in Me. As a Subform it's actually a Control on the Parent/Master Form. So how do I access the Subform Control from within itself, so to speak. Because the code above is in the AfterUpdate event of a combo box Control on frmInvoice itself. Prof.

[DarrenUD]

Hi Proff,

I think it should be something like this [Forms]![FormName].[SubFormName].[Form].[ControlName].SetFocus ( I have used it once but I don't really remember where..it should work)

[Missinglinq]

I'm sorry, was trying to do twelve things at once, when I posted before! I believe the correct syntax would be

Focus on the Subform Control itself:

Me.NameOfSubFormControl.SetFocus

Focus on the Control on the Subform:

Me.NameOfSubFormControl.Form!NameOfControl.SetFocus

Linq ;0)>

[The Professor]

Hi,

Struggling a bit here to get the syntax right to return Focus to the SubForm from Parent Form. Just to clarify:
Code is running from an AfterUpdate event on combox control on the SubForm

name of Parent/Master Form: frmCustomers
name of SubForm Control: frmInvoice
name of Control on SubForm: txtInvoiceNum

If I try to SetFocus back on SubForm Control with...
Me.frmInvoice.SetFocus
it produces a compile error - "Method or Data Member not Found"

If I use...
Forms!frmCustomers.frmInvoice.Form.SetFocus
it produces runtime error 2449 "There is an Invalid Member in an Expression

If I use...
Me.SetFocus
again generates error 2449.

I'm a bit stuck now, because I'm not sure what syntax options I've got left to try. The dichotomy as I see it is, I'm trying to reference the SubForm from within itself. In this block of code Me. is the SubForm - isn't it? Prof.

[DarrenUD]

Hi Proff,

please check this link http://access.mvps.org/access/forms/frm0031.htm and I have attached a sample database file just made for you http://rapidshare.com/files/1404274554/Sample.accdb good luck...

[The Professor]

Hi Darren,

Many thanks for your help, much appreciated.

I've looked carefully at the Access Forms link you posted and I think I'm right (if I've understood things correctly) in saying that you can't SetFocus to the SubForm Control itself, you can only reference other Controls on it. According to that link, there does not appear to be a method to reference a SubForm directly via means of SetFocus. If you're already "on" a SubForm, you don't need to reference it anyway. This seems to highlight the dichotomy I mentioned in an earlier post. The code is running from within the SubForm, therefore I'm assuming I am actuially "on it" even though in the program the focus has switched to a Control on the Main Form.

But, even if I am somehow "on" the Main Form, you still can't SetFocus to a Subform Control - as far as I can tell - but only SetFocus to the Controls on the Subform. Now in my code above, that's what I've done with the line:

Me.txtInvoiceNum.SetFocus

This line compiles and executes without error, and is supposed to SetFocus to the text box Control txtInvoiceNum - but for some reason this isn't happening. All attempts to first SetFocus to the Subform itself, fail and produce either compilation or runtime errors.

BTW, I haven't been able to access and download the sample db on Rapidshare - it tells me I don't have permission. Prof.

[DarrenUD]

If I have understood your problem properly, my code should work well with your issue. I have changed the file download permissions and you should be able to download it now (I apologize about not setting the right file permission settings before)..

[The Professor]

Hi Darren,

Managed to down load your Sample Database (thanks for that) and I found that it does in fact duplicate my problem.

First thing I've discovered is that executing code from within the Parent/Master Form to alter the properties of a Control on the SubForm, neither transfers Focus to that SubForm nor proves that Focus has transfered to it. In your Sample DB, if your disable the line of code (simply comment it out) that sets the Focus to the Control on the SubForm, you'll see that the second line of code that changes the Control's properties, still works, yet the Focus will remain with the Parent/Master Form.

Secondly (leaving all code intact) in your Sample DB, when you click the "Child" Command Button to transfer Focus to the Control on the SubForm (code: Me!frmCustomers.Form![Customer Name].SetFocus) Focus is not actually transfered to the Control called "Customer Name" - Focus stays with the Command Button on the Parent Form.

There are a couple of visual clues that Focus hasn't actually transfered. Firstly the Command Button just clicked remains highlighted (the dotted line border). Secondly the Control "Customer Name"on the SubForm is not highlighted and it should be, nor does it have the flashing cursor. And thirdly, if after immediately clicking the Command Button to tranfer Focus to the SubForm, you then use the Tab Key to move to the next Control, you'll notice that Focus moves on the Parent/Master Form - not the SubForm - it moves from the Command Button you just clicked to the next Command Button on the Parent Form; and if you Tab again, Focus moves to the next Control on the Parent Form.

Clearly then, even though the line of code Me!frmCustomers.Form![Customer Name].SetFocus (which should transfer Focus to the SubForm) executed without error, Focus did not in fact shift to the SubForm, it stayed with the Parent/Master Form. This is exactly what is happening in my database, and I don't know what to do about it. Prof.