Selecting record from table using a list box

  • Thread starter Thread starter Alejandro
  • Start date Start date
A

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.
 
Alejandro said:
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.
Click to expand...


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
 
EXCELLENT!!

Thank you so much Marshall!



Marshall Barton said:
Alejandro said:
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.
Click to expand...


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
Click to expand...
 
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.

Marshall Barton said:
Alejandro said:
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.
Click to expand...


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
Click to expand...
 
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.

Alejandro said:
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.

Marshall Barton said:
Alejandro said:
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.
Click to expand...


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
Click to expand...
Click to expand...
 
Alejandro said:
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.
Click to expand...

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.
 
Thanks so much!

Marshall Barton said:
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.
Click to expand...
 
Back
Top