Can't figure out to to get what I want

[Dave14867]

Hello all,



I am trying to get a new form to display what I want in a subform, based on some information in the main form but I can't seem to get it figured out how to do it. The subform data is based on a query and what I want to do is to have the query use the DhrID and the DHR_PN:MfgPartNumber info from the main form, then run the query. How do I populate the DhrID and the DHR_PN:MfgPartNumber progmatically if you will in the Criteria fields of the query?

To illustrate what I am trying to do open the DHR Entry form and you should see the subform has 2 sets of data in it because I can't show only the one I want (either one). I want the DHR_PN:MfgPartNumber not to show up, but be pulled from the DHR Entry form, field name CmbPartNumSelect.

I hope that makes sense, if not, I'll try to clarify, the file is attached.

Essentially, I and trying to create a list of all parts that are used in the end item, PartID 2 - 8, Part ID 1 is the end item.

Cheyenne.zip

Thanks

Dave

[kd2017]

You shouldn't need to do this pragmatically. Using the query designer, in your fields criteria boxes you can right click then select build. From there you can use the bottom left listbox to select your form, then use the middle or right listbox to select your field or control you want to reference.

Here is an example youtube video: https://www.youtube.com/watch?v=bSV1JXFsFaA

[CJ_London]

not at all clear what you are asking,

I don't see this >> DHR_PN:MfgPartNumber info from the main form

either in the form or in the underlying recordsource

then run the query

why do you need to run a query? just set the linkmaster/child properties for the subform, you can set more than one separated with a semi colon

DhrID;DHR_PN

suggest you provide some example data and what you want

Your relationships look weird so might also be a good idea to explain what the business does and what this app is supposed to do - and explain some of the industry specific terminology - whatr is a DHR for example

[Dave14867]

kd2017,

I think I know what you mean, I'll try that, I hadn't thought of that.

Ajax,

I know DHR_PN:MfgPartNumber doesn't show up n the form but if you look at the query in the designer, it's there, I'm not sure why it doesn't show up correctly on the form.

I am not sure what you mean by "just set the linkmaster/child properties for the subform, you can set more than one separated with a semi colon".

A DHR is a "Device History Record", it's used to record the critical steps and materials in the assembly of a product. This database is a means to capture those steps and materials for many products, obviously this is a sample file in the works.

Thanks

Dave

[CJ_London]

like this

[June7]

I think Ajax is describing a compound key. Is that how your tables are related - with compound keys?

[Dave14867]

June7,

No, the tables don't have compound keys, I know about them but at this point, I don't use them. I haven't had a chance yet to try kd2017's suggestion yet, but I believe I know what he is talking about and will try that tomorrow AM. I hadn't thought about that but I think I know what he is describing.

Thanks

Dave

[ssanfu]

Hmmmmmm
Looks to me like Ajax is setting the linking Master/child fields for a Main form/sub form design.

But I can't tell what fields would be linked......

[CJ_London]

no, not a compound key. I don't understand the relationships, but was providing an answer to

query use the DhrID and the DHR_PN:MfgPartNumber info from the main form, then run the query. How do I populate the DhrID and the DHR_PN:MfgPartNumber progmatically if you will in the Criteria fields of the query?

looks like two fields are used.

result returned two recordsets, test1 and test2

[ArviLaanemets]

Based on Ajax's post (#5):
When both main form and child form source tables contain fields dhrID and dhrnumber, subform properties will be LinkMasterFields = "dhrID; dhrnumber" and LinkChildFields = "dhrID;dhrnumber";
When main form source table contains fields dhrID and dhrnumber, but linked fields in child form source table have different names, like ChildField1 and ChildField2, subform properties will be LinkMasterFields = "dhrID; dhrnumber" and LinkChildFields = "ChildField1;ChildField2";
When child form must be linked to unbound control in master form, e.g. there in no field dhrnumber in master form source table, but instead you have a unbound combo box cbbDhrNumber, which get's value dhrnumber from some lookup table, subform properties will be LinkMasterFields = "dhrID; cbbDhrNumber" and LinkChildFields = "dhrID;dhrnumber".

Linking fields MUST be present in child form's source table!

As result,
1. subform displays only records where liked fields match with according values in parent form;
2. whenever a new record is added into subform, controls with linked fields as source will automatically have same values as according current values in parent form. From this follows, user must not edit linked fields in subform - the best will be to hide them at all;
3. when you add a new record into subform but want to undo this, simply press Esc twice before the record is saved. Adding the row is terminated.

[CJ_London]

I think the OP is fixated on a vba/criteria solution without using the link filtering. Often considered good practice to minimise network traffic, but not done in this way.

[kd2017]

@Dave14867, It doesn't look like my comment in post #2 is relevant. You are in much more capable hands with the rest of these guys so I'll be sitting the rest of this out. Before I go a few comments [MfgPartNumber] As DHR_PN simply is not available on your main form, you wont be able to filter with this field as is. I believe Ajax mentioned this in post #3. I've taken some screenshots of the db that may help others figure out what is going on.

DB relationship window:



The subform's query in question:



The form and subform in question:

[kd2017]

*MAYBE* you need to change the main form to be based on a query that includes the PARENT's mfrPartNumber, then use that to filter the subform like so:

Mainform's query like this: (edit: don't need to do this. see below)



And then subform links like so:



Okay, now I'm out for real this time to let the real pros take over. Good luck!

*EDIT* (I can't help myself)
Assuming MfgPartNumber is unique, change the subform's query to also include the PartID of the Parent part. Link that PartID to the mainform's PartID field. Then you shouldn't need to use the query in the main form. This is preferable to above as MfgPartNumber is a text field, filtering instead by the ID field is the way to go.





**EDIT** do you even need to filter by mfrpartno/partid? Shouldn't the DHRID alone suffice?

[Dave14867]

I have part of what I want working, I'll post another zip file later tonight. What I am trying to do is simply create a subform on the "DHR Entry" form that loads all of the parts for the Parent PN (PartID) And then have a field that I can input how many of each part was actually used, then using a command button, have that information written to the transactions table for each part (I am assuming an append query). right now I am hung up on getting a field that I can enter the actual quantity used per part that I can enter the quantity in. I tried creating an unbound field but when I input a qty for 1 part, it populates the same number for all of the parts.

Thanks

Dave

[ArviLaanemets]

I tried creating an unbound field but when I input a qty for 1 part, it populates the same number for all of the parts.

When you have a contol in continuous form, then you don't have a control for every row - you have a control which is displayed multiple times, so the control is displayed for every visible row in form. When the control is bound, then access manages what is displayed in every visible row, as displayed value is read from according record of form data source. When the control is unbound, access can display only what you entered lately - same for every visible row