Command button issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 97. I have a form that has a part number field and a
revision field that are populated from a table where I enter the data. The
part number could be listed several times with different revisions. I would
like a commad button to ask "Part Number" and "Revision" and then find that
record in the form. Sorry if this isn't detailed enough, but I'm not sure
what else I would need to tell you. Thanks in advance for any and all help.
 
The example below shows how to build the code to go into the command button
on your form. It assumes there are 2 unbound text boxes where the user
enters the part number and the revision. These unbound boxes are named
txtFindPartNumber and txtFindRevision. I like to put these and the command
button (cmdFindPart) into the Form Header section, so they are visibly
separated from the bound controls on the form.

The code assumes the fields in your table are named PartNumber and Revision,
and that both are Text fields. If they are Number fields, lose the extra
quotes, e.g.:
strWhere = strWhere & "([Revision] = " & Me.txtFindRevision & ") AND "

The code is crafted so that you can easily extend the technique to include
more search boxes if desired.

It saves the record first. Then it builds the search string from whichever
boxes the user typed in. It locates the record in the RecordsetClone of the
form. If found, it sets the form to that bookmark which shows the record. If
not found, you get a MsgBox.

Private Sub cmdFindPart_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save first
Me.Dirty = False
End If

If Not IsNull(Me.txtFindPartNumber) Then
strWhere = strWhere & "([PartNumber] = """ & Me.txtFindPartNumber &
""") AND "
End If

If Not IsNull(Me.txtFindRevision) Then
strWhere = strWhere & "([Revision] = """ & Me.txtFindRevision & """)
AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Find what?"
Else
strWhere = Left$(strWhere, lngLen)
'Find the record in the form's clone set
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
'Show the record in the form
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
 
Has the origianl poster considered doing this with a combo box? If I
understand her task, it seems like it would do the job more simply and
it's something she could create in just a few minutes.

Just a thought...

Not that I don't always admire Rick's advice. Your solutions are
always very interesting to me and ususally right to the point of the
job.
 
A combo would make sense unless there are too many parts (tens of
thousands). Even then, you could delay-load the combo if desired, like this:
http://allenbrowne.com/ser-32.html

Of couse, the code to filter or find is essentially the same whether the
controls are text boxes or combos.
 
Thank you Allen. In the later two posts, you are the correct one. I have too
many parts to use a combo box. The find would take too long to search and
load. Thanks again for all your help.

Allen Browne said:
The example below shows how to build the code to go into the command button
on your form. It assumes there are 2 unbound text boxes where the user
enters the part number and the revision. These unbound boxes are named
txtFindPartNumber and txtFindRevision. I like to put these and the command
button (cmdFindPart) into the Form Header section, so they are visibly
separated from the bound controls on the form.

The code assumes the fields in your table are named PartNumber and Revision,
and that both are Text fields. If they are Number fields, lose the extra
quotes, e.g.:
strWhere = strWhere & "([Revision] = " & Me.txtFindRevision & ") AND "

The code is crafted so that you can easily extend the technique to include
more search boxes if desired.

It saves the record first. Then it builds the search string from whichever
boxes the user typed in. It locates the record in the RecordsetClone of the
form. If found, it sets the form to that bookmark which shows the record. If
not found, you get a MsgBox.

Private Sub cmdFindPart_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save first
Me.Dirty = False
End If

If Not IsNull(Me.txtFindPartNumber) Then
strWhere = strWhere & "([PartNumber] = """ & Me.txtFindPartNumber &
""") AND "
End If

If Not IsNull(Me.txtFindRevision) Then
strWhere = strWhere & "([Revision] = """ & Me.txtFindRevision & """)
AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Find what?"
Else
strWhere = Left$(strWhere, lngLen)
'Find the record in the form's clone set
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
'Show the record in the form
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sheila said:
I am using Access 97. I have a form that has a part number field and a
revision field that are populated from a table where I enter the data. The
part number could be listed several times with different revisions. I
would
like a commad button to ask "Part Number" and "Revision" and then find
that
record in the form. Sorry if this isn't detailed enough, but I'm not sure
what else I would need to tell you. Thanks in advance for any and all
help.
Click to expand...
Click to expand...
 
Back
Top