how to search two fields in form

[bbypookins]

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:

 

[Beetle]

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:

Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

I think I"m still confused. But, they will always know what combination they
will be searching for.

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:

Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Beetle]

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey

I think I"m still confused. But, they will always know what combination they
will be searching for.

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is the
most important thing.

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey

I think I"m still confused. But, they will always know what combination they
will be searching for.

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Beetle]

OK. This is just an example with control names that I've made up, you can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table are
of the text data type. If not, some of the qoutes will need to be removed.

--
_________

Sean Bailey

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is the
most important thing.

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey

I think I"m still confused. But, they will always know what combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

I'm getting an error on this code. I had the underscore after the ampersand
but it said it was an invalid character so I took it out. It's still not
working.

..FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

OK. This is just an example with control names that I've made up, you can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table are
of the text data type. If not, some of the qoutes will need to be removed.

--
_________

Sean Bailey

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is the
most important thing.

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Evi]

The email has obligingly broken this up but the bit of code you quoted needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the ampersand
but it said it was an invalid character so I took it out. It's still not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

OK. This is just an example with control names that I've made up, you can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table are
of the text data type. If not, some of the qoutes will need to be removed.

--
_________

Sean Bailey

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

In VBA, it is all on one line.

The email has obligingly broken this up but the bit of code you quoted needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the ampersand
but it said it was an invalid character so I took it out. It's still not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

OK. This is just an example with control names that I've made up, you can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table are
of the text data type. If not, some of the qoutes will need to be removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure this out for
some time, so I'll try to clarify why I think a combo box *might* not
be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values that
are displayed in the combo box. The row source can be either a value list
that you define, or a query that pulls values from a table. In your case it
would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the first
visible column). That's probably no big deal, but your users would also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might be a little
easier just to have two unbound text boxes where they could enter the
division and sequence number, and then have your code run the search.
You could have a command button that they could click or you could just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value in the combo
box. You shouldn't have to do anything else for the search to run. However,
before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and sequence number
they want to search for? (in which case it may be easier to use two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination of two fields.
The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search once it is in
there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you want to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND [Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Evi]

Is SeqNumber text or number?
Evi

In VBA, it is all on one line.

The email has obligingly broken this up but the bit of code you quoted needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the ampersand
but it said it was an invalid character so I took it out. It's still not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,

you
can

change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could

just
copy

and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the

table
are

of the text data type. If not, some of the qoutes will need to be removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and

that is
the

most important thing.

:

Sorry for the confusion. I know you've been trying to figure

this
out for

some time, so I'll try to clarify why I think a combo box

*might*
not

be the best option in this case (I say might, because there is always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the

values
that

are displayed in the combo box. The row source can be either a

value
list

that you define, or a query that pulls values from a table. In

your
case it

would
be a query that pulls the existing division and sequence numbers from your
table.

So let's say you create a combo box with a row source query that shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division

(the
first

visible column). That's probably no big deal, but your users

would
also

not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still have to
click the row with the appropriate sequence number from the remaining
choices.

So if your users are always going to know both values it might

be a
little

easier just to have two unbound text boxes where they could

enter
the

division and sequence number, and then have your code run the search.
You could have a command button that they could click or you

could
just

run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what combination they
will be searching for.

:

The code Dave provided should fire whenever you select a

value
in the combo

box. You shouldn't have to do anything else for the search

to
run. However,

before trying to solve your combo box issues, I read some of your previous
posts and have a question.

Will you users always know in advance the division and

sequence
number

they want to search for? (in which case it may be easier to

use
two unbound

text boxes and a command button)

Or do you want to present them with a list to choose from?

(in
which case the

combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a

combination
of two fields.

The fields are "Division" and "SeqNumber". Below is the instruction and code
Dave Hargis gave me, but I don't understand how to search

once
it is in

there. The only way I know how to search a form is to do a ctrl+F , indicate
select the field you want to search and type in what you

want
to find. How

would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & "

AND
[Division] = '" &

Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

It's a number. But I can change it if need be.

Is SeqNumber text or number?
Evi

In VBA, it is all on one line.

The email has obligingly broken this up but the bit of code you quoted needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up, you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just
copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure this
out for
some time, so I'll try to clarify why I think a combo box *might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values
that
are displayed in the combo box. The row source can be either a value
list
that you define, or a query that pulls values from a table. In your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the
first
visible column). That's probably no big deal, but your users would
also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might be a
little
easier just to have two unbound text boxes where they could enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value
in the combo
box. You shouldn't have to do anything else for the search to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and sequence
number
they want to search for? (in which case it may be easier to use
two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in
which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination
of two fields.
The fields are "Division" and "SeqNumber". Below is the
instruction and code
Dave Hargis gave me, but I don't understand how to search once
it is in
there. The only way I know how to search a form is to do a
ctrl+F , indicate
select the field you want to search and type in what you want
to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the
sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND
[Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Beetle]

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey

It's a number. But I can change it if need be.

Is SeqNumber text or number?
Evi

In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up, you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" &_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could just
copy
and paste the code if you want, but it might still need a little more
adjustment.
Also, I've assumed that your Division and Sequence fields in the table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a command
button. It seems to me that would be easier for the user...and that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure this
out for
some time, so I'll try to clarify why I think a combo box *might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the values
that
are displayed in the combo box. The row source can be either a value
list
that you define, or a query that pulls values from a table. In your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In it's
unexpanded state, your combo box will only show the division (the
first
visible column). That's probably no big deal, but your users would
also
not be able to type a sequence number into the combo box. They could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might be a
little
easier just to have two unbound text boxes where they could enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a value
in the combo
box. You shouldn't have to do anything else for the search to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and sequence
number
they want to search for? (in which case it may be easier to use
two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from? (in
which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a combination
of two fields.
The fields are "Division" and "SeqNumber". Below is the
instruction and code
Dave Hargis gave me, but I don't understand how to search once
it is in
there. The only way I know how to search a form is to do a
ctrl+F , indicate
select the field you want to search and type in what you want
to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the
sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & " AND
[Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey

It's a number. But I can change it if need be.

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a
value
in the combo
box. You shouldn't have to do anything else for the search
to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and
sequence
number
they want to search for? (in which case it may be easier to
use
two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from?
(in
which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a
combination
of two fields.
The fields are "Division" and "SeqNumber". Below is the
instruction and code
Dave Hargis gave me, but I don't understand how to search
once
it is in
there. The only way I know how to search a form is to do a
ctrl+F , indicate
select the field you want to search and type in what you
want
to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the
sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & "
AND
[Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Beetle]

OK, I threw together a quick db based on what I know about yours.

My table has the following fields;

Division - (data type of text)
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a
value
in the combo
box. You shouldn't have to do anything else for the search
to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and
sequence
number
they want to search for? (in which case it may be easier to
use
two unbound
text boxes and a command button)

Or do you want to present them with a list to choose from?
(in
which case the
combo box method would be better)
--
_________

Sean Bailey


:

I need my users to be able to search a form for a
combination
of two fields.
The fields are "Division" and "SeqNumber". Below is the
instruction and code
Dave Hargis gave me, but I don't understand how to search
once
it is in
there. The only way I know how to search a form is to do a
ctrl+F , indicate
select the field you want to search and type in what you
want
to find. How
would this new field work? I'm confused.

From Dave:
Now, in your case, you need to find the division and the
sequence number.
You will need to search using both of those values.

Let's call it cboFindRecord

Private Sub cboFindRecord_AfterUpdate
With Me.RecorsetClone
.FindFirst "[SeqNumber] = " & Me.SeqNumber & "
AND
[Division] = '" &
Me.Division & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[bbypookins]

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?

Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a
value
in the combo
box. You shouldn't have to do anything else for the search
to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and
sequence

 

[Beetle]

A month ago, when I asked if you wanted to use the combo box method
or try using two unbound text boxes, you said you wanted to try the two
unbound text boxes. Now you're saying you are still using the combo box.

Will it cause problems? Maybe. Maybe not. I was under the impression
that you are trying to search for records. If that is the case, then you
should not have anything in the control source of the combo box. If you
do, then it will change the value in the source table when you change
the value in the combo box.

As far as the code goes, it would still work with the combo box as long as
you use the correct combo box name and the column within the combo
that holds the Division value is the bound column.

Of course, this is all based on the presumption that you are trying to search
for records that match a given Division and Sequence number. If that is not
what you are trying to do, then this and all my previous posts are basically
irrelevant.

--
_________

Sean Bailey

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?

Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

:

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and
we can try to get it working for you.

--
_________

Sean Bailey


:

I think I"m still confused. But, they will always know what
combination they
will be searching for.

:

The code Dave provided should fire whenever you select a
value
in the combo
box. You shouldn't have to do anything else for the search
to
run. However,
before trying to solve your combo box issues, I read some of
your previous
posts and have a question.

Will you users always know in advance the division and

 

[bbypookins]

I think the confusion is in the two separate fields...one for entering data
and one for searching. The one for enterting data in the detail of the form
is a combo box so that the choice of our seven divisions is listed in a
pull-down menu. I don't, however, want the control in the header that they
are using for searching to be a combo box...although, now that I think about
it, it probably wouldn't really matter if it was.

So, the way I've set it up is that the control in the detail of the form
called "Division" has a Control Source of "Division", the field in the table,
and a Row Source of "qryDivision." The control "txtDivision" in the header
is unbound and has no control source. It is a text field.

I'm apologize for my ignorance, but for some reason, Access really throws me
for a loop.

A month ago, when I asked if you wanted to use the combo box method
or try using two unbound text boxes, you said you wanted to try the two
unbound text boxes. Now you're saying you are still using the combo box.

Will it cause problems? Maybe. Maybe not. I was under the impression
that you are trying to search for records. If that is the case, then you
should not have anything in the control source of the combo box. If you
do, then it will change the value in the source table when you change
the value in the combo box.

As far as the code goes, it would still work with the combo box as long as
you use the correct combo box name and the column within the combo
that holds the Division value is the bound column.

Of course, this is all based on the presumption that you are trying to search
for records that match a given Division and Sequence number. If that is not
what you are trying to do, then this and all my previous posts are basically
irrelevant.

--
_________

Sean Bailey

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?

Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey


:

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

:

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and

 

[bbypookins]

A month ago, when I asked if you wanted to use the combo box method
or try using two unbound text boxes, you said you wanted to try the two
unbound text boxes. Now you're saying you are still using the combo box.

Will it cause problems? Maybe. Maybe not. I was under the impression
that you are trying to search for records. If that is the case, then you
should not have anything in the control source of the combo box. If you
do, then it will change the value in the source table when you change
the value in the combo box.

As far as the code goes, it would still work with the combo box as long as
you use the correct combo box name and the column within the combo
that holds the Division value is the bound column.

Of course, this is all based on the presumption that you are trying to search
for records that match a given Division and Sequence number. If that is not
what you are trying to do, then this and all my previous posts are basically
irrelevant.

--
_________

Sean Bailey

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?

Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey


:

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

:

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and

 

[bbypookins]

I put the code in and tried to do a search. Typed ITS in the division
control, typed 002 in the sequence control and then clicked the button and i
got this error: Data type mismatch in criteria expression.

A month ago, when I asked if you wanted to use the combo box method
or try using two unbound text boxes, you said you wanted to try the two
unbound text boxes. Now you're saying you are still using the combo box.

Will it cause problems? Maybe. Maybe not. I was under the impression
that you are trying to search for records. If that is the case, then you
should not have anything in the control source of the combo box. If you
do, then it will change the value in the source table when you change
the value in the combo box.

As far as the code goes, it would still work with the combo box as long as
you use the correct combo box name and the column within the combo
that holds the Division value is the bound column.

Of course, this is all based on the presumption that you are trying to search
for records that match a given Division and Sequence number. If that is not
what you are trying to do, then this and all my previous posts are basically
irrelevant.

--
_________

Sean Bailey

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?

Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey


:

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

:

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you
could
just
run the code in the after update event of one of the text boxes.

If you have a preference of one method over the other, post back
and

 

[Beetle]

The code example I posted is based on both the Division and Sequence
fields being a text data type (in the table). If both fields are not a text
data type in your table, then either the code will need to be modified
slightly, or the data type will need to be changed. As stated previously,
I used a text data type for the sequence number because you appear
to want to have leading zeros
--
_________

Sean Bailey

I put the code in and tried to do a search. Typed ITS in the division
control, typed 002 in the sequence control and then clicked the button and i
got this error: Data type mismatch in criteria expression.

A month ago, when I asked if you wanted to use the combo box method
or try using two unbound text boxes, you said you wanted to try the two
unbound text boxes. Now you're saying you are still using the combo box.

Will it cause problems? Maybe. Maybe not. I was under the impression
that you are trying to search for records. If that is the case, then you
should not have anything in the control source of the combo box. If you
do, then it will change the value in the source table when you change
the value in the combo box.

As far as the code goes, it would still work with the combo box as long as
you use the correct combo box name and the column within the combo
that holds the Division value is the bound column.

Of course, this is all based on the presumption that you are trying to search
for records that match a given Division and Sequence number. If that is not
what you are trying to do, then this and all my previous posts are basically
irrelevant.

--
_________

Sean Bailey

My Division control in the form is actually a combo box and the Control
Source is tblDivision and row source is qryDivision. Is that going to cause
any problems?


Division - (data type of text) I have this set up as a combo box
SeqNumber - (data type of text)

I made the SeqNumber a text data type because you appear to want to
use leading zero's

My table has the following data;

Division SeqNumber
FA 001
FA 002
FA 003
EXE 001
EXE 002
EXE 003

My form has the following controls;

txtDivision - (text box bound to Division)
txtSeqNumber - (text box bound to SeqNumber)
txtDivisionSearch - (unbound text box in the form header)
txtSequenceSearch - (unbound text box in the form header)
cmdRunSearch - (command button in the form header)

Here is the code behind my command button (On Click event);

Private Sub cmdRunSearch_Click()
On Error GoTo HandleError

If Nz(Me.txtDivisionSearch, "") = "" Then
MsgBox "Please enter a Division to search for"
Me.txtDivisionSearch.SetFocus
Exit Sub
End If

If Nz(Me.txtSequenceSearch, "") = "" Then
MsgBox "Please enter a Sequence Number to search for"
Me.txtSequenceSearch.SetFocus
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Division]=""" & Me.txtDivisionSearch & """" & _
" AND [SeqNumber]=""" & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record found"
End If
End With

Exit_cmdRunSearch_Click:
Exit Sub

HandleError:
MsgBox Err.Description
Resume Exit_cmdRunSearch_Click

End Sub

The above code works exactly as it is supposed to. As long as your field and
control names are the same, and your Division and SeqNumber fields are
a text data type, then you should be able to copy the above code sample
exactly as is, paste it into the code window for your form, do a debug/compile
and it should work. You will want to remove any existing code behind your
Search button first, and you may need to open the properties sheet for
your command button, got to the On Click event, and select the code builder
to sort of "re-link" the new code to the button.

As always - !MAKE A BACKUP! before you do any of the above.

--
_________

Sean Bailey


:

Yes, the field SeqNumber in the original table is a data type Number, long
integer.

I put this code in and I'm getting another error. Here's exactly what I did.
I tried to do a search by typing FIN in the DivisionSearch field and 002 in
the SequenceSearch field and then clicking the cmdRunSearch button and the
following error popped up: Runtime error 438. Object doesn't support this
property or method.

:

Just to clarify, when we say "is it a number?", we are talking about the
actual data type of the underlying field in the table. In other words, you
can enter a number in a text field, but Access still views it as text. So,
if both fields are a number data type, then the code should look like;

.FindFirst "[Division] = " & Me.txtDivisionSearch & " AND [SeqNumber] = " &
Me.txtSequenceSearch

(put it all on one line in your code window)

Post back if you have more questions.

--
_________

Sean Bailey


:

It's a number. But I can change it if need be.

:

Is SeqNumber text or number?
Evi
In VBA, it is all on one line.

:

The email has obligingly broken this up but the bit of code you quoted
needs
to be 1 long line
Evi

I'm getting an error on this code. I had the underscore after the
ampersand
but it said it was an invalid character so I took it out. It's still
not
working.

.FindFirst "[Division] = """ & Me.txtDivisionSearch & """" & " AND
[SeqNumber] = """ & Me.txtSequenceSearch & """"

:

OK. This is just an example with control names that I've made up,
you
can
change the names to whatever you want. If you change the names you
will also need to change the naming in the code.

Create two new unbound text boxes in, say, the header of your form
named
txtDivisionSearch and txtSequenceSearch, plus a command button named
cmdRunSearch. When you add the command button, the command button
wizard will pop up, just hit cancel to get rid of it.

Open the properties sheet for the command button, go to the events
tab/
On Click event and click the elipse to the right, then select code
builder.
This should open the VBA window with the following lines displayed;

Private Sub cmdRunSearch_Click()

End Sub

In between those lines put the following code (or something similar)

If IsNull(Me.txtDivisionSearch) Then
Msgbox "Please enter a Division"
Exit Sub
ElseIf IsNull(Me.txtSequenceSearch) Then
Msgbox "Please enter a Sequence number"
Exit Sub
End If

With Me.RecorsetClone
.FindFirst "[Division] = """ & Me.txtDivisionSearch & """"
&_
" AND [SeqNumber] = """ & Me.txtSequenceSearch & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


I've tried to adjust for line wrap in this response so you could
just
copy
and paste the code if you want, but it might still need a little
more
adjustment.
Also, I've assumed that your Division and Sequence fields in the
table
are
of the text data type. If not, some of the qoutes will need to be
removed.

--
_________

Sean Bailey


:

I think your idea sounds good and would probably want to use a
command
button. It seems to me that would be easier for the user...and
that is
the
most important thing.

:

Sorry for the confusion. I know you've been trying to figure
this
out for
some time, so I'll try to clarify why I think a combo box
*might*
not
be the best option in this case (I say might, because there is
always the
possibility that I'm wrong, wouldn't be the first time).

A combo box has a row source property, which determines the
values
that
are displayed in the combo box. The row source can be either a
value
list
that you define, or a query that pulls values from a table. In
your
case it
would
be a query that pulls the existing division and sequence numbers
from your
table.

So let's say you create a combo box with a row source query that
shows
the division and sequence numbers in two separate columns. In
it's
unexpanded state, your combo box will only show the division
(the
first
visible column). That's probably no big deal, but your users
would
also
not be able to type a sequence number into the combo box. They
could
type a division to narrow the results, but then they would still
have to
click the row with the appropriate sequence number from the
remaining
choices.

So if your users are always going to know both values it might
be a
little
easier just to have two unbound text boxes where they could
enter
the
division and sequence number, and then have your code run the
search.
You could have a command button that they could click or you