Need help with DAO coding, find entry in table and edit loop. (1 Viewer)

[wakamoe]

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.

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.

 

[Cronk]

Instead of opening your recordset based on the whole table, filter the recordset to include just those records defined by your combo criteria

set rs1 =db.openrecordset("select * from table1 where item_number='" & me.item-number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "'")

(Note the need to include single quotes because you are using text fields)

Then just loop through the number required to be updated.

Incidentally, you have no check that the number selected to be changed is not more than the number of relevant records.

Also when you declare variables like
Dim Customer_orders, item_number, Week_no, year_no As String
only year_no is a string, the rest are variants

If you want strings, you need
Dim Customer_orders as string, item_number as string, Week_no as string, year_no As String

 

[wakamoe]

Instead of opening your recordset based on the whole table, filter the recordset to include just those records defined by your combo criteria

set rs1 =db.openrecordset("select * from table1 where item_number='" & me.item-number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "'")

(Note the need to include single quotes because you are using text fields)

Then just loop through the number required to be updated.

Incidentally, you have no check that the number selected to be changed is not more than the number of relevant records.

Also when you declare variables like
Dim Customer_orders, item_number, Week_no, year_no As String
only year_no is a string, the rest are variants

If you want strings, you need
Dim Customer_orders as string, item_number as string, Week_no as string, year_no As String

Thank you for the reply. I changed the the declaration for String and also edited the setting for RS1.

How should I make it so that the value keyed into the text box will not be more than the records for the Item number/Customer order?

Also, After editing, I am having an error "Objects required" for this line:

If rs1.Fields("Customer_orders").Value = Me.[Customer_order_combo] And rs1.Fields("item_number").Value = Me.[Item_number_combo] And rs1.Fields("week_no").Value Is Null Then

Currently, this is the code. Isit because I did not declare the form and it's objects? How can I declare it if that is the problem?

Dim db
Dim rs1 As Recordset
Dim Customer_orders As String, item_number As String, Week_no, year_no As String
Dim counter1 As Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordset("select * from table1 where item_number='" & Me.Item_number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "'")
 
    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

      [B]              If rs1.Fields("Customer_orders").Value = Me.[Customer_order_combo] And rs1.Fields("item_number").Value = Me.[Item_number_combo] And rs1.Fields("week_no").Value Is Null Then
                    Set Me.[Week_no_combo].Value = rs1.Fields("Week_no").Value
[/B]
                    rs1.MoveNext
                    End If
                    counter1 = counter1 + 1

                Loop

                    rs1.MoveNext

                Loop

  Me.Requery
  [child1].Form.Requery

 

[Cronk]

You have filtered the recordset so it only contains those records you want to change. You do not need to test again that the records match the criteria

So try something like

Dim db as recordset
Dim rs1 As Recordset
Dim Customer_orders As String, item_number As String, Week_no, year_no As String
Dim counter1 As Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordset("select * from table1 where item_number='" & Me.Item_number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "' AND Week_no is null")

counter = 0
rs1.MoveFirst
do while not rst1.eof and counter <me.Me.Records_txtbox
   rst1.edit
   rst1!Week_no= me.Week_no_combo
   rst1.update
   rst1.movenext
   counter = counter +1
loop

 

[wakamoe]

You have filtered the recordset so it only contains those records you want to change. You do not need to test again that the records match the criteria

So try something like

Dim db as recordset
Dim rs1 As Recordset
Dim Customer_orders As String, item_number As String, Week_no, year_no As String
Dim counter1 As Integer

Set db = CurrentDb
Set rs1 = db.OpenRecordset("select * from table1 where item_number='" & Me.Item_number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "' AND Week_no is null")

counter = 0
rs1.MoveFirst
do while not rst1.eof and counter <me.Me.Records_txtbox
   rst1.edit
   rst1!Week_no= me.Week_no_combo
   rst1.update
   rst1.movenext
   counter = counter +1
loop

Wow, thank you very much for the reply. Your code is a lot more streamline and easier to understand.

However, there seems to be a problem with the code. "Set db = CurrentDb" has a type mismatch error. I tried to swap it to RST1 or deleting it, then the next line"

Set rs1 = db.OpenRecordset("select * from table1 where item_number='" & Me.Item_number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "' AND Week_no is null")

Has the error: Object variable or with block variable not set.

Also, why did you use RST1 instead of RS1.


Sorry for the many questions and thank you once again for helping out!

 

[Cronk]

Sorry, air code done quickly

dim db as Database
and
rst1 should be rs1

 

[wakamoe]

Sorry, air code done quickly

dim db as Database
and
rst1 should be rs1

Amazing! Thank you so much!

Manage to get it working, there were some typos that confused me a bit but after changing it, it works wonders.

One last thing, do you know how I can limit the value keyed into the text field (for the amount of records to be changed) based on the "Customer Order" and "Item Number" chosen?

===

Here is the final code! I had to make an edit to Counter as it was declared Counter1 but was written Counter for the rest of the code haha.

Dim db As Database
Dim rs1 As Recordset
Dim Customer_orders As String, item_number As String, Week_no As String, year_no As String
Dim counter1 As Integer

Set rs1 = CurrentDb.OpenRecordset("select * from table1 where item_number='" & Me.Item_number_combo & "' AND Customer_orders='" & Me.[Customer_order_combo] & "' AND Week_no is null")

counter1 = 0
rs1.MoveFirst
Do While Not rs1.EOF And counter1 < Me.Records_txtbox
   rs1.Edit
   rs1!Week_no = Me.Week_no_combo
   rs1.Update
   rs1.MoveNext
   counter1 = counter1 + 1
Loop

  Me.Requery
  [child1].Form.Requery

 

[Cronk]

Regarding my typos, if you are coding a lot, you should make sure that your variables are explicity declared. To do this automatically, in VBA screen, set in the menu Tools | Options | Require Variable Declaration => clicked

This will add the line
Option explicit
automatically to the top of every module. (You can do this manually afterwards).

As to worrying about the number keyed into the No to be changed, don't.
If the number is more than the number in the recordset, the loop hits the end of file and stops. Eg if there are say 4 records and you put 1000 to change, it just stops after 4.

However if you want to automatically change the 1000 to 4, search the function DLookup