Prev: Change Forecolor based upon if statement
Next: Populate date to another
From: KSmith on 17 May 2010 17:04
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
--

Thanks,
KSmith
From: Marshall Barton on 17 May 2010 18:38
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.

--
Marsh
MVP [MS Access]
From: KSmith on 18 May 2010 08:30
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


"Marshall Barton" wrote:

> 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.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Daryl S on 18 May 2010 12:40
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


"KSmith" wrote:

> 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
>
>
> "Marshall Barton" wrote:
>
> > 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.
> >
> > --
> > Marsh
> > MVP [MS Access]
> > .
> >
From: Marshall Barton on 18 May 2010 12:46
KSmith wrote:

>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.
>

I presume the error is occuring on the FinFirst line. If so
the only other thing I can think of is that the RouterID
field is not in the form's record source.

Beyond that, I would start to suspect some kind of
corruption in the form or its module.

--
Marsh
MVP [MS Access]
 |  Next  |  Last
Pages: 1 2 3
Prev: Change Forecolor based upon if statement
Next: Populate date to another