Using combobox "Find record on my form..."

[xenophon]

I wish to use a combobox as a record selector in an Access 2003 asset
tracking database. The problem is, the database is in 3NF, so eight of the
assets table fields hold only longint FKs, including those fields of most
value in selecting an asset record (e.g. current owner, asset category,
manufacturer, etc.). The combo presents only several columns containing FKs
after it's built. When you use the wizard to create the combo it only
permits selecting from the base table (Assets) for the main form, not from
other tables or queries.

I tried building a combobox with the wizard and checking the AfterUpdate
code to see how it was implementing the find record procedure. Then I built
an independent multi-table query to provide the text fields associated with
the assets table FKs. Worked fine, so I built another combobox using that
query, which displayed the fields I wanted, then went into its AfterUpdate
event and modified the necessary combobox names. When I try to use it,
nothing happens. No exception thrown, just nothing.

I come from a VB background, and am unfamiliar with Access VBA. Perhaps I am
not understanding the syntax. Here is the generated AfterUpdate code from
the (working) wizard-generated code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Seems straightforward enough, but I don't understand this line:

rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))

From appearance, it seems to be extracting a non-null string from the
combobox and concatenating it with the first part of the FindFirst argument,
but which one? Obviously, for this to work, it must be getting the value of
the AssetID column, but how does it know, since the whole current row of the
combobox is contained in the argument [Combo119], isn't it? More to the
point, why won't it do that for my query-based combobox? Is there a bang/dot
notation I can employ to the same purpose, like [Combo119].[AssetID] or
maybe [NameOfQuery].[AssetID]? The key seems to lie in that line, so I think
that if I could grok it I'd be OK.

I would appreciate any direction anyone could give me, or a pointer to a
tutorial or article explaining how to implement this functionality using a
query-based combobox. I do not want to denormalize my database just for this
purpose. Thanks.

Scott

 

[Alex Dybenko]

Hi,
Me.Recordset.clone returns recordset, based on the form's recordsource
property, so if you have assets table there - then FindFirst will find first
records where AssetID match AssetID selected in combobox

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I wish to use a combobox as a record selector in an Access 2003 asset
tracking database. The problem is, the database is in 3NF, so eight of the
assets table fields hold only longint FKs, including those fields of most
value in selecting an asset record (e.g. current owner, asset category,
manufacturer, etc.). The combo presents only several columns containing FKs
after it's built. When you use the wizard to create the combo it only
permits selecting from the base table (Assets) for the main form, not from
other tables or queries.

I tried building a combobox with the wizard and checking the AfterUpdate
code to see how it was implementing the find record procedure. Then I
built an independent multi-table query to provide the text fields
associated with the assets table FKs. Worked fine, so I built another
combobox using that query, which displayed the fields I wanted, then went
into its AfterUpdate event and modified the necessary combobox names. When
I try to use it, nothing happens. No exception thrown, just nothing.

I come from a VB background, and am unfamiliar with Access VBA. Perhaps I
am not understanding the syntax. Here is the generated AfterUpdate code
from the (working) wizard-generated code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Seems straightforward enough, but I don't understand this line:

rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))

From appearance, it seems to be extracting a non-null string from the
combobox and concatenating it with the first part of the FindFirst
argument, but which one? Obviously, for this to work, it must be getting
the value of the AssetID column, but how does it know, since the whole
current row of the combobox is contained in the argument [Combo119], isn't
it? More to the point, why won't it do that for my query-based combobox?
Is there a bang/dot notation I can employ to the same purpose, like
[Combo119].[AssetID] or maybe [NameOfQuery].[AssetID]? The key seems to
lie in that line, so I think that if I could grok it I'd be OK.

I would appreciate any direction anyone could give me, or a pointer to a
tutorial or article explaining how to implement this functionality using a
query-based combobox. I do not want to denormalize my database just for
this purpose. Thanks.

Scott

 

[xenophon]

Hi,
Me.Recordset.clone returns recordset, based on the form's recordsource
property, so if you have assets table there - then FindFirst will
find first records where AssetID match AssetID selected in combobox

Umm, yes, but that is not my problem. FindFirst apparently works the same as
the same function in VB6 code. The issue I am having is exactly how to
extract the value of the AssetID column from the current row of the combobox
control in the AfterUpdate event of the control. What is the correct syntax
to retrieve the value of a particular column of a combobox? Thanks.

Scott

I wish to use a combobox as a record selector in an Access 2003 asset
tracking database. The problem is, the database is in 3NF, so eight
of the assets table fields hold only longint FKs, including those
fields of most value in selecting an asset record (e.g. current
owner, asset category, manufacturer, etc.). The combo presents only
several columns containing FKs after it's built. When you use the
wizard to create the combo it only permits selecting from the base
table (Assets) for the main form, not from other tables or queries.

I tried building a combobox with the wizard and checking the
AfterUpdate code to see how it was implementing the find record
procedure. Then I built an independent multi-table query to provide
the text fields associated with the assets table FKs. Worked fine,
so I built another combobox using that query, which displayed the
fields I wanted, then went into its AfterUpdate event and modified
the necessary combobox names. When I try to use it, nothing happens.
No exception thrown, just nothing. I come from a VB background, and am
unfamiliar with Access VBA.
Perhaps I am not understanding the syntax. Here is the generated
AfterUpdate code from the (working) wizard-generated code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Seems straightforward enough, but I don't understand this line:

rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))

From appearance, it seems to be extracting a non-null string from the
combobox and concatenating it with the first part of the FindFirst
argument, but which one? Obviously, for this to work, it must be
getting the value of the AssetID column, but how does it know, since
the whole current row of the combobox is contained in the argument
[Combo119], isn't it? More to the point, why won't it do that for my
query-based combobox? Is there a bang/dot notation I can employ to
the same purpose, like [Combo119].[AssetID] or maybe
[NameOfQuery].[AssetID]? The key seems to lie in that line, so I
think that if I could grok it I'd be OK. I would appreciate any direction
anyone could give me, or a pointer
to a tutorial or article explaining how to implement this
functionality using a query-based combobox. I do not want to
denormalize my database just for this purpose. Thanks.

Scott

 

[xenophon]

Never mind, I figured it out. My solution was to let the wizard build the
combobox, then go into its RowSource builder and add the necessary tables
and fields to display the related table field values instead of FKs. Still
don't understand why my query-based combo didn't work, since the VBA code is
identical, but there you are. There must be some other factor of which I am
not aware. And I'm still curious as to how one obtains in code the value of
a given column in the current (selected) row of a combobox, so if anyone can
point me to a tut I would appreciate it.

Scott

I wish to use a combobox as a record selector in an Access 2003 asset
tracking database. The problem is, the database is in 3NF, so eight
of the assets table fields hold only longint FKs, including those
fields of most value in selecting an asset record (e.g. current
owner, asset category, manufacturer, etc.). The combo presents only
several columns containing FKs after it's built. When you use the
wizard to create the combo it only permits selecting from the base
table (Assets) for the main form, not from other tables or queries.

I tried building a combobox with the wizard and checking the
AfterUpdate code to see how it was implementing the find record
procedure. Then I built an independent multi-table query to provide
the text fields associated with the assets table FKs. Worked fine, so
I built another combobox using that query, which displayed the fields
I wanted, then went into its AfterUpdate event and modified the
necessary combobox names. When I try to use it, nothing happens. No
exception thrown, just nothing.
I come from a VB background, and am unfamiliar with Access VBA.
Perhaps I am not understanding the syntax. Here is the generated
AfterUpdate code from the (working) wizard-generated code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Seems straightforward enough, but I don't understand this line:

rs.FindFirst "[AssetID] = " & Str(Nz(Me![Combo119], 0))

From appearance, it seems to be extracting a non-null string from the
combobox and concatenating it with the first part of the FindFirst
argument, but which one? Obviously, for this to work, it must be
getting the value of the AssetID column, but how does it know, since
the whole current row of the combobox is contained in the argument
[Combo119], isn't it? More to the point, why won't it do that for my
query-based combobox? Is there a bang/dot notation I can employ to
the same purpose, like [Combo119].[AssetID] or maybe
[NameOfQuery].[AssetID]? The key seems to lie in that line, so I
think that if I could grok it I'd be OK.
I would appreciate any direction anyone could give me, or a pointer
to a tutorial or article explaining how to implement this
functionality using a query-based combobox. I do not want to
denormalize my database just for this purpose. Thanks.

Scott