We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
5 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
HELP
Posted on 2000-03-10
I know am asking a lot! But here's the deal!.. I have a form with like 10 text boxes. How can I save whatever is inside the text book into a table in ACCESS using a click of a button or any other way!
Please I need the answer before 16th of March!
Thanks
Please I need the answer before 16th of March!
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
-
2
-
2
11 Comments
Do you have to create the Access table pogramatically or can we assume it already exists?
If it already exists.. its kinda easy to do.. Overview steps:
1.. Add a Data Control to your form. In the DataBaseName Property place the File name of your Access database.. eg. C:\pathname\NWind.MDB (the property page has a browse feature).. and then in the Recordsource property place the table you want to use (if the Databasename is already specified, a dropdown list of tables will appear.
2. Add 10 Text boxes. In each Textbox, change its DataSource property to read Data1 (there is a dropdown once the Data control has been defined).. and change the DataField property to the Table Field you want to use in this Textbox. (Tip.. also change the Textbox.Text property to the field name it will make the next step easier.
3. Add 10 Labels and align them next to the Textboxes so that the user will know what each Textbox is for.
Your very basic data update program is now complete.. <smile>. Just type into a textbox, and your change is immediately recorded in the Database.
If it already exists.. its kinda easy to do.. Overview steps:
1.. Add a Data Control to your form. In the DataBaseName Property place the File name of your Access database.. eg. C:\pathname\NWind.MDB (the property page has a browse feature).. and then in the Recordsource property place the table you want to use (if the Databasename is already specified, a dropdown list of tables will appear.
2. Add 10 Text boxes. In each Textbox, change its DataSource property to read Data1 (there is a dropdown once the Data control has been defined).. and change the DataField property to the Table Field you want to use in this Textbox. (Tip.. also change the Textbox.Text property to the field name it will make the next step easier.
3. Add 10 Labels and align them next to the Textboxes so that the user will know what each Textbox is for.
Your very basic data update program is now complete.. <smile>. Just type into a textbox, and your change is immediately recorded in the Database.
Ok. Here:
In your project, add the reference to the MS ActiveX Data Objects Lib.
Now, do this:
Dim objCommand As ADODB.Command
On Error GoTo Error_Handler
' make sure vntError isn't holding anything
vntError = ""
Set objConnection = New ADODB.Connection
Set objCommand = New ADODB.Command
The following code makes a connection to the database through a DSN. However, this is not required, and I suggest using a DSNless connection whenever possible (using connecting string: "driver={Microsoft Access Driver (*.mdb)};dbq=C:\path\to\da
' open a connection to the datasource
With objConnection
.ConnectionString = "DSN=MyDSN"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open
End With
Once a connection is open, the new record is added to the database through a simple INSERT statement. Any errors that arise in the routine are handled in the Error_Handler: section. This section takes the description, number, and source of the error and puts it into the vntError variable. This allows the client to check the variable to see if the routine succeeded or failed.
' add the contents of the txt ctrl.
With objCommand
Set .ActiveConnection = objConnection
.CommandText = "INSERT INTO MyTable(Fieldname) VALUES('" &
txtCtrl.text & ");"
.CommandType = adCmdText
.Execute
End With
Exit_Handler:
'clean up
Set objCommand = Nothing
If objConnection.State = adStateOpen Then objConnection.Close
Set objConnection = Nothing
Exit Sub
Error_Handler:
vntError = Err.Description & " (" & Err.Number & " - " & Err.Source & ")"
Resume Exit_Handler
That will add the text in the txtCtrl.text into the Fieldname field in the Authors table of the database that the MyDSN points to.
In your project, add the reference to the MS ActiveX Data Objects Lib.
Now, do this:
Dim objCommand As ADODB.Command
On Error GoTo Error_Handler
' make sure vntError isn't holding anything
vntError = ""
Set objConnection = New ADODB.Connection
Set objCommand = New ADODB.Command
The following code makes a connection to the database through a DSN. However, this is not required, and I suggest using a DSNless connection whenever possible (using connecting string: "driver={Microsoft Access Driver (*.mdb)};dbq=C:\path\to\da
' open a connection to the datasource
With objConnection
.ConnectionString = "DSN=MyDSN"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open
End With
Once a connection is open, the new record is added to the database through a simple INSERT statement. Any errors that arise in the routine are handled in the Error_Handler: section. This section takes the description, number, and source of the error and puts it into the vntError variable. This allows the client to check the variable to see if the routine succeeded or failed.
' add the contents of the txt ctrl.
With objCommand
Set .ActiveConnection = objConnection
.CommandText = "INSERT INTO MyTable(Fieldname) VALUES('" &
txtCtrl.text & ");"
.CommandType = adCmdText
.Execute
End With
Exit_Handler:
'clean up
Set objCommand = Nothing
If objConnection.State = adStateOpen Then objConnection.Close
Set objConnection = Nothing
Exit Sub
Error_Handler:
vntError = Err.Description & " (" & Err.Number & " - " & Err.Source & ")"
Resume Exit_Handler
That will add the text in the txtCtrl.text into the Fieldname field in the Authors table of the database that the MyDSN points to.
Coolass.. how can you lock this question with an answer like that?
Locking the question presumes that this is the best and most definitive answer to the question.. and that no other expert need comment on any other solution. In my opinion, you are bold in concluding this with your answer.. and for the following reasons.
1. The scope of this question is not entirely clear. Both you and I are ASSUMING that the Access table he wants to port to, already exists.. but what if it doesn't?.. Code would have to be added to create it. The questioner should have been more specific.
2. The questioner indicates that there are 10 Textboxs, your solution only provides for one. Additionally, do the 10 Textboxs represent one record, or are they to be treated as 10 separate records? Once again the scope of the question is unclear. Your example would create a table record for each textbox.. mine treats the 10 boxes as a recordset.. both of us are assuming.. (but one of us is right.. LOL).
3. Correct me if I am wrong, but isn't the ADO defautlt LockType Read Only?
Anyhow.. I don't want to get too anal here. My only point is this.. give others a chance to comment before you lock an answer. Believe me your comments are well appreciated.. and well deserved of being posted here.. but let's give others a chance to comment.. and the questioner the opportunity to fully define his/her requirement before locking da sucka down.. <smile>.
Locking the question presumes that this is the best and most definitive answer to the question.. and that no other expert need comment on any other solution. In my opinion, you are bold in concluding this with your answer.. and for the following reasons.
1. The scope of this question is not entirely clear. Both you and I are ASSUMING that the Access table he wants to port to, already exists.. but what if it doesn't?.. Code would have to be added to create it. The questioner should have been more specific.
2. The questioner indicates that there are 10 Textboxs, your solution only provides for one. Additionally, do the 10 Textboxs represent one record, or are they to be treated as 10 separate records? Once again the scope of the question is unclear. Your example would create a table record for each textbox.. mine treats the 10 boxes as a recordset.. both of us are assuming.. (but one of us is right.. LOL).
3. Correct me if I am wrong, but isn't the ADO defautlt LockType Read Only?
Anyhow.. I don't want to get too anal here. My only point is this.. give others a chance to comment before you lock an answer. Believe me your comments are well appreciated.. and well deserved of being posted here.. but let's give others a chance to comment.. and the questioner the opportunity to fully define his/her requirement before locking da sucka down.. <smile>.
I felt my answer was good enough, considering he is only offering 40 points. And yes, the ADO default lock is read only, that's why I said:
..Mode = adModeReadWrite
By posting this code he could use it to easily write his own code to do all 10 text boxes...
He didn't ask how to create the Access database, if he had I would have told him how to. His question was:
"How can I save whatever is inside the text book into a table in ACCESS using a click of a button or any other way!"
And that's what I answered. To say that I should wait for people to post a bunch of comments before I give an answer which I beleive is correct is silly.
..Mode = adModeReadWrite
By posting this code he could use it to easily write his own code to do all 10 text boxes...
He didn't ask how to create the Access database, if he had I would have told him how to. His question was:
"How can I save whatever is inside the text book into a table in ACCESS using a click of a button or any other way!"
And that's what I answered. To say that I should wait for people to post a bunch of comments before I give an answer which I beleive is correct is silly.
Bunch of comments.. Is Silly?.. Isn't the questioner the judge of that?..
By locking the question down.. you severely limit the questioner's ability to get answers/comments from other experts. If this question were still open, chances are good there would be more comments here than our paltry two.
And why would other comments be such a bother to you? If your answer is the best one (which it could be) then the questioner will accord you his points accordingly.. or other experts would pass over responding to the question in the knowledge that your answer is correct and definitive. I mean, what's wrong with a little competition.. and may the best answer win?
Ultimately, the questioner is the judge of all this. It is now his/her choice.. to accept your answer (or reject it).. accept my answer (or reject it).. or to reopen the question for further comment. The choice is entirely his/hers.. <smile>.
Good chatting with ya..
Bill.
By locking the question down.. you severely limit the questioner's ability to get answers/comments from other experts. If this question were still open, chances are good there would be more comments here than our paltry two.
And why would other comments be such a bother to you? If your answer is the best one (which it could be) then the questioner will accord you his points accordingly.. or other experts would pass over responding to the question in the knowledge that your answer is correct and definitive. I mean, what's wrong with a little competition.. and may the best answer win?
Ultimately, the questioner is the judge of all this. It is now his/her choice.. to accept your answer (or reject it).. accept my answer (or reject it).. or to reopen the question for further comment. The choice is entirely his/hers.. <smile>.
Good chatting with ya..
Bill.
Oh.. and by the way Coolass.. I missed seeing the Mode statement.. <d*mn bifocals and 14 inch screen.. LOL>.. Thank YOU for pointing it out.. <smile>.
Bill.
Bill.
Wsh2 I already know how to get information from an access database plus am not getting information from there I am putting data into it. The data which I am giving Access is from
Excel.
coolAss I have tried your way but it keeps giving me an error when I try to run the program "Invalid Outside Procedure". Shall I insert the code you gave me on Form load or in general or what exactly?????.....
By the way I already have a database and the fields already exist! Like i said whatever is inside the text book into a table in ACCESS using a click of a button or any other way!
Excel.
coolAss I have tried your way but it keeps giving me an error when I try to run the program "Invalid Outside Procedure". Shall I insert the code you gave me on Form load or in general or what exactly?????.....
By the way I already have a database and the fields already exist! Like i said whatever is inside the text book into a table in ACCESS using a click of a button or any other way!
As CoolAss has covered the ADO methodology (which IS the current Microsoft standard.. <smile>), I will resort to covering the old, more plebian DAO Method.
Add a reference in your project to "Microsoft DAO 3.51 Object Library"
<----- Code Begin ----->
Public Sub AddRecord2Access()
Dim wsNwind As Workspace
Set wsNwind = CreateWorkspace("", "admin", "", dbUseJet)
Dim dbNwind As Database
Set dbNwind = wsNwind.OpenDatabase("c:\p
Dim rsNwind As Recordset
Set rsNwind = dbNwind.OpenRecordset("You
With rsNwind
.AddNew
!FieldName1 = Text1.Text
!FieldName2 = Text2.Text
!Fieldname3 = Text3.Text
!Fieldname4 = Text4.Text
!Fieldname5 = Text5.Text
!Fieldname6 = Text6.Text
!Fieldname7 = Text7.Text
!Fieldname8 = Text8.Text
!Fieldname9 = Text9.Text
!Fieldname10 = Text10.Text
.Update
End With
rsNwind.Close
dbNwind.Close
wsNwind.Close
<----- Code End ----->
Perhaps you are right Coolass.. for 40 points its just not worth it.. <sigh>.
Oh well.. <smile>.
Add a reference in your project to "Microsoft DAO 3.51 Object Library"
<----- Code Begin ----->
Public Sub AddRecord2Access()
Dim wsNwind As Workspace
Set wsNwind = CreateWorkspace("", "admin", "", dbUseJet)
Dim dbNwind As Database
Set dbNwind = wsNwind.OpenDatabase("c:\p
Dim rsNwind As Recordset
Set rsNwind = dbNwind.OpenRecordset("You
With rsNwind
.AddNew
!FieldName1 = Text1.Text
!FieldName2 = Text2.Text
!Fieldname3 = Text3.Text
!Fieldname4 = Text4.Text
!Fieldname5 = Text5.Text
!Fieldname6 = Text6.Text
!Fieldname7 = Text7.Text
!Fieldname8 = Text8.Text
!Fieldname9 = Text9.Text
!Fieldname10 = Text10.Text
.Update
End With
rsNwind.Close
dbNwind.Close
wsNwind.Close
<----- Code End ----->
Perhaps you are right Coolass.. for 40 points its just not worth it.. <sigh>.
Oh well.. <smile>.
wsh2 Nothing is in my database whenever i enter something in the field and close the VB application there's nothing added to the database?!?!
Add a debug breakpoint (or a Msgbox) to the code that I provided to make sure that you are cycling through the .addnew / .update code.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…
Suggested Courses
Course of the Month5 days, 6 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.