PLease Help: Pulling up a particular record using a form

[Skully Matjas]

I am using the following code (created by the wizard) to
allow to bring my form to a particular entery. But when I
edit the entery (ex: put new information into a blank
cell), it puts that record onto the bottom of the list
(even though it keeps its record number).

Also, There are certin names that i click on the list, and
it will not bring it up, rather it brings to the first
record (no matter how many times i try going to that
record) But if I were to go to the record by typing in its
record number on the bottom, then it would work.
Here is the Code:

Option Compare Database

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo31] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo33] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo35_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo35] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo39_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo39] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Thank You
..

[Larry Linson]

Have you tried requerying the Combo Box after adding the new record? Or, is
it the Form itself you mean -- try requerying the Form, if so.

You don't have any test in your code to determine if you did or did not find
a match... use the .NoMatch property of the recordset to make such a
determination... that would be one reason that you might be located to the
first record.

And, as you know that "rs" is, in fact, a DAO.Recordset, why would you Dim
it as an Object? And, a Form's RecordsetClone is simply one word... it is
not the .Clone property of the Form's Recordset property.

Try all these changes and if you still get relocated to the first record,
follow up with any more detail you can think of.

Larry Linson
Microsoft Access MVP
"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:cc******************************@news.teranew s.com...

I am using the following code (created by the wizard) to
allow to bring my form to a particular entery. But when I
edit the entery (ex: put new information into a blank
cell), it puts that record onto the bottom of the list
(even though it keeps its record number).

Also, There are certin names that i click on the list, and
it will not bring it up, rather it brings to the first
record (no matter how many times i try going to that
record) But if I were to go to the record by typing in its
record number on the bottom, then it would work.
Here is the Code:

Option Compare Database

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo31] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo33] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo35_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo35] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo39_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo39] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Thank You
.

[Larry Linson]

Sorry, Skully, as a matter of practice, I do not open attachments to
newsgroup posts. No offense intended, but anyone who wanted to run some
malicious code could use the same approach. Also, it is considered bad
practice to attach files to post in newsgroups that don't have "binaries" in
the name because it can be costly for those who still pay by the minute for
their Internet connect time. (See http://www.mvps.org/access/netiquette.htm
for other good suggestions on using the newsgroup.)

The Combo Box Wizard has an option for using the Combo Box to select a
Record on the Form. Is that what you used? It generates code in the
AfterUpdate event of the Combo Box -- you might look at that to determine
whether you have in fact used enough fields to uniquely identify a parent.

Larry Linson
Microsoft Access MVP

"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:64******************************@news.teranew s.com...

Thank you for getting back to me. I am very new at this so i didnot
understand what you said, here i will give as much cetails as possible:

1) The combo box i am using is combox39
2) I imported a a table of parents of a school (it has name,address,#,
etc...)
3) I nead to input a ID Number (ralphs card) for some of the parents
4) The ID numbers do not go inorder of the parents, and i am not being
handed the id numbers in order
4) I want to be able to select a parent from the combobox, it will then
bring up that parent onto the form, where i could then enter the id number. (I use the calender to record the date that i enterd the id number)

Problem: Some of the parents, when i click on thier name it does not bring to their record. And also once I add the ID number, it puts thier name to
the bottom of the combobox list eventhough In the table they are still in
alphabetical order.

here Is a screenshoot of the form


"Larry Linson" <bo*****@localhost.not> wrote in
news:Cq*******************@nwrddc01.gnilink.net:

Have you tried requerying the Combo Box after adding the new record?
Or, is it the Form itself you mean -- try requerying the Form, if so.

You don't have any test in your code to determine if you did or did
not find a match... use the .NoMatch property of the recordset to make
such a determination... that would be one reason that you might be
located to the first record.

And, as you know that "rs" is, in fact, a DAO.Recordset, why would you
Dim it as an Object? And, a Form's RecordsetClone is simply one
word... it is not the .Clone property of the Form's Recordset
property.

Try all these changes and if you still get relocated to the first
record, follow up with any more detail you can think of.

Larry Linson
Microsoft Access MVP
"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:cc******************************@news.teranew s.com...

I am using the following code (created by the wizard) to
allow to bring my form to a particular entery. But when I
edit the entery (ex: put new information into a blank
cell), it puts that record onto the bottom of the list
(even though it keeps its record number).

Also, There are certin names that i click on the list, and
it will not bring it up, rather it brings to the first
record (no matter how many times i try going to that
record) But if I were to go to the record by typing in its
record number on the bottom, then it would work.
Here is the Code:

Option Compare Database

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo31] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo33] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo35_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Ralphs Card #] = '" & Me![Combo35] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo39_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Phone Number] = '" & Me![Combo39] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Thank You
.

[Skully Matjas]

"Larry Linson" <bo*****@localhost.not> wrote in
news:Nk******************@nwrddc03.gnilink.net:

Yes, I did use that, and i selected it to say first, and last naem (last
name first) I think That is pretty uniqe

So how come some records dont come up.

Also how come when i eddit records it puts them on the bottom of the
list. Is there extra code that I can input. If so please type in exactly
how i should do, as i am very new at this.

Thank you

P.S. Sorry about the attachments

Sorry, Skully, as a matter of practice, I do not open attachments to
newsgroup posts. No offense intended, but anyone who wanted to run
some malicious code could use the same approach. Also, it is
considered bad practice to attach files to post in newsgroups that
don't have "binaries" in the name because it can be costly for those
who still pay by the minute for their Internet connect time. (See
http://www.mvps.org/access/netiquette.htm for other good suggestions
on using the newsgroup.)

The Combo Box Wizard has an option for using the Combo Box to select a
Record on the Form. Is that what you used? It generates code in the
AfterUpdate event of the Combo Box -- you might look at that to
determine whether you have in fact used enough fields to uniquely
identify a parent.

Larry Linson
Microsoft Access MVP

"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:64******************************@news.teranew s.com...

Thank you for getting back to me. I am very new at this so i didnot
understand what you said, here i will give as much cetails as
possible:

1) The combo box i am using is combox39
2) I imported a a table of parents of a school (it has
name,address,#, etc...)
3) I nead to input a ID Number (ralphs card) for some of the parents
4) The ID numbers do not go inorder of the parents, and i am not
being handed the id numbers in order
4) I want to be able to select a parent from the combobox, it will
then bring up that parent onto the form, where i could then enter the
id

number.

(I use the calender to record the date that i enterd the id number)

Problem: Some of the parents, when i click on thier name it does not

bring

to their record. And also once I add the ID number, it puts thier
name to the bottom of the combobox list eventhough In the table they
are still in alphabetical order.

here Is a screenshoot of the form


"Larry Linson" <bo*****@localhost.not> wrote in
news:Cq*******************@nwrddc01.gnilink.net:

> Have you tried requerying the Combo Box after adding the new
> record? Or, is it the Form itself you mean -- try requerying the
> Form, if so.
>
> You don't have any test in your code to determine if you did or did
> not find a match... use the .NoMatch property of the recordset to
> make such a determination... that would be one reason that you
> might be located to the first record.
>
> And, as you know that "rs" is, in fact, a DAO.Recordset, why would
> you Dim it as an Object? And, a Form's RecordsetClone is simply one
> word... it is not the .Clone property of the Form's Recordset
> property.
>
> Try all these changes and if you still get relocated to the first
> record, follow up with any more detail you can think of.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Skully Matjas" <sk**********@yahoo.com> wrote in message
> news:cc******************************@news.teranew s.com...
>> I am using the following code (created by the wizard) to
>> allow to bring my form to a particular entery. But when I
>> edit the entery (ex: put new information into a blank
>> cell), it puts that record onto the bottom of the list
>> (even though it keeps its record number).
>>
>> Also, There are certin names that i click on the list, and
>> it will not bring it up, rather it brings to the first
>> record (no matter how many times i try going to that
>> record) But if I were to go to the record by typing in its
>> record number on the bottom, then it would work.
>> Here is the Code:
>>
>> Option Compare Database
>>
>> Private Sub Combo22_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo22] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>> Private Sub Combo31_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Ralphs Card #] = '" & Me![Combo31] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo33_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo33] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo35_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Ralphs Card #] = '" & Me![Combo35] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo39_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo39] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Thank You
>> .
>>
>
>
>

[Larry Linson]

Skully,

Do you read VBA code? If you do, click on the AfterUpdate event in the
properties for your Combo Box and you will find, I am certain, that although
it displays both the Last and First name, it only selects on the Last Name.
That is, it will select the first record it finds that matches the Last
name. So if you have Bob Jones, Sam Jones, and James Jones and they are in
the table in the order I have listed them, even though you click on Sam
Jones or James Jones, it will populate the form with Bob Jones because that
was the first Jones in the table. It's easy to add code to do the find on
both names, but it is specific to the names of the fields that you are
using.

Substitute the name of your combo box for cboN, and the names of your fields
for Last and First:

You'll likely see code

rs.FindFirst "[Last] = '" & Me![cboN] & "'

change it to

rs.FindFirst "[Last] = '" & Me![cboN] & "' AND [First] = '" &
Me![cboN].Column(1) & "'"

Have you done anything to enforce a specific order on the records you have
and are displaying? That is, is the Row Source of your ComboBox a Query with
sorting specified, or an SQL statement with an ORDER BY clause? By
definition, data in relational database tables is UNORDERED, and, in
practice, every recored _added_ in Accesss is added at the end. Revise your
Combo to use a Query or SQL statement as described above sorting on Last
Name and on First Name, then in the AfterUpdate event of the form, add a
statement to requery the combo box. Substitute the name of your combo for
cboN:

Me!cboN.Requery

but, be aware that this will alone will not do what you want -- you must use
a Row Source for the Combo Box that has a sort order, as described above.

If you don't understand what I'm suggesting, or if there is information I am
overlooking, please feel free to follow up here (but please don't repost the
same original question again to start a new thread, as you did before).

Larry Linson
Microsoft Access MVP
"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:86******************************@news.teranew s.com...

"Larry Linson" <bo*****@localhost.not> wrote in
news:Nk******************@nwrddc03.gnilink.net:

Yes, I did use that, and i selected it to say first, and last naem (last
name first) I think That is pretty uniqe

So how come some records dont come up.

Also how come when i eddit records it puts them on the bottom of the
list. Is there extra code that I can input. If so please type in exactly
how i should do, as i am very new at this.

Thank you

P.S. Sorry about the attachments

Sorry, Skully, as a matter of practice, I do not open attachments to
newsgroup posts. No offense intended, but anyone who wanted to run
some malicious code could use the same approach. Also, it is
considered bad practice to attach files to post in newsgroups that
don't have "binaries" in the name because it can be costly for those
who still pay by the minute for their Internet connect time. (See
http://www.mvps.org/access/netiquette.htm for other good suggestions
on using the newsgroup.)

The Combo Box Wizard has an option for using the Combo Box to select a
Record on the Form. Is that what you used? It generates code in the
AfterUpdate event of the Combo Box -- you might look at that to
determine whether you have in fact used enough fields to uniquely
identify a parent.

Larry Linson
Microsoft Access MVP

"Skully Matjas" <sk**********@yahoo.com> wrote in message
news:64******************************@news.teranew s.com...

Thank you for getting back to me. I am very new at this so i didnot
understand what you said, here i will give as much cetails as
possible:

1) The combo box i am using is combox39
2) I imported a a table of parents of a school (it has
name,address,#, etc...)
3) I nead to input a ID Number (ralphs card) for some of the parents
4) The ID numbers do not go inorder of the parents, and i am not
being handed the id numbers in order
4) I want to be able to select a parent from the combobox, it will
then bring up that parent onto the form, where i could then enter the
id

number.

(I use the calender to record the date that i enterd the id number)

Problem: Some of the parents, when i click on thier name it does not

bring

to their record. And also once I add the ID number, it puts thier
name to the bottom of the combobox list eventhough In the table they
are still in alphabetical order.

here Is a screenshoot of the form


"Larry Linson" <bo*****@localhost.not> wrote in
news:Cq*******************@nwrddc01.gnilink.net:

> Have you tried requerying the Combo Box after adding the new
> record? Or, is it the Form itself you mean -- try requerying the
> Form, if so.
>
> You don't have any test in your code to determine if you did or did
> not find a match... use the .NoMatch property of the recordset to
> make such a determination... that would be one reason that you
> might be located to the first record.
>
> And, as you know that "rs" is, in fact, a DAO.Recordset, why would
> you Dim it as an Object? And, a Form's RecordsetClone is simply one
> word... it is not the .Clone property of the Form's Recordset
> property.
>
> Try all these changes and if you still get relocated to the first
> record, follow up with any more detail you can think of.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Skully Matjas" <sk**********@yahoo.com> wrote in message
> news:cc******************************@news.teranew s.com...
>> I am using the following code (created by the wizard) to
>> allow to bring my form to a particular entery. But when I
>> edit the entery (ex: put new information into a blank
>> cell), it puts that record onto the bottom of the list
>> (even though it keeps its record number).
>>
>> Also, There are certin names that i click on the list, and
>> it will not bring it up, rather it brings to the first
>> record (no matter how many times i try going to that
>> record) But if I were to go to the record by typing in its
>> record number on the bottom, then it would work.
>> Here is the Code:
>>
>> Option Compare Database
>>
>> Private Sub Combo22_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo22] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>> Private Sub Combo31_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Ralphs Card #] = '" & Me![Combo31] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo33_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo33] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo35_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Ralphs Card #] = '" & Me![Combo35] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Private Sub Combo39_AfterUpdate()
>> ' Find the record that matches the control.
>> Dim rs As Object
>>
>> Set rs = Me.Recordset.Clone
>> rs.FindFirst "[Phone Number] = '" & Me![Combo39] & "'"
>> Me.Bookmark = rs.Bookmark
>> End Sub
>>
>> Thank You
>> .
>>
>
>
>