Page 2 of 2

[SOLVED]Re: How to add orders for multiple parts in a form?

Posted: Wed Sep 25, 2013 9:43 pm
by dazedandconfused
Villeroy wrote:When you edit a group you can select each member of that group. You temporarily de-activate the grouping without dissolving the group. This is helpful when you have groups of dozends of members (mostly in Impress or Draw).
I just want my list box to be a part of the main form. What's the best way to do that? Customer A goes w/ PO 1 and customer B goes w/ PO 2. However, right now if I go to PO 2 it shows customer A and that list box never changes and the selection isn't saved with the rest of the form data.

Re: How to add orders for multiple parts in a form?

Posted: Wed Sep 25, 2013 10:21 pm
by Villeroy
Get the form navigator and drag the control to the right form.

Re: How to add orders for multiple parts in a form?

Posted: Thu Sep 26, 2013 5:29 pm
by dazedandconfused
Villeroy wrote:Get the form navigator and drag the control to the right form.
Thanks. Ugh, turns out it was part of the main form which is why drag and drop didn't work earlier.. However it doesn't function along with the main form. I wonder if I'm making any sense? Customer is a dropdown and there should be a different one for each record. It allows me to select a customer from the dropdown but it isn't saved with the rest of the data. Everything I created w/ the wizard is saved together but my customer dropdown is an unwelcome guest. I added this dropdown as a list box after I used the agent to create my form w/ a subform

Let me try again. From the dropdown I select customer 1 for PO 1 then use the navigation bar to move to PO 2 and customer 1 is still in the drop down. I change it to customer 2. Then I move to PO 3 and customer 2 is still selected in the dropdown. If I then move back to PO 1 now customer 2 is shown in the dropdown area. How the heck do I get it to function along with the rest of the mainform??? I want customer 1 to be saved w/ PO one data and so on.

Re: How to add orders for multiple parts in a form?

Posted: Thu Sep 26, 2013 9:19 pm
by dazedandconfused
I have an unrelated question to the problem I still have above.

I have the salesman name in the PO invoice form. I linked it by the master/slave to a column in PO Items because I think it might make it easier to run a query when trying to figure out how much each salesman has sold over a period of time. I'll probably hide this column from the user. Anyway, this column shows the salesman number instead of the name. In other words in the main form it shows the name "Jim" who is the first salesman listed. In the subform it shows the number 1 instead of the name "Jim." Is there a way to change that so it shows the name instead of the number? If so I can't figure it out.

Re: How to add orders for multiple parts in a form?

Posted: Thu Sep 26, 2013 9:26 pm
by MTP
Do you have a version of your file that you could upload? There are several setup selections that could be related to your listbox function that are difficult to diagnose with word descriptions but would be easy to find when looking at an actual file.

A listbox can have two values: a 'bound' value that is saved to the underlying table field, and a 'visible' column that is what you see on the form. The most common way to do this: on the 'Data' tab of the properties window, 'Type of list contents' has to be 'SQL' and in 'List content' you have to write a SQL statement, something like

Code: Select all

SELECT "VisibleField", "BoundField" FROM "Table"

Re: How to add orders for multiple parts in a form?

Posted: Thu Sep 26, 2013 10:01 pm
by dazedandconfused
MTP wrote:Do you have a version of your file that you could upload? There are several setup selections that could be related to your listbox function that are difficult to diagnose with word descriptions but would be easy to find when looking at an actual file.

A listbox can have two values: a 'bound' value that is saved to the underlying table field, and a 'visible' column that is what you see on the form. The most common way to do this: on the 'Data' tab of the properties window, 'Type of list contents' has to be 'SQL' and in 'List content' you have to write a SQL statement, something like

Code: Select all

SELECT "VisibleField", "BoundField" FROM "Table"
Unfortunately there are some name and phone numbers, etc there that I probably shouldn't put on the internet.

The main form is called "Po Invoice" The column in question is called "Salesman." I have that as a dropdown list box w/ an sql statement in there. I really just clicked that "..." box and selected the name and ID from the salesman table. I didn't write anything fancy. The sub form is called "PO items" and the column is also called "Salesman." Are you suggesting that I change that subform to a list box so I can write an sql statement?

If so would that be :

Code: Select all

SELECT "VisibleField", "BoundField" FROM "Po invoice"
I tried and got the error attached. I tried replacing "visiblefield" "boundfield" with "salesman" but still got an error. How should I write it exactly?

Re: How to add orders for multiple parts in a form?

Posted: Fri Sep 27, 2013 1:33 am
by MTP
Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write

Code: Select all

SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).

Re: How to add orders for multiple parts in a form?

Posted: Mon Sep 30, 2013 10:40 pm
by dazedandconfused
MTP wrote:Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write

Code: Select all

SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).
Great idea! I did that and have attached the database.

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 01, 2013 3:44 pm
by dazedandconfused
MTP wrote:Can you make a copy of your database, delete all the rows in the tables, and put in just 2-3 dummy rows in each table to make it safe to upload?

Clicking the "..." box gives you a GUI and Base will try to write the sql statement for you. IMO the GUI is more difficult to use than the sql. You have to learn the same things to use either one effectively. How everything relates behind the scenes is a lot to take in, but it gets easier with practice.

I'm guessing you have a table that lists your salesman's name, their ID number, and maybe other information? If that table were called "SalesmanTable" and had columns "SalesmanName" and "SalesmanID", then to get a listbox to show the names but save the ID number you'd write

Code: Select all

SELECT "SalesmanName", "SalesmanID" FROM "SalesmanTable"
Note that you are pulling the names FROM the salesman table (per the sql) and saving the ID numbers TO the PO table (per the listbox being part of a form based on the PO table).
The table is called, "PO INVOICE." Columns are "Salesman" and "ID" Therefore I changed the box on the sub form to a list box and changed type of list contents to "SQL" I entered

Code: Select all

SELECT "Salesman", "ID" FROM "PO INVOICE"
however that still didn't help. Now it has a 1 in the box, but doesn't actually show it unless I click on it.

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 01, 2013 6:34 pm
by Villeroy
Get the data properties of the subform.
Currently the master --> slave binding is:
FK_ID_PO --> ID
SALESMAN --> Salesman
The second pair is obsolete.

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 01, 2013 8:26 pm
by dazedandconfused
Villeroy wrote:Get the data properties of the subform.
Currently the master --> slave binding is:
FK_ID_PO --> ID
SALESMAN --> Salesman
The second pair is obsolete.
Thank you. I removed SALESMAN --> Salesman, but I would like it to display mickey mouse or Donald duck instead of 1 and 2

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 01, 2013 8:47 pm
by Villeroy
dazedandconfused wrote: Thank you. I removed SALESMAN --> Salesman, but I would like it to display mickey mouse or Donald duck instead of 1 and 2
Use a list box like you do in the parent form.

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 01, 2013 10:21 pm
by dazedandconfused
THANKS!! I swear I tried that before but it didn't work. Obviously I was wrong because it works now.

I'm having a weird problem and it could be a rather big problem. I select customer and salesmen in the main form then move down to the Items subform to enter data. Here in ID 17 I select product type then tab to order price and enter 6 items. For ID 46 I entered 200, then pressed enter. Now I'm done entering data and I want to move on to the 5th Purchase order. I used the navigator to move to item 5 and then came back to item 4 immediately. When I did noticed that the order price of 200 that I just entered wasn't saved. However, if I enter 200 then click on the row below it or even tab to the row below it the data is saved.

So it looks like I have to select the next row for the data to save and that can be a problem. Is there any way to change this so that the information is saved as soon as I hit enter? I can see a salesman forgetting to tab all the way to the next line and therefore constantly losing the last item they enter.

Make that 2 problems. When I enter an item, here in the screen shot ID 47 the salesman's name is saved on the row below where it should be.

Re: How to add orders for multiple parts in a form?

Posted: Tue Oct 08, 2013 7:49 pm
by dazedandconfused
Is there a way to change the input so that it's saved when I press the enter button or if I tab to another column? Right now it looks like the only way an entry is saved in the sub form is if I move the cursor to the next row. If someone enters data and forgets to click on the row below their entry then the data is lost.

Re: How to add orders for multiple parts in a form?

Posted: Tue Mar 12, 2019 7:43 pm
by Hir23
F3K Total wrote:Hi,
if i got you right, you need an invoice table and an item table with foreign key from invoice table.
In a form, this can be done with a MainForm and a SubForm, bound to the MainForm by the foreign key, which is the ID of the current invoice.
Find attached a simple example to study the structure, especially the Link master Fields/Link slave fields on data tab of the SubForm.
R
Hi can you elaborate on how you created your form? in the test db?