By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,210 Members | 1,509 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,210 IT Pros & Developers. It's quick & easy.

Form/Subform Data Linkage Problem

sickenhoofer
P: 19
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!
Attached Images
File Type: jpg MainFormSnapshot.jpg (16.8 KB, 76 views)
File Type: jpg TableRelationships.jpg (16.9 KB, 72 views)
Jun 19 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.