Multiple Entries Form With Common Fields

[Maax555]

Hi, after a few months break I am returning to my database creation
I still have not resolved one of the issues which is adding several records on the same form which have common field entries.
That is to say I am adding perhaps 10 new part numbers at the same time which all have the same customer and sales reference number.
So my form NewPartNumberMulti has 4 listboxes on the left.
I choose the customer which filters the Sales Ref listbox and also the Cust Purch listbox.
I do this then enter a new part number and description in the control table. The correct ID's are updated from the listboxes on the left.
We can ignore the similarID and replacesID for now.
When i then go to enter the next part number and description I want the CustomerID, SalesREFID and CustPurchID to remain the same.
However the listboxes on the left are refreshing and clearing. How can I have the listboxes remain the same so the details are copied over for the next part number?
https://www.dropbox.com/s/zpt2p0bzarmof59/Project%20Tracking%20Test%20V1.34.odb?dl=0

many thanks

[UnklDonald418]

Have you looked at this?
Base Copy Field or Clone Record - Example

[Villeroy]

Without macros: download/file.php?id=22304

[Maax555]

thanks, I will look through this. I was hoping there would be a way to perhaps stop the listbox's on the left from refreshing when i go to enter details on the second row of the control table. I will however spend sometime going through your suggestion.
As always many thanks.

*Just seen the second post (without Macros). Looking now.
thanks again.

[Maax555]

I think the non macro one looks very similar to what i originally intended. I will look at adapting this to my form.
thanks

[Villeroy]

One form is a simple combination of a form with a new-record-subform.
The other is "power filtering" applied to a new-record-subform.

[Maax555]

One form is a simple combination of a form with a new-record-subform.
The other is "power filtering" applied to a new-record-subform.

I'm attempting to use the Form1 example. Looks nearest to my requirement. Its not proving to be as simple as I hope though

[Villeroy]

menu:Tools>SQL...

Code: Select all   Expand viewCollapse view

CREATE TABLE "Filter"(
  ID INT PRIMARY KEY, DESCR VARCHAR(100), TXT VARCHAR_IGNORECASE(100), INT1 INT, INT2 INT, D1 DATE, D2 DATE, B BOOLEAN
);
INSERT INTO "Filter" VALUES(
  0,
  'Filter record with ID=0 for some input form',
  NULL, NULL,NULL,NULL,NULL,NULL
)

menu:View>Refresh Tables
This creates a filter table with one record having an ID=0, some descriptive text and various fields of various types.

Create a form with the following data properties:
Source type: SQL
Source: SELECT * FROM "Filter" WHERE "ID"=0 (only this specific record)
Allow insertion = No (prevent that users tab into the new record)
Allow deletion = No
Allow modification = Yes (we want to edit filter criteria)
Navigation bar: No (makes no sense with a single record)
------------------
Add the right form controls to take your filter criteria and bind them to the corresponding types.
Add a cancel button in order to cancel editing of filter criteria (Action = Undo)
-----------------
Add a subform with data property "new records only" and with the fields that should inherit their values linked to the corresponding fields of the filter form.
Add a refresh button to the subform in order to refresh the subform after editing the filter criteria (Action = Refresh)

For other forms with other filter criteria, just add another record with ID=1 and another filter form.
Various examples of the same: download/file.php?id=26483

[Maax555]

Struggling a little with this. I think i understand whats going on but when i try to use my existing PartNumber2 table and change around the relevant references its not working. I will continue to play around until it sinks in.

thanks

[Villeroy]

Struggling a little with this. I think i understand whats going on but when i try to use my existing PartNumber2 table and change around the relevant references its not working. I will continue to play around until it sinks in.

thanks

Upload your file so we can have a look at it.
Or open my file and go through the properties.
Form data:
SELECT * FROM "Filter" Where "FID"=0 with modification = Yes and everything else = No
Your names of tables and fields may vary but this example requires that the filter table has one record with primary key FID=0

Subform is linked to the data table "Table1" and linked in the following way:
Master.DATE = Slave.Date
Master.CHAR = Slave.Category

[Maax555]

Struggling a little with this. I think i understand whats going on but when i try to use my existing PartNumber2 table and change around the relevant references its not working. I will continue to play around until it sinks in.

thanks

Upload your file so we can have a look at it.
Or open my file and go through the properties.
Form data:
SELECT * FROM "Filter" Where "FID"=0 with modification = Yes and everything else = No
Your names of tables and fields may vary but this example requires that the filter table has one record with primary key FID=0

Subform is linked to the data table "Table1" and linked in the following way:
Master.DATE = Slave.Date
Master.CHAR = Slave.Category

Hi Yes, i have all of the above in place. However when i try to use my PartNumber2 table in place of table1 or try to bring in other fields I fail.
I will keep going today and see where I am up to this afternoon.
thanks

[Maax555]

OK, so attached is a link to the database. I have created Form2.
Here I have three listboxes, Customer, Sales Ref and Cust Purch.
Firstly I choose the customer, this then filters the sales Ref and also the Cust Purch.
So I want the three listboxes to then be copied into the associated fields in the table control.
Currently the table control is listing records that match my listboxes, although this is not what I intended it's ok as I can live with clicking on the new record line to add further records.
However depending on which choices I make in the listboxes, sometime the fields are copied and other time they are not.
For example, choose customer 1, sales ref 11 and person1, purchasing the fields are copied ok and I can click in the last record which then updates with the correct details for me to add PartNumber and Description.
Now if you try the same thing but choose Customer 5, sales Ref 555 and Person 5, purchasing, only the sales Ref is being copied to the new record row. The rows about are empty I as they don’t have the details already saved.
So I am wondering if the new record line is only copying from the last record and not from the top listboxes?

UPDATE- when I unhide the ID fields for the three listboxes the fields are populated fully.
I chose to hide the ID fields which is my preferred choice but could live with them showing.
While I could use table as it is I would prefer to not show existing records.
Is this a case of changing the form properties for modification Etc?

UPDATE 2. I removed a WHERE command from the table control fields. I already did this previously but must have neglected to save it?


https://www.dropbox.com/s/d7k35za5wpcco9v/Project%20Tracking%20Test%20V1.35.odb?dl=0

[Maax555]

I think I actually have it working

https://www.dropbox.com/s/umidli7j5l68nnj/Project%20Tracking%20Test%20V1.37.odb?dl=0

[Maax555]

Hmm, a moderator has marked this as solved? Although i have progressed its still not working correctly. Another link below.
So on form Form2_Add, when i choose the details in the list boxes then click in the table control the record is saved immediately.
This means that if for some reason I decide not to add a part number and description a record is added without.
If i change the PartNumber2 table to force an entry for PartNumber an error comes up as as Form2_Add is saving the record prior to me entering the PartNumber.
How do i change the Form2_Add so it only saves the record when i press save or when part number field is not blank?

https://www.dropbox.com/s/ra9wwxzsyhhl9gf/Project%20Tracking%20Test%20V1.38.odb?dl=0

[Villeroy]

I copied the involved tables and the form into a new, smaller database. There is something wrong with my cloud storage and I don't have the time to fix it right now.

[Maax555]

I copied the involved tables and the form into a new, smaller database. There is something wrong with my cloud storage and I don't have the time to fix it right now.

Thanks, however I am getting errors because of missing Macros. Should I just be looking at the way the forms are constructed and attempt to apply to my forms?

thanks

[Villeroy]

There are no macros involved. Just remove the events from the customer and sales list box.

P.S.: I added another gimmick. See SQL source of subform. However, in this particular case ("add-data-only form") it is not very useful. The subform binding would also work with the normal field mapping.

If the form would be a filtered form for editing the query would ignore any missing parameters. It filters by CustomerID (if given), SalesREFID (if given) and CustPurchID (if given).

Code: Select all   Expand viewCollapse view

SELECT * FROM "PartNumber2"
WHERE ("CustomerID" = :pINT1 OR :pINT1 IS NULL)
AND ("SalesREFID" = :pINT2 OR :pINT2 IS NULL)
AND ("CustPurchID" = :pINT3 OR :pINT3 IS NULL)


Using parameters with subforms is useful when ...
1. you need an editable filtered row set
2. the relation between master and slave is more complex than A=B AND X=Y, for instance ROUND(A,0)>B AND UPPER(X)=UPPER(Y)

[Villeroy]

Your mistake was that you did not use any filter table. The filter table has nothing to do with your actual data. It has only a technical purpose storing the filter criteria, so the filter criteria are stored somewhere and can be used in a main form/subform relation. Base can not have a main form that is not linked to some actual table. So we give Base what it needs to filter a subform.

[Maax555]

ok, so the only thing that was not working for me was the Sales Ref and Cust Purch listboxes were not being filtered against the Custer listbox.
I checked the Cust Purch list content details and noticed the WHERE command was missing. I added back in "SELECT "SalesREF", "SalesREFID" FROM "SalesREF" WHERE "CustomerID" = 0" and it looks to be working now.
I will tidy up and delete all the entries without part number and description, add some new entries and see if all is well.

I like to know how things work so i can attempt to replicate and modify them for future use. I need to look at how the SubForm content lines work as looks very confusing at first glance.

(SELECT * FROM "PartNumber2" WHERE ( "CustPurchID" = :pINT3 AND "CustomerID" = :pINT1 AND "SalesREFID" = :pINT2 OR "CustPurchID" = :pINT3 AND "CustomerID" = :pINT1 AND :pINT2 IS NULL OR "CustomerID" = :pINT1 AND "SalesREFID" = :pINT2 AND :pINT3 IS NULL OR "CustomerID" = :pINT1 AND :pINT2 IS NULL AND :pINT3 IS NULL OR "CustPurchID" = :pINT3 AND "SalesREFID" = :pINT2 AND :pINT1 IS NULL OR "CustPurchID" = :pINT3 AND :pINT2 IS NULL AND :pINT1 IS NULL OR "SalesREFID" = :pINT2 AND :pINT3 IS NULL AND :pINT1 IS NULL OR :pINT2 IS NULL AND :pINT3 IS NULL AND :pINT1 IS NULL ))

thanks

UPDATE. This is not working, i thought the filters had worked when i first tried but nope it was only picking up the CustomerID of "0" from the added WHERE command. Will investigate further

[Villeroy]

Why do you restrict the list boxes to one entry? The list box should provide all entries you want to choose from. A list box is just a method to enter a named number. You pick one of the names and the list box enters the corresponding number into its linked field of the current record.

The filtering form is restricted to one particular record in a filter table. This single record has fields INT1, INT2, INT3 to take the values of your 3 list boxex and it has an integer primary key to make filtler records distinguishable and editable. Tables with no primary key are read-only.

The main form which is linked to the record where FID=0 provides 3 integer values coming from the 3 entries you select from your list boxes and passes them over to the subform where they filter the existing rows of the subform or fill a new record with default values.

[Maax555]

Why do you restrict the list boxes to one entry? The list box should provide all entries you want to choose from. A list box is just a method to enter a named number. You pick one of the names and the list box enters the corresponding number into its linked field of the current record.

Hi Sorry, the Customer Listbox lists all customers. The SalesRef list box should list all entries that are related to the choosen customer as should the Cust Purch listbox will show records relating to the chosen customer.

[Villeroy]

Cascading list boxes with and without macros
[Tutorial] Cascading Listboxes with macros

[Villeroy]

First of all: If each purchase and sale has a customer ID, why do you save another customer ID in the PartNumber2 table? How is the PartNumber related to one distinct customer? A part may have a manufacturer or a vendor but in my honest opinion that would be an entity other than the customer. But what can I know about your business?
-------------------------------------------------------------------
The attached version has 2 more form documents.
Form2_Add is the one you already know with a filtering form and a form pulling the filter values into the new record.
There is one more thing you have to understand about this simple one. The refresh button belongs to the subform with the grid. It refreshes its containing subform based on the modified value(s) in the parent form. Base forms are saved automatically when the input focus changes from one modified form to any other (sub-)form. When clicking the refresh button, the button takes the focus which triggers the saving of the modified form which used to have the focus before clicking the button. Then the button performs its action or macro.
------------------------------------------------------------------------
Form2_Add1 has 3 levels: a filter form taking a customer ID, a subform with 2 listboxes and a refresh button [-->] showing the customers purchases and sales after hitting the refresh button and a sub-subform with the resulting form getting the preset values for the new record. I did not pass through the customID because I think it is obsolete but it would be possible.
The first refresh button takes the focus (saves the parent form), refreshes the subform with the green list boxes and the list box contents depend on the INT1 in row #1.
The 2 green list boxes store their values in INT1 and INT2 of row #2. When you click the second refresh button it moves the focus to the third form and refreshes it according to the corresponding values INT1 and INT2 of the parent form.
---------------------------------------------------
Form2_Add2 is a macro driven version. It requires this Python code: download/file.php?id=35500 which comes as a text document with an install button which triggers an embedded Basic installer.
The macro is very minimalistic. It can refresh any collection of refreshable items (combo boxes, list boxes and subforms) within the same form document without editing the macro code. Because this macro is supposed to work with any form, the installing routine installs the macro to the global container "My Macros", subfolder "pyDBA", file AutoRefresh.py and my example form depends on the macro being installed to the right place. Just open the file and click the button.
The information about the items to be refreshed is stored in hidden controls. Hidden controls are named values attached to logical forms visible in the form navigator only. Their only purpose is macro configuration. For this auto refresh macro I added a hidden control named "AutoRefresh" to both filtering forms and entered the name of the subform to be refreshed below the name. Then I assigned the form_Action macro to the "After Record Action" event of same form having the hidden field AutoRefresh.
Any macro assigned to the "After Record Action" is called whenever the current record of the calling form has been modified and saved, deleted or when a new record has been inserted and saved.
The macro then looks up the hidden control "AutoRefresh", reads the (list of) item(s) to be refreshed and refreshes them.
I moved the two buttons which used to refresh their containing subforms one level up to their parent forms, changed the action property from "Reload" to "Save form". Furthermore I set property "Default button", so these save buttons are triggered by the enter key. Saving the parent form triggers the macro. Now the modified form is saved when you hit enter or click the button and the subform in the hidden control is refreshed.

[Maax555]

Hi, firstly i would like to thanks you again for your help and patience with this and the many other issues I have posted to this group.
My preferred form would be Form2_Add for its simplicity in that no additional buttons need to be pressed to have the fields update.
However as this method has the record saving before any part number is added I cant use it as I can see many occasions where a user may may start the process of adding data in this form and simply quit out of the program or close the form and go to another form or report.
The other two versions look to achieve the exact same result using slightly different methods, i prefer the non macro version for its simplicity.
I will find it easier to understand and modify for other forms I may require. Although this adds two additional clicks to the form (versus the original) this is fine as its a great time saver to be able to have the details copied down and to enter several records all relating to the same customer etc.
So this is the one I will use and study to allow me to understand its workings properly. I will however be looking to include the customer ID. This is because I will use customerID to generate other reports. Hopefully I will be able to work out how to include this myself.

Many thanks again.

EDIT: Not as simple as i though. I was hoping to simply link INT3 to CustomerID on the Filtered Grid Subform and set the FID to 1 to use the three integes. This simply puts customerID 1 in the field not matter what customer I choose. More investigating to be done.

[Villeroy]

Have a look at my simple example. It has two form controls (a letter form list box and a date) in the filtering form and a grid with the 2 repeated values and a numeric field to be filled by the user. There is no need to show the same values in the slave form if it is already shown in the master form. I could have put a single numeric input field into that subform. I made the demo more verbose than necessary.

When you switch from one modified form to some other form control of another form, the previously focussed record is saved automatically before anything else.
I'm not entirely sure but I believe that you can omit the buttons when using my little macro. I tested this with my example and it works.

Therefore you can have one customer list box in the first form with a hidden value "AutoRefresh"(pointing to next form) and my macro assigned to the form action event.
The other two list boxes on another form with a hidden value "AutoRefresh"(pointing to next form) and my macro assigned to the form action event.
Then you can have a form with single controls and labels with the second form as parent. Once you are sure that the form is updated correctly and reliably, you can delete the automatically filled form controls. The values will be taken over anyway. Now the only buttons belonging to the last form should be an OK button with action "New Record" (which saves the current record before jumping to the next new record) and an cancel button to cancel input. The OK button can be set up as default button so hitting the enter key saves the record and jumps to the next new record.

[Villeroy]

easier to do than to describe

[Maax555]

easier to do than to describe

Python Macro is installed but filtering not working. When i open the Add2b form the filters will filter for the selection that is already present in the Customer listbox. So if i open the form and customer 1 is showing then the sales ref and cust purch will show related details. However when i change the Customer listbox the sales ref and cust purch listboxes do not up date.

[img]

py1.JPG

[/img]

[Villeroy]

You must not run that macro manually. You have to assign it to the form's event "After record action".
Go to the form properties, 3rd tab "Events".
Click the ellipsis button right of the "Form Action" event.
Choose the macro that should be called automatically when a record has been saved, removed or inserted.

The same form gets a hidden field "AutoRefresh" with the hierarchical names of the list boxes or the subform respectively.

In my example file that is:
Hidden control "AutoRefresh" of form "Select Custom Form" has value Sales_Purch_Form/ListboxSalesRef;Sales_Purch_Form/ListboxCustPurch (2 hierarchical names separated by semicolon) in order to refresh the 2 listboxes in subform "Sales_Purch_Form"
and hidden control "AutoRefresh" of "Sales_Purch_Form" has value Filtered_White_Form in order to fills out the linked fields by refreshing the subform.

Off topic: To refresh some element in a parent form you would specify ../element_name. The ../ refers to this form's parent form.

[Villeroy]

We could spend months of form design without getting anything really done. Your table structure is faulty. You store information about different things in one huge table.

[Maax555]

The form does have the macro assigned in the same place you show, it just doesnt seem to refresh the sales ref and cust purch list boxes after updating the Customer listbox.