Form selection criteria acting independently

P

Phil

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
 
G

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,


Phil said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top