FindFirst - 2 criteria

  • Thread starter Thread starter WestWingFan
  • Start date Start date
W

WestWingFan

Ok, I'm sure this is a syntax thing. I'm trying to find a record on a form
based on the members selected via two different criteria. For the following
statement

rs.FindFirst "[ConvActivityRtgID] = " & [TARSkillClusterID] =
lstGroupingSelect " AND "&[AASSID] = LstAASSselect"

I get an "expected end of expression" error somewhere around the "and"
section of the statement. Can anyone tell me what I'm doing wrong? Thanks in
advance!
 
WestWingFan said:
Ok, I'm sure this is a syntax thing. I'm trying to find a record on a form
based on the members selected via two different criteria. For the
following
statement

rs.FindFirst "[ConvActivityRtgID] = " & [TARSkillClusterID] =
lstGroupingSelect " AND "&[AASSID] = LstAASSselect"

I get an "expected end of expression" error somewhere around the "and"
section of the statement. Can anyone tell me what I'm doing wrong? Thanks
in
advance!
Click to expand...


There's definitely a syntax error there, but I'm not sure sure what you're
trying to specify with your criteria. Please list (a) the fields in your
table to whih criteria are to be applied, and (b) for each field, the name
of the control on the form to which thart field is to be compared.
 
Hi -

You want to use two conditions, which means you need two A = B pairings, i.e.
" A = B AND C = D", but there are 5 variable and/or field references in your
statement. One should not be there - but which one?

John


Ok, I'm sure this is a syntax thing. I'm trying to find a record on a form
based on the members selected via two different criteria. For the following
statement

rs.FindFirst "[ConvActivityRtgID] = " & [TARSkillClusterID] =
lstGroupingSelect " AND "&[AASSID] = LstAASSselect"

I get an "expected end of expression" error somewhere around the "and"
section of the statement. Can anyone tell me what I'm doing wrong? Thanks in
advance!
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 
Dirk,

Thanks for the quick response.

Field Names Control Names
TARSkillClusterID lstGroupingSelect
AASSID lstAASSselect

Both fields are numbers. both controls are list boxes. Thanks so much for
your help!


Dirk Goldgar said:
WestWingFan said:
Ok, I'm sure this is a syntax thing. I'm trying to find a record on a form
based on the members selected via two different criteria. For the
following
statement

rs.FindFirst "[ConvActivityRtgID] = " & [TARSkillClusterID] =
lstGroupingSelect " AND "&[AASSID] = LstAASSselect"

I get an "expected end of expression" error somewhere around the "and"
section of the statement. Can anyone tell me what I'm doing wrong? Thanks
in
advance!
Click to expand...


There's definitely a syntax error there, but I'm not sure sure what you're
trying to specify with your criteria. Please list (a) the fields in your
table to whih criteria are to be applied, and (b) for each field, the name
of the control on the form to which thart field is to be compared.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
WestWingFan said:
Dirk,

Thanks for the quick response.

Field Names Control Names
TARSkillClusterID lstGroupingSelect
AASSID lstAASSselect

Both fields are numbers. both controls are list boxes.
Click to expand...

Okay, then something like this should work:

rs.FindFirst _
"[TARSkillClusterID] = " & Me!lstGroupingSelect & _
" AND [AASSID] = " & Me!LstAASSselect

Now, if either of those fields is a text field, you'll have to modify the
above statement to embed quotes around the values you get from the form
controls. If you need to do that and can't figure out how, post back.
 
Dirk,

The code isn't throwing an error anymore. Hurray! However, the form isn't
working... drat!

Ok, so my form has two list boxes (unbound). I want the user to be able to
select values from those boxes and for the form to find the correct record on
the back end and then record values from the two bound controls (one is an
option group and the other is a comments field). At no point should there be
a need to add records to the underlying table. Does this make sense? So, when
I select 1 from lstGroupingSelect and 2 from lstAASSselect, I want to write
to the ONLY record on the table which has both of those values (1,2). I then
want to change the selection in lstAASSselect to 3 and be able to write to
the ONLY record on the table which has these values (1,3). When I view the
form and attempt such an interaction it gives me the error that Update or
CancelUpdate has been employed, but not AddNew or Edit. Did I miss something?
I'm new to coding and prone to such things.

Thanks for all your help!

Dirk Goldgar said:
WestWingFan said:
Dirk,

Thanks for the quick response.

Field Names Control Names
TARSkillClusterID lstGroupingSelect
AASSID lstAASSselect

Both fields are numbers. both controls are list boxes.
Click to expand...

Okay, then something like this should work:

rs.FindFirst _
"[TARSkillClusterID] = " & Me!lstGroupingSelect & _
" AND [AASSID] = " & Me!LstAASSselect

Now, if either of those fields is a text field, you'll have to modify the
above statement to embed quotes around the values you get from the form
controls. If you need to do that and can't figure out how, post back.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
John,

I'm not sure. I started with the code automatically programmed into the
wizard. The table has an autonumber ID field (ConvActivityRtgID) which is
unique. The combination of the other two fields (TARSkillClusterID & AASSID)
is also unique. I've had problems using a double PK in the past, hence the
autonumber ID field. Does that help?

J_Goddard via AccessMonster.com said:
Hi -

You want to use two conditions, which means you need two A = B pairings, i.e.
" A = B AND C = D", but there are 5 variable and/or field references in your
statement. One should not be there - but which one?

John


Ok, I'm sure this is a syntax thing. I'm trying to find a record on a form
based on the members selected via two different criteria. For the following
statement

rs.FindFirst "[ConvActivityRtgID] = " & [TARSkillClusterID] =
lstGroupingSelect " AND "&[AASSID] = LstAASSselect"

I get an "expected end of expression" error somewhere around the "and"
section of the statement. Can anyone tell me what I'm doing wrong? Thanks in
advance!
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
Click to expand...
 
WestWingFan said:
Dirk,

The code isn't throwing an error anymore. Hurray! However, the form isn't
working... drat!

Ok, so my form has two list boxes (unbound). I want the user to be able to
select values from those boxes and for the form to find the correct record
on
the back end and then record values from the two bound controls (one is an
option group and the other is a comments field). At no point should there
be
a need to add records to the underlying table. Does this make sense? So,
when
I select 1 from lstGroupingSelect and 2 from lstAASSselect, I want to
write
to the ONLY record on the table which has both of those values (1,2). I
then
want to change the selection in lstAASSselect to 3 and be able to write to
the ONLY record on the table which has these values (1,3). When I view the
form and attempt such an interaction it gives me the error that Update or
CancelUpdate has been employed, but not AddNew or Edit. Did I miss
something?
I'm new to coding and prone to such things.
Click to expand...


You'd better post the rest of the code. You mentioned that you have bound
controls on the form (and hence the form is bound), but I'm not sure that
fits well into the usage scenario you described. In addition to posting the
code you're using, could you please describe the user's intended interaction
with the form from start to finish?
 
Dirk,

Ok. Here goes. Purpose: We are trying to collect feedback from the users
about the ways these two things (AASSID and TARSkillClusterID) interact. The
intented use is to record a rating (1-3 so I'm using an option group bound to
ConvActivityRating) and comments (bound to ConvActivitycomments) from the
users about the various intersections of AASSID and TARSkillClusterID.

The table has fields(of type): RtgID(autonumber), TARSkillClusterID(number),
AASSID(number), ConvActivityRating(number), and ConvActivityComments(text-
maybe should be memo).

The form has controls in the header, details, and footer.
In the header, there are three unbound controls. The first is a combo box to
correctly filter the second. The second is a listbox to correctly filter the
third. The third is a listbox (lstGroupingSelect), which allows the user to
select the desired TARSkillClusterID.
In the details section, the first control is a listbox (unbound) which
displays (no add or edits here) the results of a query based on the
TARSkillClusterID. The users need to see this information to produce their
rating. The second control is the listbox (unbound) also known as
lstAASSselect. I'll post the code in the after event procedure below. The
third control(s?) is an option group(bound to ConvActivityRating). The fourth
control is the text field for user comments (bound to ConvActivityComments).
In the footer is a subform which allows the user to make suggestions. I'll
give you more detail on this if you think it is relevant.

On to the code....

Private Sub LstAASSselect_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TARSkillClusterID] = " & Me!lstGroupingSelect & " AND _
[AASSID] = " & Me!LstAASSselect
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Please let me know if I need to clarify anything. Thanks again!

WestWingFan
 
WestWingFan said:
Dirk,

Ok. Here goes. Purpose: We are trying to collect feedback from the users
about the ways these two things (AASSID and TARSkillClusterID) interact.
The
intented use is to record a rating (1-3 so I'm using an option group bound
to
ConvActivityRating) and comments (bound to ConvActivitycomments) from the
users about the various intersections of AASSID and TARSkillClusterID.

The table has fields(of type): RtgID(autonumber),
TARSkillClusterID(number),
AASSID(number), ConvActivityRating(number), and ConvActivityComments(text-
maybe should be memo).

The form has controls in the header, details, and footer.
In the header, there are three unbound controls. The first is a combo box
to
correctly filter the second. The second is a listbox to correctly filter
the
third. The third is a listbox (lstGroupingSelect), which allows the user
to
select the desired TARSkillClusterID.
In the details section, the first control is a listbox (unbound) which
displays (no add or edits here) the results of a query based on the
TARSkillClusterID. The users need to see this information to produce their
rating. The second control is the listbox (unbound) also known as
lstAASSselect. I'll post the code in the after event procedure below. The
third control(s?) is an option group(bound to ConvActivityRating). The
fourth
control is the text field for user comments (bound to
ConvActivityComments).
In the footer is a subform which allows the user to make suggestions. I'll
give you more detail on this if you think it is relevant.

On to the code....

Private Sub LstAASSselect_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TARSkillClusterID] = " & Me!lstGroupingSelect & " AND _
[AASSID] = " & Me!LstAASSselect
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...

That code is wrong. In particular, the FindFirst call is *not* what I
posted, and not equivalent to it. But also, the checking for the "record
not found" condition is not correct for an .mdb or .accdb file. (Yes, I
know that matches what the "Find Record" combo box wizard produces, but it's
still wrong.) Try this:

'----- start of revised code -----
Private Sub LstAASSselect_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[TARSkillClusterID] = " & Me!lstGroupingSelect & _
" AND [AASSID] = " & Me!LstAASSselect
If .NoMatch Then
MsgBox _
"No record was found matching your selection.", _
vbInformation, _
"Record Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
'----- end of revised code -----
Please let me know if I need to clarify anything. Thanks again!
Click to expand...

Let me ask a couple of questions to verify and clarify my understanding.

Are you saying that the table is prepopulated with one and only one record
for every combination of TARSkillClusterID and AASSID? So the purpose of
this form is to allow the user to update those existing records?

What is the user's intended interaction with the form? Does the following
describe it?

1. Open Form

2. Choose from combo in header

3. Choose from first list box in header

4. Choose from lstGroupingSelect (in header)

5. Choose from lstAASSselect (in detail)

(form positions to record matching lstGroupingSelect and lstAASSselect)

6. Edit ConvActivityRating and ConvActivityComments

7. Enter suggestion in subform if desired

8. Repeat from step 5 until done with current TARSkillClusterID

9. Repeat from step 4 until done with current supergroup (defined by step 3)

10. Repeat from step 3 until done with current super-supergroup (defined by
step 2)

11. Repeat from step 2 until done with all records.

12. Close form.

Is there any other code behind this form? If my understanding is correct,
then I would expect the code revision above to get the job done.
 
Dirk,

You hit it right on the money. It works great! Thanks for your help.

Yes, the table is prepopulated and the users are just updating the records.
Your description of the use is very clear and correct. The only other code is
to requery so the combo/list boxes update accordingly.

You truly are a MVP! Thanks again!

Dirk Goldgar said:
WestWingFan said:
Dirk,

Ok. Here goes. Purpose: We are trying to collect feedback from the users
about the ways these two things (AASSID and TARSkillClusterID) interact.
The
intented use is to record a rating (1-3 so I'm using an option group bound
to
ConvActivityRating) and comments (bound to ConvActivitycomments) from the
users about the various intersections of AASSID and TARSkillClusterID.

The table has fields(of type): RtgID(autonumber),
TARSkillClusterID(number),
AASSID(number), ConvActivityRating(number), and ConvActivityComments(text-
maybe should be memo).

The form has controls in the header, details, and footer.
In the header, there are three unbound controls. The first is a combo box
to
correctly filter the second. The second is a listbox to correctly filter
the
third. The third is a listbox (lstGroupingSelect), which allows the user
to
select the desired TARSkillClusterID.
In the details section, the first control is a listbox (unbound) which
displays (no add or edits here) the results of a query based on the
TARSkillClusterID. The users need to see this information to produce their
rating. The second control is the listbox (unbound) also known as
lstAASSselect. I'll post the code in the after event procedure below. The
third control(s?) is an option group(bound to ConvActivityRating). The
fourth
control is the text field for user comments (bound to
ConvActivityComments).
In the footer is a subform which allows the user to make suggestions. I'll
give you more detail on this if you think it is relevant.

On to the code....

Private Sub LstAASSselect_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[TARSkillClusterID] = " & Me!lstGroupingSelect & " AND _
[AASSID] = " & Me!LstAASSselect
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...

That code is wrong. In particular, the FindFirst call is *not* what I
posted, and not equivalent to it. But also, the checking for the "record
not found" condition is not correct for an .mdb or .accdb file. (Yes, I
know that matches what the "Find Record" combo box wizard produces, but it's
still wrong.) Try this:

'----- start of revised code -----
Private Sub LstAASSselect_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[TARSkillClusterID] = " & Me!lstGroupingSelect & _
" AND [AASSID] = " & Me!LstAASSselect
If .NoMatch Then
MsgBox _
"No record was found matching your selection.", _
vbInformation, _
"Record Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
'----- end of revised code -----
Please let me know if I need to clarify anything. Thanks again!
Click to expand...

Let me ask a couple of questions to verify and clarify my understanding.

Are you saying that the table is prepopulated with one and only one record
for every combination of TARSkillClusterID and AASSID? So the purpose of
this form is to allow the user to update those existing records?

What is the user's intended interaction with the form? Does the following
describe it?

1. Open Form

2. Choose from combo in header

3. Choose from first list box in header

4. Choose from lstGroupingSelect (in header)

5. Choose from lstAASSselect (in detail)

(form positions to record matching lstGroupingSelect and lstAASSselect)

6. Edit ConvActivityRating and ConvActivityComments

7. Enter suggestion in subform if desired

8. Repeat from step 5 until done with current TARSkillClusterID

9. Repeat from step 4 until done with current supergroup (defined by step 3)

10. Repeat from step 3 until done with current super-supergroup (defined by
step 2)

11. Repeat from step 2 until done with all records.

12. Close form.

Is there any other code behind this form? If my understanding is correct,
then I would expect the code revision above to get the job done.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top