linking combo boxes

[jamccarley]

I have a form with about 30 fields on it. I don't want to have to use the
find record function all of the time, so I created a combo box called
"cboLocate" and made its source a query that returns all of the records ID
numbers. How do I link that so when I select a specific record number it
brings back the corresponding information in the correct fields? My form name
is "SVR" and some of the field names are [OccDate], [Problem] and [RespDept]

Josh

 

[Al Campagna]

jamccarley,
On my website (below) I have a 98 and 2003 sample file called Quick Find
Combo.
It demonstrates how to slect a key/unique value form a comb, and find
and go to that record in the recordset.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[BruceM]

If you mean you want to go to the selected record, and if the ID is a number
field, but something like this in the After Update of the (unbound) combo
box:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RecordID] = " & Me.cboFindRecord
Me.Bookmark = rs.Bookmark

RecordID is the ID field, and cboFindRecord is the name of the combo box.
Use whatever names you like, but if the names contain anything other than
letters, numbers, and underscores you need to place the name in brackets as
I have done for RecordID. Other than RecordID and cboFindRecord the text is
exactly as I have shown it.

BTW, you can use two or more columns in the combo box Row Source. If
RecordID is the first column, and something like a name is in the second
column, you can set the combo box Column Count to 2, the Column widths to
something like 0";1", and the Bound Column to 1. That way you can select
something other than the ID field, but still have Access use the ID field to
find the record.

 

[jamccarley]

This worked like a charm

Thank you

If you mean you want to go to the selected record, and if the ID is a number
field, but something like this in the After Update of the (unbound) combo
box:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RecordID] = " & Me.cboFindRecord
Me.Bookmark = rs.Bookmark

RecordID is the ID field, and cboFindRecord is the name of the combo box.
Use whatever names you like, but if the names contain anything other than
letters, numbers, and underscores you need to place the name in brackets as
I have done for RecordID. Other than RecordID and cboFindRecord the text is
exactly as I have shown it.

BTW, you can use two or more columns in the combo box Row Source. If
RecordID is the first column, and something like a name is in the second
column, you can set the combo box Column Count to 2, the Column widths to
something like 0";1", and the Bound Column to 1. That way you can select
something other than the ID field, but still have Access use the ID field to
find the record.

I have a form with about 30 fields on it. I don't want to have to use the
find record function all of the time, so I created a combo box called
"cboLocate" and made its source a query that returns all of the records ID
numbers. How do I link that so when I select a specific record number it
brings back the corresponding information in the correct fields? My form
name
is "SVR" and some of the field names are [OccDate], [Problem] and
[RespDept]

Josh

 

[BruceM]

Glad to help. Good luck with the project.

This worked like a charm

Thank you

If you mean you want to go to the selected record, and if the ID is a
number
field, but something like this in the After Update of the (unbound) combo
box:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RecordID] = " & Me.cboFindRecord
Me.Bookmark = rs.Bookmark

RecordID is the ID field, and cboFindRecord is the name of the combo box.
Use whatever names you like, but if the names contain anything other than
letters, numbers, and underscores you need to place the name in brackets
as
I have done for RecordID. Other than RecordID and cboFindRecord the text
is
exactly as I have shown it.

BTW, you can use two or more columns in the combo box Row Source. If
RecordID is the first column, and something like a name is in the second
column, you can set the combo box Column Count to 2, the Column widths to
something like 0";1", and the Bound Column to 1. That way you can select
something other than the ID field, but still have Access use the ID field
to
find the record.

I have a form with about 30 fields on it. I don't want to have to use
the
find record function all of the time, so I created a combo box called
"cboLocate" and made its source a query that returns all of the records
ID
numbers. How do I link that so when I select a specific record number
it
brings back the corresponding information in the correct fields? My
form
name
is "SVR" and some of the field names are [OccDate], [Problem] and
[RespDept]

Josh