form not selecting correct record.

[Guest]

I have the following combo box, which works
SELECT [tblVersion].[verID], [tblVersion].[verPerfYearEndDate] FROM tblVersion WHERE ((([tblVersion].[peoID])=txtpeoID)) ORDER BY [tblVersion].[verPerfYearEndDate] DESC;

The form is based on the following quer
SELECT tblPeople.peoID, tblVersion.verID, ..
FROM (tblPeople INNER JOIN tblVersion ON tblPeople.peoID = tblVersion.peoID) INNER JOIN tblPlanTasks ON tblVersion.verID = tblPlanTasks.pverI
WHERE (((tblPeople.peoID)=[Forms]![frmEvaluation]![cboEvalSelectName]) AND ((tblVersion.verID)=[Forms]![frmPastEval]![cboSelectPerfYearEndDate]))

I want the form to show the version selected in the combo box, but every time I and the AND statement, it retrieves nothing
How do I get it to work
I have Me.Requery following the update of the combo box.

 

[Graham R Seach]

Since cboSelectPerfYearEndDate looks like a date, is tblVersion.verID also a
date? I ask because you're joining it to tblPlanTasks.pverID, which doesn't
look like a date, but your WHERE clause seems to imply that it is.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I have the following combo box, which works.
SELECT [tblVersion].[verID], [tblVersion].[verPerfYearEndDate] FROM

tblVersion WHERE ((([tblVersion].[peoID])=txtpeoID)) ORDER BY
[tblVersion].[verPerfYearEndDate] DESC;

The form is based on the following query
SELECT tblPeople.peoID, tblVersion.verID, ...
FROM (tblPeople INNER JOIN tblVersion ON tblPeople.peoID =

tblVersion.peoID) INNER JOIN tblPlanTasks ON tblVersion.verID =
tblPlanTasks.pverID

WHERE (((tblPeople.peoID)=[Forms]![frmEvaluation]![cboEvalSelectName]) AND ((tblVersion.verID)=[Forms]![frmPastEval]![cboSelectPerfYearEndDate]));

I want the form to show the version selected in the combo box, but every

time I and the AND statement, it retrieves nothing.

 

[Guest]

tblVersion.verID is not a date. It's an auto-number ID which I've learned to put with each field in a combo box. supposedly the combo box stores the ID matching the selected field (a date in this instance)

 

[Graham R Seach]

If the bound column for cboSelectPerfYearEndDate is the numeric ID field
(rather than the date), your query should work. If it isn't - it won't.

Also, I notice you're trying to draw values from two forms; frmEvaluation
and frmPastEval. These two forms must be open when the query evaluates.

I wonder...is one of them a subform?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

tblVersion.verID is not a date. It's an auto-number ID which I've

learned to put with each field in a combo box. supposedly the combo box
stores the ID matching the selected field (a date in this instance)

 

[Guest]

1) The bound column for cboSelectPerfYearEndDate is the numeric ID field
2) Both forms are up. frmPastEval is a popup from frmEvaluation

frmPastEval is based on qryPastEval
1) If I set the query up correctly to search for person and cboSelectPerfYearEndDate, then nothing shows in the name or cboSelectPerfYearEndDate
2) Otherwise the first date's record shows, which means the query isn't selecting on dat
3) The query does appear to work. When run on its own, with both forms up and values for name and date selected, it comes up with the correct record
4) I have a similar form, query based, with name and version cbo selections (two selections, one based on the selection of the other), that works fine.
5) so why all the hassle trying to replicate that feat
6) frmPastEval comes up with a filter on the name field set to the correct person's ID
or, plan B,
can I run a different query after the date selection and have the form set to that?

 

[Graham R Seach]

I notice that the combo's query references txtpeoID directly, when it should
be referencing it via the form (ie: [Forms]![frmEvaluation]![txtpeoID]). Try
making that change, and see how it goes.

The usual way of doing what you're trying to do is to use recordset
Bookmarks. If the above doesn't work, try adding the following to
cboSelectPerfYearEndDate's AfterUpdate event:
Dim rs As DAO.Recordset

'Only do something if the user selected a value.
If Not IsNull(Me.cboSelectPerfYearEndDate) Then
Set rs = Me.RecordSetClone

'Search for the record.
rs.FindFirst "[verID] = " & Me.cboSelectPerfYearEndDate

'Find it? If so, move to that record.
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

'Requery the combo.
Me.cboSelectPerfYearEndDate.Requery
End If

'Clean up.
rs.Close
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

1) The bound column for cboSelectPerfYearEndDate is the numeric ID field.
2) Both forms are up. frmPastEval is a popup from frmEvaluation.

frmPastEval is based on qryPastEval.
1) If I set the query up correctly to search for person and

cboSelectPerfYearEndDate, then nothing shows in the name or
cboSelectPerfYearEndDate.

2) Otherwise the first date's record shows, which means the query isn't selecting on date
3) The query does appear to work. When run on its own, with both forms up

and values for name and date selected, it comes up with the correct record.

4) I have a similar form, query based, with name and version cbo

selections (two selections, one based on the selection of the other), that
works fine.

5) so why all the hassle trying to replicate that feat?
6) frmPastEval comes up with a filter on the name field set to the correct person's ID.
or, plan B,
can I run a different query after the date selection and have the form set

to that?