Find button to locate record in form and display that record

Find button to locate record in form and display that record

Post by eslee » Sat, 13 Oct 2007 02:18:52


Hi,

I have a unbound text field. I want to add a find button that when the
user clicks, take the value typed in the unbound text field and
searches all my forms. When it locates this unique ID in one of the
forms, it opens up the form and displays that specific record.

The unbound text field is called searchID
The button is called clickFind

Any help would be great!
 
 
 

Find button to locate record in form and display that record

Post by fred » Sat, 13 Oct 2007 02:39:33


I would suggest an alternative approach as remembering an ID value is
not what people do best. People remember words, i.e. Names, Company
Names, etc. We know we wish to find records for Ford Motor Co. but who
can remember that their ID number is 83162.

Add an unbound combo box to the Form Header.
If you use the Combo Box Wizard for this, select the 3rd option on the
first set of questions, something like "Find the record ... etc."
Continue on, including the RecordID field as well as the associated
text name field. Hide the ID field.

When done, start entering the word value. The combo will search ahead
as you type and display all those values. When you find the correct
one, select it. That record will be displayed on the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

 
 
 

Find button to locate record in form and display that record

Post by eslee » Sat, 13 Oct 2007 06:35:13


Hi,

Perhaps I didn't do a good job explaining...

I have a form (call this Aform) which lists results from a query; but
I also want to add a find option to the form so that the user can
just enter in the unique ID ( which is display on the result form).
When the user clicks find, it searches 6 other different types of
forms (more detail information on these forms). If it finds the ID, it
opens the new form (Bform) and displays that record on Bform.

I tried DoCmd.OpenForm but I couldn't get it to work.

Thanks
 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Thu, 18 Oct 2007 01:40:43


This is a very interesting approach and I would like to be able to
utilize it. I have added the unbound Combo box to the header as
suggested. The combo box populates but nothing shows up in the form.
Any further thoughts?
 
 
 

Find button to locate record in form and display that record

Post by fred » Thu, 18 Oct 2007 02:05:24


I need some help from you with this.
What is the actual Rowsource of the Combo box? (Copy and Paste it)
What is the Combo box Bound Column number?
What is the Combo Box Column Count?
What is the Combo Box Column Width property?
What is the Combo Box Control source? It should be blank.
What is the code behind the Combo Box's AfterUpdate event? (Copy and
Paste it).

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Thu, 18 Oct 2007 02:21:23


Thanks for response. Following are answers in same order.

1) SELECT qryMSDSdata.ProdName FROM qryMSDSdata;
2) 1
3) 1
4) 3.4063
5) BLANK
6) Private Sub Combo77_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProdName] = '" & Me![Combo77] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
 
 

Find button to locate record in form and display that record

Post by fred » Thu, 18 Oct 2007 03:39:55


*** snipped ***


You are generating just one column in the rowsource; it's showing 1
column and bound to that column. I'll assume that the column width is
in inches 3.0463". The AfterUpdate code looks good. This control
should not be bound to any field in the table.

All of the above looks OK.
When you select one of the rows in the combo box, that record should
be displayed on the form.

1) Are you absolutely sure that the value in the row selected actually
exists in the form's recordsource.

2) Are you sure that the datatype of the bound column in the Combo Box
is the same as the Table field's datatype (your code shows it's text).
In other words, while the combo may show "Coffee Pot" the [ProdName]
field in the table is really a Number datatype and is storing, for
example an 8 (which corresponds to "Coffee Pot"). This can occur if
your table is using that ill begotten LookUp field, where it stores
one value but displays another.
Make sure of the table field's actual datatype.
If in fact it is a number, than change the AfterUpdate event to:

rs.FindFirst "[ProdName] = " & Me![Combo77]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Thu, 18 Oct 2007 04:58:51

n Oct 16, 2:39 pm, fredg < XXXX@XXXXX.COM > wrote:

While awaiting your reply I created a new temporary form in order to
try to correct this The following are the answers to your original six
questions as relating to this new form.

1) SELECT tblMSDSdata.RecordNum, tblMSDSdata.ProdName FROM
tblMSDSdata;
2) 1
3) 2
4) 0";3.5417"
5)Blank
6) Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo4], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Now in this case the process works up to a point. I originally had the
data for the combo box come directly from the table but because we are
using "FINDFIRST" and was getting something like the following. Lets
say my input to the combo is "adhesive"

adhesive
wool
soap
nails
adhesive
Thought I'd try a query sorted by ProdName but still get the same
thing I think because it is referencing the [RecordNum] rather than
[ProdName].

 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Thu, 18 Oct 2007 09:16:27

n Oct 16, 3:58 pm, TeeSee < XXXX@XXXXX.COM > wrote:

Success! I changed the RowSource to this "SELECT
tblMSDSdata.RecordNum, tblMSDSdata.ProdName FROM
qryMSDSdataIndex;
And it now works just fine. Ignore that last nonsense.

Thanks for asking those questions that pointed me in the right
direction of thinking. Best regards

 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Thu, 18 Oct 2007 22:17:45

n Oct 16, 8:16 pm, TeeSee < XXXX@XXXXX.COM > wrote:

Tomorrow is another day, already. I have come across a strange anomaly
this morning having had another look at it.

The last post functions as expected for every letter of the alphabet
except the letter "aA". When entering "aA" as the first letter into
Combo4 the curser immediately jumps to the end of the field and adds
any further input to the end.?????

Andy other start letter operates properly. Any thoughts on that one?

 
 
 

Find button to locate record in form and display that record

Post by Pieter Wij » Thu, 18 Oct 2007 22:52:44

on't fully understand you, and haven't read the whole thread, but Aa is the
same as, or replacement for, in the nordic languages.
Access therefore thinks it's dealing with that letter

Pieter

"TeeSee"<< XXXX@XXXXX.COM >> wrote in message
news: XXXX@XXXXX.COM ... >> On Oct 16, 8:16 pm, TeeSee<< XXXX@XXXXX.COM >> wrote: >>> On Oct 16, 3:58 pm, TeeSee<< XXXX@XXXXX.COM >> wrote: >>> >>> >>> >>> >>> >>>>> On Oct 16, 2:39 pm, fredg<< XXXX@XXXXX.COM >> wrote: >>> >>>>>>> On Tue, 16 Oct 2007 10:21:23 -0700, TeeSee wrote: >>>>>>>>> On Oct 16, 1:05 pm, fredg<< XXXX@XXXXX.COM >> wrote: >>>>>>>>>> On Tue, 16 Oct 2007 09:40:43 -0700, TeeSee wrote: >>>>>>> *** snipped *** >>>>>>>>>>> This is a very interesting approach and I would like to be able >>>>>>>>>>> to >>>>>>>>>>> utilize it. I have added the unbound Combo box to the header as >>>>>>>>>>> suggested. The combo box populates but nothing shows up in the >>>>>>>>>>> form. >>>>>>>>>>> Any further thoughts? >>> >>>>>>>>>> I need some help from you with this. >>>>>>>>>> What is the actual Rowsource of the Combo box? (Copy and Paste it) >>>>>>>>>> What is the Combo box Bound Column number? >>>>>>>>>> What is the Combo Box Column Count? >>>>>>>>>> What is the Combo Box Column Width property? >>>>>>>>>> What is the Combo Box Control source? It should be blank. >>>>>>>>>> What is the code behind the Combo Box's AfterUpdate event? (Copy >>>>>>>>>> and >>>>>>>>>> Paste it). >>> >>>>>>>>>> -- >>>>>>>>>> Fred >>>>>>>>>> Please respond only to this newsgroup. >>>>>>>>>> I do not reply to personal e-mail- Hide quotedtext- >>> >>>>>>>>>> - Show quotedtext- >>> >>>>>>>>> Thanks for response. Following are answers in same order. >>> >>>>>>>>> 1) SELECT qryMSDSdata.ProdName FROM qryMSDSdata; >>>>>>>>> 2) 1 >>>>>>>>> 3) 1 >>>>>>>>> 4) 3.4063 >>>>>>>>> 5) BLANK >>>>>>>>> 6) Private Sub Combo77_AfterUpdate() >>>>>>>>> ' Find the record that matches the control. >>>>>>>>> Dim rs As Object >>> >>>>>>>>> Set rs = Me.Recordset.Clone >>>>>>>>> rs.FindFirst "[ProdName] = '" & Me![Combo77] & "'" >>>>>>>>> If Not rs.EOF Then Me.Bookmark = rs.Bookmark >>>>>>>>> End Sub >>> >>>>>>> You are generating just one column in the rowsource; it's showing 1 >>>>>>> column and bound to that column. I'll assume that the column width is >>>>>>> in inches 3.0463". The AfterUpdate code looks good. This control >>>>>>> should not be bound to any field in the table. >>> >>>>>>> All of the above looks OK. >>>>>>> When you select one of the rows in the combo box, that record should >>>>>>> be displayed on the form. >>> >>>>>>> 1) Are you absolutely sure that the
 
 
 

Find button to locate record in form and display that record

Post by TeeSe » Fri, 19 Oct 2007 05:43:46

n Oct 17, 9:52 am, "Pieter Wijnen"
< XXXX@XXXXX.COM >
wrote:

Sorry to mislead you. That was intended to depict both upper and lower
case.