Auto fill fields in web form from Access

J

Joshann

Registered User.
Local time
Today, 00:32
Joined
Mar 22, 2002
Messages
142
Does anyone know how to automatically fill in the fields on a web form from within Access? Here's what I have so far:

Code: 
Call Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE http://pageontheinternet.com", vbMaximizedFocus)

That opens the page that has the fields to be filled in on it fine (pageontheinternet.com is not the real name of the web page). But how do I fill in the fields from within Access? The data that needs to go into the fields is always the same, so it can be hard coded.
 
You need to use ASP or ASP.net

Build a Recordset, something like...

'your connection string
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source=" & server.mappath("MyDatabase.mdb")

set conn=server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.recordset")


conn.open strconnect

Dim strSQL
strSQL = "SELECT tbl_Members.Member_ID" & vbCrLf
strSQL = strSQL & " , tbl_Members.Company_Name" & vbCrLf
strSQL = strSQL & " FROM tbl_Members;"

rs.Open strSQL, conn

then add the values to form inputs like this:
<input type="text" name="company_name" value="<%=rs("Company_Name") %>" size="30">


...and of course, tidy up at the end

<%
rs.Close
set rs=nothing

conn.Close
set conn=nothing
%>
 
Could you explain a little bit about what this code is doing? It looks to me like it is getting the values to put in the web form from a table. The values I need to input do not come from a table; they are a single value and are always the same, so they can be hard coded. Given that, do I need to create a recordset? Let's say all I want to do is input a name in a field on the form called company name, and a city in a field called city, what would I need to do? Let's say company name = "ABC Company" and city = "Dallas". How would the code you gave me be changed?

Thanks for your help. I really appreciate it.
 
Sorry. I thought the values were being pulled from the DB.

No ASP needed to hardcode, just put

<input type="text" name="company_name" value="ABC Company">

<input type="text" name="city" value="Dallas">
 
O.K. Am I supposed to put that statement in VBA? If so, I tried it, and it doesn't like it.
 
Hi Joshann, what do you mean by "web form"?, I took it you had built a HTML page, put in a form tag and some form controls? The code I gave you is for a HTML page.

Access has no "web forms" that you can use on the web. There are Data Access Pages but they are only suitable for tightly controlled intranets, not the internet

If you want to interact with an Access datbase on the web then you will need ASP.
 
Last edited:
You can try setting focus to the browser form - you may need an API to do this. You can then try using SendKeys() to "type" in the form.
 
Sorry I wasn't more clear. Here's what I'm trying to do. I have an Access database. I am opening an external web page that I don't own or have any control over from within Access using the following code in a module:

Call Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE https://nameofwebsite.html", vbMaximizedFocus)

Once the web page opens, I want to fill in the form on the web page from within Access.

I tried setting the focus to the Browser and using SendKeys() as Pat suggested, but it only works some of the time. The problem is that sometimes the web page loads very quickly, so the SendKeys() command is getting executed after the page loaded, but sometimes the web page loads slowly, so the SendKeys() command is getting executed before the page is loaded which causes wierd things to happen.

Is there some code or API or something to test and make sure the web page is fully loaded before executing the SendKeys command?
 
Look up DoEvents in help. It lets you yield your time slice to the CPU until it is finished with other tasks. There are caveats though so read carefully.
 
DoEvents appears to be working. Thanks very much for your help.
 
Hi there,

I am trying to fill in a webpage with data from access using sendkeys, I see that you managed to get it working could you please post the code you used.

Thanks
Geoff
 
geoffcodd said:
Hi there,

I am trying to fill in a webpage with data from access using sendkeys, I see that you managed to get it working could you please post the code you used.

Thanks
Geoff
Click to expand...

It turns out that DoEvents does not very well by itself, but it does work most of the time with the extra code I added (see below). When it doesn't work, it's the same problem that I mentioned before where the keys get sent to the page before the page is fully loaded. What we really need is something that actually tests to see if a web page has been fully loaded.

Here's what I did:
Code: 
    Dim strLink As String
    Dim blnOpening As Boolean
    Dim strWebTitle As String
    On Error GoTo err_handler
    
    strLink = "http://NameOfWebsite.html"
    strLink = "C:\Program Files\Internet Explorer\IEXPLORE.EXE " & strLink
    strWebTitle = "Title of Website that appears on the Title bar when site is open"
            
    Call Shell(strLink, vbMaximizedFocus)
    
    'Here's the code I added to go with DoEvents
    blnOpening = True
    Do While blnOpening = True
        blnOpening = False
        AppActivate strWebTitle, True
    Loop
    
    DoEvents
    
    'I read some where that this user defined function is better than using SendKeys
    Call fSendKeys("DataForFirstField", True)
    Call fSendKeys("{Tab}", True)
    Call fSendKeys("DataForSecondField", True)
    Call fSendKeys("{Tab}", True)
    Call fSendKeys("DataForThirdField", True)
    Call fSendKeys("{Tab}", True)
    Call fSendKeys("{ENTER}", True)
    Exit Sub
    
err_handler:
    If Err.NUMBER = 5 Then 'Invalid procedure call or argument
        blnOpening = True
    End If
    Resume Next
    
End Sub

Here's the user defined send keys function (put it in a module):
Code: 
' Declare Type for API call:
      Private Type OSVERSIONINFO
        dwOSVersionInfoSize As Long
        dwMajorVersion As Long
        dwMinorVersion As Long
        dwBuildNumber As Long
        dwPlatformId As Long
        szCSDVersion As String * 128   '  Maintenance string for PSS usage
      End Type

      ' API declarations:
      Private Declare Function GetVersionEx Lib "kernel32" _
         Alias "GetVersionExA" _
         (lpVersionInformation As OSVERSIONINFO) As Long

      Private Declare Sub keybd_event Lib "user32" _
         (ByVal bVk As Byte, _
          ByVal bScan As Byte, _
          ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

      Private Declare Function GetKeyboardState Lib "user32" _
         (pbKeyState As Byte) As Long

      Private Declare Function SetKeyboardState Lib "user32" _
         (lppbKeyState As Byte) As Long

      ' Constant declarations:
      Const VK_NUMLOCK = &H90
      Const VK_SCROLL = &H91
      Const VK_CAPITAL = &H14
      Const KEYEVENTF_EXTENDEDKEY = &H1
      Const KEYEVENTF_KEYUP = &H2
      Const VER_PLATFORM_WIN32_NT = 2
      Const VER_PLATFORM_WIN32_WINDOWS = 1

Function IsCapsLockOn() As Boolean
        Dim o As OSVERSIONINFO

        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)
        IsCapsLockOn = keys(VK_CAPITAL)
End Function

Sub ToggleCapsLock()
        Dim o As OSVERSIONINFO

        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)

        If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then  '=====Win95
        'Toggle capslock
            keys(VK_CAPITAL) = Abs(Not keys(VK_CAPITAL))
            SetKeyboardState keys(0)
        ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then   '=====WinNT
          'Simulate Key Press>
            keybd_event VK_CAPITAL, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
          'Simulate Key Release
            keybd_event VK_CAPITAL, &H45, KEYEVENTF_EXTENDEDKEY _
               Or KEYEVENTF_KEYUP, 0
        End If
End Sub

Function IsNumLockOn() As Boolean
        Dim o As OSVERSIONINFO
        
        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)
        IsNumLockOn = keys(VK_NUMLOCK)
End Function

Sub ToggleNumLock()
        Dim o As OSVERSIONINFO
                
        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)

          If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then  '=====Win95
                keys(VK_NUMLOCK) = Abs(Not keys(VK_NUMLOCK))
                SetKeyboardState keys(0)
          ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then   '=====WinNT
          'Simulate Key Press
            keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
          'Simulate Key Release
            keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY _
               Or KEYEVENTF_KEYUP, 0
          End If
        
End Sub

Function IsScrollLockOn()
        Dim o As OSVERSIONINFO
        
        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)
        IsScrollLockOn = keys(VK_SCROLL)
End Function

Sub ToggleScrollLock()
        Dim o As OSVERSIONINFO
        
        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx o
        Dim keys(0 To 255) As Byte
        GetKeyboardState keys(0)
        If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then  '=====Win95
            keys(VK_SCROLL) = Abs(Not keys(VK_SCROLL))
            SetKeyboardState keys(0)
        ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then   '=====WinNT
            'Simulate Key Press
            keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
            'Simulate Key Release
            keybd_event VK_SCROLL, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0
        End If
End Sub

Sub mySendKeys(sKeys As String, Optional bWait As Boolean = False)
Dim bNumLockState As Boolean
Dim bCapsLockState As Boolean
Dim bScrollLockState As Boolean
    bNumLockState = IsNumLockOn()
    bCapsLockState = IsCapsLockOn()
    bScrollLockState = IsScrollLockOn()
    SendKeys sKeys, bWait
    If IsNumLockOn() <> bNumLockState Then
        ToggleNumLock
    End If
    If IsCapsLockOn() <> bCapsLockState Then
        ToggleCapsLock
    End If
    If IsScrollLockOn() <> bScrollLockState Then
        ToggleScrollLock
    End If
End Sub

Function fSendKeys(sKeys As String, Optional bWait As Boolean = False)
' Function to make it callable from macros
    mySendKeys sKeys, bWait
End Function
 
You must log in or register to reply here.

Similar threads

S
Problem to run Access database
Replies
3
Views
259
mike60smart
mike60smart
B
Automating browser operations from MS Access
Replies
3
Views
411
Bennet
B
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
172
Trevor G
Trevor G
S
Solved Create new child record with modal dialog?
Replies
15
Views
423
Sheridan
S
E
Solved DLookup value display with a minus sign
Replies
16
Views
550
Mark_
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom