Problem with Combo Box FindNext

[KSmith]

I'm an old dBase programer that trying to help some folks write an inventory
control database.

I made one form that substracts inventory from one table and adds it to
another. When I copied this form to make another 'Move' screen I started
having trouble.

I copied Allen Browne's code from his help screen (listed below) and it
gives me this error message. Unknown or Invalied Field Reference Error 3345

cboMaterialLookup is a combo box that finds the data
RouterID is the field that FindFirst can't find! It's the Primary Key field
and AutoNumber field type.


I have tried several different ways of doing this and nothing seems to work,
the first form works find.


Sub cboMaterialLookUp_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.Recordset.Clone
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If rs.NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

 

[KSmith]

I checked the things you said to check and they are set correctly, I copied
your code to my code and got the same results. The ComboBox is looking-up
the data correctly. The problem happends when I select a record.

Thanks for responding.
--
KSmith

I'm an old dBase programer that trying to help some folks write an inventory
control database.

I made one form that substracts inventory from one table and adds it to
another. When I copied this form to make another 'Move' screen I started
having trouble.

I copied Allen Browne's code from his help screen (listed below) and it
gives me this error message. Unknown or Invalied Field Reference Error 3345

cboMaterialLookup is a combo box that finds the data
RouterID is the field that FindFirst can't find! It's the Primary Key field
and AutoNumber field type.

I have tried several different ways of doing this and nothing seems to work,
the first form works find.

Sub cboMaterialLookUp_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.Recordset.Clone
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If rs.NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The code looks good to me, except that you do not need to
create a clone of the form's recordset. Access provides a
ready made clone, appropriately named RecordsetClone, for
just this kind of thing. I also prefer to use a With
statement instead of an object variable. It won't make much
diffference, but I would write it:

Sub cboMaterialLookUp_AfterUpdate()
If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If .NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

As for the same code working in another form, but not here,
double check the combo box's properties to make sure the
BoundColumn is getting the RouterID field, the ColumnCount
property matches the number of columns in the row source
table/query and that the RouterID field is a number type
field in its table.

 

[Daryl S]

KSmith -

Is the first column of the combo box the RouterID? If not, you may want to
add the column lookup:
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp.Column(1)
Column(1) refers to the second column in the combobox, and the counts
include any hidden columns.

You can also add this:
Debug.Print Me.cboMaterialLookup.Column(0) 'Displays the first column
in the immediate window
If you have the code window open while this runs, or if you step through the
code, you will see the value of what you are looking up, which could give you
a clue as to what is wrong (like the wrong column).

--
Daryl S

I checked the things you said to check and they are set correctly, I copied
your code to my code and got the same results. The ComboBox is looking-up
the data correctly. The problem happends when I select a record.

Thanks for responding.
--
KSmith

I'm an old dBase programer that trying to help some folks write an inventory
control database.

I made one form that substracts inventory from one table and adds it to
another. When I copied this form to make another 'Move' screen I started
having trouble.

I copied Allen Browne's code from his help screen (listed below) and it
gives me this error message. Unknown or Invalied Field Reference Error 3345

cboMaterialLookup is a combo box that finds the data
RouterID is the field that FindFirst can't find! It's the Primary Key field
and AutoNumber field type.

I have tried several different ways of doing this and nothing seems to work,
the first form works find.

Sub cboMaterialLookUp_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.Recordset.Clone
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If rs.NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The code looks good to me, except that you do not need to
create a clone of the form's recordset. Access provides a
ready made clone, appropriately named RecordsetClone, for
just this kind of thing. I also prefer to use a With
statement instead of an object variable. It won't make much
diffference, but I would write it:

Sub cboMaterialLookUp_AfterUpdate()
If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If .NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

As for the same code working in another form, but not here,
double check the combo box's properties to make sure the
BoundColumn is getting the RouterID field, the ColumnCount
property matches the number of columns in the row source
table/query and that the RouterID field is a number type
field in its table.

 

[KSmith]

The first column in the the table I am looking in, is RouterID. It's the
AutoNumber Field. And like I said this same code works in the form that I
copied from. I have made a 'TEST' form that is set up like the 'FIRST' form.
It does not work either.
The error Code that I am now getting is:
Run-time error '3070'
The Microsoft Office Access database engine does not reconize
'RouterID_tblRouterArea' as a valid field name or
expression.

The crazy thing about this is; the AfterUpdate subroutine works in the first
form
but not in the second one.

It's almost like the 'FindFirst' function is not working in the second form!

Guys I really appreciate the effort in helping me with this problem.

--
KSmith

KSmith -

Is the first column of the combo box the RouterID? If not, you may want to
add the column lookup:
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp.Column(1)
Column(1) refers to the second column in the combobox, and the counts
include any hidden columns.

You can also add this:
Debug.Print Me.cboMaterialLookup.Column(0) 'Displays the first column
in the immediate window
If you have the code window open while this runs, or if you step through the
code, you will see the value of what you are looking up, which could give you
a clue as to what is wrong (like the wrong column).

--
Daryl S

I checked the things you said to check and they are set correctly, I copied
your code to my code and got the same results. The ComboBox is looking-up
the data correctly. The problem happends when I select a record.

Thanks for responding.
--
KSmith

KSmith wrote:

I'm an old dBase programer that trying to help some folks write an inventory
control database.

I made one form that substracts inventory from one table and adds it to
another. When I copied this form to make another 'Move' screen I started
having trouble.

I copied Allen Browne's code from his help screen (listed below) and it
gives me this error message. Unknown or Invalied Field Reference Error 3345

cboMaterialLookup is a combo box that finds the data
RouterID is the field that FindFirst can't find! It's the Primary Key field
and AutoNumber field type.

I have tried several different ways of doing this and nothing seems to work,
the first form works find.

Sub cboMaterialLookUp_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.Recordset.Clone
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If rs.NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


The code looks good to me, except that you do not need to
create a clone of the form's recordset. Access provides a
ready made clone, appropriately named RecordsetClone, for
just this kind of thing. I also prefer to use a With
statement instead of an object variable. It won't make much
diffference, but I would write it:

Sub cboMaterialLookUp_AfterUpdate()
If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If .NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

As for the same code working in another form, but not here,
double check the combo box's properties to make sure the
BoundColumn is getting the RouterID field, the ColumnCount
property matches the number of columns in the row source
table/query and that the RouterID field is a number type
field in its table.

 

[KSmith]

I double checked the things you just said and they are correct. In the FORM
Record Source: RouterID is first in line. In the ComboBox Row Source:
RouterID is first in line and Bound Column is set to '1'. I even made a
spreadsheet that has all of the Property Sheet Items for FORM and CONTROL
BOX. Noted all items on the form and ComboBox that is working and compared
that with the one that is not working and everything is the same, except for
the different table name and field name in the Record Source and Row Source.
I deleted my copy and re-built the form from scratch letting Access build in
the Record Source and Row Source. And he ComboBox will not find the the
record using the FindFirst function. BUT, it does in the first form I made.

This is crazy!!!! Makes me wish I had an old copy of
dBaseIV....!!!!!!!!!!!!!!!!!!!!!

Anyway, Thanks for all of your effort. I think I am going to delete the
problem forms run the clean-up programs and start over.

 

[Daryl S]

KSmith -

Where does the 'tblRouterArea' in the error message come from? What is the
recordsource for this new form? What line of code errors out with the error?
(Set a breakpoint at the top of the code and step through it.) Did you try
the Debug.Print?

--
Daryl S

The first column in the the table I am looking in, is RouterID. It's the
AutoNumber Field. And like I said this same code works in the form that I
copied from. I have made a 'TEST' form that is set up like the 'FIRST' form.
It does not work either.
The error Code that I am now getting is:
Run-time error '3070'
The Microsoft Office Access database engine does not reconize
'RouterID_tblRouterArea' as a valid field name or
expression.

The crazy thing about this is; the AfterUpdate subroutine works in the first
form
but not in the second one.

It's almost like the 'FindFirst' function is not working in the second form!

Guys I really appreciate the effort in helping me with this problem.

--
KSmith

KSmith -

Is the first column of the combo box the RouterID? If not, you may want to
add the column lookup:
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp.Column(1)
Column(1) refers to the second column in the combobox, and the counts
include any hidden columns.

You can also add this:
Debug.Print Me.cboMaterialLookup.Column(0) 'Displays the first column
in the immediate window
If you have the code window open while this runs, or if you step through the
code, you will see the value of what you are looking up, which could give you
a clue as to what is wrong (like the wrong column).

--
Daryl S

I checked the things you said to check and they are set correctly, I copied
your code to my code and got the same results. The ComboBox is looking-up
the data correctly. The problem happends when I select a record.

Thanks for responding.
--
KSmith


:

KSmith wrote:

I'm an old dBase programer that trying to help some folks write an inventory
control database.

I made one form that substracts inventory from one table and adds it to
another. When I copied this form to make another 'Move' screen I started
having trouble.

I copied Allen Browne's code from his help screen (listed below) and it
gives me this error message. Unknown or Invalied Field Reference Error 3345

cboMaterialLookup is a combo box that finds the data
RouterID is the field that FindFirst can't find! It's the Primary Key field
and AutoNumber field type.

I have tried several different ways of doing this and nothing seems to work,
the first form works find.

Sub cboMaterialLookUp_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.Recordset.Clone
rs.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If rs.NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


The code looks good to me, except that you do not need to
create a clone of the form's recordset. Access provides a
ready made clone, appropriately named RecordsetClone, for
just this kind of thing. I also prefer to use a With
statement instead of an object variable. It won't make much
diffference, but I would write it:

Sub cboMaterialLookUp_AfterUpdate()
If Not IsNull(Me.cboMaterialLookUp) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
With Me.RecordsetClone
.FindFirst "[RouterID] = " & Me.cboMaterialLookUp
If .NoMatch Then
Msgbox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

As for the same code working in another form, but not here,
double check the combo box's properties to make sure the
BoundColumn is getting the RouterID field, the ColumnCount
property matches the number of columns in the row source
table/query and that the RouterID field is a number type
field in its table.

 

[KSmith]

I solved the problem, kinda. I removed some of the spaces that were in the
middle of the FindFirst line of code, and it Ran like it should.

Now the spaces are in the code that I copied from. I am new at this and I
don't have all of my procedures in a module, they are attached to the forms.

Wonder why the spaces are a problem in one place but not in another?

Thanks for all of your help, nice to know this is here when you need it.

 

[KSmith]

Thanks for responding to my problem. Sorry for the long delay in getting
back, I had a death in the family.

The "spaces" were not the problem, as you said. To solve this problem I
looked back over my notes and found that I created the first form as a
columnar form with the widzard. I made a new columnar form with the same
tables as my problem form. I then copied the Record Source line from the
newly made form to the Record Source line in the problem second form.

This corrected the problem I was having.

I have another problem that I will post.

And thank you for all of your help.