Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9766

Re: DLookup (subform) & GoToRecord (main form)

Show all headers | View raw

On 11/08/2014 19:07:14, croy wrote:
> On Mon, 11 Aug 2014 16:23:52 +0100, "Phil"
> <phil@stantonfamily.co.uk> wrote:
> 
>>DLookup needs 2 strings for the field to look up and the table to look up,
>>and optionally a third string for the WHERE condiion.
>>
>>Try strWhere = DLookup("DE1Id", "tblDE1Det", "[HTnum1] = " & Chr(34) &
>>Forms!frmDE1.Form!txtHTDE1LU & Chr(34))
> 
> 
> Aha!  That works for the lookup part.
> 
> I'll get back to the whole glob:
> 
> *****
> Private Sub cmdHTLU_Click()
> Dim strWhere As String
> 
> strWhere = DLookup("DE1Id", "tblDE1Det", "[HTnum1] = " &
> Chr(34) & Forms!frmDE1.Form!txtHTDE1LU & Chr(34))
> 
> With Me.RecordsetClone
> .FindFirst strWhere
> If .NoMatch Then
> MsgBox "not found"
> Else
> Me.Bookmark = .Bookmark
> End If
> End With
> 
> End Sub
> *****
> 
> 
> 
> The dLookup part works, but after that, the code throws:
> 
> "Run-time error 3001
> Invalid argument."
> 
> ...at the line .FindFirst strWhere
> 
> That error message doesn't mean a lot to me.
> 
> The goal is to move to the main form record that is parent
> to the subform record that holds the subform field-data
> being looked for (txtHTDE1LU).
> 
> What I don't see here is how the FindFirst knows what field
> it is supposed to match up to.  strWhere (it seems to me)
> supplies a number that I know to be a DE1Id number, but I
> don't get how the FindFirst knows it to be the key Id for
> that table or form.
> 
> I don't know anything about "bookmarks" either, but I'm
> about to look into that.
> 
> Thanks again for your help.
> 

You're perfectly correct.
The strWhere should be of the form 
"MyField = " & DLookup("DE1Id", "tblDE1Det", "[HTnum1] = " & Chr(34) &
Forms!frmDE1.Form!txtHTDE1LU & Chr(34))

I am assuming that the value returned by the DLookup function is a number,
not a string.

Mind, I think you may be doing things the difficult way.
Does your subform contain the correct DE1ID field and the same field in the
main form? In which case, on the OnCurrent of the Subform you need something
like Me.Parent!txtHTDE1LU.SetFocus ' (Assuming that is the same as the DE1ID
field on the subform, and I suspwecr that is a wrong assumption as one would
appear to be a text field and the other a numeric field) Me.Parent FindFirst
"txtHTDE1LU = " & DEiID

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-06 08:13 -0700
  Re: DLookup (subform) & GoToRecord (main form) JB Guernsey <jeff@angelsystems.co.uk> - 2014-08-06 09:37 -0700
    Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-06 21:23 +0100
  Re: DLookup (subform) & GoToRecord (main form) JB Guernsey <jeff@angelsystems.co.uk> - 2014-08-06 14:09 -0700
    Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-11 06:30 -0700
      Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-11 16:23 +0100
        Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-11 11:07 -0700
          Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-11 22:39 +0100
            Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-12 06:30 -0700
              Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-12 08:07 -0700
                Re: DLookup (subform) & GoToRecord (main form) "Ron Paii" <None@none.com> - 2014-08-13 08:17 -0500

csiph-web