Selecting record from table using a list box

[Alejandro]

Good morning,

I have a table that keeps track of registrations, some of them are complete
and some don't. I have a main form based on this table that shows all data
entered, one record at a time. The form has a list box that, when updated,
shows all registrations in the table that are incomplete. I would like to use
some sort of "FindRecord" procedure where by double-clicking one of the
incomplete records in the list box the whole record information is pulled up
in the main form. I have a unique identifier for each record called [PATIENT
ID]; I assume I would need that to pull up the right record in the form.
Could anyone give me a hand with this?

Thanks a lot for your help

A.

 

[Marshall Barton]

I have a table that keeps track of registrations, some of them are complete
and some don't. I have a main form based on this table that shows all data
entered, one record at a time. The form has a list box that, when updated,
shows all registrations in the table that are incomplete. I would like to use
some sort of "FindRecord" procedure where by double-clicking one of the
incomplete records in the list box the whole record information is pulled up
in the main form. I have a unique identifier for each record called [PATIENT
ID]; I assume I would need that to pull up the right record in the form.

If the list box's row source query includes the unique
identifier as the BoundColumn and the ID field is a number
type field, then its double click event procedure could be
like:

With Me.RecordsetClone
.FindFirst "[PATIENT ID] = " & Me.[the listbox]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

 

[Alejandro]

EXCELLENT!!

Thank you so much Marshall!

I have a table that keeps track of registrations, some of them are complete
and some don't. I have a main form based on this table that shows all data
entered, one record at a time. The form has a list box that, when updated,
shows all registrations in the table that are incomplete. I would like to use
some sort of "FindRecord" procedure where by double-clicking one of the
incomplete records in the list box the whole record information is pulled up
in the main form. I have a unique identifier for each record called [PATIENT
ID]; I assume I would need that to pull up the right record in the form.

If the list box's row source query includes the unique
identifier as the BoundColumn and the ID field is a number
type field, then its double click event procedure could be
like:

With Me.RecordsetClone
.FindFirst "[PATIENT ID] = " & Me.[the listbox]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

 

[Alejandro]

Marshall, what you indicated in your previous post worked just fine; your
help is really appreciated.

I wanted to ask you something else, though. The patient ID is a Autonumber
field so it doesn't make much sense to the user. I need it to be able to find
the record that I want, but what I had thought of doing is to make the width
of column where PATIENT ID is in the listbox = 0 so users don't get confused
with that number. My idea was for users to double-click on the patient name
which would trigger your code and use PATIENT ID to find the record, but I
notice that if I double-click on the patient name I get an error; I have to
double-click on the patient id for the code to retrieve the record I want.

Is there a way to trigger what you gave me by double-clicking the patient
name, but still using the ID to find the record?

Thanks so much for your help

Alejandro.

I have a table that keeps track of registrations, some of them are complete
and some don't. I have a main form based on this table that shows all data
entered, one record at a time. The form has a list box that, when updated,
shows all registrations in the table that are incomplete. I would like to use
some sort of "FindRecord" procedure where by double-clicking one of the
incomplete records in the list box the whole record information is pulled up
in the main form. I have a unique identifier for each record called [PATIENT
ID]; I assume I would need that to pull up the right record in the form.

If the list box's row source query includes the unique
identifier as the BoundColumn and the ID field is a number
type field, then its double click event procedure could be
like:

With Me.RecordsetClone
.FindFirst "[PATIENT ID] = " & Me.[the listbox]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

 

[Alejandro]

Marshall, don't worry about my previous post. In the wizard for creating the
listbox, If I make PATIENT ID the value to save for later use when a record
is selected, then regardless of where I clicked on the ID is used to find the
correct record.

Thanks again for your help before.

A.

Marshall, what you indicated in your previous post worked just fine; your
help is really appreciated.

I wanted to ask you something else, though. The patient ID is a Autonumber
field so it doesn't make much sense to the user. I need it to be able to find
the record that I want, but what I had thought of doing is to make the width
of column where PATIENT ID is in the listbox = 0 so users don't get confused
with that number. My idea was for users to double-click on the patient name
which would trigger your code and use PATIENT ID to find the record, but I
notice that if I double-click on the patient name I get an error; I have to
double-click on the patient id for the code to retrieve the record I want.

Is there a way to trigger what you gave me by double-clicking the patient
name, but still using the ID to find the record?

Thanks so much for your help

Alejandro.

I have a table that keeps track of registrations, some of them are complete
and some don't. I have a main form based on this table that shows all data
entered, one record at a time. The form has a list box that, when updated,
shows all registrations in the table that are incomplete. I would like to use
some sort of "FindRecord" procedure where by double-clicking one of the
incomplete records in the list box the whole record information is pulled up
in the main form. I have a unique identifier for each record called [PATIENT
ID]; I assume I would need that to pull up the right record in the form.

If the list box's row source query includes the unique
identifier as the BoundColumn and the ID field is a number
type field, then its double click event procedure could be
like:

With Me.RecordsetClone
.FindFirst "[PATIENT ID] = " & Me.[the listbox]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

 

[Marshall Barton]

Marshall, don't worry about my previous post. In the wizard for creating the
listbox, If I make PATIENT ID the value to save for later use when a record
is selected, then regardless of where I clicked on the ID is used to find the
correct record.

Right. The field value you want the combo/list box to have
is specified in the BoundColumn property. It should almost
always be the primary key field.

If you do not want to see a column in the list, then set its
corresponding ColumnWidth to 0. For example, your's should
be set to 0;

Not specifying a value for a column width tells Access to
use the available space (the width of the list box) for that
column. If multiple column widths are not specified, the
widths are the available space less specified column widths
divided by the number of unspecified columns.

There is no need to use the double click event if the list
box's AfterUpdate event will do. OTOH, if you don't want to
automatically move to the matching record in the form, then
double click is fine.

 

[Alejandro]

Thanks so much!

Right. The field value you want the combo/list box to have
is specified in the BoundColumn property. It should almost
always be the primary key field.

If you do not want to see a column in the list, then set its
corresponding ColumnWidth to 0. For example, your's should
be set to 0;

Not specifying a value for a column width tells Access to
use the available space (the width of the list box) for that
column. If multiple column widths are not specified, the
widths are the available space less specified column widths
divided by the number of unspecified columns.

There is no need to use the double click event if the list
box's AfterUpdate event will do. OTOH, if you don't want to
automatically move to the matching record in the form, then
double click is fine.