Hello all,
I am trying to create a form that will look for entries in a table and edit them. I will try my best to explain everything so it will be easier to understand.
The goal of the form is to allow the user to add Week and Year into entries in a table that does not have values in those fields. Only adding Week and Year to those entries that are empty. AND the user can choose how many of those entries they want to add Week and Year to.
To show those Item Numbers/Customer Orders that has no week value inputed, there is a Subform that goes through a Query that finds all entries in the table that is "Null" is week.
===
The way the user inputs the value for each field is through combo boxes.
There is a total of 4 combo boxes and 1 text box on the form.
The combo boxes to allow the user to choose which Customer Order and Item Number for that order they want to add Week and Year to is linked to the subform mentioned above. This is so that the user can ONLY choose Customer Order and Item Number that has no value inputted in Week.
The combo boxes to allow the user to select the Week Number and Year has the inputted into the combo box itself, into the "Row source" under the property sheet.
Lastly on the form, there is a text box that allows the user to select how many entries of the selected Customer Order and Item number they want to add Week and Year to.
NOTE: In the table, there is a Tag number for every entry. The tag number always goes from small to big but it might not always be the same. Meaning that it can be e.g. 1,2,3,4,5 or e.g. A1,A2,A3,A4. Currently, this can be ignored.
A customer can order multiple of different items. Customer1 can order 10A and 5B.
==========================================
Currently this is my code, I am very new to coding in general and cant seem to get it to work. How I tried to code it is:
Find all entries in the table that has the same Customer Order and Item Number the user selected in the form. (Should add that "Week = Null" in the criteria, but whenever I added that I had some issues with Null)
After the first entry, the code will that swap that entry's Week and Year to the one the user chose on the form.
After editing the entry, the code will loop and counter will +1. The code will keep finding the next entry with the criteria above UNTIL the counter reaches the same value as keyed in by the user in the text box.
Attached is the database.
Thank you very much for reading. All advice is greatly appreciated.
I am trying to create a form that will look for entries in a table and edit them. I will try my best to explain everything so it will be easier to understand.
The goal of the form is to allow the user to add Week and Year into entries in a table that does not have values in those fields. Only adding Week and Year to those entries that are empty. AND the user can choose how many of those entries they want to add Week and Year to.
To show those Item Numbers/Customer Orders that has no week value inputed, there is a Subform that goes through a Query that finds all entries in the table that is "Null" is week.
===
The way the user inputs the value for each field is through combo boxes.
There is a total of 4 combo boxes and 1 text box on the form.
The combo boxes to allow the user to choose which Customer Order and Item Number for that order they want to add Week and Year to is linked to the subform mentioned above. This is so that the user can ONLY choose Customer Order and Item Number that has no value inputted in Week.
The combo boxes to allow the user to select the Week Number and Year has the inputted into the combo box itself, into the "Row source" under the property sheet.
Lastly on the form, there is a text box that allows the user to select how many entries of the selected Customer Order and Item number they want to add Week and Year to.
NOTE: In the table, there is a Tag number for every entry. The tag number always goes from small to big but it might not always be the same. Meaning that it can be e.g. 1,2,3,4,5 or e.g. A1,A2,A3,A4. Currently, this can be ignored.
A customer can order multiple of different items. Customer1 can order 10A and 5B.
==========================================
Currently this is my code, I am very new to coding in general and cant seem to get it to work. How I tried to code it is:
Find all entries in the table that has the same Customer Order and Item Number the user selected in the form. (Should add that "Week = Null" in the criteria, but whenever I added that I had some issues with Null)
After the first entry, the code will that swap that entry's Week and Year to the one the user chose on the form.
After editing the entry, the code will loop and counter will +1. The code will keep finding the next entry with the criteria above UNTIL the counter reaches the same value as keyed in by the user in the text box.
Code:
Dim db
Dim rs1 As Recordset
Dim Customer_orders, item_number, Week_no, year_no As String
Dim counter1 As Integer
Set db = CurrentDb
Set rs1 = db.OpenRecordset("table1", dbOpenDynaset, dbreadwrite)
rs1.MoveFirst
Do Until rs1.EOF
Customer_orders = rs1.Fields("Customer_orders").Value
item_number = rs1.Fields("item_number").Value
Week_no = rs1.Fields("Week_no").Value
counter1 = 0
Do Until counter1 = Me.Records_txtbox
If rs1.Fields("Customer_orders").Value = Me.[Customer_order_combo] And rs1.Fields("item_number").Value = Me.[Item_number_combo] Then
Set Me.[Week_no_combo].Value = rs1.Fields("Week_no").Value
Else
rs1.MoveNext
End If
counter1 = counter1 + 1
Loop
rs1.MoveNext
Loop
Me.Requery
[child1].Form.Requery
Attached is the database.
Thank you very much for reading. All advice is greatly appreciated.
Attachments
Last edited: