run time error 3075 1

[tksy]

HI

I am gettinga run time error in´my code

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


I am getting runtime error on operator missing
a.fall = table1.fall

 

[tksy]

that works
thankyou very much
now i ll get back to implement this in the code.

but now in the code i guess the same provblem will occur or should i change the sql string

 

[Remou]

Change the code to read:

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 = 5"

<...>

The code I posted is an example. You will need to test and modify to suit your system. It would be unlikely that it worked the way you want the first time.

 

[PHV]

From the OP:
strSQL = "Select * From [" & tdf.Name & "] a Inner Join [!][[/!]table1[!]][/!] On a.fall = [!][[/!]table1[!]][/!].fall Where a.Status = 5"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksy]

actually only the braces need to be added on 01umwelt and teh code runs, at some point an erro too many fields is popping out, but i ll look into it.

one more doubt will a vba code i have written in excel work in access. i had written a code in excel vba for writing data to a word file . can i use the same function here to write the data or the fall numbers getting updated to a word file .

 

[Remou]

Most of the code should be ok. You will need to test.

 

[tksy]

while runnin this code i get a run time error 3190 too many fields

i googled the error but only thing i can find is that the database must be compact and repaired and then query must be run again.still the error occurs

i dont have more than 255 fields in any tables also.

any insight in this error

 

[PHV]

while runnin this code i get a run time error 3190
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksy]

sorry

i meant the code posted at the beginning of thread

but ya i have got through it. i took a back up of the database and deleted the current and tried running the code on teh new on e and so far it seems to be ok

thanks

 

[tksy]

When i run this(given at the start) code half way through the database i get an error 3001 invalid arguments.

i found out that this is because the databse has reached the 2gb size limit. any methods to to get through this.

 

[tksy]

to solve error 3001 i tried splitting th edatabse and then running the code now i am back with error 3190
too many fields

 

[Remou]

It seems that some of your tables have more than 127 fields:

http://support.microsoft.com/kb/199076

 

[tksy]

I am trying now that instead of updating all fields it would be better updating only a few fields due to size constraints.

would it be possible to have a text file containing only name of the fields which have to be checked and filled.

example if there s a field speed then only the field speed has to be checked in all records and updated.

 

[PHV]

To shorten the number of fields:
strSQL = "Select [!]a.[/!]* From [" & tdf.Name & "] a Inner Join [table1] On a.fall = [table1].fall Where a.Status = 5"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

'Yes, that is possible. It is possible to either create an array of fields that must be updated or to build a table of such fields.

Sub BuildList()

Dim strSQL As String
Dim strList As String
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

If IsNull(DLookup("Name", "MSysObjects", "[Name]='FieldList' AND (Type = 1 Or Type = 6)")) Then
    strSQL = "Create Table FieldList (FieldName Text(255), IncField YesNo)"
    db.Execute strSQL
Else
    Exit Sub
End If

For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "FieldList" Then
        For Each fld In tdf.Fields
            If InStr(strList, fld.Name) = 0 Then
                strList = fld.Name & "," & strList
                strSQL = "Insert into FieldList (Fieldname) Values ('" & fld.Name & "')"
                db.Execute strSQL
            End If
        Next
    End If
Next

Debug.Print Mid(strList, 2)
End Sub

 

[tksy]

I modified the code to update the fields which ar e given from a text file.

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 <> "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 run time error62 input past end of file
at line
Line Input #1, variable

am i not reading the text file properly
the text file has the name of the fields one below other

 

[tksy]

Hello PHV

you had given this query
To shorten the number of fields:
strSQL = "Select a.* From [" & tdf.Name & "] a Inner Join [table1] On a.fall = [table1].fall Where a.Status = 5"

but when i use this the first table is getting missed out i.e it is not getting updated.

 

[PHV]

Sorry, I quite don't understand what you're saying.
Could you please post the REAL code and explain whtat happens ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksy]

This is my code

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 <> "01UMWELT" Then
strSQL = "Select * From [" & tdf.Name & "] a Inner Join 01UMWELT On a.fall = [01UMWELT].fall Where [01UMWELT].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

Debug.Print rs.Fields(1)





rs.Fields(i) = 888
rs.Update
End If
Next
rs.MoveNext
Loop

End If
Next
End Sub


i was getting error 3190 too many fields

and u had reccomended changing the sqlto

strSQL = "Select a.* From [" & tdf.Name & "] a Inner Join [table1] On a.fall = [table1].fall Where a.Status = 5"

to shorten the fields

but now when i am updating the tables the first table is not getting updated.

 

[PHV]

What about this ?
strSQL = "SELECT * FROM [" & tdf.Name & "] WHERE fall In (SELECT fall FROM [01UMWELT] WHERE Status=5)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

I think I would update table1 first and then use PHV's suggestion for limiting the fields.

For Each tdf In CurrentDb.TableDefs
   If Left(tdf.Name, 4) <> "Msys" Then
      If tdf.Name = "Table1" Then
         strSQL = "Select * From table1 Where a.Status = 5"
      Else
         strSQL = "Select a.* From [" & tdf.Name _
         & "] a Inner Join table1 On a.fall = table1.fall Where a.Status = 5"
      End If
<...>