Subform Coding

[mommom]

Listed below is the information from my form and coding received. It worked when it was part of my regular form. The user needed it changed because they wanted to be able to select multiple items. I created a subform and entered in the same coding, however it is not working. Is there something else that needs to be added to make this happen?

Combo1 : What
ID What
1 PC
2 Wireless Device
3 Wireless Service

Combo2 : Type
ID Type WhatID
1 Laptop 1 (PC)
2 Cellphone 2 (Wireless Device)

Combo3 : Make
ID Make Type
1 Dell 1 (Laptop)
2 Verizon 2 (Cellphone)
3 Sprint 2 "
4 T-Mobile 2 "

Combo4: Model
ID Model Make
1 D300 1 (Dell)
2 LG3300 2 (Verizon)




Remou (TechnicalUser) 8 Apr 06 20:04
You need something a little like this. I have called my form frmCombos, you need to change this to the right name. You must also change the names of the tables (tblWhat etc) to match your tables, and the combo controls (cboWhat etc) to match your controls.

Row Source for combos:
cboWhat : SELECT [tblWhat].[ID], [tblWhat].[What] FROM [tblWhat]
cboType : SELECT tblType.ID, tblType.Type FROM tblType WHERE (((tblType.WhatID)=[Forms]![frmCombos]![cboWhat]));
cboMake : SELECT tblMake.ID, tblMake.Make FROM tblMake WHERE (((tblMake.TypeID)=[Forms]![frmCombos]![cboType]));
cboModel : SELECT tblModel.ID, tblModel.Model FROM tblModel WHERE (((tblModel.MakeID)=[Forms]![frmCombos]![cboMake]));

Then you will need some code. This is just the very bones, and bare at that, so that you can see how it works. You will need error coding and code to deal with items that are not in the lists (Not In List).

CODE
Private Sub cboWhat_AfterUpdate()
Me.cboType = ""
Me.cboType.Requery
Me.cboMake = ""
Me.cboMake.Requery
Me.cboModel = ""
Me.cboModel.Requery
End Sub

Private Sub cboType_AfterUpdate()
Me.cboMake = ""
Me.cboMake.Requery
Me.cboModel = ""
Me.cboModel.Requery
End Sub

Private Sub cboMake_AfterUpdate()
Me.cboModel = ""
Me.cboModel.Requery
End Sub

 

[Remou]

Try adding the name of the subform control to these lines:
[tt]cboType : SELECT tblType.ID, tblType.Type FROM tblType WHERE (((tblType.WhatID)=[Forms]![frmCombos]![NameOfSubformControl].Form![cboWhat]));
cboMake : SELECT tblMake.ID, tblMake.Make FROM tblMake WHERE (((tblMake.TypeID)=[Forms]![frmCombos]![NameOfSubformControl].Form![cboType]));
cboModel : SELECT tblModel.ID, tblModel.Model FROM tblModel WHERE (((tblModel.MakeID)=[Forms]![frmCombos][NameOfSubformControl].Form![cboMake]));[/tt]

 

[mommom]

Hello Remou,

I have made the changes, however, it is saying

Enter Parameter Value for:
Form!Tickets!tblRFES.Form!WhatID
Form!Tickets!tblRFES.Form!MakeID
Form!Tickets!tblRFES.Form!ModelID

The name of my subform is: tblRFES
Listed in the table are:

RFESID
TicketID
WhatID
TypeID
MakeID
ModelID

The RFESID and the TicketID are both hidden. The way it is set up, when the information is added into the form it saves in the table as ID numbers but on the report the actual information will appear.

 

[Remou]

Can you check that tblRFES is the name of the subform control and not just the name of the subform? The subform control name is usually the same as the subform name, but not always.

 

[mommom]

Yes, the name is tblRFES

The Link Child Fields is: TicketID

The Link Master Fields is: TicketId

Not sure if that has anything to do with it.

 

[Remou]

No, I do not think so. When it says "Enter parameter values for ..." it means that it cannot find something.
I have just noticed that you have Form, rather than Forms. Have you tried (?):
Forms!Tickets!tblRFES.Form!WhatID

 

[mommom]

I typed in wrong to you. I do have "Forms" listed. Below is what I actually have listed. Where I orginally had What and WhatID it's been changed to Category and CategoryID.

SELECT tblType.TypeID, tblType.Type FROM tblType WHERE (((tblType.CategoryID)=Forms!Tickets!tblRFES.Form!Category));

SELECT tblMake.MakeID, tblMake.Make FROM tblMake WHERE (((tblMake.TypeID)=Forms!Tickets!tblRFES.Form!Type));

SELECT tblModel.ModelID, tblModel.Model FROM tblModel WHERE (((tblModel.MakeID)=[Forms]![Tickets][tblRFES].Form![Make]));

 

[Remou]

It seems likely that there is a name gone a little astray somewhere. You need to check the names carefully. I will set this out in more detail than you need, as it may prompt an idea:
[tt]Forms! : as is
Tickets! : the name of the main form
tblRFES. : the name of the subform control, not the
: form contained by the control
Form! : as is
Category : the name of an appropriate textbox on the
: subform[/tt]
It can help to paste the sql into a query design window while the form is open. This way, you can easily check if anything is being returned by the query.

 

[PHV]

I personnally don't have ever wrote any Access app, so I'm just guessing:
shouldn't a parameters form be a main form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mommom]

I have looked at all the information and still not sure why it isn't working. I just noticed also that even on the regular form that since changes have been made two of my other fields are now not working.

My Tickets form which is now tblTickets in the caption but now in the Record Source it is:

SELECT tblTickets.TicketId, tblTickets.[Date Requested], tblTickets.[Date Approved], tblTickets.[Date Deployed], tblTickets.[SPOC Ticket #], tblTickets.POCID, tblTickets.[Asset #], tblTickets.User, tblTickets.[Desk Phone], tblTickets.[E-mail], tblTickets.[Cell Phone], tblTickets.OrganizationID AS tblTickets_OrganizationID, tblTickets.SubOrganizationID AS tblTickets_SubOrganizationID, tblTickets.OriginatingLocationID, tblTickets.Bldg, tblTickets.[Office/Cube], tblTickets.DeliveryLocationID, tblTickets.Remarks, SubOrganization.SubOrganizationID AS SubOrganization_SubOrganizationID, SubOrganization.SubOrganization, SubOrganization.OrganizationID AS SubOrganization_OrganizationID, tblLocation.LocationID, tblLocation.Location, tblOrganization.OrganizationID AS tblOrganization_OrganizationID, tblOrganization.Organization FROM (tblOrganization INNER JOIN SubOrganization ON tblOrganization.OrganizationID=SubOrganization.OrganizationID) INNER JOIN (tblLocation INNER JOIN tblTickets ON tblLocation.LocationID=tblTickets.DeliveryLocationID) ON SubOrganization.SubOrganizationID=tblTickets.SubOrganizationID;

For information below the name in my Organization is
tblTickets_OrganizationID

name in my SubOrganization is
tblTickets_SubOrganizationID

SELECT tblOrganization.OrganizationID, tblOrganization.Organization FROM tblOrganization;

SELECT SubOrganization.SubOrganizationID, SubOrganization.SubOrganization FROM SubOrganization WHERE (((SubOrganization.OrganizationID)=Forms!Tickets!Organization));

 

[Remou]

Are you saying that this:
SELECT SubOrganization.SubOrganizationID, SubOrganization.SubOrganization FROM SubOrganization WHERE (((SubOrganization.OrganizationID)=Forms!Tickets!Organization));
Is not working?
What happens if you put:
SELECT SubOrganization.SubOrganizationID, SubOrganization.SubOrganization FROM SubOrganization WHERE (((SubOrganization.OrganizationID)=Forms![blue]tblTickets[/blue]!Organization));

 

[mommom]

When I put that in, the SubOrganization is blank.

 

[Remou]

I have missed your point I'm afraid, what is it that is not working. You mention that two 'fields' are not working, which are they?
Also, have you considered your form design in the light of PHV's comments?

 

[mommom]

The two fields are listed below:

Organization
Name is: tblTickets_OrganizationID

Row Source: SELECT tblOrganization.OrganizationID, tblOrganization.Organization FROM tblOrganization;


SubOrganization
Name is: tblTickets_SubOrganizationID

Row Source: SELECT SubOrganization.SubOrganizationID, SubOrganization.SubOrganization FROM SubOrganization WHERE (((SubOrganization.OrganizationID)=Forms!Tickets!Organization));

I replaced Organization with tblTickets_SubOrganizationID -still not working

 

[PHV]

Perhaps this ?
Row Source: SELECT SubOrganization.SubOrganizationID, SubOrganization.SubOrganization FROM SubOrganization WHERE SubOrganization.OrganizationID=Forms!Tickets!tblTickets_OrganizationID;

Hopefully tblTickets_OrganizationID.BoundColumn = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mommom]

PHV - That did work for "ONE" record then when I tried to go into putting in another record it wouldn't read the second line. In additon, I received:

The Microsoft Jet database engine cannot find a record in the table <name> with key matching field(s) 'tblTickets_SubOrganizationID'.

 

[Pampers]

I personnally don't have ever wrote any Access app, so I'm just guessing

. With more then 15.000 replies on the subject...how do you do that?

Pampers
Just let it go...

 

[PHV]

how do you do that?
I simply love to play with the F2 and F1 keys ...

 

[Pampers]

I might try that a bit more too...

Pampers
Just let it go...