I am having some problems with a form and its subforms. It was designed to click on a list of numbers on a subform, which causes another subform's data to change to data relevant to the number selected.
Attached are 2 pictures: 1. snapshot of master form w both subforms; 2. snapshot of relationships for tables involved in problem. Here is the main set up info:
Table 1 = tblProviderMain
ProviderMainID (PK - autonum)
Table 2 = tblProviderNumbers
ProviderNumberID (PK - autonum)
ProviderMainID (foreignKey)
Table 3 = tblServiceLocations
ServiceLocationID (PK - autonum)
Table 4 = tblProviderNumberJoined2Location
ProviderNumberJoined2LocationID (PK - autonum)
ProviderNumberID (foreign Key from Table 2)
ServiceLocationID (foreign Key from Table 3)
Subform set up info:
Subform 1
Name = frmProviderNumbers_BCBSList
Source = frmNumbers_BCBS (the source of this form is a qryNumbers_BCBS as follows)
SELECT [NumberType] & " - " & [Number] AS NumberAndType, tblProviderNumbers.ProviderNumberID, tblProviderNumbers.ProviderMainID
FROM tblProviderNumbers
WHERE ((([NumberType] & " - " & [Number]) Like "BCBS" & "*"));
Subform 2
Name = frmProviderBillingNumbers_BCBS_sbfrm
Source = frmProviderBillingNumbers_BCBS (source for this form is the following qryProviderBillingNumbers_BCBS)
SELECT DISTINCT tblProviderNumbers.ProviderNumberID, tblProviderNumbers.ProviderMainID, tblProviderNumbers.EnterDate, tblProviderNumbers.NumberType, tblProviderNumbers.ProviderbyLocationCode, tblProviderNumbers.StateCode, tblProviderNumbers.Number, tblProviderNumbers.EffectiveDate, tblProviderNumbers.TerminationDate, tblProviderNumbers.Specialty, tblProviderNumbers.Taxonomy, tblProviderNumbers.EnrollDate, tblProviderNumbers.TermEnrollDate, tblProviderNumbers.ReactivationDate, tblProviderNumbers.GroupNumber, tblProviderNumbers.Verified, tblProviderNumbers.Source, tblProviderNumbers.VerifyDate, tblProviderNumbers.Status, tblProviderNumbers.Comment, tblProviderNumberJoined2Location.TRADPinNumber, tblProviderNumberJoined2Location.ServiceLocationID , tblProviderNumbers.BenefitCode, [tblLocations_Service].[ServiceLocationID] & " - " & [ServiceLocationAddr1] & ", " & [ServiceLocationAddr2] AS ServiceLocation
FROM tblProviderNumbers INNER JOIN (tblLocations_Service INNER JOIN tblProviderNumberJoined2Location ON tblLocations_Service.ServiceLocationID = tblProviderNumberJoined2Location.ServiceLocationID ) ON tblProviderNumbers.ProviderNumberID = tblProviderNumberJoined2Location.ProviderNumberID
WHERE (((tblProviderNumbers.ProviderNumberID)=[Forms]![frmProviderMain]![frmNumbers_BCBSList].[Form].[ProviderID]) AND ((tblProviderNumbers.NumberType)="BCBS"));
When end user clicks on a number listed on the left subform (frmProviderNumbers_BCBSList), an event procedure causes the detail of the subform on the right (frmProviderBillingNumbers_BCBS_sbfrm) to change. Here is the coding for the event procedure contained within properties of first subform. It refers to control NumberandType.
Private Sub NumberAndType_Click()
Forms![frmProviderMain]![frmProviderBillingNumbers_BCBS_sbfrm].Requery
End Sub
The problem:
When I open the main form (frmProviderMain) I get a message box to ENTER PARAMETER VALUE . . . .which contains text from query above . . ."[Forms]![frmProviderMain]![frmNumbers_BCBSList].[Form].[ProviderID]"
I appreciate any help. My head is getting kind of sore from beating it against the wall!