Setting focus to a SubDatasheet

[crich21]

problem for an expert

I found some code on this forum that works beautiful but I would like to increase it's functionality. What I have is a Vendor form with a Parts datasheet subform within it. I have a search command button that opens a search box and then allows the user to do a full search. Then the user can select one of the results which in turn sets the gets the VendorID. The problem is the code I have returns to the Vendor form and looks for the VendorID record but I need it to find the vendor record then the part record in the subdatasheet.

I have a file that is simplified to exactly what I am referring to in this problem but I can't make it small enough to post so if someone will please email me I can send it to you or you can download it at
http://www.instrumentsofwealth.com/Needhelp.zip

Anyone with some experience with a problem like this, your help will be greatly appreciated.

Thank you in advance

 

[crich21]

update

I am going to paste the code I am currently using if this will help anyone, help me solve this problem

to elaborate on the problem I need to modify this code to not only go to the record that matches the VendorID but then also goto or highlight the respective record in the Parts_subform by matching PartID. Notice that all Parts have a PartID and a VendorID related to them so I believe I should be able to do this.

Mainform is Vendors
subdatasheet is Parts_subform


This code is behind my search button on the Vendors Form


Private Sub searchcommand_Click()
On Error Resume Next
Dim lngVendorID As Long
lngVendorID = GetVendorID

If lngVendorID <> 0 Then

Dim rs As Recordset
Dim db As Database
Dim criteria As String


Set db = CurrentDb()
Set rs = Me.RecordsetClone
criteria = "[VendorID] =" & lngVendorID
rs.FindFirst criteria

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found"
End If
Me.Combo74 = Me.VendorID
Me.Refresh
End If

-----------------------------------------
this code is in a module called modsearch


Option Compare Database
Option Explicit

Public lngVendorIDSelect As Long

Public Function GetVendorID() As Long
On Error GoTo Err_GetVendorID
lngVendorIDSelect = 0
DoCmd.OpenForm "SuperSearch"
Do While lngVendorIDSelect = 0
DoEvents
Loop
If lngVendorIDSelect = -1 Then
lngVendorIDSelect = 0
End If
GetVendorID = lngVendorIDSelect
Exit Function

Err_GetVendorID:
MsgBox Err.Number
Exit Function
End Function
------------------------------------------------

the search form contains a textbox (txtSearchString) and a listbox (lstresults)

this is the code behind this form

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_btnSelect_Click

If Not IsNull(Me![lstResults].Column(0)) Then
lngVendorIDSelect = Me![lstResults].Column(0)
DoCmd.close
End If
Exit Sub

Err_btnSelect_Click:
MsgBox Err.Number
Exit Sub
End Sub

Private Sub txtSearchString_AfterUpdate()

Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me![txtSearchString]

If Not IsNull(Me![txtSearchString]) Then
strSQL = "SELECT DISTINCTROW SearchQuery.[VendorID], SearchQuery.[Part Number], SearchQuery.[Our Description], SearchQuery.[Vendor], SearchQuery.[PartID] FROM SearchQuery "
strSQL = strSQL & "WHERE ((SearchQuery.[Part Number]) Like '*" & txtSearchString & "*') OR "
strSQL = strSQL & "((SearchQuery.[Our Description]) Like '*" & txtSearchString & "*')"
strSQL = strSQL & "ORDER BY SearchQuery.[Vendor], SearchQuery.[Our Description]"
Else
strSQL = "SELECT SearchQuery.[Part Number], SearchQuery.[Our Description] FROM SearchQuery "
strSQL = strSQL & "WHERE ((SearchQuery.=[Part Number]) Is Not Null) "
strSQL = strSQL & "ORDER BY SearchQuery.[Part Number], SearchQuery.[Our Description]"
End If

Me!lstResults.RowSource = strSQL
Me!txtSearchString.SetFocus
End Sub

I hope this clarifies the question!

 

[Oldsoftboss]

Had a look at your zip. Hope the modifications I made will lead to a solution
Dave

 

[Oldsoftboss]

PS If the DB is too big to post here, try importing the relevent forms, tables etc to a new DB. Seems to drop off all the BS that Access collects when modifications are made.

 

[crich21]

I am forever grateful

I have been working on this all night and was able to come up with something similar to this but I LOVE the updating list box as you type it sure beats typing in something then having to research because that item wasn't spelled the same. Thank you so much and I hope I wasn't a bother. I have searched several sites and forums and to anyone looking for a search function this one rocks..

Thank you to OldSoftBoss,

Chad

 

[crich21]

I noticed you set the focus of the subform to part number

I tried to set the focus to PartID then I changed the column to (1) instead of (2)
but now I get an error stating that It cannot setfocus to PartID why would this be

this is only a situation because I am working with data that can have duplicate part numbers and some even have missing part numbers

Thank again
Chad

 

[Jack Cowley]

Make sure that the PartID is displayed in the list box and the search text box should work....

hth,
Jack

 

[crich21]

Thats perfect

I had the visible property to false
I corrected this and everything works perfect I really appreciate all your help this will make my job at work a lot easier.

Happy Access World Surfer,
Chad

 

[Oldsoftboss]

The next step I was going to suggest was to search by the autonumber field (PartID) but as the DB stood the field was not displayed. The only other thing I did was to display the vendor in your search form.

Glad we can help

Dave

 

[Oldsoftboss]

And included a sick joke.

Also got rid of the unneeded queries.

Dave

 

[crich21]

Question

You said you got rid of the queries. But I was using PartsQuery as a recordsource for Parts_subform to sort the partnumber column in ascending order then the Description column in ascending order. Is there another way to sort these fields everytime? the searchquery I know isn't needed anymore. But curious how to sort multiple fields without a query

p.s. what is the sick joke?

Chad

 

[Oldsoftboss]

Have a look at the record source for the subform.

PS the joke was at the start of the code on one of the forms
Dave

 

[crich21]

Cool

I from reading alot of these forums I thought you had to make a query instead of build one. That's a funny joke. I appreciate all your help....