Importing Data From A Microsoft Access Database Into A Data Entry Form Dropdown List. S’abonner

0
Avatar
Legacy Poster

Hello,

 

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
labelPath="C:\Users\LucasVL7\Documents\Data.mdb"


'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.
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
rs.movefirst
do while not rs.EOF
value = value & rs.fields("CollumnData") & vbcr
rs.MoveNext 
loop


'Close the connection
rs.Close
Set rs = Nothing
 

 

 

Vous devez vous connecter pour laisser un commentaire.