Importing Data From A Microsoft Access Database Into A Data Entry Form Dropdown List. Follow
Here is the code which makes it possible to import data from a Microsoft Access Database into a Data Entry Form Dropdown List.
How to add the code to the Dropdown List.
Go to the Data Entry Form and drag a Dropdown list into it. Rightclick on the Dropdown list and choose Properties. In the just opened window Dropdown List Control Properties, select Dropdown List Control > List Items and change the Source to Visual Basic Script. Now click on the button Edit VB Script... and the window Script Assistant will pop up. Choose the option OnFillList in the left tab which will show you some example code. Delete this example code. The following code has to be pasted in the textbox where the example code used to be. CHANGE THE UNDERLINED VALUES TO YOUR OWN VALUES!
'Setting up the variables
'These variables are used to connect to the database
'These variables are used to query the database
'The path to the Microsoft Access Database
'Set this value to nothing to start, otherwise Sample Text will be in the dropdown list
value = ""
'Connect to the Database
strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & labelPath & name & ";Extended Properties=''"
Set objConn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'This is the SQL statement which selects the values of the collumn called 'CollumnData' from the 'DATA' table. In this example I use the SQL statement 'SQL DISTINCT' which prevents SQL from selecting duplicate values.
sqlStr = "SELECT DISTINCT CollumnData FROM DATA"
'Execute the SQL Statement
rs.Open sqlStr, objConn, adCmdText
'Go to the first record, read the cells until its reaches the end of the set
do while not rs.EOF
value = value & rs.fields("CollumnData") & vbcr
'Close the connection
Set rs = Nothing
Please sign in to leave a comment.