![]() |
#1
|
|||
|
|||
![]()
Hi!
I found this set of code to update an Access database using a Word form. It's for a school project where we HAVE to use nothing but Office to create a website, so...I know... Anyways, the original code is set to two buttons. - CommandButton1: Checks the form fields against what is in the database to make sure there are no duplicates - CommandButton2: If there are no duplicates, the user clicks this button to add their info to the database. NEED: I'd like to combine the two. - It needs to see if in database (verifying with email later on) - If so, give a simple message "Already On File" - If not, proceed to add the form fields - I'd also like to CLEAR the form fields on either of the above once verified and they are on the list, or after they sign up. - I'm also only using 2 fields ("FullName and Email"). Don't need all the other stuff or the Auto-Fill portion (part of something else in the tutorial). Thank you!!! ----------------------------------- Private Sub CommandButton1_Click() 'declare variables for new connection and recordset and declare variables Dim vConnection As New ADODB.Connection Dim vRecordSet As New ADODB.Recordset Dim vClientFName As String Dim vClientLName As String Dim vCompany As String Dim vAddress As String Dim vCity As String Dim vState As String Dim vZip As String Dim vPhone As String Dim vNotes As String 'provide connection string for data using Jet Provider for Access database vConnection.ConnectionString = "data source=c:\computorcompanion\Client_Info.mdb;" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" 'open connection vConnection.Open 'test connection state...this can later be commented out...but it helps 'let you know if the connection was successful when testing the code vConnectionState = vConnection.State If vConnectionState = 1 Then MsgBox "The connection to this database is working!", vbInformation Else MsgBox "You were unable to connect to the assigned database!", vbInformation End If 'set variable equal to whatever is entered into First & LastName form field 'so you can query the DB with this name to see if a record exists vClientFName = ActiveDocument.FormFields("bkClientFName").Result vClientLName = ActiveDocument.FormFields("bkClientLName").Result 'open a RecordSet with SQL query results...to see if first/last name matches a record vRecordSet.Open "SELECT * FROM ClientInfo WHERE ClientInfo!FName = " & _ Chr(34) & vClientFName & Chr(34) & "AND ClientInfo!LName = " & _ Chr(34) & vClientLName & Chr(34), vConnection, adOpenKeyset, adLockOptimistic 'if a match is found, display it to the user in a message box 'you'll get a match if you are NOT .EOF (end of file) With vRecordSet If Not .EOF Then vCorrectRecord = MsgBox("Is this the correct record?" & Chr(13) & Chr(13) & _ vRecordSet("FName") & " " & _ vRecordSet("LName") & ", " & _ vRecordSet("Address") & ", " & _ vRecordSet("City"), _ vbYesNo + vbInformation, "User Record") Else 'if you ARE *else* (are .EOF), that means no record was matched, tell the user MsgBox "No possible match was found." End If End With 'If the answer equals 6, it means they clicked Yes on the MsgBox accepting this match If vCorrectRecord = 6 Then 'if yes...set variables from DB fields (the name variables are already set above) vCompany = vRecordSet("Company") vAddress = vRecordSet("Address") vCity = vRecordSet("City") vState = vRecordSet("State") vZip = vRecordSet("Zip") vPhone = vRecordSet("Phone") vNotes = vRecordSet("Notes") 'set the form field's bookmarks to the results from the database field variables ActiveDocument.FormFields("bkCompany").Result = vCompany ActiveDocument.FormFields("bkAddress").Result = vAddress ActiveDocument.FormFields("bkCity").Result = vCity ActiveDocument.FormFields("bkState").Result = vState ActiveDocument.FormFields("bkZip").Result = vZip ActiveDocument.FormFields("bkPhone").Result = vPhone ActiveDocument.FormFields("bkNotes").Result = vNotes Else 'if not 6, then not Yes, so must be NO...remind user to update database! MsgBox "Since this is not the correct entry..." & Chr(13) & _ "be sure to fill out remaining form fields and click *Update* " & Chr(13) & _ "so this person will be added to the database." End If 'close objects vRecordSet.Close vConnection.Close 'clear object to free up memory Set vRecordSet = Nothing Set vConnection = Nothing End Sub --------------------------------------------------------------------- Private Sub CommandButton2_Click() 'declare new connection, recordset and variables Dim vConnection As New ADODB.Connection Dim vRecordSet As New ADODB.Recordset Dim vClientFName As String Dim vClientLName As String Dim vCompany As String Dim vAddress As String Dim vCity As String Dim vState As String Dim vZip As String Dim vPhone As String Dim vNotes As String 'provide same as previous connection string for data using Jet Provider for Access database vConnection.ConnectionString = "data source=c:\computorcompanion\Client_Info.mdb;" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" 'open connection vConnection.Open 'set variables from text enter into all the AutoForm field bookmark results vClientFName = ActiveDocument.FormFields("bkClientFName").Result vClientLName = ActiveDocument.FormFields("bkClientLName").Result vCompany = ActiveDocument.FormFields("bkCompany").Result vAddress = ActiveDocument.FormFields("bkAddress").Result vCity = ActiveDocument.FormFields("bkCity").Result vState = ActiveDocument.FormFields("bkState").Result vZip = ActiveDocument.FormFields("bkZip").Result vPhone = ActiveDocument.FormFields("bkPhone").Result vNotes = ActiveDocument.FormFields("bkNotes").Result 'Open a new version of the temporary RecordSet accessing the ClientInfo table in Database vRecordSet.Open "ClientInfo", vConnection, adOpenKeyset, adLockOptimistic vRecordSet.AddNew 'we can't enter "nothing", so only set variables with some data entered info the field If vClientFName <> "" Then vRecordSet!FName = vClientFName If vClientLName <> "" Then vRecordSet!LName = vClientLName If vCompany <> "" Then vRecordSet!Company = vCompany If vAddress <> "" Then vRecordSet!Address = vAddress If vCity <> "" Then vRecordSet!City = vCity If vState <> "" Then vRecordSet!State = vState If vZip <> "" Then vRecordSet!Zip = vZip If vPhone <> "" Then vRecordSet!Phone = vPhone If vNotes <> "" Then vRecordSet!Notes = vNotes 'update the RecordSet to the database, this adds your new client to the DB vRecordSet.Update 'advise the user the client has been added MsgBox vClientFName & " " & vClientLName & " has been added to your database." 'close objects vRecordSet.Close vConnection.Close 'clear object to free up memory Set vRecordSet = Nothing Set vConnection = Nothing End Sub |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
NISMOJim | Word | 9 | 06-23-2011 02:29 AM |
![]() |
WilfredoMolina | Office | 1 | 10-10-2010 06:47 AM |
Create a Custome Form and export data to Access | ashleybyrdnc | Office | 0 | 03-05-2010 09:41 AM |
Trouble updating a form | TomCaesar | Outlook | 0 | 11-29-2006 10:52 AM |
Microsoft Access - Form For Sales Analysis | kennyharrill | Office | 0 | 09-15-2005 08:49 AM |