Form - Subform
- Thread starter John liem
- Start date
Hi Kevin, I did try before I posted this on the forum, the way you suggested me to do, but it doesn't work. It works only if I choose one link, p.e.: Credit #1 or Credit #2 only, but doesn't work if I do Credit #1; Credit #2.
There will not be Credit #3 so far ... Why?. I can see that teher is a limitation to 3 fields, isn't it?
There will not be Credit #3 so far ... Why?. I can see that teher is a limitation to 3 fields, isn't it?
Hi Kevin, attached I have a print screen of my form and sub-form and the parent - child link property.
If I only use 1 linked field (either RE with Credit Memo # or RE Credit Memo with Credit Memo #) than it works, but not if I am using both links as shown in the print screen.
If I only use 1 linked field (either RE with Credit Memo # or RE Credit Memo with Credit Memo #) than it works, but not if I am using both links as shown in the print screen.
You can't use the same link field twice.
Not sure why you would want to do this anyway.
It's hard to see what you are actually trying to do without knowing the relationship between 'credit memos' and the main form data.
I aksed about other credit fields because this would lead to problems (which it probably already has with just 2 credit fields).
Consider 'normalising' the credit table to include the 'many' credits. I know it's only two but it will still make things a lot easier for you, like the problem you have now trying to link two fields to one (duplicated).
Not sure why you would want to do this anyway.
It's hard to see what you are actually trying to do without knowing the relationship between 'credit memos' and the main form data.
I aksed about other credit fields because this would lead to problems (which it probably already has with just 2 credit fields).
Consider 'normalising' the credit table to include the 'many' credits. I know it's only two but it will still make things a lot easier for you, like the problem you have now trying to link two fields to one (duplicated).
Easiesy way is to get your 2 Credit#s in ONE column.
This can easily be done in an expression in a query.
Then simply base your form on this query and use this expression field as the link on the main form.
But I still think they should be in ONE column in the first place.
The query expression I described is just making the data a bit more 'normal' and therefore easier to work with (in your case, linking two forms
This can easily be done in an expression in a query.
Then simply base your form on this query and use this expression field as the link on the main form.
But I still think they should be in ONE column in the first place.
The query expression I described is just making the data a bit more 'normal' and therefore easier to work with (in your case, linking two forms
Kevin,
But I do get the 2 credits # in one column (Table A - from Financial DB). The Main Form is linked to the Main Table and is used as a Data Entry form as well as an Update form. For that reason, credit #1 is separated from credit #2.
The subform is used to give additional information on what the financial dept. has done so far with the credits (credit #1 and credit #2: the values and when they were invoiced).
Do you have other suggestions how to do this?. Tx
But I do get the 2 credits # in one column (Table A - from Financial DB). The Main Form is linked to the Main Table and is used as a Data Entry form as well as an Update form. For that reason, credit #1 is separated from credit #2.
The subform is used to give additional information on what the financial dept. has done so far with the credits (credit #1 and credit #2: the values and when they were invoiced).
Do you have other suggestions how to do this?. Tx
According to your last pic you have TWO credit columns in Table A.
I assume this is the main form data.
If, as you say that only one column will ever display a value (the other will be null and v.v) then I don't see the point in having two columns anyway. Surely one will suffice.
I assume this is the main form data.
If, as you say that only one column will ever display a value (the other will be null and v.v) then I don't see the point in having two columns anyway. Surely one will suffice.
I am sorry Kevin if my last picture gives you a wrong image, the grey zone is the sub-form with the fields: credit coll and value.
The Main Form has Credit #1 and Credit #2 as fields.
Main Form is linked to Main Table
Sub Form is linked to Table A
So, in Table A, I have only one column (Credit Coll) with the collection of Credit numbers (Credit #1 and Credit #2) and the corresponding values.
In Main Table I have 2 columns of Credit numbers, Credit #1 and Credit #2, without values, only numbers which can be found in Table A in Column Credit Coll.
The exampe I gave was to show you that if in the Main Table a "e" is shown (from Credit #1 - Main Table), I should see a value in the subform of "4,565" as result.
So, I assume that the field "Credit Coll" has to be linked to Credit #1 and Credit #2 in the Main Form (child - parent).
If I link only the Credit #1 with the Credit Coll, the right result will show on the value, the same thing will happen if only Credit #2 is linked to Credit Coll, it will work. But if I link Credit #1 and Credit #2 with Credit Coll the same time, it doesn't work ... it doesn't output any values in the "value" field in the subform.
The Main Form has Credit #1 and Credit #2 as fields.
Main Form is linked to Main Table
Sub Form is linked to Table A
So, in Table A, I have only one column (Credit Coll) with the collection of Credit numbers (Credit #1 and Credit #2) and the corresponding values.
In Main Table I have 2 columns of Credit numbers, Credit #1 and Credit #2, without values, only numbers which can be found in Table A in Column Credit Coll.
The exampe I gave was to show you that if in the Main Table a "e" is shown (from Credit #1 - Main Table), I should see a value in the subform of "4,565" as result.
So, I assume that the field "Credit Coll" has to be linked to Credit #1 and Credit #2 in the Main Form (child - parent).
If I link only the Credit #1 with the Credit Coll, the right result will show on the value, the same thing will happen if only Credit #2 is linked to Credit Coll, it will work. But if I link Credit #1 and Credit #2 with Credit Coll the same time, it doesn't work ... it doesn't output any values in the "value" field in the subform.
KevinM said:
Sorry, I meant Main Table.
Yep, you've explained it well and exactly how I understood it.
My solution still stands...
ONE column for Main Table Credit1 and Credit2.
Either in your table or in a query expression...
Credit: iif([Credit1] Is Null, [Credit2],[Credit1])
Base your main form on this query and use [Credit] as the link field in the Main form (it can be hidden) and your 'Credit Coll' as the link in the subform.
Do you not see the irrelevance of having a TWO Credit# columns in your main table if one will always be null if the other isn't?
Hi Kevin,
As my Main Table is located in the SQL Server, how can I add a column in this table to get Credit #1 and Credit #2 together into this column?. I know how to do it in a "view" by using "case when, else, end". But how do I do for a table?. Are you familiar too with SQL?. I want to add this new column in the Main Table as I use this table as data entry in the Main Form. Tx.
As my Main Table is located in the SQL Server, how can I add a column in this table to get Credit #1 and Credit #2 together into this column?. I know how to do it in a "view" by using "case when, else, end". But how do I do for a table?. Are you familiar too with SQL?. I want to add this new column in the Main Table as I use this table as data entry in the Main Form. Tx.
Kevin,
I created a query/view in SQL server, but I can not use this query in the Main Form as a Data Entry, it doesn't allow me to enter or change data. I have to add column into the Main Table with the merge expression, but don't know how to do it. Can you help me?. Tx.
I created a query/view in SQL server, but I can not use this query in the Main Form as a Data Entry, it doesn't allow me to enter or change data. I have to add column into the Main Table with the merge expression, but don't know how to do it. Can you help me?. Tx.
I have created an unbound text box "Credits", but how can I link this to the subform using the Subform Field linker?, it doesn't recoqnize the unbound text box in the "Link Master Fields" property?.
The control source of the unbound text box:
=IIF ([Credit #1] Is Null, [Credit #2],[Credit #1])
This is getting too complicated, isn't it Kevin?, sorry to give you so much trouble ....
The control source of the unbound text box:
=IIF ([Credit #1] Is Null, [Credit #2],[Credit #1])
This is getting too complicated, isn't it Kevin?, sorry to give you so much trouble ....
