Importing Data From A Microsoft Access Database Into A Data Entry Form Dropdown List. Follow

Legacy Poster



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!


The code.


'Setting up the variables
'These variables are used to connect to the database
dim objConn
dim strConn

'These variables are used to query the database
dim sqlStr
dim selection
dim rs

'The path to the Microsoft Access Database
dim labelPath

'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")
objConn.Open strConn

'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.

'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.