In Distress
New Member
- Joined
- Mar 12, 2003
- Messages
- 37
Morning all,
I've got a small problem that I'm hoping people can help me out with.
I've been given the task of creating an excel tool capable of collecting certain information about attendees of an annual event, I thought this would be a great chance to tap into the power of ADO by using a VBA Userform front end that connects and updates data on an Access database. The problem being - seems I went a little over my head on promising to deliver this one is a very small time frame.
The scenario:
I have a 3 userforms named 'Opening, Questions, & Details'. The basic operation of this front end is to drag up all the details on the person that the user selects on the 'Opening' Userform, ask the person a few questions which are selected by using radio buttons on the 'Questions' form and also update any person details on the 'Details' userform. Then I want to send all this information back to access database to update the record.
I have managed to figure out how to drag up all the information on a person from the database. Basically, when the user selects a name from a combobox on the 'Opening' userform, I have a small script that locates that person on the database, then copies all the columns of data for that person into a seperate sheet called 'Sheet3'. There are approx. 23 columns of data for each person in the database.
The code I use to grab all the persons details is as follows:
The problem I can't seem to fix is how to update the record on the database. I have tried stripping down the Plain text SQL script, but I'm still getting an error of a 'Type mismatch in criteria expression".
To give you details, the database name is AGM_2006.mdb. The table in the database is called 'AGMtable'. The fields contained in the table are as follows: 'DB_ID' (This is the primary Key). 'DB_Name', 'DB_Parnter', 'DB_Position', 'DB_Company', 'DB_Address1', 'DB_Address2', 'DB_Suburb', 'DB_State', 'DB_Postcode', 'DB_Phone1', 'DB_Phone2', 'DB_Phone3', 'DB_Email', 'DB_Fax', 'DB_PrefFormat', 'DB_Type', 'DB_Q1', 'DB_Q2A', 'DB_Q2B', 'DB_Q3', 'DB_Q4A', 'DB_Q4B'.
To help clarify - Say a user selects Person "C A Problem" from the Combobox on the 'Opening form'. A code snippet establishes a connection to the database, finds "C A Problem" and returns all the columns of data mentioned above into a sheet called "Sheet3" and closes the connection. This data is then used to populate values on the 'Details' Userform. Once the user has made changes and selected answers on the 'Details' and 'Questions' userforms respectively, then new values are written to "Sheet3" into their respective columns, replacing the values that were retrieved from the database. When the user clicks on the finish button, I want to write all the information on sheet3 back to the record of "C A Problem" on the database.
I apologise for the long winded post, but I had to give you all the details to hopefully prevent a back and forth of needed details. If someone can also tell me how to create a 'New Record' to the database as well, that would be great, because that's the last piece of the puzzle. I need to get this done today (I know, always over estimate timeframes, but I didn't), so anyone that can help me out A.S.A.P, just may save my skin.
Thanks in advance all.
I've got a small problem that I'm hoping people can help me out with.
I've been given the task of creating an excel tool capable of collecting certain information about attendees of an annual event, I thought this would be a great chance to tap into the power of ADO by using a VBA Userform front end that connects and updates data on an Access database. The problem being - seems I went a little over my head on promising to deliver this one is a very small time frame.
The scenario:
I have a 3 userforms named 'Opening, Questions, & Details'. The basic operation of this front end is to drag up all the details on the person that the user selects on the 'Opening' Userform, ask the person a few questions which are selected by using radio buttons on the 'Questions' form and also update any person details on the 'Details' userform. Then I want to send all this information back to access database to update the record.
I have managed to figure out how to drag up all the information on a person from the database. Basically, when the user selects a name from a combobox on the 'Opening' userform, I have a small script that locates that person on the database, then copies all the columns of data for that person into a seperate sheet called 'Sheet3'. There are approx. 23 columns of data for each person in the database.
The code I use to grab all the persons details is as follows:
Code:
Public Sub GetInviteesDetailsFromDB()
Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source = C:\Documents and Settings\HPUser\Desktop\AGM_2006.mdb;Persist Security Info=False"
Dim SQL
SQL = "SELECT DB_Name,DB_Partner,DB_Position,DB_Company,DB_Address1,DB_Address2,DB_Suburb,DB_State,DB_PostCode,DB_Phone1,DB_Phone2,DB_Phone3,DB_Email,DB_Fax,DB_PrefFormat,DB_Type,DB_ID FROM AGMtable WHERE DB_Name = '" & SelectedName & "'"
Dim Recordset As ADODB.Recordset
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1:E600").Select
With Selection
.ClearContents
End With
Sheet3.Range("A1").Select
Set Recordset = New ADODB.Recordset
Call Recordset.Open(SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
If Not Recordset.EOF Then
Call Sheet3.Range("e2").CopyFromRecordset(Recordset)
With Sheet3.Range("A1:C1")
.Value = Array("Name", "Company", "Phone No")
.Font.Bold = True
End With
Sheet3.UsedRange.EntireColumn.AutoFit
Else
Call MsgBox("Error: No Records returned.", vbCritical)
End If
End Sub
The problem I can't seem to fix is how to update the record on the database. I have tried stripping down the Plain text SQL script, but I'm still getting an error of a 'Type mismatch in criteria expression".
To give you details, the database name is AGM_2006.mdb. The table in the database is called 'AGMtable'. The fields contained in the table are as follows: 'DB_ID' (This is the primary Key). 'DB_Name', 'DB_Parnter', 'DB_Position', 'DB_Company', 'DB_Address1', 'DB_Address2', 'DB_Suburb', 'DB_State', 'DB_Postcode', 'DB_Phone1', 'DB_Phone2', 'DB_Phone3', 'DB_Email', 'DB_Fax', 'DB_PrefFormat', 'DB_Type', 'DB_Q1', 'DB_Q2A', 'DB_Q2B', 'DB_Q3', 'DB_Q4A', 'DB_Q4B'.
To help clarify - Say a user selects Person "C A Problem" from the Combobox on the 'Opening form'. A code snippet establishes a connection to the database, finds "C A Problem" and returns all the columns of data mentioned above into a sheet called "Sheet3" and closes the connection. This data is then used to populate values on the 'Details' Userform. Once the user has made changes and selected answers on the 'Details' and 'Questions' userforms respectively, then new values are written to "Sheet3" into their respective columns, replacing the values that were retrieved from the database. When the user clicks on the finish button, I want to write all the information on sheet3 back to the record of "C A Problem" on the database.
I apologise for the long winded post, but I had to give you all the details to hopefully prevent a back and forth of needed details. If someone can also tell me how to create a 'New Record' to the database as well, that would be great, because that's the last piece of the puzzle. I need to get this done today (I know, always over estimate timeframes, but I didn't), so anyone that can help me out A.S.A.P, just may save my skin.
Thanks in advance all.