If you see my first response to your last post, it explains how the fields
are set up and why. How will I need to change the code to make it work with
what I have? I have the SequenceNumber set up as a text field.
If you didn't see it, here's my first response to your last post:
"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."
Beetle said:
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
bbypookins said:
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
