adding information into a field using text boxes and combo box.

[wakamoe]

Hello everyone,

I am currently trying to create a form that would help me add week number and year into entries. I am not so sure how to go about doing this and dont know how to start.

The form consist of 2 tables, a query and a form.

Table 1: shows all orders individually

Table 2: orders are then combined (group-by) by the query

The form I would like to create will allow the user to select the customer order then the item number. After they choose both of that, they can enter the amount of records they would like to edit.

They will then proceed to select the week and year for that amount of entries.

Some things about the tables:

A customer order can consist of more than 1 type of item, which is why the user can select "item number"

After selecting "customer order" and "Item number", the user then selects the amount of records they would like to edit. The total amount of records they can edit should not go beyond the "CountOfItem_Number" seen on the subform.

The user can either use of the drop down menu first. Which means that if the user selects the customer order "000001", only "651093" or "928613" can be selected in "item number". Same goes if they select item number "928613", only "000001" can be selected in "customer order"


Does anyone know how I can go about doing this?

Thank you very much, all advice is greatly appreciated!

 

[Ranman256]

The 2 combos work like this:
CboCust holds the cust IDs. (Simple)
CboItem uses a query that looks at cboCust in its criteria....

Select * from tItems where [custID]=forms!frmMain!cbocust

Note: you MUST put a refresh in the cboCust AFTERUPDATE event in order to get the new Item list....

Sub cboCust_afterupdate()
  CboItem.requery
End sun

 

[wakamoe]

The 2 combos work like this:
CboCust holds the cust IDs. (Simple)
CboItem uses a query that looks at cboCust in its criteria....

Select * from tItems where [custID]=forms!frmMain!cbocust

Note: you MUST put a refresh in the cboCust AFTERUPDATE event in order to get the new Item list....

Sub cboCust_afterupdate()
  CboItem.requery
End sun

Hello,

Thank you for your reply but I am quite lost...

When making the customer order combo box hold the customer ID, do I go row source, click on the table and only place customer id (from table) into the query?

Also, where should I place the code "Select * from final_table where [Customer_order_combo]=forms!form1!Customer_order_combo" into for item number? Should it be placed under row source or control source.

I am unsure if it is possible but maybe I should explain how I need the database a little more as I am very bad at explaining problems. sorry.

The main table consist the total amount of each item the customer orders by using a group by. Count will tell me how many of the items the customer has ordered.

The form will allow me to select the customer order and item (as a customer can order different items). After selecting that, I then proceed to type in how many of those items I would like to insert a week number and year.

For e.g. initially this is how 1 entry in the table will look like

Customer order ------- item number ------- total amount ------ week ------ year
----098123----------------123871------------------50

As the customer might order 50 of item A but I can only ship 25 of them by week 40 2016. This would only add weeks and years to 25 of the items, changing the table to look like this:

Customer order ------- item number ------- total amount ------ week ------ year
----098123----------------123871------------------25

----098123----------------123871------------------25---------------40-----------16

I am unsure if we need another query to split the item up as this is not to add an entry but to edit the existing entries.

Thank you once again.

 

[wakamoe]

Hello everyone,

I managed to do the base of the form (combo boxes works now) but I am still unable to insert new values into the table (edit).

Currently there is two tables, [table1] with all the individual information of each item and [final_table] that uses the same information as the first but goes through a query that group-by and finds items that has not yet been produced.

The combo boxes will get the information from [final_table]. This is done so that the user can only select items that has not yet been produced.

Now what I do not know how to do is this. If you see the form, the user can key in the number of records for an item they would like to add week/year to. How do I make it so that if there is 5 records for item "A" that has no date and the user wishes to update 2 records. Only 2 records in [table1] will get updated.

The tough part is that there is a lot of the "same" (besides tag number) entries in [table1]. How do you make it so that you select two entries with the [customer order] and [item number] selected by the user to add [week] and [year] to?

Can anyone help with this?

Attached is the latest version of the database.

Thank you