Go to same Record/ Syntax Error

[Confused]

This same code works on another similar database. But on this I keep getting
a syntax error (missing operator) on rst.FindFirst section in the code below.
I did a Debug and it says Sub of Function not Defined on rst.FindFirst
section.

Private Sub Show_Record_Click()

' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

' Store the recordset for the CLECS form.
Set rst = Forms!CLECS2MainForm.RecordsetClone

' Locate the record for the selected CLECS.
rst.FindFirst "[CLEC ID] = " & List2

' Set the form's Bookmark property to move to the record.
Forms!CLECS2MainForm.Bookmark = rst.Bookmark

' Close the dialog box.
DoCmd.Close acForm, "GoToRecordDialog"

Sorry I posted the same question before, but I tried many different ways to
make this work, so I felt the need to start over. Please forgive...

 

[Dirk Goldgar]

This same code works on another similar database. But on this I keep
getting
a syntax error (missing operator) on rst.FindFirst section in the code
below.
I did a Debug and it says Sub of Function not Defined on rst.FindFirst
section.

Private Sub Show_Record_Click()

' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

' Store the recordset for the CLECS form.
Set rst = Forms!CLECS2MainForm.RecordsetClone

' Locate the record for the selected CLECS.
rst.FindFirst "[CLEC ID] = " & List2

' Set the form's Bookmark property to move to the record.
Forms!CLECS2MainForm.Bookmark = rst.Bookmark

' Close the dialog box.
DoCmd.Close acForm, "GoToRecordDialog"

Sorry I posted the same question before, but I tried many different ways
to
make this work, so I felt the need to start over. Please forgive...

If I recall, you had good people helping you in that thread, so you would
probably have done better to continue there. However, the main questions to
be asked in debugging your question are:

1. What is the value of List2 when this code is executed? If the value is
Null, that would explain the error message.

2. What is data type of the field, [CLEC ID]? That determines whether you
need any quotes around the value of in your FindFirst criterion.

 

[John W. Vinson]

This same code works on another similar database. But on this I keep getting
a syntax error (missing operator) on rst.FindFirst section in the code below.
I did a Debug and it says Sub of Function not Defined on rst.FindFirst
section.

Private Sub Show_Record_Click()

' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

' Store the recordset for the CLECS form.
Set rst = Forms!CLECS2MainForm.RecordsetClone

' Locate the record for the selected CLECS.
rst.FindFirst "[CLEC ID] = " & List2

' Set the form's Bookmark property to move to the record.
Forms!CLECS2MainForm.Bookmark = rst.Bookmark

' Close the dialog box.
DoCmd.Close acForm, "GoToRecordDialog"

Sorry I posted the same question before, but I tried many different ways to
make this work, so I felt the need to start over. Please forgive...

As written this assumes that you have an undimensioned variable named List2
that has a value. You don't.

If there is a control on the form named List2 change that line to

rst.FindFirst "[CLEC ID] = " & Me.List2

It's also a good idea to put a Breakpoint in the code: open the VBA editor and
mouseclick in the grey vertical bar left of the code window, next to this
line. Run the code; the program will stop at this line and you can check the
values of variables by hovering the mouse over the variable name, or by typing

?List2

in the Immediate window.

It's also a VERY good idea to routinely put a line

Option Explicit

at the very top of every module, above the first Private Sub line. This will
ensure that your code doesn't have any undimensioned, undefined variables.

 

[Dirk Goldgar]

As written this assumes that you have an undimensioned variable named
List2
that has a value. You don't.

If there is a control on the form named List2 change that line to

rst.FindFirst "[CLEC ID] = " & Me.List2

While I agree that it would be better to explicitly qualify the control
name, it isn't actually required. So long as there isn't anything else
named "List2" in scope, VBA should understand that name as a reference to
the control.

 

[Confused]

Is there another way to accomplish the same thing? How do I open the VBA
Editor? I'm looking in the code and don't see a grey box.

I've created a list box List2, which is based off of CLECS2. CLECID is the
primary key and is a number field. The form CLECS2MainForm is based on the
same recordsource of CLECS2. I simply want the dialog form with the List2
Box to open so I can scroll through the records, which it does. But when I
select the record with the command button, I want it to move to that record.
I've even changed the name CLEC ID to CLECID in the table and still get
missing syntax error. I've tried this so many times. There must be another
way?

This same code works on another similar database. But on this I keep getting
a syntax error (missing operator) on rst.FindFirst section in the code below.
I did a Debug and it says Sub of Function not Defined on rst.FindFirst
section.

Private Sub Show_Record_Click()

' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

' Store the recordset for the CLECS form.
Set rst = Forms!CLECS2MainForm.RecordsetClone

' Locate the record for the selected CLECS.
rst.FindFirst "[CLEC ID] = " & List2

' Set the form's Bookmark property to move to the record.
Forms!CLECS2MainForm.Bookmark = rst.Bookmark

' Close the dialog box.
DoCmd.Close acForm, "GoToRecordDialog"

Sorry I posted the same question before, but I tried many different ways to
make this work, so I felt the need to start over. Please forgive...

As written this assumes that you have an undimensioned variable named List2
that has a value. You don't.

If there is a control on the form named List2 change that line to

rst.FindFirst "[CLEC ID] = " & Me.List2

It's also a good idea to put a Breakpoint in the code: open the VBA editor and
mouseclick in the grey vertical bar left of the code window, next to this
line. Run the code; the program will stop at this line and you can check the
values of variables by hovering the mouse over the variable name, or by typing

?List2

in the Immediate window.

It's also a VERY good idea to routinely put a line

Option Explicit

at the very top of every module, above the first Private Sub line. This will
ensure that your code doesn't have any undimensioned, undefined variables.

 

[Dirk Goldgar]

Is there another way to accomplish the same thing? How do I open the VBA
Editor? I'm looking in the code and don't see a grey box.

I've created a list box List2, which is based off of CLECS2. CLECID is
the
primary key and is a number field. The form CLECS2MainForm is based on
the
same recordsource of CLECS2. I simply want the dialog form with the List2
Box to open so I can scroll through the records, which it does. But when
I
select the record with the command button, I want it to move to that
record.
I've even changed the name CLEC ID to CLECID in the table and still get
missing syntax error. I've tried this so many times. There must be
another
way?

This is the correct way to go about it, but from the sound of it, you have
been changing things without making other changes that are necessary. If you
changed the name of the field in the table to "CLECID", then the FindFirst
code must be changed accordingly, to:

rst.FindFirst "CLECID = " & List2

It also makes a difference what type of field that is. You didn't answer my
question, but it is important to the code: What type of field is CLECID?
If it's a text field, then the code must be changed slightly.

This is another crucial question:

+ What is the value of List2 when the code is executed? You can determine
this by setting a breakpoint in the code and, when the code stops at the
breakpoint, hovering your mouse pointer over the name "List2" in the
procedure.

 

[John W. Vinson]

Is there another way to accomplish the same thing? How do I open the VBA
Editor? I'm looking in the code and don't see a grey box.

If you're looking at the code you're IN the VBA editor (you can type Ctrl-G to
get there too).

I didn't say a grey box: I said a grey *bar*, it's a vertical bar in the left
margin of the window containing the VBA code.

 

[Confused]

Sorry I thought I answered when I said CLECID is the primary key and is a
number in an AutoNumber Field.

I finally gave up. I put this same thing in another database and it works
perfect. I've checked and rechecked and updated all the entries. No reason
it shouldn't work. I finally just created a pop up form and made it
datasheet view when it opens. Not exactly what I wanted, but it works.
Thanks again...

 

[Dirk Goldgar]

Sorry I thought I answered when I said CLECID is the primary key and is a
number in an AutoNumber Field.

Looking back through the thread, i see that you did say that. I'm sorry if
I sounded cranky.

I finally gave up. I put this same thing in another database and it works
perfect. I've checked and rechecked and updated all the entries. No
reason
it shouldn't work.

There's always a reason something doesn't work. It's just a matter of
figuring out what that reason is. I would be willing to bet that there's
something simple you've overlooked, or something you think is so, that
isn't. Have you established that your code is even being called? If you're
working in Access 2007, VBA code won't execute unless either the database is
in a trusted location, or is digitally signed and explicitly trusted.

Well, if you've had enough of looking into this problem, that's your choice.
I personally hate to admit defeat, but life is a balance between
stubbornness and practicality.