Page 2 of 2

Re: [Solved] Multiple Entries Form With Common Fields

PostPosted: Mon Jan 28, 2019 1:57 pm
by Maax555
Think i have found the problem. The autorefresh associated with the Select Custom Form was showing "Sales_Purch_Form" only for the value. I updated this inline with your example and changed it to "Sales_Purch_Form/ListboxSalesRef;Sales_Purch_Form/ListboxCustPurch".
However the same issue is there that the CustomerID is not being saved into the record.

Re: [Solved] Multiple Entries Form With Common Fields

PostPosted: Tue Jan 29, 2019 2:41 pm
by Villeroy
Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.

How to pass the customer ID from the master form through the intermediate form forward to the data form:
Open the forms navigator.
Source of intermediate form:
Code: Select all   Expand viewCollapse view
SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2

Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)
Now the named parameter :pCID which is passed from the parent form appears as another field "CID" in this form's record as you can see when you hit the ellipsis button next to the source field and run the query.
Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.
The macro setup remains untouched.

Re: [Solved] Multiple Entries Form With Common Fields

PostPosted: Tue Jan 29, 2019 3:19 pm
by Maax555
Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.


Not all Part Numbers will have a Sales Ref or Purch ref depending on where the item has come from. They will always have a customerID.
I appreciate that my efforts are very amateurish but i believe it is working ok and achieves my needs thus far.
I dare say that once I have got this up and running I will rewrite it minimising the table sizes like partnumber, as you correctly point out this table far too many fields which would best be presented in separate linked table. Its been a hard learning process and as you can plainly see see i still have a long way to go just to grasp the basics.
As long as I can keep getting help I will keep trying to progress.

Thanks.

Re: [Solved] Multiple Entries Form With Common Fields

PostPosted: Tue Jan 29, 2019 3:31 pm
by Villeroy
By default all newly created columns are optional (nulll values allowed). Nulllable columns are useful for less important additional info or with filter table fields. You should mark most of your table column as mandatory (not optional, not null), so the database engine takes care that only complete records can be stored. This can only be done when your tables have no rows or only valid rows. You can not mark a column as not nullable when it contains null entries.

Re: [Solved] Multiple Entries Form With Common Fields

PostPosted: Tue Jan 29, 2019 4:08 pm
by Maax555
Villeroy wrote:Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.

How to pass the customer ID from the master form through the intermediate form forward to the data form:
Open the forms navigator.
Source of intermediate form:
Code: Select all   Expand viewCollapse view
SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2

Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)
Now the named parameter :pCID which is passed from the parent form appears as another field "CID" in this form's record as you can see when you hit the ellipsis button next to the source field and run the query.
Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.
The macro setup remains untouched.


So i have attempted to enter the above as it was not immediately obvious to me. As soon as I open the form i get a pop up box requesting a value for pCID
This is the steps I took to achieve the above.

Open the forms navigator.
Source of intermediate form:
Code: Select all   Expand viewCollapse view
SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2

Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)

I opened the Sales_Purch_Form and changed the Content to "SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2"
I linked master INT1 with slave CID

Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.

I opened the Filtered_White_Form and added a third link master CID with slave CustomerID

Re: Multiple Entries Form With Common Fields

PostPosted: Mon Feb 04, 2019 10:56 am
by Maax555
So now made the following changes as I did not fully understand where to update in the previous post.
Added "SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2" to Filter contents form.
In the Filter Subform added Master Link INT1
Added pCID in the Slave field (this was not in the listbox as an option so just typed it in).
As soon as I open the form i get a pop up asking for pCID value, i can enter any number, press enter and the form correctly updates the fields and saves the record.
Its obvious I have still got something wrong as not expecting a pop up and can enter any number and it works.

Re: Multiple Entries Form With Common Fields

PostPosted: Mon Feb 04, 2019 11:19 am
by Maax555
Ah, i opened the Filter SQL command, unticked the visible option for pCID and looks like it may be working.
Now testing further

Re: Multiple Entries Form With Common Fields

PostPosted: Tue Feb 05, 2019 1:17 pm
by Villeroy
More in depth explanations in the last form.

Re: Multiple Entries Form With Common Fields

PostPosted: Wed Feb 20, 2019 2:03 pm
by Maax555
Villeroy wrote:More in depth explanations in the last form.


Sorry. Have been away from this as computer failed (again) and new install of everything. I thought prior to the computer failing this was pretty much complete.
However I am now using the last example uploaded (with explanation), while the customer ID is being recorded, its always saving as a 0.
When i check my other back up files i have it working, saving the customer ID correctly but only with the undesirable saving of the form before part number or description is added. Thus if the entry is abandoned before adding this information a new record is created without part number and description.
Also I have lost the table control version which was allowing multiple entries to be entered using the same Customer, SalesREF and CustPurch.
:-(