Access: update a row of values based on a field value

[atksamy]

1

HI

i have database with many tables. In the first table i have a field called status.

[PHP]table 1
idno name status
111 hjghf yes
225 hjgjj no
345 hgj yes

other tables could have same idno with different fields.
2nd table

idno salary expense
111 456 469
225 42
345 456[/PHP]


I want to check the status for each id no and if it is yes then for that id number in all tables for all null and blank fields i want to update them as 111111.

I want the the field salary for id no 345 for which status is yes in first table to be updated by 111111. like this i need a loop as there are many tables in the database

I am looking for a sample vba code for this which i can adapt.

Thanks

[NeoPa]

Start by using a For Each loop for all the TableDefs in CurrentDB.

Within that, exclude any you're not interested in.

Within the loop set up a RecordSet (rs) with SQL incorporating each table linked to [Table1] on the ID field.

Process through the recordset ensuring all empty fields are set to "111111" (or 111111). Use rs.Edit & rs.Update around any changes to allow them to be applied.

[atksamy]

Hi I v started like this

Sub UpdateNulls()
Dim strSQL As String
Dim rs As DAO.Recordset
For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "table1" Then
        strSQL = "Select * From [" & tdf.Name & "] a Inner Join " _
        & "table1 On a.fall = table1.fall Where a.Status = 5"
 
 
        Set rs = CurrentDb.OpenRecordset(strSQL)
 
        Do While Not rs.EOF
            For i = 0 To rs.Fields.Count - 1
                If IsNull(rs.Fields(i)) Then
                    rs.Edit
                    rs.Fields(i) = 111111
                    rs.Update
                End If
            Next
            rs.MoveNext
        Loop
 
    End If
Next
End Sub

but i am getting runtime error 3075 at a.fall = table1.fall
in the strsql statement.

can anyone help me out wit teh reason

[NeoPa]

I will start looking at this for you, but it would be very helpful to see the error message too.

For clarity - the error occurs at lines #6/7 in your code.

[NeoPa]

I've checked through those lines and I cannot see anything which might throw an error.

Can you confirm the line number of the line the error is actually occurring on, as well as the error message itself, to give us something more to go on.

PS. You've clearly gone a long way with the concept with just the outline of some clues. Nice going :)

[atksamy]

i have made te code work

what i am trying to do now is instaed of updating all field i am trying to update only certain fields.
this list of certain fields, i am planningto give througha text file.

ub UpdateNulls()
Dim strSQL As String
Dim rs As DAO.Recordset
 
 
 
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "01UMWELT" Then
strSQL = "Select * From [" & tdf.Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 6"
 
 
Set rs = CurrentDb.OpenRecordset(strSQL)
 
 
 
Dim variable
 
Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
 
 
 
Do While Not rs.EOF
 
Do While Not EOF(1)
For i = 0 To rs.Fields.Count - 1
 
Line Input #1, variable
Debug.Print TextLine
If IsNull(rs.Fields(variable)) Then
rs.Edit
Debug.Print rs.Fields(variable)
rs.Fields(variable) = 888
rs.Update
 
 
Close #1
End If
Next
rs.MoveNext
Loop
Loop
 
End If
Next
End Sub
 
 

but i am getting an error runtime 62 input past end of line at Line Input #1, variable

i think i am confusing up with teh text file calling ?

thanks
atks

[atksamy]

i have cleared that runtime error
but now i am getting a runtime erro 3265
at

If IsNull(rs.Fields(variable)) Then
 

what could be wrong

[NeoPa]

I assume this is at line #29 of the code in post #6?

I also still need an error message please. I can't see anything obviously wrong, so the error message is necessary (always a good idea to provide it anyway) to give me a clue.

[atksamy]

yes it is in line 29

the error is element in listing cannot be found.



ps: when i am checking whether the fields of that variable to be null i am meaning it column wise is this correct

[NeoPa]

Right, it looks like line #28 has been left over from a previous version.

What you need to do is to change things so that they display (Debug.Print or MsgBox() are both ok for this) the line just read in from the file. I suggest you actually scrap the variable called "variable" as that is a reserved word and shouldn't ever be used as the name for a variable (It's also very likely to confuse - who needs that).

When you can see what the variable contains, you will see that it probably doesn't match the name of any of your fields. That is your problem I'm fairly sure.

[atksamy]

well i changed the 'variable'
but yes the error still occurs and the varaiable reflects a field in the immediate window which is there in the field

[NeoPa]

Can you post exactly (I'll say that again for emphasis - EXACTLY) what the value displayed is, and the names of all the fields in the recordset please.

[atksamy]

well the field i am trying to update is ortsl which is taken from the text file

I am not sure which value displayed you are asking for.

error is 3265 "element in the listing canot be found"


Is there some way i can view the contents of the record set like some debug statement to display the recordset

[NeoPa]

Also, could you please repost the current live version of UpdateNulls().

[atksamy]

here is the current version

Dim rs As DAO.Recordset
 
 
 
 
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "01UMWELT" Then
strSQL = "Select t.* From [" & tdf.Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 6"
 
 
Set rs = CurrentDb.OpenRecordset(strSQL)
 
 
 
Dim varii As String
 
Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
 
 
 
Do While Not rs.EOF
 
Do While Not EOF(1)
For i = 0 To rs.Fields.Count - 1
 
Line Input #1, varii
 
Debug.Print rs.Fields(i)
Debug.Print varii
 
 
If IsNull(rs.Fields(varii)) Then
 
 
rs.Edit
Debug.Print rs.Fields(varii)
rs.Fields(varii) = 888
rs.Update
 
 
Close #1
End If
Next
rs.MoveNext
variable_ok:
Loop
Loop
 
End If
Next
End Sub
 

[NeoPa]

well the field i am trying to update is ortsl which is taken from the text file

I am not sure which value displayed you are asking for.

I am looking for the value sent to the Immediate pane by your line #28 (Debug.Print...).

Is there some way i can view the contents of the record set like some debug statement to display the recordset

Try the following code entered into the Immediate pane :

For Each fld in rs.Fields : ?fld : Next fld

Be sure to run it only when the code is at a point where rs is set up.

[NeoPa]

Try the following code entered into the Immediate pane :

For Each fld in rs.Fields : ?fld : Next fld

Be sure to run it only when the code is at a point where rs is set up.

Unless you're after the names of the fields instead, in which case use the following instead :

For Each fld in rs.Fields : ?fld.Name : Next fld

[NeoPa]

Try replacing lines #28 & #29 with the following and posting what it says when the code fails :

Debug.Print "rs.Fields(i) = '" & rs.Fields(i).Name & "'" & vbCrLf & _
            "varii        = '" & varii & "'"

Remember to use Copy / Paste to get the info into the post for me if you would.

[atksamy]

Try replacing lines #28 & #29 with the following and posting what it says when the code fails :

Debug.Print "rs.Fields(i) = '" & rs.Fields(i).Name & "'" & vbCrLf & _
            "varii        = '" & varii & "'"

Remember to use Copy / Paste to get the info into the post for me if you would.

rs.Fields(i) = 'FALL'
varii = 'ORTSL'


this is teh result in immediate window

fall is the first field of the table

[atksamy]

Unless you're after the names of the fields instead, in which case use the following instead :

For Each fld in rs.Fields : ?fld.Name : Next fld

regarding this i am actually a beginner in vba so i am not sure what you are asking me to do.

should i enter this code in immediate window
then how do i run it from immediate window

[NeoPa]

rs.Fields(i) = 'FALL'
varii = 'ORTSL'


this is teh result in immediate window

fall is the first field of the table

Clearly these do not match. Maybe because they come in a different order (Doesn't seem promising).

Let's look at the full field list to see what we have as choices. I will post a new version of your code to test this out with.

[NeoPa]

Before I continue, what are you trying to do with the data found in the text file?

I thought it was to tell you the name of the field but it doesn't seem to be used for that in the code.

[atksamy]

yes it is supposed to be the name of the field which i am trying to update

[NeoPa]

So, why do you need the name of the field supplied separately (from an outside source)?

You already have the field itself (rs.Fields(i)) as well as its name (rs.Fields(i).Name). I'm a little confused.

[atksamy]

I dont want to update all fields i want to update only those fields which are provided in the text file.
like this i can add or remove the fields which have to be updated from time to time.

[NeoPa]

Ah. In that case the logic of your code is fairly wrong.

Give me a while & I'll post something for you that incorporates the logic you're after.

[NeoPa]

Try this :

Private Sub UpdateNulls()
  Dim rs As DAO.Recordset
  Dim varii As String, strField As String
  Dim astrFields As Variant    'Stores the array of Field names
  Dim intIx As Integer
 
  Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
  varii = ""
  Do While Not EOF(1)
    Line Input #1, strField
    varii = varii & "," & strField
  Loop
  Close #1
  astrFields = Split(varii, ",")  'Element 0 empty
 
  For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "01UMWELT" Then
      strSQL = "SELECT t.* " & _
               "FROM [" & tdf.Name & "] AS t INNER JOIN " & _
                    "01UMWELT " & _
                 "ON t.fall=[01UMWELT].fall " & _
               "WHERE [01UMWELT].Status=6"
 
      Set rs = CurrentDb.OpenRecordset(strSQL)
 
      Do While Not rs.EOF
        For intIx = 1 To UBound(astrFields)
          With rs
            On Error Resume Next   'Ignore field if table doesn't have it
            Call Err.Clear
            varii = Nz(.Fields(astrFields(intIx)), "NullValue")
            If Err.Number <> 3265 Then
              If varii = "NullValue" Then
                Call .Edit
                .Fields(astrFields(intIx)) = 888
                Call .Update
              End If
            End If
            On Error GoTo 0        'End of special handling
          End With
        Next intIx
        rs.MoveNext
      Loop
    End If
  Next
End Sub

[atksamy]

thanks i had a feeling there was something wrong when i was looping within the loops

[NeoPa]

Please check again. I posted at pretty well the same time as you did.

[atksamy]

Thanks very much .

i just checked the code.
i have come across two things

firstly the first table is not getting updated

secondly after all the tables are checked the loop is not ending i am gettin
run time error 3061 one parameter expected but too few parameters

i put a watch on the table definition and I see that after the last table
the value of tabledef in watch window is " MsysAccessobjects"

[NeoPa]

Well, let's start by adding some code in where line #15 is currently. This is simply for debugging so doesn't need to stay in long-term.

For intIx = 1 To UBound(astrFields)
  Debug.Print "'" & astrFields(intIx) & "'",
Next intIx

Can you post me back the results please.

BTW Debugging remotely is not very reliable so great care needs to be taken. I am taking this a step at a time.

[atksamy]

these are the results

'ORTSL' 'hursau' 'folge1' 'arztanh' 'alter2' 'kopfumf' 'gcs2' 'babin' 'vteil' 'kemler22' 'hklasse' 'kopf20' 'tueravl' 'ORTSL' 'hursau' 'folge1' 'arztanh' 'alter2' 'kopfumf' 'gcs2' 'babin' 'vteil' 'kemler22' 'hklasse' 'kopf20' 'tueravl'


these are the field i am giving as input in the text file

[NeoPa]

Can you explain why each of the fields is included twice?

[atksamy]

oh sorry about that my mistake
I had run the code twice

[NeoPa]

OK. That's good :)

Now, let's replace that code with some data about the tables and post that information for me if you will.

For Each tdf In CurrentDb.TableDefs
  Debug.Print "'" & tdf.Name & "'",
Next tdf

BTW, you should always use Option Explicit in all of your modules. That way the easier to find and fix errors will be found for you. Syntax etc.

Option Explicit

With that set you will also need to add in the following line that I omitted from the original code (after line #2 I suggest) :

Dim tdf As DAO.TableDef

[atksamy]

Here s the result

'01UMWELT' '03_PERSDAT' '04_VERLUEB' '05_VERL' '06_RETTUNG' '07_CRASHPER' '08_PKW' '10_LKW' '11_LKWB' '12_ZWEIRAD' '13_INNEN' '14_HAENGER' '15_FZGA' '16_FZGI' '17_BEFRAG' '18_FGDAT' '19_ZWDAT' '20_REKO' '22_SEQUENZ' '23_MPERS' '24_SITZE' '25_FZGAS' '28_VUANZMF' '29_VUANZBET' '30_VUANZ' '50_FZG1' '51_FZG2' '52_PKWB1' '53_PKWB2' 'MSysAccessObjects' 'MSysAccessXML' 'MSysACEs' 'MSysObjects' 'MSysQueries' 'MSysRelationships'


i see that system tables are also getting included when they should nt be.

[NeoPa]

Actually, in this list, they should. This is all good.

[atksamy]

i was trying step by step trouble shooting and at this step

For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "01UMWELT" Then
       strsql = "SELECT t.* " & _
               "FROM [" & tdf.Name & "]AS t INNER JOIN " & _
                    "[01UMWELT] " & _
                 "ON t.fall=[01UMWELT].fall " & _
               "WHERE [01UMWELT].Status= 6"

even though tabledef is "msys" the control is goin to strsql

[atksamy]

I have cleared the run time error i am not sure if this is the answer but in the code

sql "Msys" i changed it to "MSys"

and now the runtime error is gone( i hope for good)

so now have to check out updation of first table

[atksamy]

also for the first table not getting updated i figure that the sql query if changed to the following

strsql = "SELECT * " & _
               "FROM [" & tdf.Name & "] t INNER JOIN " & _
                    "[01UMWELT] " & _
                 "ON t.fall=[01UMWELT].fall " & _
               "WHERE [01UMWELT].Status= 6"
 

the first field gets updated

but if change it in the above method i get an runtime error 3190 too many fields defined

[NeoPa]

...
so now have to check out updation of first table

As the first table is '01UMWELT' and you said you didn't want to update '01UMWELT', what is the problem?

[NeoPa]

...
sql "Msys" i changed it to "MSys"

That is the change that is necessary. The string comparison here is case-sensitive.

[atksamy]

actually i do want the first table also to be updated so i changed the sql to followeing

 
 For Each tdf In CurrentDb.TableDefs
  If Left(tdf.Name, 4) <> "MSys" Then
    strsql = "Select t.* From [" & tdf.Name & "] t Inner Join 01_UMWELT On t.fall = [01_UMWELT].fall Where [01_UMWELT].Status = 4"
 
 

but still the first table is not gettin updated. i tried trouble shooting and can see that the code is running through the first table and for ceratin paramerters even though null values exist varii is returning some number and for some fileds even though varii is returning null values update is not taking place but as the loop progresses to next table the code works fine

[NeoPa]

actually i do want the first table also to be updated so i changed the sql to followeing

 For Each tdf In CurrentDb.TableDefs
  If Left(tdf.Name, 4) <> "MSys" Then
    strsql = "Select t.* From [" & tdf.Name & "] t Inner Join 01_UMWELT On t.fall = [01_UMWELT].fall Where [01_UMWELT].Status = 4"

The [Status] field in the WHERE clause is now 4 instead of 6. No idea why, but it is probably not causing your problem.

but still the first table is not gettin updated. i tried trouble shooting and can see that the code is running through the first table and for ceratin paramerters even though null values exist varii is returning some number and for some fileds even though varii is returning null values update is not taking place but as the loop progresses to next table the code works fine

I'd like to understand what you're saying but I'm afraid you've lost me. I suspect you're doing the right thing by debugging, but once you have the interesting information you need to pass it on to me. I think this is the sticking point at the moment.

[atksamy]

i changed the status code for testing purposes but yes that is not creating problem.

one information i have is previously when i was trying to export these tables to excel using vba code (in excel) I noticed that all tables used to get exported correctly but there was a problem in the first table where the first 50 record used to get exported in the middle and not in the order they should be.

similarly here in the recordset i guess initially the first 50 record s are not being considered.

now the problem is updates is occuring in all tables except the first table even when code runs through the first table as i saw it while debugging.

[NeoPa]

...but there was a problem in the first table where the first 50 record used to get exported in the middle and not in the order they should be.

There is no "order they should be" unless you specify one in a query.

similarly here in the recordset i guess initially the first 50 record s are not being considered.

now the problem is updates is occuring in all tables except the first table even when code runs through the first table as i saw it while debugging.

I need you to explain what you saw or found out while debugging.

[atksamy]

During debugging i saw that varii was returning null values for fields in the first table and control was passin through code

Call .Edit
.Fields(astrFields(intIx)) = 888
Call .Update

but the table is not getting updated.

[NeoPa]

Now you're talking.

Did ANY of the records, or any of the fields in any record, get updated for this table within your code run?

[atksamy]

No none of the records for this table got updated.
updation began only from next table

[atksamy]

Also i was trying to get a kind of report in word of the fields getting updated and their record ids

i can manage to get the name of the fields in the word file but how do i get the each id :(

something in word file like


recordid variable existing value updated value
1111 ortsl null value 888