This worked out perfectly. Thank you.
With warm regards,
Arthur Lorenzini
Sioux Falls, South Dakota
________________________________
From: Crystal <strive4peace2008@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, March 23, 2012 6:31 PM
Subject: Re: [MS_AccessPros] Search box for a continous form
Hi Art,
in the header of almost every main form, I create one or more combos to Find a record. The display part of the combo does not have to be more than a few characters
In the unbound (no ControlSource) combos on your form , let the first column be invisible and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event, put this in the [Event Procedure] code:
'~~~~~~~~~~~~~~~~
FindRecord
'~~~~~~~~~~~~~~~~
this code goes behind the form:
'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()
'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
'declare a variable to hold the primary key value to look up
Dim nRecordID As Long
'set value to look up by what is selected
nRecordID = Me.ActiveControl
'clear the choice to find
Me.ActiveControl = Null
With Me
'find the first value that matches
.RecordsetClone.FindFirst "SomeID = " & nRecordID
'if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Function
'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers)
Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching).
For instance, if your main form is People, you could have a combo to find a person using their phone number by linking to the Phones table (assuming your data is normalized) and putting the PeopleID in the first column (hidden) and the Phone in the column that displays. In this case, I use a procedure to strip non-numeric characters so the user does not have to type parentheses or dashes since I store phone numbers with symbols. The width of this column is set tiny so it is greater than zero, but is small enough so the user does not see it -- and this is the second column. The user actually sees the third column -- the one with the mask symbols.
If you are searching the recordset on another form, change the FindRecord name to be specific (like FindRecord_Order) and, substitute
With Me --> With forms!formname
If the record you are looking for is on a subform, change the FindRecord name to be specific (like FindRecord_Whatever) and, substitute:
With Me --> with Me.subform_controlname.form
Caveat: if you show users how to filter forms, I do have a version of this function that checks for a filter if the record is not found, removes the filter, and finds the record
Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access
Warm Regards,
Crystal
Microsoft MVP
remote programming and training
*
(: have an awesome day :)
*
________________________________
From: Art <dbalorenzini@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, March 23, 2012 4:26 PM
Subject: [MS_AccessPros] Search box for a continous form
Does any on have an example of using a searchbox for a continous form. I have one on a split form but cannot figure it out how to adapt it for a continous form. Any ideas?
Thanks
Art Lorenzini
Sioux Falls, SD
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar