Cannot add or edit to my form, nor select from the combo boxes on myform

Z

zufie

I cannot add or edit to my form, nor select from the combo boxes on my
form.

Here is the code behind my command button that brings up my form via a
query:

Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuery5"

stLinkCriteria = "[CallDate] Between #" & Me.txtBeginQAEvry5th &
"# And #" & Me.txtEndQAStopEvry5th & "#"

Debug.Print stLinkCriteria

DoCmd.OPENFORM stDocName, acNormal, "qryEvery5thQuery",
stLinkCriteria, acFormEdit

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub
-----------------------------------------------------------------------------------
HERE is my code behind my query, "qryEvery5thQuery":

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate, [IBCCP
Referral].CallTaker, [IBCCP Referral].ProviderNameNumber, [IBCCP
Referral].HowHear, [IBCCP Referral].[IRIS Referral Number], [IBCCP
Referral]![First Name] & " " & [IBCCP Referral]![Middle Initial] & "
" & [IBCCP Referral]![Last Name] AS FullName, [IBCCP
Referral].LanguagePreferenceID, [IBCCP Referral].Age, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].Comments, [IBCCP
Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP Referral].DateSent, [IBCCP
Referral].DateSentTwo, [IBCCP Referral].DateSentThree, [IBCCP
Referral].SentTo, [IBCCP Referral].SentToTwo, [IBCCP
Referral].SentToThree, [IBCCP Referral].QualityAssurance, [IBCCP
Referral].ChckIBCCP
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
[IBCCP Referral].CallTaker, [IBCCP Referral].ProviderNameNumber,
[IBCCP Referral].HowHear, [IBCCP Referral].[IRIS Referral Number],
[IBCCP Referral]![First Name] & " " & [IBCCP Referral]![Middle
Initial] & " " & [IBCCP Referral]![Last Name], [IBCCP
Referral].LanguagePreferenceID, [IBCCP Referral].Age, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].Comments, [IBCCP
Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP Referral].DateSent, [IBCCP
Referral].DateSentTwo, [IBCCP Referral].DateSentThree, [IBCCP
Referral].SentTo, [IBCCP Referral].SentToTwo, [IBCCP
Referral].SentToThree, [IBCCP Referral].QualityAssurance, [IBCCP
Referral].ChckIBCCP, DCount("[Caller ID]","IBCCP Referral","[Caller
ID] <= " & [Caller ID]) Mod 5
HAVING (((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " &
[Caller ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

Thankful for any ideas you may have,

John
 
J

Jerry Whittle

GROUP BY

A Group By will make the query not updatable. You will not be able to edit
or add records through that query.
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Click to expand...
Agencies].ID = [IBCCP Referral].AgencyID

Even if you do remove the group by, you have to make sure that the two
fields above are joined in the Relationships window with Referiential
Integrity enabled to have a chance that you could use the query to modifiy
data.

For more info on updatable queries, see the following:
http://support.microsoft.com/?kbid=328828
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


zufie said:
I cannot add or edit to my form, nor select from the combo boxes on my
form.

Here is the code behind my command button that brings up my form via a
query:

Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuery5"

stLinkCriteria = "[CallDate] Between #" & Me.txtBeginQAEvry5th &
"# And #" & Me.txtEndQAStopEvry5th & "#"

Debug.Print stLinkCriteria

DoCmd.OPENFORM stDocName, acNormal, "qryEvery5thQuery",
stLinkCriteria, acFormEdit

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub
-----------------------------------------------------------------------------------
HERE is my code behind my query, "qryEvery5thQuery":

SELECT [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate, [IBCCP
Referral].CallTaker, [IBCCP Referral].ProviderNameNumber, [IBCCP
Referral].HowHear, [IBCCP Referral].[IRIS Referral Number], [IBCCP
Referral]![First Name] & " " & [IBCCP Referral]![Middle Initial] & "
" & [IBCCP Referral]![Last Name] AS FullName, [IBCCP
Referral].LanguagePreferenceID, [IBCCP Referral].Age, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].Comments, [IBCCP
Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP Referral].DateSent, [IBCCP
Referral].DateSentTwo, [IBCCP Referral].DateSentThree, [IBCCP
Referral].SentTo, [IBCCP Referral].SentToTwo, [IBCCP
Referral].SentToThree, [IBCCP Referral].QualityAssurance, [IBCCP
Referral].ChckIBCCP
FROM [IBCCP Agencies] INNER JOIN [IBCCP Referral] ON [IBCCP
Agencies].ID = [IBCCP Referral].AgencyID
GROUP BY [IBCCP Referral].[Caller ID], [IBCCP Referral].CallDate,
[IBCCP Referral].CallTaker, [IBCCP Referral].ProviderNameNumber,
[IBCCP Referral].HowHear, [IBCCP Referral].[IRIS Referral Number],
[IBCCP Referral]![First Name] & " " & [IBCCP Referral]![Middle
Initial] & " " & [IBCCP Referral]![Last Name], [IBCCP
Referral].LanguagePreferenceID, [IBCCP Referral].Age, [IBCCP
Referral].PrimaryPhone, [IBCCP Referral].SecondaryPhone, [IBCCP
Referral].Address, [IBCCP Referral].City, [IBCCP Referral].Zip, [IBCCP
Referral].[County Code ID], [IBCCP Agencies].Name, [IBCCP
Referral].OtherReferralAgency, [IBCCP Referral].Comments, [IBCCP
Referral].AgencyContactYes, [IBCCP Referral].AgencyContactNo, [IBCCP
Referral].DaysBeforeHeardFromAgency1Week, [IBCCP
Referral].DaysBeforeHeardFromAgency2Weeks, [IBCCP
Referral].DaysBeforeHeardFromAgency3orMoreWeeks, [IBCCP
Referral].BecomeIBCCPClientYes, [IBCCP Referral].BecomeIBCCPClientNo,
[IBCCP Referral].BecomeIBCCPClientDontKnow, [IBCCP
Referral].SatisfiedWithHelpReceivedYes, [IBCCP
Referral].SatisfiedWithHelpReceivedNo, [IBCCP
Referral].InNeedFurtherAssistanceYes, [IBCCP
Referral].InNeedFurtherAssistanceNo, [IBCCP Referral].DateSent, [IBCCP
Referral].DateSentTwo, [IBCCP Referral].DateSentThree, [IBCCP
Referral].SentTo, [IBCCP Referral].SentToTwo, [IBCCP
Referral].SentToThree, [IBCCP Referral].QualityAssurance, [IBCCP
Referral].ChckIBCCP, DCount("[Caller ID]","IBCCP Referral","[Caller
ID] <= " & [Caller ID]) Mod 5
HAVING (((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " &
[Caller ID]) Mod 5)=0))
ORDER BY [IBCCP Referral].[Caller ID];

Thankful for any ideas you may have,

John
Click to expand...
 
K

Krzysztof Naworyta

Juzer Jerry Whittle <[email protected]> napisa³


(...)
| Even if you do remove the group by, you have to make sure that the two
| fields above are joined in the Relationships window with Referiential
| Integrity enabled to have a chance that you could use the query to
| modifiy data.
|
| For more info on updatable queries, see the following:
| http://support.microsoft.com/?kbid=328828

There's no need of having any relation. Referential integrity is not
required at all!
The minimum is that tableOne has unique index (not even primary key!) on
joined
field.
Of course there are many other restrictions that can make query not
updateable...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't get Enter Parameter Value boxes to disappear 1

Top