Linking Access data to Excel textbox?

casinokid

New Member
Joined
Jan 14, 2003
Messages
31
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

zilpher

Active Member
Joined
Mar 27, 2003
Messages
442
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):

Rich (BB code):
'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

	
	
	
	
	
	


Rich (BB code):
more info on connection strings is available here http://www.able-consulting.com/ADO_Conn.htm

HTH
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top