SELECT query question

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

Hello All,
I am running into a quandry as to why the following SELECT
statement does not work.
Some background is I have a main form
(usrfrmClaimReviewAudit) that has for fields on it;
ClientCode (txt)
ClaimStyle (cbo)
StartDate (txt)
EndDate (txt)
There is also a combobox (cbxStartDate) that only provides
a listing of all the start dates for the selected client.
A listbox (lstStartDates) also graces the main form.

There is a subform (usrfrmClientReviewAuditClaims) on the
main form as well. The subform is where the claims are
entered. On the subform there are the following fields;
ClaimNumber
ClaimantName
DateOfInjury
ClaimStyleProcess
ClaimOffice

Now comes the quandry that is actually two-fold.
1) When the date is selected from cbxStartDate I need
the lstStartDates to populate with the appropriate claims
associated with the date selected. That is the following
SELECT statement. At least I'm not getting any errors.
It just doesn't populate the listbox and requeries.
2) When a claim is selected in the listbox the subform
makes the change. I tried the bookmark method but an
error occurs for this. Any suggestions would help.

Here is the language used attempting to populate the
listbox. What's wrong?

Private Sub cbxStartDate_AfterUpdate()
Forms![usrfrmClaimReviewAudit]!
[lstClaimsReviewed].RowSource = _
"SELECT " & _
"usrtblClaimReviewAuditClaims.ClaimReviewAuditC
laimsID, " & _
"usrtblClaimReviewAuditClaims.Clientcode, " & _
"usrtblClaimReviewAuditClaims.ClaimNumber, " &
_
"usrtblClaimReviewAuditClaims.StartDate " & _
"FROM usrtblClaimReviewAudit " & _
"INNER JOIN usrtblClaimReviewAuditClaims, " & _
"ON (usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode), " & _
"AND (usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate), " & _
"WHERE (((usrtblClaimReviewAuditClaims.Clientcode)
= [Forms]![usrfrmClaimReviewAudit]![Clientcode]), " & _
"And ((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])), " & _
"ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;"

Me.Requery

End Sub

Thanks in advance to anyone who works on this. I am
getting frustrated over it.

*** John
 
Looks like one of those complex headaches that sometimes
just needs a small tweaking. Nonetheless, they are
frustrating to no end. All I'm going to offer is a
couple possibilities from the "outside looking in"
perspective.

Have you tried applying the SQL statement in the
On_Change Event instead of After_Update. I find that
On_Change often works better for me. Next, I noticed at
the bottom you have

Me.Requery

Instead, try to requery only the list box

Me.lstClaimsReviewed.Requery

Or, instead of Me.Requery try Me.Recalc.

Sorry, but to me your SQL looks good, and you said it was
working, the problem I think is likely in just refreshing
the data displayed in the list box and I think either -
or both - of the above might resolve the issue.

Good Luck!
-----Original Message-----
Hello All,
I am running into a quandry as to why the following SELECT
statement does not work.
Some background is I have a main form
(usrfrmClaimReviewAudit) that has for fields on it;
ClientCode (txt)
ClaimStyle (cbo)
StartDate (txt)
EndDate (txt)
There is also a combobox (cbxStartDate) that only provides
a listing of all the start dates for the selected client.
A listbox (lstStartDates) also graces the main form.

There is a subform (usrfrmClientReviewAuditClaims) on the
main form as well. The subform is where the claims are
entered. On the subform there are the following fields;
ClaimNumber
ClaimantName
DateOfInjury
ClaimStyleProcess
ClaimOffice

Now comes the quandry that is actually two-fold.
1) When the date is selected from cbxStartDate I need
the lstStartDates to populate with the appropriate claims
associated with the date selected. That is the following
SELECT statement. At least I'm not getting any errors.
It just doesn't populate the listbox and requeries.
2) When a claim is selected in the listbox the subform
makes the change. I tried the bookmark method but an
error occurs for this. Any suggestions would help.

Here is the language used attempting to populate the
listbox. What's wrong?

Private Sub cbxStartDate_AfterUpdate()
Forms![usrfrmClaimReviewAudit]!
[lstClaimsReviewed].RowSource = _
"SELECT " & _
"usrtblClaimReviewAuditClaims.ClaimReviewAudi tC
laimsID, " & _
"usrtblClaimReviewAuditClaims.Clientcode, " & _
"usrtblClaimReviewAuditClaims.ClaimNumber, " &
_
"usrtblClaimReviewAuditClaims.StartDate " & _
"FROM usrtblClaimReviewAudit " & _
"INNER JOIN usrtblClaimReviewAuditClaims, " & _
"ON (usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode), " & _
"AND (usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate), " & _
"WHERE (((usrtblClaimReviewAuditClaims.Clientcode)
= [Forms]![usrfrmClaimReviewAudit]![Clientcode]), " & _
"And ((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])), " & _
"ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;"

Me.Requery

End Sub

Thanks in advance to anyone who works on this. I am
getting frustrated over it.

*** John
.
Click to expand...
 
JohnE said:
Hello All,
I am running into a quandry as to why the following SELECT
statement does not work.
Some background is I have a main form
(usrfrmClaimReviewAudit) that has for fields on it;
ClientCode (txt)
ClaimStyle (cbo)
StartDate (txt)
EndDate (txt)
There is also a combobox (cbxStartDate) that only provides
a listing of all the start dates for the selected client.
A listbox (lstStartDates) also graces the main form.

There is a subform (usrfrmClientReviewAuditClaims) on the
main form as well. The subform is where the claims are
entered. On the subform there are the following fields;
ClaimNumber
ClaimantName
DateOfInjury
ClaimStyleProcess
ClaimOffice

Now comes the quandry that is actually two-fold.
1) When the date is selected from cbxStartDate I need
the lstStartDates to populate with the appropriate claims
associated with the date selected. That is the following
SELECT statement. At least I'm not getting any errors.
It just doesn't populate the listbox and requeries.
2) When a claim is selected in the listbox the subform
makes the change. I tried the bookmark method but an
error occurs for this. Any suggestions would help.

Here is the language used attempting to populate the
listbox. What's wrong?

Private Sub cbxStartDate_AfterUpdate()
Forms![usrfrmClaimReviewAudit]!
[lstClaimsReviewed].RowSource = _
"SELECT " & _
"usrtblClaimReviewAuditClaims.ClaimReviewAuditC
laimsID, " & _
"usrtblClaimReviewAuditClaims.Clientcode, " & _
"usrtblClaimReviewAuditClaims.ClaimNumber, " &
_
"usrtblClaimReviewAuditClaims.StartDate " & _
"FROM usrtblClaimReviewAudit " & _
"INNER JOIN usrtblClaimReviewAuditClaims, " & _
"ON (usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode), " & _
"AND (usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate), " & _
"WHERE (((usrtblClaimReviewAuditClaims.Clientcode)
= [Forms]![usrfrmClaimReviewAudit]![Clientcode]), " & _
"And ((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])), " & _
"ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;"

Me.Requery

End Sub

Thanks in advance to anyone who works on this. I am
getting frustrated over it.

*** John
Click to expand...

Your SQL statement is invalid -- it has commas in all kinds of places
where they don't belong. It comes out to this:

------------ start of erroneous SQL --------------
SELECT
usrtblClaimReviewAuditClaims.ClaimReviewAuditClaimsID,
usrtblClaimReviewAuditClaims.Clientcode,
usrtblClaimReviewAuditClaims.ClaimNumber,
usrtblClaimReviewAuditClaims.StartDate
FROM
usrtblClaimReviewAudit
INNER JOIN
usrtblClaimReviewAuditClaims,
ON
(usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode),
AND
(usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate),
WHERE
(
((usrtblClaimReviewAuditClaims.Clientcode) =
[Forms]![usrfrmClaimReviewAudit]![Clientcode]),
And
((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])
),
ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;

------------ end of erroneous SQL --------------

You need to remove some of the commas so that it comes out to this:

------------ start of revised SQL --------------
SELECT
usrtblClaimReviewAuditClaims.ClaimReviewAuditClaimsID,
usrtblClaimReviewAuditClaims.Clientcode,
usrtblClaimReviewAuditClaims.ClaimNumber,
usrtblClaimReviewAuditClaims.StartDate
FROM
usrtblClaimReviewAudit
INNER JOIN
usrtblClaimReviewAuditClaims
ON
(usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode)
AND
(usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate)
WHERE
(
((usrtblClaimReviewAuditClaims.Clientcode) =
[Forms]![usrfrmClaimReviewAudit]![Clientcode])
And
((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])
)
ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;

------------ end of revised SQL --------------

I can't promise that will give you the results you want, and I may even
have overlooked one or more errors, but it should be a lot closer.
Incidentally, I don't see where you're actually using any fields from
usrtblClaimReviewAudit. Are you joining to it so as to limit the
records returned from usrtblClaimReviewAuditClaims?
 
David, thanks for the info. I ended going into the query
builder and putting it together and it worked after I
added the requery to the after update of the
cbxStartDate. Now I need to the the following so it works.

Me.RecordsetClone.FindFirst "[ClaimReviewAuditClaimsID]
=" & Me![lstClaimsReviewed]
Me.Bookmark = Me.RecordsetClone.Bookmark

Thanks.
*** John

-----Original Message-----
Looks like one of those complex headaches that sometimes
just needs a small tweaking. Nonetheless, they are
frustrating to no end. All I'm going to offer is a
couple possibilities from the "outside looking in"
perspective.

Have you tried applying the SQL statement in the
On_Change Event instead of After_Update. I find that
On_Change often works better for me. Next, I noticed at
the bottom you have

Me.Requery

Instead, try to requery only the list box

Me.lstClaimsReviewed.Requery

Or, instead of Me.Requery try Me.Recalc.

Sorry, but to me your SQL looks good, and you said it was
working, the problem I think is likely in just refreshing
the data displayed in the list box and I think either -
or both - of the above might resolve the issue.

Good Luck!
-----Original Message-----
Hello All,
I am running into a quandry as to why the following SELECT
statement does not work.
Some background is I have a main form
(usrfrmClaimReviewAudit) that has for fields on it;
ClientCode (txt)
ClaimStyle (cbo)
StartDate (txt)
EndDate (txt)
There is also a combobox (cbxStartDate) that only provides
a listing of all the start dates for the selected client.
A listbox (lstStartDates) also graces the main form.

There is a subform (usrfrmClientReviewAuditClaims) on the
main form as well. The subform is where the claims are
entered. On the subform there are the following fields;
ClaimNumber
ClaimantName
DateOfInjury
ClaimStyleProcess
ClaimOffice

Now comes the quandry that is actually two-fold.
1) When the date is selected from cbxStartDate I need
the lstStartDates to populate with the appropriate claims
associated with the date selected. That is the following
SELECT statement. At least I'm not getting any errors.
It just doesn't populate the listbox and requeries.
2) When a claim is selected in the listbox the subform
makes the change. I tried the bookmark method but an
error occurs for this. Any suggestions would help.

Here is the language used attempting to populate the
listbox. What's wrong?

Private Sub cbxStartDate_AfterUpdate()
Forms![usrfrmClaimReviewAudit]!
[lstClaimsReviewed].RowSource = _
"SELECT " & _
"usrtblClaimReviewAuditClaims.ClaimReviewAudi tC
laimsID, " & _
"usrtblClaimReviewAuditClaims.Clientcode, " & _
"usrtblClaimReviewAuditClaims.ClaimNumber, " &
_
"usrtblClaimReviewAuditClaims.StartDate " & _
"FROM usrtblClaimReviewAudit " & _
"INNER JOIN usrtblClaimReviewAuditClaims, " & _
"ON (usrtblClaimReviewAudit.ClientCode =
usrtblClaimReviewAuditClaims.ClientCode), " & _
"AND (usrtblClaimReviewAudit.StartDate =
usrtblClaimReviewAuditClaims.StartDate), " & _
"WHERE (((usrtblClaimReviewAuditClaims.Clientcode)
= [Forms]![usrfrmClaimReviewAudit]![Clientcode]), " & _
"And ((usrtblClaimReviewAuditClaims.StartDate) =
[Forms]![usrfrmClaimReviewAudit]![cbxStartDate])), " & _
"ORDER BY
usrtblClaimReviewAuditClaims.ClaimNumber;"

Me.Requery

End Sub

Thanks in advance to anyone who works on this. I am
getting frustrated over it.

*** John
.
Click to expand...
.
Click to expand...
 
Back
Top