Access VB coding

[Guest]

It has been years since I wrote this to find and sort records and now I need
to modify it. The original code looks like this with key being an autonumber
field. The second example is the updated (or trying to update). What I am
trying to do is have the combo box select the records from a table
[discrepancies], and display all of the records that match.

I don't know if I can do it this way because of multiple records wit the
same name. Basically what I need is a group function on the control/form.

Thanks
rich001 at att.net

Private Sub selection_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[key] = " & Str(Nz(Me![selection], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

Private Sub Combo0_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Facility] = " & Str(Nz(Me![Combo0], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Arvin Meyer [MVP]]

Don't you want to do something like this instead? Try (aircode):

Private Sub Combo0_AfterUpdate()
Dim strSQL

strSQL = " Select * From tblAdd Where [Facility] = " & Me![Combo0]
'strSQL = " Select * From tblAdd Where [Facility] = '" & Me![Combo0] & "'"

Me.Recordsource = strSQL

End Sub

This should work as long as the bound column in the combo box. Use the first
strSQL if Facility is numeric and comment the first and uncomment and use
the second one, if it's text.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access