Using Unbound Text box to scroll through Subform HOW? 1

[james33]

Dear All,
I have a
Mainform: REPAIRSMAINFRM
With a subform: REPAIRSSFRM
Whose record source is a Table: REPAIRSX
On the Mainform I have an unbound Text box called TEXTLOOKUP
I am trying to use it as a way of navigating through records in REPAIRSSFRM which is a Datasheet, by using the AFTERUPDATE event to set the focus on the subform REPAIRSSFRM to the first record that agrees with the text the user has typed into TEXTLOOKUP.
I got the following code in thread702-1176848 from HandsOnAccess and adapted it to my circumstances but it doesn't seem to work and as HandsOnAccess has unmarked that thread (having answered the original question to another person), I am posting my question as a new thread.

Private Sub TEXTLOOKUP_AfterUpdate()
   If Not IsNull(me.TEXTLOOKUP) = True and Len(Trim(me.TEXTLOOKUP)) > 0 Then
      If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")) = True Then
         me.REPAIRSSFRM = Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")
      Else
         Msgbox "Last Name Not Found.", VbCritical,"User Alert"
         Exit Sub
      Endif
   Endif
End Sub

I would prefer it to scroll down the subform as I add the characters to TEXTLOOKUP.
I appreciate any input as I have got a little rusty!
Thanks in advance.

Jim

 

[HandsOnAccess]

REPAIRSSFRM is a subform
You cannot assign me.REPAIRSSFRM directly to DLookUp!
Try to implement this way...

Private Sub TEXTLOOKUP_AfterUpdate()
   If Not IsNull(me.TEXTLOOKUP) = True and Len(Trim(me.TEXTLOOKUP)) > 0 Then
      If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")) = True Then
          Dim rs As Object
          Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone
          rs.FindFirst "[CUSTID] = " & Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")
          Me.REPAIRSSFRM.Form.Bookmark = rs.Bookmark
      Else
         Msgbox "Last Name Not Found.", VbCritical,"User Alert"
         Exit Sub
      Endif
   Endif
End Sub

Hope this helps you..
Regards,

 

[james33]

Thanks HandsOnAccess,

Unfortunately it comes up with this in the VB editor:

Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone

Compile Error
Method or Data member not found

Regards
Jim

 

[HandsOnAccess]

I assume you have placed this code in REPAIRSMAINFRM Form

I assume that you have a subform named REPAIRSSFRM placed in REPAIRSMAINFRM Form

I have created a dummy form and subform with same name and same code with sample data, Its working fine here...

Regards,

 

[james33]

Dear HandsOnAccess,
Thanks so much
I got it to work eventually, by swapping around Tables and I realised that I had not mentioned that I had 6 Fields in the form & not the 2 you had included.

I would be really pleased if you could explain the function of the following lines as I want to extend (my knowledge) and the Form to include all the fields.

 If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")) = True Then
          Dim rs As Object
          Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone
          rs.FindFirst "[CUSTID] = " & Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")
          Me.REPAIRSSFRM.Form.Bookmark = rs.Bookmark

I understand the avoidance of nulls and spaces and trimming the data and I am a 'little' (but not ever so) familiar with the idea of creating a cloned recordset which can be searched and the result applied to the form.

Furthermore I also have a problem, in that in Ireland (where I live) there is a fairly large section of the population who have names like O'Flaherty or O'Dowd and these do throw up a

Run_time error '3075'
Syntax error (missing operator)
in query expression '[Last_Name]= O'Beirne

with this code

I would also be REALLY interested (as I mentioned earlier) in getting the focus to scroll down the subform as I add the characters to TEXTLOOKUP.

Is that possible working from the above code.

I am really grateful for the time you have devoted already you have certainly earned the Star!

Thanks
Jim

 

[HandsOnAccess]

Code Explination

If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")) = True Then
'Check if CustID exist for that particular TextLookUp
'This is used so that rs.FindFirst does not fail

Dim rs As Object
'Declaration

Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone
'Create an exact replica of the recordset
'Clone = Exact Replica

rs.FindFirst "[CUSTID] = " & Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = '" & me.TEXTLOOKUP & "'")
'Search and bookmark the location of the desired record in the cloned recordset.

Me.REPAIRSSFRM.Form.Bookmark = rs.Bookmark
'Moves to desired record by using bookmark location of cloned recordset.
___________________________________

Code to Overcome Run_time error '3075'
Syntax error (missing operator) in query expression '[Last_Name]= O'Beirne

If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = [Forms]![REPAIRSMAINFRM].TEXTLOOKUP")) = True Then
          Dim rs As Object
          Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone
          rs.FindFirst "[CUSTID] = " & Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = [Forms]![REPAIRSMAINFRM].TEXTLOOKUP")
          Me.REPAIRSSFRM.Form.Bookmark = rs.Bookmark

or

If Not IsNull(Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = """ & me.TEXTLOOKUP & """")) = True Then
          Dim rs As Object
          Set rs = Me.REPAIRSSFRM.Form.Recordset.Clone
          rs.FindFirst "[CUSTID] = " & Dlookup("CUSTID","REPAIRSX","[LAST_NAME] = """ & me.TEXTLOOKUP & """")
          Me.REPAIRSSFRM.Form.Bookmark = rs.Bookmark

________________

Lastly, as for your request-
"I would also be REALLY interested (as I mentioned earlier) in getting the focus to scroll down the subform as I add the characters to TEXTLOOKUP."

This needs extra coding and is slightly different.
I will try to work on it tomorrow.

Hope this helps you...
Regards,

 

[james33]

Thank you HandsOnAccess,

That is great, the second version (which I tried first) works brilliantly.
The first does too, is the first one a tiny bit quicker it does seem to be a fraction?
The difference I can see is in the """ is that a kind of shorthand?

The explanation is taking my knowledge of VB to new heights wow!
I had done a fair bit in Access 97 but I think a lot of it is implemented a little differently in 2000 and 2003.
Thank you very much.

Thank you very much.

Jim