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

  • Thread starter Thread starter zufie
  • Start date Start date
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
 
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...
 
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...
 
Back
Top