Populate userform field from access database

[hypuk]

Hi there

I waswondering if you can point me in the right direction.

I have a userform in Excel and want to poplate a couple of fields with data from a Access Database.

Basicaly I have a Account number field, I want to enter a account number and click on a search button and for the correct information attached to that account number be populated in the First Name and Surname field only. If no data is found then a message box appear stating no information found.

A link to a screenshot of my form is below
http://imageshack.us/photo/my-images/851/formg.gif/

I use Office 2007.

Any help would be much appreciated.

Mark

 

[Trevor G]

Mark have you tried any code so far?

I use this code to fill a workbook from an access database using a query in the database and refering to a parameter value, this could be adapted.

So the data could gointo a worksheet then populate the form from the worksheet once you click your search button.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RunParameterQuery()<br><br><SPAN style="color:#007F00">'Step 1: Declare your variables</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyDatabase <SPAN style="color:#00007F">As</SPAN> DAO.Database<br><SPAN style="color:#00007F">Dim</SPAN> MyQueryDef <SPAN style="color:#00007F">As</SPAN> DAO.QueryDef<br><SPAN style="color:#00007F">Dim</SPAN> MyRecordset <SPAN style="color:#00007F">As</SPAN> DAO.Recordset<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#007F00">'Step 2: Identify the database and query</SPAN><br><SPAN style="color:#00007F">Set</SPAN> MyDatabase = DBEngine.OpenDatabase("M:\Access Files\Test ME Today.mdb")<br><SPAN style="color:#00007F">Set</SPAN> MyQueryDef = MyDatabase.QueryDefs("qryGoToExcel") <SPAN style="color:#007F00">'Query name in the database</SPAN><br><br><SPAN style="color:#007F00">'Step 3: Define the Parameters</SPAN><br><SPAN style="color:#00007F">With</SPAN> MyQueryDef<br>.Parameters("[Enter Department]") = Range("D3").Value <SPAN style="color:#007F00">'From parameter field in access</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#007F00">'Step 4: Open the query</SPAN><br><SPAN style="color:#00007F">Set</SPAN> MyRecordset = MyQueryDef.OpenRecordset<br><br><SPAN style="color:#007F00">'Step 5: Clear previous contents</SPAN><br>Sheets("Sheet1").Select<br>ActiveSheet.Range("A6:K10000").ClearContents<br><br><SPAN style="color:#007F00">'Step 6: Copy the recordset to Excel</SPAN><br>ActiveSheet.Range("A7").CopyFromRecordset MyRecordset<br><br><SPAN style="color:#007F00">'Step 7: Add column heading names to the spreadsheet</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> MyRecordset.Fields.Count<br>ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name<br><SPAN style="color:#00007F">Next</SPAN> i<br>Cells.EntireColumn.AutoFit<br><br>MsgBox "Query has been successful", vbInformation, "Sample"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>