adding subform to main form (1 Viewer)

[neilsolaris]

Hi,

I have a form which is working great, which I'm very happy with. I'm attached a sample database in case it helps to put my question in context.

Basically, I posted for help regarding my main form, and arnelgp suggested adding a subform below, showing the summary. I'd like to incorporate his idea, but with a tiny difference. I'd like the subform to show details relating to just the musician and job number, linked to the info on the main form (I hope that makes sense!).

So, if you open F_Payments, the first entry is Amy Jones, engagement number 1000. She has one other entry relating to that engagement number. Therefore, below I'd like the subform to list both entries for Any Jones and engagement 1000.

I've played around with it, but I'm not sure how to make it work. Ideally, instead of doing it for me, would it be possible to talk me through the process please? That way I can learn for next time.

Many thanks for your help.

 

[Gasman]

Should start searching here, there is a wealth of information.
I found this with a quick google
I was thinking of concatenating the fields, but it appears that is not the way?
https://www.access-programmers.co.uk/forums/threads/link-multiple-fields-from-a-subform.118748/

 

[neilsolaris]

Should start searching here, there is a wealth of information.
I found this with a quick google
I was thinking of concatenating the fields, but it appears that is not the way?
https://www.access-programmers.co.uk/forums/threads/link-multiple-fields-from-a-subform.118748/

Thanks for the link Gasman. The person asking the question in that link is clearly much more knowledgeable than me, I have no idea what they're talking about! I'll keep searching though.

Are you asking me whether concatenating the fields is the way? I don't know, sorry.

 

[Uncle Gizmo]

I'd like to incorporate his idea, but with a tiny difference. I'd like the subform to show details relating to just the musician and job number,

To clarify your question, your existing form shows data related to the main form linked on:- engagement number, however you would like to change the link so that the subform shows data related on "job number"?

 

[neilsolaris]

I'm making some progress now, I just need to remember what I did!

 

[neilsolaris]

To clarify your question, your existing form shows data related to the main form linked on:- engagement number, however you would like to change the link so that the subform shows data related on "job number"?

Exactly, that's right. But I'd like it to be linked to both. I just managed to make that happen now, I think. Thanks for your help though. If I get stuck again I'll come back.

 

[Gasman]

Thanks for the link Gasman. The person asking the question in that link is clearly much more knowledgeable than me, I have no idea what they're talking about! I'll keep searching though.

Are you asking me whether concatenating the fields is the way? I don't know, sorry.

I have only ever linked a subform to a mainform with one field.
You put the field names in the subform control properties.
That link says that you can use two (perhaps even three or more?) by separating them with ;

 

[neilsolaris]

I have only ever linked a subform to a mainform with one field.
You put the field names in the subform control properties.
That link says that you can use two (perhaps even three or more?) by separating them with ;

I used the wizard to do it, and it gave me an option to link more than one field. I'll try to research more about what you mentioned regarding putting field names in the subform control properties. I suspect that I need to know more about the syntax for that to work, but I don't know until I try.

 

[mike60smart]

I used the wizard to do it, and it gave me an option to link more than one field. I'll try to research more about what you mentioned regarding putting field names in the subform control properties. I suspect that I need to know more about the syntax for that to work, but I don't know until I try.

Hi
Your construction of tables is a bit off in relation to the T_Engagements

You do not need a Combobox to select the FK to relate an Engagement to a Payment.

I changed the field EnagementText_FK - ShortText in T_Payments to EngagementID - Autonumber

I also changed the field in EngagementsText_PK -ShortText in T_Engagements to EngagementID - Autonumber

The relationship between T-Payments and T-Engagement is on an Autonumber linked to a Number DataType

The Form that opens at startup allows you to select a Musician and then enter Payments specific for the Musician selected.

 

[neilsolaris]

Thanks Mike. Unfortunately I'm not able to open the file (it said unreconized database format). I'm using Access 2007 if that's at all relevant.

The reason I set the EnagementText_FK to a text, not a number, is because I wanted to have control over the format. In my full version, there are some engagement 'numbers' such as 0.002 etc. If it's an integer, I didn't want any zeros after the decimal point, because it looks a bit messy on the remittance advice report. Maybe there's a way of achieving that with numbers, but I wasn't sure how to do it. But I'll have a think about what you suggested.

 

[mike60smart]

Hi
My apologies but I forgot to add the EngagementNumber to the subform. This is set as a Text Field

 

[neilsolaris]

Hi
My apologies but I forgot to add the EngagementNumber to the subform. This is set as a Text Field

That's ok! But I still can't open your file.

 

[mike60smart]

Hi
I saved the file in 2003 Format. See if that helps.

 

[neilsolaris]

Hi
I saved the file in 2003 Format. See if that helps.

I could open that, thanks a lot. The form looks really neat and tidy! I'd like to learn how to present mine like that one day. I'll play around with your version so I can see exactly what you did.

Many thanks.

 

[arnelgp]

see also this.

 

[neilsolaris]

see also this.

Thanks arnelgp. I'll check that once I'm back at my computer. It was you who have me the idea of the subform in the first place!