Edit Record function in VBA (1 Viewer)

[mfiedler]

I have tried to search for an answer on this elsewhere, and it is likely easy to fix, but I am having no luv. I included the function I have written below. This function is to look at a record in table T, make some comparisons and if criteria is met, open and edit a record in table S with the data from table T. I am confused just typing that. lol. Anyway, I keep getting an error at the line s.Edit (which was EditMode, but that did not work either). That is the line that is supposed to open and edit the records into the S table. I am not sure where I am going wrong. Ideas would be welcome. Thanks.

Function calc_bulk_levels()
'Dim w As Workspace
'Dim db As Database
Dim t As Recordset
Dim s As Recordset
Dim r, v, x As Integer
Dim u, y, z As String
Set w = DBEngine.Workspaces(0)
Set db = w.Databases(0)
Set t = db![tbl: Level Load Testing].Openrecordset()
Set s = db![tbl: Summary of Analysis].Openrecordset()

t.MoveFirst
s.MoveFirst
u = t![Base Num]
x = 0
v = 0

Do Until t.EOF
If t![Base Num] = u Then
If x = 0 Then
x = x + t![Bulk Qty]
v = t!
If x <= v Then
t.MoveNext
End If
Else
x = x + t![Bulk Qty]
If x <= v Then
t.MoveNext
Else
s.Edit
s![Base Num] = u
s![MRP Controller] = y
s![Bulk Qty] = x
s![Inventory] = v
s![Basic Start Date] = z
s.Update
Do Until t.[Bas Num] = !u
t.MoveNext
Loop

End If
End If
y = t![MRP ctrlr]
v = t!
z = t![Bsc start]
Else
u = t![Base Num]
End If
Loop

End Function

 

[Fear Naught]

I have laid out your code in a more structured way so it is easier to read.

I have made the following changes:

1. Used DAO for retrieving data (ensure that the Microsoft DAO 3.6 Object Library is selected under references).

2. In the Loop to determine the value of [Bas Num] in the 't' RecordSet I have changed your sysntax so that the code will compile correctly.

3. I have closed the 't' and 's' RecordSets at the end of the function.

It seems to me that you only ever change the first record in the 's' recordset. Is that how you want it to work??

The revised code structure is:

Function calc_bulk_levels()

'Dimension variable and objects
Dim DB As DAO.Database
Dim t As DAO.Recordset
Dim s As DAO.Recordset
Dim r, v, x As Integer
Dim u, y, z As String

'open database and recordset objects

Set DB = DBEngine.Workspaces(0).Databases(0)
Set t = DB.OpenRecordset("Level Load Testing", dbOpenDynaset)
Set s = DB.OpenRecordset("Summary of Analysis", dbOpenDynaset)


t.MoveFirst
s.MoveFirst
u = t![Base Num]
x = 0
v = 0

Do Until t.EOF
    If t![Base Num] = u Then
        If x = 0 Then
            x = x + t![Bulk Qty]
            v = t![i]
            If x <= v Then
                t.MoveNext
            End If
        Else
            x = x + t![Bulk Qty]
            If x <= v Then
                t.MoveNext
            Else
                s.Edit
                s![Base Num] = u
                s![MRP Controller] = y
                s![Bulk Qty] = x
                s![Inventory] = v
                s![Basic Start Date] = z
                s.Update
                Do Until t![Bas Num] <> u
                    t.MoveNext
                Loop

            End If
        End If
        y = t![MRP ctrlr]
        v = t![i]
        z = t![Bsc start]
    Else
        u = t![Base Num]
    End If
Loop

End Function

I hope this helps.

 

[mfiedler]

Thanks for the help Fear Naught. I had my original code structured in a traditional format, it did not translate to the post when I pasted it. My apologies, it is much easier to read when it is setup correctly. Thanks for making it easier on the eyes.

Although this is going to sound like an easy question, being new to VBA, everything is a challenge right now. Where would I go to find the "references" you spoke of so I can make certain the DAO functionality will work properly?

And as for the question pertaining to only updating the first record, you are correct in assuming that this is not what I desired. I got stuck on trying to understand why I was getting the error on the s.Edit command and forgot to place in the logic for moving to the next record in the S table as each line is updated. I will repair that. Thanks for the reminder.

Again, I appreciate your help and patience with this novice.

Thanks,
Matt

 

[mfiedler]

Holy Brain Fart!!!! Please ignore my question of where to find the References section to activate the DAO functionality. Gee, it wouldn't be under "References" would it? LOL!!!!!!!! Oh well, at least it is Friday. Thanks again for your help Fear Naught. I will insert the next record functionality for the S table and give it a test run, but at least there were no compiling errors now.

Cheers,
Matt

 

[mfiedler]

OK. I made a few changes to advance the record in the S table, but when I run the function, it gives me a run-time error 3021 and points to the statement s.MoveFirst as having no record. So I tried putting in some default values just to have data it could identify with, but it still had the same result. The funtion is supposed to build the S table as values from the T table meet certain criteria. Any thoughts would be greatly appreciated.

Thanks,
Matt

Function calc_bulk_levels()

'Dimension variable and objects
Dim DB As DAO.Database
Dim t As DAO.Recordset
Dim s As DAO.Recordset
Dim r, v, x As Integer
Dim u, y, z As String

'open database and recordset objects

Set DB = DBEngine.Workspaces(0).Databases(0)
Set t = DB.OpenRecordset("tbl: Level Load Testing", dbOpenDynaset)
Set s = DB.OpenRecordset("tbl: Summary of Analysis", dbOpenDynaset)


t.MoveFirst
[COLOR="Red"][B]s.MoveFirst[/B][/COLOR]
u = t![Base Num]
x = 0
v = 0

Do Until t.EOF
    If t![Base Num] = u Then
        If x = 0 Then
                x = x + t![Bulk Qty]
                v = t![I]
                y = t![MRP ctrlr]
                z = t![Bsc start]
            If x <= v Then
                t.MoveNext
            Else
                    s.Edit
                    s![Base Num] = u
                    s![MRP Controller] = y
                    s![Bulk Qty] = x
                    s![Inventory] = v
                    s![Basic Start Date] = z
                    s.Update
                    x = 0
                    v = 0
                    s.MoveNext
                Do Until t![Bas Num] <> u
                    t.MoveNext
                Loop
            End If
        Else
                x = x + t![Bulk Qty]
                y = t![MRP ctrlr]
                v = t![I]
                z = t![Bsc start]
            If x <= v Then
                t.MoveNext
            Else
                    s.Edit
                    s![Base Num] = u
                    s![MRP Controller] = y
                    s![Bulk Qty] = x
                    s![Inventory] = v
                    s![Basic Start Date] = z
                    s.Update
                    x = 0
                    v = 0
                    s.MoveNext
                Do Until t![Bas Num] <> u
                    t.MoveNext
                Loop
            End If
        End If
    Else
        u = t![Base Num]
    End If
Loop

End Function

 

[Fear Naught]

How many records do you have in "tbl: Summary of Analysis". If there are less records than need updating then you will of course have a problem as you will have nowhere to move to.

Should you not be appending records to "tbl: Summary of Analysis" rather than updating them?

 

[mfiedler]

With some offline advice, I managed to get past the 3021 run-time error, but now have run into a run-time 6 error (Overflow). It is on the line x = x + t![Bulk Qty] located in the second If-Then-Else structure. I stepped into the function using the F8 key and it runs through several times, after about the 8th rendition, it throws up the run time error. I have checked some other posts on this site and noticed that the error can relate to field type, so changed the field type from double (numeric) to long integer (numeric) but still have the same result. I am stumped since it works for the first several records and then freezes up, instead of just not working out of the gate. The line of code is executing in a fashion brokedown below:

initial x       t![Bulk Qty]      new x
1               20                  21
21             40                  61
61             5                    66
66        etc....

Each new value of x is compared against a base number to determine next action. The cumlative values of X might peak into the millions, but most of the time the numbers will be less than that. I can't think this would be tapping to capacity issues, but that is all I can think of. Thoughts?

As always thanks ahead of time,
Matt

 

[mfiedler]

Great point on the append rather than update Fear Naught. I am assuming I would just have to change the text from s.Update to s.Append?

Thanks,
Matt

 

[Fear Naught]

Great point on the append rather than update Fear Naught. I am assuming I would just have to change the text from s.Update to s.Append?

Thanks,
Matt

The correct syntax is:

s.AddNew

When writing your code VB should assist you by giving you the options available in many cases. In this one when you type 's.' you get a combobox with the options available!!

 

[Fear Naught]

With some offline advice, I managed to get past the 3021 run-time error, but now have run into a run-time 6 error (Overflow). It is on the line x = x + t![Bulk Qty] located in the second If-Then-Else structure. I stepped into the function using the F8 key and it runs through several times, after about the 8th rendition, it throws up the run time error. Matt

When you break into the code what is the value of x? You should be able to get this by putting your mouse over it on the highlighted line.

 

[mfiedler]

Thanks for the advice Fear Naught on the help from VBA. A list does indeed pop-up. Wow this program is user-friendly (at least when you have a better idea what it is doing).

As for the value of x in the break code, it listed 32509. Why do I get the feeling this is hitting some kind of limit...I seem to recall in other programming languages a value around this 32,000 causing overload.

Thanks,
Matt

 

[mfiedler]

I wanted to provide a little more detail. The value of X when it stopped was 32509, but it is when it tried to add 261 to that value that it froze the function. The value of v at this comparison point is 625000. I have included a word file below that has some sample data from the T table.

You will see in the first pass, it should run through cleanly because the sum of the "Bulk Qty" for the Base Num A1153, is less than 625000. So no record would load to the S table. However, on the first pass of Base Num A1653, there is a quantity of 0 in the "I" field, so at this point the function should append a record to the S table that would put the appropriate data related to that failure and then move on to the next Base Num. The first record in the S table should have, A1653 | DJX | 12 | 0 | 5/6/2006 (I used | as a seperator of fields for this example).

Did any of that make sense? Yikes

 

[mfiedler]

I think I need to go back to programming basics 101....

I discovered the overflow error is related to long integer types that overload after 32670, hence explaining the error. I also learned after looking at this more closely, that my tables had the data numeric types of Double, not Integer as the function was trying to work with. I changed the types to Double in the function and that error went away and a new one arose...but of course, something else I am sure I messed up along the way.

The new error is focusing on the code:

Do Until t![Bas Num] <> u
                    t.MoveNext
                Loop

It displays run-time error '3265' - Item not found in collection. I am researching other posts now to see if some clues might be around, just wanted to update my progress with this function. Thanks for all the help Fear Naught. I really do appreciate your time and expertise.

 

[mfiedler]

I was able to get the function running. I replaced the Do Until Loop logic, with a Do While Loop logic. A few other errors popped up and I worked through those, but the code is now working properly. Just wanted to update anyone who may have been looking at this. I also have included the final code for future reference for people as lost as me. Thanks again for the help!!!

Function calc_bulk_levels()

'Dimension variable and objects
Dim DB As DAO.Database
Dim t As DAO.Recordset
Dim s As DAO.Recordset
Dim r, v, x As Double
Dim u, y, z As String

'open database and recordset objects

Set DB = DBEngine.Workspaces(0).Databases(0)
Set t = DB.OpenRecordset("tbl: Level Load Testing", dbOpenDynaset)
Set s = DB.OpenRecordset("tbl: Summary of Analysis", dbOpenDynaset)


t.MoveFirst
s.MoveFirst
u = t![Base Num]
x = 0
v = 0
r = 0

Do Until t.EOF
    If t![Base Num] = u Then
        r = t![Bulk Qty]
        If x = 0 Then
                x = x + r
                v = t![I]
                y = t![MRP ctrlr]
                z = t![Bsc start]
            If x <= v Then
                t.MoveNext
            Else
                    s.AddNew
                    s![Base SAP Material] = u
                    s![MRP Controller] = y
                    s![Bulk Qty] = x
                    s![Inventory] = v
                    s![Basic Start Date] = z
                    s.Update
                    x = 0
                    v = 0
                    s.MoveNext
                Do While t![Base Num] = u
                    t.MoveNext
                Loop
            End If
        Else
                x = x + r
                y = t![MRP ctrlr]
                v = t![I]
                z = t![Bsc start]
            If x <= v Then
                t.MoveNext
            Else
                    s.AddNew
                    s![Base SAP Material] = u
                    s![MRP Controller] = y
                    s![Bulk Qty] = x
                    s![Inventory] = v
                    s![Basic Start Date] = z
                    s.Update
                    x = 0
                    v = 0
                    s.MoveNext
                Do While t![Base Num] = u
                    t.MoveNext
                Loop
            End If
        End If
    Else
        u = t![Base Num]
        x = 0
    End If
Loop

End Function