Find Record Failure

[The Professor]

Hi All,

I'm trying to find a record using data from a Combo Box drop down list. For the most part it's working except for the actual DoCmd.FindRecord bit of the code, which is generating an error.

From a control on the SubForm, the user selects the search criteria from the Combo Box drop-down list. Then in the AfterUpdate Event, I have the following code which traps the search criteria and uses it to find the relevant record on the Main (parent) Form. These are the relevant lines of code:

Parent.CmbCustID.SetFocus
DoCmd.FindRecord lngCustNo, acEntire, False, acSearchAll, False, acCurrent, True
Me.CmbInvoiceNo.SetFocus

First line shifts focus to the Main Form


The second line finds the record (this is where the error is occurring)
The Third line shifts the focus back to the SubForm.

The error produced by the DoCmd.FindRecord method, is, " a macro set to one of the current field's properties failed because of an error in a Find Record action argument."

I've gone through the DoCmd.FindRecord statement and can find nothing wrong with it. The lngCustNo (long integer) is correct. My only concern is this - is the first line actually shifting the focus to the control on the Main (parent) Form? Or is the DoCmd.FindRecord method trying to find the criteria in field that is active at the time the Combo Box event is triggered? How can I check for sure where the focus really is at the time the DoCmd.FindRecord method is executed?

Prof.

[June7]

Normally, a main form would not be filtered by parameter selected in a subform. What you are trying seems odd. What is this form/subform arrangement? What are the forms bound to? Why isn't the unbound combobox for CustID and its code not on the main form?

You don't show code that sets the lngCustNo variable.

I've never used DoCmd.FindRecord. I build criteria string and set form Filter and FilterOn properties. Review http://www.allenbrowne.com/ser-62code.html

[The Professor]

Hi June7,
Thank you for your help, and please forgive me if some of my methods appear unorthodox. I'm not a professional programmer, nowhere near, just a self taught hobbyist and my code may not be as polished as some other peoples. I'm producing this database to help out my brother. It's purpose is to manage customer data and their invoices.

The Main Form is the Customer's form and holds all the Customer's basic information. The SubForm is the Invoice and holds all the basic info for a particular job for that customer. It's a one to many relationship, ie one customer can have many invoices (but not vice versa). Each customer has a unique CustID and this is the primary key that links the two underlying Tables. The Combo Box (you refer to) for the CustID, is on the Main Form. That's why the first line of code in my original post shifts the focus to the Main Form having acquired the information I need (invoice number) from the SubForm. The whole procedure (which is intended to goto a particular Invoice) is run from the SubForm. This is the whole procedure:

Private Sub cmbInvoiceNo_AfterUpdate()
On Error GoTo Err_cmbInvNo

'Jump to a particular Invoice Number

Dim lngInvNo As Long
Dim lngCustNo As Long

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

'shift focus to Customer Form to goto the correct CustID
Parent.CmbCustID.SetFocus
DoCmd.FindRecord lngCustNo, acEntire, False, acSearchAll, False, acCurrent, True
Me.CmbInvoiceNo.SetFocus

Exit_cmbInvNo:
Exit Sub
Err_cmbInvNo:
MsgBox Err.Description
Resume Exit_cmbInvNo

End Sub

Now if I'm going about this all the wrong way, I'm certainly happy to listen to advice on a better or easier way of doing it, particularly as my way isn't working.

Prof.

[June7]

The main form and subform should be synchronized by the Master/Child Link properties of the subform/subreport container control. This means the subform can show only those invoices that are related to the customer record of the main form. You seem to have this correctly set up.

If you are trying to find an invoice for another customer, then must filter the main form for the customer that belongs to the invoice. Your code appears to attempt that.

You may be correct that the issue is due to jumping between main and sub forms and the search is not being done on the correct field.

Could try unbound combobox on main form with all the invoices listed. Wherever the combobox is, include the CustID in another column and the DLookup() is not needed, just refer to the column by its index to get the CustID.

Is the subform in continuous or single view?

Step debug the code. Refer to link at bottom of my post for debugging guidelines.

[burrina]

Try setting focus to the form first.

[The Professor]

Hi June7,

Very helpful, appreciate your time and I'll look careful at what you've said.
I'll let you know how I get on.

Prof.

[The Professor]

Hi Burrina,

As far as I know (albeit I accept my knowledge is limited) there is no code that will allow you to shift focus to the underlying Form itself, but only to a Control on a Form.

Prof.

[burrina]

Forms!YourFormNameHere.SetFocus

Good Luck . . .

[June7]

I tested the code and the search works.

Setting focus back to the subform did not.

Burrina is correct, must set focus to subform container first then the control. Need both the following lines.

Parent.subformcontainername.Setfocus
Me.controlname.setfocus

However, still makes more sense to me to have the unbound combobox to select invoice on the main form.

[The Professor]

Hi there,

I've finally done it - at last, and it seems solid.

I took your advice June, and moved the find invoice number control onto the Customers Form.

I did initially try Filtering, but this resulted in some undesirable side-effects, one of which was, instead of simply going to a specified Record, it limited the Form to just that one Record and when I tried to remove the Filter the Form just reverted straight back to the First Record in the Table.

The Solution I eventually settled on involved creating a dialog box, activated by a Button (on the Main Customers Form) that asked for user input, and from this I obtained the search criteria. I also found that the easiest and most useful method for me was DoCmd.FindRecord - sorry, but it just works so well. The earlier problem I had with this method outlined in my original post, resulted in trying to use DoCmd.FindRecord on the unbound combo box, rather than the actual field control - duh!

The other great breakthrough (that I would never have sussed out without your help, and that of Burrina) was understanding the difference between addressing the Sub Form Container Control, and the Sub Form itself. Properly understanding this helped immensely, as finally getting it right solved a lot of niggly problems. Thanks indeed.

Prof.