combo box incorrectly finding records

[angelharp]

My search form has an unbound combo box control box named
PartNum (text field) which is for finding a record based
on a value selected. The form displays data from three
linked tables: Part Information, Manufacturers, and Land
Patterns.


I'm using the following code in the AfterUpdate event:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNum] = '" & Me![PartNum] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Most records show up fine. However, some values I choose
result in display of record with the primary key value
of '1', not the value I've chosen in the combo box.
(Primary Key is PartNum_ID which is an autonumber field)

I have tried using DAO 3.6 and 3.51 out of suspicion that
I was victim of Microsoft Knowledge Base Article #238134,
but I still get the same results.

I've also changed the code inside my subroutine to the
following:

'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[PartNum] = "'" & Me!
[PartNum] & "'"
Me.Bookmark = Me.RecordSetClone.Bookmark

.... and still get the same results.

Anybody know what my problem is?

 

[Dan Artuso]

Hi,
If PartNum is really an autonumber, then you shouldn't be delimiting with
single quotes.
Numbers require no delimiting, strings require quotes and dates require #
signs.
So try:
rs.FindFirst "[PartNum] = " & Me![PartNum]

HTH
Dan Artuso, MVP

 

[angelharp]

Sorry about the confusion.

PartNum is a text field.

PartNum_ID is an autonumber(primary key).

So delimiting isn't my problem. Its something else...

-----Original Message-----
Hi,
If PartNum is really an autonumber, then you shouldn't be delimiting with
single quotes.
Numbers require no delimiting, strings require quotes and dates require #
signs.
So try:
rs.FindFirst "[PartNum] = " & Me![PartNum]

HTH
Dan Artuso, MVP

My search form has an unbound combo box control box named
PartNum (text field) which is for finding a record based
on a value selected. The form displays data from three
linked tables: Part Information, Manufacturers, and Land
Patterns.


I'm using the following code in the AfterUpdate event:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNum] = '" & Me![PartNum] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Most records show up fine. However, some values I choose
result in display of record with the primary key value
of '1', not the value I've chosen in the combo box.
(Primary Key is PartNum_ID which is an autonumber field)

I have tried using DAO 3.6 and 3.51 out of suspicion that
I was victim of Microsoft Knowledge Base Article #238134,
but I still get the same results.

I've also changed the code inside my subroutine to the
following:

'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[PartNum] = "'" & Me!
[PartNum] & "'"
Me.Bookmark = Me.RecordSetClone.Bookmark

... and still get the same results.

Anybody know what my problem is?

.

 

[Jim/Chris]

It looks like you have named the combo box the same name as
your primary key. Try naming your combo box a different
name than your primary key.

Jim

 

[angelharp]

I have another form with the same problem that uses a
different name for the combo box. The code looks like
this:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNum] = '" & Me![Combo41] & "'"
Me.Bookmark = rs.Bookmark
End Sub

So its not the name of the combo box thats causing the
problem, but I appreciate the input.

Angel

-----Original Message-----
It looks like you have named the combo box the same name as
your primary key. Try naming your combo box a different
name than your primary key.

Jim

-----Original Message-----
My search form has an unbound combo box control box named
PartNum (text field) which is for finding a record based
on a value selected. The form displays data from three
linked tables: Part Information, Manufacturers, and Land
Patterns.


I'm using the following code in the AfterUpdate event:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNum] = '" & Me![PartNum] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Most records show up fine. However, some values I choose
result in display of record with the primary key value
of '1', not the value I've chosen in the combo box.
(Primary Key is PartNum_ID which is an autonumber field)

I have tried using DAO 3.6 and 3.51 out of suspicion that
I was victim of Microsoft Knowledge Base Article #238134,
but I still get the same results.

I've also changed the code inside my subroutine to the
following:

'Move to the record selected in the control
Me.RecordsetClone.Findfirst "[PartNum] = "'" & Me!
[PartNum] & "'"
Me.Bookmark = Me.RecordSetClone.Bookmark

.... and still get the same results.

Anybody know what my problem is?




.

.

 

[John Vinson]

My search form has an unbound combo box control box named
PartNum (text field) which is for finding a record based
on a value selected. The form displays data from three
linked tables: Part Information, Manufacturers, and Land
Patterns.


I'm using the following code in the AfterUpdate event:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNum] = '" & Me![PartNum] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Most records show up fine. However, some values I choose
result in display of record with the primary key value
of '1', not the value I've chosen in the combo box.

The recordset will be left at the first record if the PartNum is not
found. You can - and should - use the NoMatch method of the recordset
to detect when this has happened:

rs.FindFirst "[PartNum] = '" & Me![PartNum] & "'"
If rs.NoMatch Then
MsgBox "This part does not seem to exist!"
<take appropriate action>
Else
Me.Bookmark = rs.Bookmark
End If

 

[John Vinson]

I used everyone's advice, but I liked John's NoMatch event
suggestion to verify that indeed Access was not finding
the record in my unbound combo box. This made me look
more closely at my data.

Some texts suggest that *every* time you use FindFirst you should use
NoMatch to cover just this problem! Glad it helped.

The problem is that I have values in a foreign key that do
not exist in the table they relate to. I just started
this database project for my company to consolidate a text
file and an Excel spreadsheet that were being maintained
seperately, but never synchronized nor checked. So I have
some data to clean.

Yep. That's one nice thing about Access over Excel - data integrity is
built in.

Specifically, my problem is this:

I assume there is no work around for this because
technically, foreign keys should always have a record in a
related table. So in the meantime, I'll just make a
record called "PENDING" for records with Decal Names that
do not exist in the related table and update them to
correct names when we get further funding for this project.

Good move. Yes, a foreign key - *by definition* - is a link to an
existing record. If it isn't a link, it isn't a foreign key!

I just hope if anyone else tries to consolidate different
file types into a database, they don't run into this
problem. I lost sleep over it.

I've burned a lot of midnight oil over the years in very similar
circumstances so you've got my sympathy!