Find button to locate record in form and display that record

[esleea]

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!

 

[fredg]

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!

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.

 

[esleea]

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.

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

 

[TeeSee]

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 associatedtextname field. Hide the ID field.

When done, start entering the word value. The combo willsearchahead
as you type and display all those values. When you find the correct
one, select it. That record will be displayed on the form.

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?

 

[fredg]

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).

 

[TeeSee]

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

 

[fredg]

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 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]

 

[TeeSee]

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 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'stext).
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- Hide quotedtext-

- Show quotedtext-

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].

 

[TeeSee]

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 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'stext).
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]

- Show quotedtext-

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].- Hide quotedtext-

- Show quotedtext-

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

 

[TeeSee]

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 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'stext).
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- Hide quotedtext-
- Show quotedtext-

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].- Hide quotedtext-

- Show quotedtext-

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- Hide quotedtext-

- Show quotedtext-

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?

 

[Pieter Wijnen]

Don'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

On Oct 16, 2:39 pm, fredg <[email protected]> wrote:

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).

- 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 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'stext).
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]

- Show quotedtext-

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].- Hide quotedtext-

- Show quotedtext-

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- Hide quotedtext-

- Show quotedtext-

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?

 

[TeeSee]

Don'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

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 widthis
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'stext).
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- Hide quotedtext-
- Show quotedtext-
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].- Hide quotedtext-
- Show quotedtext-
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- Hide quotedtext-
- Show quotedtext-

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?- Hide quotedtext-

- Show quotedtext-

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