Linking Access data to Excel textbox?

[casinokid]

I have a userform in Excel that has some textboxes that are used for Name,address, city, state, etc... I also have a Access database with a list of names and addresses. I would like to be able to type a persons name in textbox1 on my userform and if that name exists in the database, use the data from the database to fill in the remaining fields of my userform. I know how to link Excel and Access but am not sure how to search the database for the name and fill the rest of the textboxes. Can someone help me out with this one?

 

[zilpher]

I'd use an ADO query of the access database, triggered by a commandbutton.

to do that you#ll need some code, this might help (NB no error trapping included here):

'set a reference to Microsoft ActiveX Data Objects 2.x Library 
'(mine is 2.7 yours might not be, no matter)

Dim dbConn As ADODB.Connection 'we'll need a database connection object
Dim dbRS As ADODB.Recordset ' and a recordset
Dim strSQL As String ' we'll need some SQL
Dim strConn As String ' we'll need a connection string

Set dbConn = New ADODB.Connection 'create a new db connection object

strConn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\s.mdb;" 
'when we open the database, we'll need a connection string (in this case my db is called s.mdb and it's on the root of c:\

dbConn.Open strConn 'open the database with the connection string

strSQL = "select * from employees where emp_id = 2" 'the sql we'll use

Set dbRS = dbConn.Execute(strSQL) ' create a recordset and fill it with th results of the sql

If dbRS.EOF And dbRS.BOF Then msgbox "No records returned" 'if the recordset is at the beginning and end of file, it's empty so nothing was returned

dbrs.Close 'tidy up

dbconn.close

set dbrs = nothing

set dbconn = nothing

	
	
	
	
	
	



	
	

		
more info on connection strings is available here http://www.able-consulting.com/ADO_Conn.htm

HTH