Adding data to table, with VBA y u no easy!?

[redbull]

I have a table (tbl_21_only_temp) full of data. This table has 8 columns. The first 7 are already populated, however the 8th column is blank..I need to fill in this column on the current record selected. I really don't know how to acheive this using vba. I can tell you that Data will always be the same.. it will be "WebFrm.Document.all.Item("result").innerText"

Sub Looper()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_21_days_only_Temp"
DoCmd.SetWarnings True


Dim r As DAO.Recordset
  Set r = CurrentDb.OpenRecordset("Select * from tbl_21_only_temp") ' Query or Table records to loop thru
   'Check to see if the recordset actually contains rows
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
        Do Until r.EOF = True
'Start code for each record to do here


            Call "fancyStuff"
       
            ''''HERE IS WHERE I NEED TO ADD THE DATA FROM "fancystuff" to the 8th column of the current record set. 
       
'End code for each record here
        'Move to the next record. Don't ever forget to do this.
        r.MoveNext
    Loop
Else
    MsgBox "There are not records in the recordset."
End If


MsgBox "Reached end of process"


r.Close
Set r = Nothing
End Sub

Columns on this table

Client Number
CLient Name
InstallStatus
ProjType
Install Completion Date
Software Name
Vendor name


& the one I need to fill out is "URL Created"

[June7]

What is "fancystuff" - a function or code you haven't built yet?

Maybe simply:

r![URL Created] = fancystuff()

Or maybe just an UPDATE sql instead of opening and looping a recordset.

CurrentDb.Execute "UPDATE tbl_21_only_temp SET [URL Created]='" & fancystuff() & "'"

[redbull]

I'll give this a shot today. Fancy stuff is a pretty elaborate IE navigation piece that utilizes a URL generator.

[redbull]

So I am still having a hard time, I think it might be with how I am breaking it up.. Take a look at what "fancyStuff" is doing

Sub fancystuff()
Dim WebFrm As InternetExplorer
Dim Number, Name As String


Set WebFrm = Login("http://theurlwenavigate", True)


Call SleepIE(WebFrm)


 Number = DLookup("[Client number]", "tbl_21_only_temp")

 Name = DLookup("[Client Name]", "tbl_21_only_temp")
 
WebFrm.Document.getelementbyID("name").Value = Number & " - " & Name ' enters acct# and name with " - "


PauseApp 1


WebFrm.Navigate "javascript:doSubmit(encode())"  ' this one works!


PauseApp 1  'Pause for page load, JS does not display readystates so Sleep does not work here




Debug.Print WebFrm.Document.all.Item("result").innerText ' This is the text from the result box This is what I need to add to the URLCreated column of the table.


End Sub

So there is fancy stuff... I really don't know what I'm doing wrong. I am fairly new to modifying records etc etc. Any help is appreciated.

[redbull]

Alright I comnbines the two things... Maybe you can make sense of it.

Sub Looper()


Dim WebFrm As InternetExplorer
Dim Number, Name As String
Dim r As DAO.Recordset
 
  Set r = CurrentDb.OpenRecordset("Select * from tbl_21_only_temp") ' Query or Table records to loop thru
     'Check to see if the recordset actually contains rows
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
        Do Until r.EOF = True
'Start code for each record to do here



Set WebFrm = Login("http:website", True)
  Call SleepIE(WebFrm)
    Number = DLookup("[Client number]", "tbl_21_only_temp")
    Name = DLookup("[Client Name]", "tbl_21_only_temp")
  
WebFrm.Document.getelementbyID("name").Value = Number & " - " & Name ' enters acct# and name with " - "
   PauseApp 1


WebFrm.Navigate "javascript:doSubmit(encode())"  ' this one works!
   PauseApp 1  'Pause for page load, JS does not display readystates so Sleep does not work here


Debug.Print WebFrm.Document.all.Item("result").innerText ' This is the text from the result box (this is what I need added to the column)
       


'End code for each record here
        'Move to the next record. Don't ever forget to do this.
         r.MoveNext
    Loop
Else
    MsgBox "There are not records in the recordset."
End If


MsgBox "Reached end of process"


r.Close
Set r = Nothing
End Sub

[June7]

I don't really see need for the recordset. Nothing in the recordset is used in the code. The DLookups don't have any filter criteria so they will just return the first value in the table in each loop. But why are the Dlookups needed when you have the recordset of the same data?

What is it you are trying to do? Do you want the same value from web page populated into all records?

[redbull]

I have a table with lets say 20 records.

I want the loop to start with the first record set

I want the ieautomation to fire for each record at the end of this process

WebFrm.Document.all.Item("result").innerText is what I want to add to the URLCreated Column on the table for that record

over and over again

[June7]

Okay, but that code as is will just save the same data to all records. The DLookups are useless. Instead, try:

WebFrm.Document.getelementbyID("name").Value = r![Client Number] & " - " & r![Client Name] ' enters acct# and name with " - "
...
r![URL Created] = WebFrm.Document.all.Item("result").innerText


Might need to set the edit mode of recordset, so before the Do While loop: r.Edit

[redbull]

Alright, it looks good, it is cycling thru the records correctly. However it is not writing to the column. I get an error "3020 - Update or CancelUpdate without Addnew or Edit." Any thoughts?

Sub Looper()
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qry_21_days_only_Temp"
'DoCmd.SetWarnings True


Dim WebFrm As InternetExplorer
Dim Number, Name As String
Dim r As DAO.Recordset
 
  Set r = CurrentDb.OpenRecordset("Select * from tbl_21_only_temp") ' Query or Table records to loop thru
     'Check to see if the recordset actually contains rows
     
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
       r.Edit
        Do Until r.EOF = True
'Start code for each record to do here


Set WebFrm = Login("httpwebsite", True)
Call SleepIE(WebFrm)
   Number = DLookup("[Client number]", "tbl_21_only_temp")
   Name = DLookup("[Client Name]", "tbl_21_only_temp")
  
WebFrm.Document.getelementbyID("name").Value = r![Client Number] & " - " & r![Client Name] ' enters acct# and name with " - "
   PauseApp 1
WebFrm.Navigate "javascript:doSubmit(encode())"  ' this one works!
PauseApp 1  'Pause for page load, JS does not display readystates so Sleep does not work here




 r![URLCreated] = WebFrm.Document.all.Item("result").innerText 'error 3020 here
 
 
'End code for each record here
        'Move to the next record. Don't ever forget to do this.
        r.MoveNext
    Loop
Else
    MsgBox "There are not records in the recordset."
End If


MsgBox "Reached end of process"


r.Close
Set r = Nothing
End Sub

I also removed the Dlookups, they were worthless

[June7]

Trial and error. The only time I've ever edited recordset is a RecordsetClone.

Review http://msdn.microsoft.com/en-us/libr.../ff821175.aspx

Maybe:
r.Edit
r![URLCreated] = WebFrm.Document.all.Item("result").innerText
r.Update
r.MoveNext

[redbull]

Got it!

Sub Looper()
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qry_21_days_only_Temp"
'DoCmd.SetWarnings True


Dim WebFrm As InternetExplorer
Dim Number, Name As String
Dim r As DAO.Recordset
 
  Set r = CurrentDb.OpenRecordset("Select * from tbl_21_only_temp") ' Query or Table records to loop thru
     'Check to see if the recordset actually contains rows
     
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
       r.Edit
        Do Until r.EOF = True
'Start code for each record to do here


Set WebFrm = Login("http://website", True)
Call SleepIE(WebFrm)
  
WebFrm.Document.getelementbyID("name").Value = r![Client Number] & " - " & r![Client Name] ' enters acct# and name with " - "
   PauseApp 1
WebFrm.Navigate "javascript:doSubmit(encode())"  ' this one works!
PauseApp 1  'Pause for page load, JS does not display readystates so Sleep does not work here


Debug.Print WebFrm.Document.all.Item("result").Value
 r.Edit
 r![URLCreated] = WebFrm.Document.all.Item("result").Value
 
 
'End code for each record here
        'Move to the next record. Don't ever forget to do this.
        r.Update
        r.MoveNext
        
    Loop
Else
    MsgBox "There are not records in the recordset."
End If


MsgBox "Reached end of process"


r.Close
Set r = Nothing
End Sub

It WAS editing the first record, but not the second.. I added another r.edit within the loop, then an r.update before the r.movenext. Works like a charm. Once again you saved me. Give me a paypal donate button or something.