473,289 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Form/Subform Data Linkage Problem

sickenhoofer
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, 93 views)
File Type: jpg TableRelationships.jpg (16.9 KB, 95 views)
Jun 19 '09 #1
0 1685

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

Similar topics

4
by: William Wisnieski | last post by:
Hello Everyone, Access 2000 I have a form with multiple pages on it. There is one text field on the third page of the form that I need the user to complete before leaving the form or moving...
3
by: Earthling | last post by:
Any help would be appreciated to solve the following simple problem that I will describe. *** There is a form called "red chocolate form". The form has a particular subform field that has a...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
7
by: Stu | last post by:
Hi, I have a combobox who's values change the recordsource of the form. Within this form, there is a subform, whos records also need to change pending the value in the combobox. I am able to get...
6
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after...
3
by: Darin | last post by:
I have a problem I just can't figure out. I have a form with a subform, and the recordsource of the subform has criteria based on some unbound fields in the parent form so that data in the parent...
11
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
4
by: sparks | last post by:
TRYING TO USE SHORT NAMES might make more since. I have a main form and its tied to tblmain subform1 and it is tied to tbl1 subsubformA on subform1 tied to tbl2 MAIN---tblmain----autoid...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.