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


"Daryl S" wrote:

> 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: KSmith on 18 May 2010 14:30
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.
--
KSmith


"Marshall Barton" wrote:

> 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]
> .
>
From: Daryl S on 18 May 2010 14:39
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


"KSmith" wrote:

> 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
>
>
> "Daryl S" wrote:
>
> > 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: KSmith on 18 May 2010 17:43
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


"KSmith" wrote:

> 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.
> --
> KSmith
>
>
> "Marshall Barton" wrote:
>
> > 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]
> > .
> >
From: Marshall Barton on 18 May 2010 19:06
KSmith wrote:

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


What spaces? I don't see any extra spaces in what you
posted/

Extra spaces are ignored between operands and operators in
VBA statements and several other places. They can be
critical in names and quotes strings.

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