Form selection criteria acting independently

[Guest]

I am writing a program for a form so that someone can go back and correct
erronous data. I want to be able to select on a date and then a specific
machine. It appears the two selection criteria are acting independently ( I
want to narrow it down to a day and then a specific machine on that day, an
"AND" function I believe.) Below is the VBA code if that helps. (I am
working with ACCESS not VBA)

Option Compare Database

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Date] = #" & Format(Me![Combo10], "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MachineDesignator] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks.

Phil

 

[George Nicholson]

Something like:

Private Sub Combo10_AfterUpdate()
Call FindRecord
End Sub

Private Sub Combo12_AfterUpdate()
Call FindRecord
End Sub

Private Sub FindRecord()
' Find the record that matches the controls
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MachineDesignator] = '" & nz(Me![Combo12],"") & "' AND
[Date] = #" & Format(nz(Me![Combo10],0), "mm\/dd\/yyyy") & "#"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I am working with ACCESS not VBA

Hate to be the one to tell you, but this is VBA. Welcome to the club.

I added the NZ(aka Null-To-Zero) functions to (hopefully) avoid errors if
either of the combo boxes are Null (as they might be following start up).
Definetly needed if the Date field is Null, probably not for the other.

BTW, Date is a reserved word. Using it for your own purpose can potentially
cause all sorts of problems. You are much safer in the long run if you use
something else for a field name.

HTH,

I am writing a program for a form so that someone can go back and correct
erronous data. I want to be able to select on a date and then a specific
machine. It appears the two selection criteria are acting independently
( I
want to narrow it down to a day and then a specific machine on that day,
an
"AND" function I believe.) Below is the VBA code if that helps. (I am
working with ACCESS not VBA)

Option Compare Database

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Date] = #" & Format(Me![Combo10], "mm\/dd\/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MachineDesignator] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks.

Phil