Automating an online enrollment

[Feddy]

i am looking to run a command that will automate a line of code for each record in my database.

i need each record in my table "data" run this command(without outside quotes): "Application.FollowHyperlink Address:="https://www.dell.com/support/Assets-Online/us/en/blahblah/#/product-list?servicetag=" & [service tag#]"

if possible with a 3 or so second delay in between each one, and also if possible in the same window.

i would assume that there will be some type of "For each" command, i just don't know how to implement.

Thanks in advance for the help!
-David

[CJ_London]

something like

dim rst as dao.recordset

set rst=currentdb.openrecordset(SELECT * FROM myTable, dbopensnapshot)
while not rst.eof
    Application.FollowHyperlink Address:="https://www.dell.com/support/Assets-Online/us/en/blahblah/#/product-list?servicetag=" & [service tag#]
    waitFor 3
    rst.movenext
wend

not sure what you mean by 'in same window'

code to wait for a few seconds -

Function waitFor(NumberOfSeconds As Variant)
Dim start As Variant
    
    start = Timer
    Do While Timer < start + NumberOfSeconds
        DoEvents
    Loop
 
End Function

[Feddy]

Thank you!
could i run this from a query as well?
also as-is it's giving me a compile error "expected: expression" i changed "mytable" to "data"(name of table)


this is what is currently in the window:

Function waitFor(NumberOfSeconds As Variant)Dim start As Variant
    
    start = Timer
    Do While Timer < start + NumberOfSeconds
        DoEvents
    Loop
 
End Function


Option Compare Database


Private Sub Command0_Click()
Dim rst As dao.Recordset


set rst=currentdb.openrecordset(SELECT * From data, dbopensnapshot)
While Not rst.EOF
    Application.FollowHyperlink Address:="https://www.dell.com/support/Assets-Online/us/en/blahblah/#/product-list?servicetag=" & [service tag#]
    waitFor 3
    rst.MoveNext
Wend


End Sub

[CJ_London]

sorry, written in a hurry, should be

set rst=currentdb.openrecordset("SELECT * From data", dbopensnapshot)

Also your 'option compare database' needs to be at the top of the page, not halfway down

As to running from a query, possible but not sure what effect the timer will have -all depends on what data you are passing. At the moment your hyperlink will be going to the same place each time

[Feddy]

ok i have made the changes, however, now when it runs it comes up with the error: "Microsoft Access can't find the field '|1' referred to in your expression." i see no field reference in the code, i'm sorry i am not very knowledgeable when it comes to this type of coding.

[CJ_London]

what line is it stopping at?

Is data a query or a table?

what is the exact code you are using now? copy and paste it

[Feddy]

please see images to answer your question.
"data" is a table

Option Compare Database


Function waitFor(NumberOfSeconds As Variant)
Dim start As Variant
    
    start = Timer
    Do While Timer < start + NumberOfSeconds
        DoEvents
    Loop
 
End Function


Private Sub Command0_Click()
Dim rst As dao.Recordset


Set rst = CurrentDb.openrecordset("SELECT * From data", dbOpenSnapshot)
While Not rst.EOF
    Application.FollowHyperlink Address:="https://www.dell.com/support/Assets-Online/us/en/blahblah/#/product-list?servicetag=" & [service tag#]
    waitFor 3
    rst.MoveNext
Wend


End Sub

[CJ_London]

three things strike me about this line

:="https://www.dell.com/support/Assets-Online/us/en/blahblah/#/product-list?servicetag=" & [service tag#]

is blahblah correct?

is the whole hyperlink correct? - suggest use debug.print to display the calculated string in the immediate window and compare with one you know works - perhaps [service tag#] needs to be surround with quotes?


and access is looking for a something called 'Service tag#'

1. using # in field and control names creates all sorts of issues - this may be one of them
2. where is 'Service tag#'?
if it is part of the data recordset you should use rst.fields([Service tag#]") - the # messes things up so syntax may be slightly different
if it is a control on your form it should be me.[service tag#] - again, the # messes things up so syntax may be slightly different

[CJ_London]

one other thing - if you need to be logged into your dell account, I would expect (but I may be wrong) either your hyperlink to include login details, or there is something else you need to do to login - you would need to talk to dell.

[Feddy]

no, Blahblah is in place of what i assume is my customer number, i am replacing it each time with the actual number(simple numbers and letters, no special characters)

"service Tag# is the name of the field in the table "data" that i am trying to include in each generated string. i got this string from a known working button that i would have to click through a few hundred times which is why i am trying to automate it (for now and the future)


i was trying to avoid rewriting all of the field references all over the database, but if its neccissary i can rename the field.


where would the reference rst.fields([Service tag#]") be used? at the end of the url string? if so, i just tried that and it didnt want to work (cant compile with the ' " ' and gives the same error as before without it)

as far as login goes, my plan was to manually login before running the batch, i know the string works from a form on an individual basis.


thanks again

[CJ_London]

I missed a quote

should be

rst.fields("[Service tag#]")

if you didn't have spaces and non alphanumeric characters, it would just be rst!Servicetag

where would the reference rst.fields([Service tag#]") be used? at the end of the url string?

yes - duly corrected

[Feddy]

ok, that got it!

works great, i increased the delay to 6 seconds.

Final Code:

Option Compare Database

Function waitFor(NumberOfSeconds As Variant)
Dim start As Variant
    
    start = Timer
    Do While Timer < start + NumberOfSeconds
        DoEvents
    Loop
 
End Function


Private Sub Command0_Click()
Dim rst As dao.Recordset


Set rst = CurrentDb.openrecordset("SELECT * From data", dbOpenSnapshot)
While Not rst.EOF
    Application.FollowHyperlink Address:="https://www.dell.com/support/Assets-Online/us/en/mycodehere/#/product-list?servicetag=" & rst.Fields("[Service tag#]")
    waitFor 6
    rst.MoveNext
Wend


End Sub

[CJ_London]

glad you got it working