populating record number from a drop-down selection

E

eawallac

here's my question:

I have created a combo box and successfully populated data in the box from a
table. My afterUpdate event for the combo box populates the data on the rest
of the form correctly. However, the record number remains at "1" regardless
of what record I select in the combo box. Any advice? I must be missing a
setting somewhere, but can't figure where... Many thanks for any input!
 
J

John Vinson

here's my question:

I have created a combo box and successfully populated data in the box from a
table. My afterUpdate event for the combo box populates the data on the rest
of the form correctly. However, the record number remains at "1" regardless
of what record I select in the combo box. Any advice? I must be missing a
setting somewhere, but can't figure where... Many thanks for any input!
Click to expand...

ummm...

Access tables don't HAVE record numbers.

Where are you seeing this number? Could you post your AfterUpdate
code? It sounds like you might be *overwriting* the existing data in
the selected record, rather than *navigating* to a desired record.

John W. Vinson[MVP]
 
E

eawallac

Here is the code I have for AfterUpdate of fullname (my combobox):

fullname.Value = fullname.Column(0)
birthdate.Value = fullname.Column(1)
athletics.Value = fullname.Column(2)
nature.Value = fullname.Column(3)
boating.Value = fullname.Column(4)
crafts.Value = fullname.Column(5)
swimming.Value = fullname.Column(6)
finearts.Value = fullname.Column(7)
outcamping.Value = fullname.Column(8)
fitness.Value = fullname.Column(9)
service.Value = fullname.Column(10)
religion.Value = fullname.Column(11)

The select is the RowSource: SELECT DISTINCTROW campers.fullname,
campers.birthdate, campers.athletics, campers.nature, campers.boating,
campers.crafts, campers.swimming, campers.finearts, campers.outcamping,
campers.fitness, campers.service, campers.religion FROM CAMPERS;

campers.fullname is my primary key.

If I open the form, I can navigate through what appear to be record numbers
by using the record selector at the bottom of the form. This correctly
populates the data on the form. If I select a value in the combo, it also
populates the data correctly... but the record number stays at 1... which
becomes a problem when I modify data and try to select the next record using
the record navigators... I receive an error indicating my update would cause
a non-unique value.
 
J

John Vinson

If I open the form, I can navigate through what appear to be record numbers
by using the record selector at the bottom of the form. This correctly
populates the data on the form. If I select a value in the combo, it also
populates the data correctly... but the record number stays at 1... which
becomes a problem when I modify data and try to select the next record using
the record navigators... I receive an error indicating my update would cause
a non-unique value.
Click to expand...

Well, the code is doing exactly and precisely what you're telling it
to do: overwriting the data in the currently selected record (the
first, as it happens) with values from the combo box. Since it's a
bound form, you are EDITING the value of the record in the table
(destroying its previous contents in the process)!

I gather that you want instead to use the combo to *find* an existing
record and open it for editing. In order to do so, you don't need to
copy the contents of the combo into a record; instead, use the Form's
Recordsource to find the unique ID from the combo box. It *appears*
that you are using Fullname as the Primary Key, or at least assuming
that it is unique; this is *most* unwise, as it is pretty common for
different people to have the same name (I have three friends named
Fred Brown for example). You really should have some unique PersonID
in your table!

Using the existing table structure, though, and assuming that FullName
is unique, try the following. Make this combo box UNBOUND - its
Control Source should be blank (to prevent overwriting the current
record's fullname with the one you're looking up). Then use the
following event code:

Private Sub Fullname_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's record source
rs.FindFirst "[FullName] = """ & Me!Fullname & """" ' find name
If rs.NoMatch Then
<handle the error condition, unlikely in this case>
Else
Me.Bookmark = rs.Bookmark ' synch the form to the found record
End If
Set rs = Nothing ' clean up after yourself
End Sub

You may get an error on the Dim rs line; if so, you need to open the
VBA editor and select Tools... References. Scroll down and find the
"Microsoft DAO x.xx Object Library" (highest version number) and check
it.

I would again very strongly suggest creating a Primary Key that you
can count on to be unique (such as an autonumber); use it as the bound
column of your combo and replace the FindFirst line with

rs.FindFirst "[PersonID] = " & Me!comboboxname

The """" stuff is just to put the syntactically required quote mark
delimiters around the Fullname criterion.

John W. Vinson[MVP]
 
E

eawallac

Thank you!!!! Solution worked great!!

John Vinson said:
If I open the form, I can navigate through what appear to be record numbers
by using the record selector at the bottom of the form. This correctly
populates the data on the form. If I select a value in the combo, it also
populates the data correctly... but the record number stays at 1... which
becomes a problem when I modify data and try to select the next record using
the record navigators... I receive an error indicating my update would cause
a non-unique value.
Click to expand...

Well, the code is doing exactly and precisely what you're telling it
to do: overwriting the data in the currently selected record (the
first, as it happens) with values from the combo box. Since it's a
bound form, you are EDITING the value of the record in the table
(destroying its previous contents in the process)!

I gather that you want instead to use the combo to *find* an existing
record and open it for editing. In order to do so, you don't need to
copy the contents of the combo into a record; instead, use the Form's
Recordsource to find the unique ID from the combo box. It *appears*
that you are using Fullname as the Primary Key, or at least assuming
that it is unique; this is *most* unwise, as it is pretty common for
different people to have the same name (I have three friends named
Fred Brown for example). You really should have some unique PersonID
in your table!

Using the existing table structure, though, and assuming that FullName
is unique, try the following. Make this combo box UNBOUND - its
Control Source should be blank (to prevent overwriting the current
record's fullname with the one you're looking up). Then use the
following event code:

Private Sub Fullname_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's record source
rs.FindFirst "[FullName] = """ & Me!Fullname & """" ' find name
If rs.NoMatch Then
<handle the error condition, unlikely in this case>
Else
Me.Bookmark = rs.Bookmark ' synch the form to the found record
End If
Set rs = Nothing ' clean up after yourself
End Sub

You may get an error on the Dim rs line; if so, you need to open the
VBA editor and select Tools... References. Scroll down and find the
"Microsoft DAO x.xx Object Library" (highest version number) and check
it.

I would again very strongly suggest creating a Primary Key that you
can count on to be unique (such as an autonumber); use it as the bound
column of your combo and replace the FindFirst line with

rs.FindFirst "[PersonID] = " & Me!comboboxname

The """" stuff is just to put the syntactically required quote mark
delimiters around the Fullname criterion.

John W. Vinson[MVP]
Click to expand...
 
Top