subform problem (97)

Topic

New Reply

I

Reply | Quote

Replies

Just a quick guess but you may need to remove the word subform from the gotorecord method argument “tblNewMainCopy subform”.

Reply | Quote

I find the best way to maneuver in a subform from a mainform is via the subform recordsetclone. First of all, you need to know the name of the subform control on the mainform. The subform control name may or may not be the same as the name of the subform itself. I’ll just use subformcontrolname.

with Me.subformcontrolname.form.recordsetclone
    .findfirst "refno=" & txtrefno
    if .nomatch=true then            ' means you didn't find it
       exit sub
    end if
    Me.subformcontrolname.form.bookmark = .bookmark
end with

Reply | Quote

Hi Mark,
I tried your suggestion but the .noMatch=True criteria is set for all values I query on, whether they

Reply | Quote

Are your fields you are search against numeric or strings? This would affect how you form the FindFirst where string.

Reply | Quote

no Charlotte, If Not .NoMatch Then did not make any difference.
Mark, I only tested your code for 1 field, refNum & that comes from a query, a concatenation of 2 number fields. InvoiceNum, the one I didn

Reply | Quote

I just sort of assumed since you used the phrases “refnum” and “invoicenum” that you were dealing with numbers! You must modify your code to enclose the value of the controls in apostrophes, like this:

      invoicenum='" & txtinvoicenum & "'"

And invoicenum is a memo field? Why not a text field?

Reply | Quote

Try testing this way:

if Not .nomatch then       ' means you did find it   
    Me.subformcontrolname.form.bookmark = .bookmark
End If

Reply | Quote

Unfortunately, no Charlotte, did not make any change at all.

Reply | Quote

>>Forms!frmL60Tin!sfTin.SetFocus
DoCmd.GoToRecord , , acNewRec<<

Try this instead:

Forms!frmL60Tin!sfTin.setfocus
RunCommand acCmdRecordsGoToNew

Reply | Quote

>>Had no idea that RunCommand was also a method, thought it was only part of the DoCmd object.<<

It is actually, but the DoCmd. is implied if not specified. I don't know why your other doCmd didn't work, as I only tried the RunCommand version.

Reply | Quote

‘find the record
rst.FindFirst strSearch
Forms!frmL60Tin!sfTin.SetFocus
DoCmd.RunCommand acCmdRecordsGoToNew
Forms!frmL60Tin!sfTin.Form!txtOp.SetFocus

Assuming you put together your strSearch, then I think your problem is here. What is rst? I would do this:

set rst = me.recordsetclone
rst.findfirst strSearch
if rst.nomatch=false then
me.bookmark = rst.bookmark
end if
rst.close
set rst = nothing

Reply | Quote

I’ve gotten confused as to what you were trying to do. Going back, I see it was to move to a record on a subform. Therefore, you need to declare the recordset differently. You can’t use Me.REcordsetClone, as this is for the recordset on the mainform. You need to refer to the recordset in the subform; you do this via the subform control that is on the mainform (and not the actual subform name, although they may be the same). Let’s assume this control name is call subControl. You need this:

set rst = me!subControl.Form.recordsetclone

AFter that, everything is the same as I previously wrote.

Reply | Quote

I think you have a sequencing problem. Your did your rst.FindFirst, which finds a record in the recordsetclone. But then you move focus to the subform, etc. However, positioning yourself in the recordsetclone doesn’t do anything by itself. YOu need to do the Me.book=rst.bookmark at this time, BEFORE you move focus to the subform!

Reply | Quote