Forms in Access 2007

40 pts.
Tags:
Access 2007
Access forms
Microsoft Access
SQL
Visual Basic
I am using a form to upload data from the form into a database. I have a submit button on my form that upon clicking, needs to input all the users data into the correct slots in the database but I have no clue on how to do this, or where to get started. How should I go about solving this problem?



Software/Hardware used:
Microsoft Access 2007
ASKED: September 25, 2009  7:55 PM
UPDATED: January 28, 2014  12:16 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are multiple ways to solve your issue. If I understood correctly you want to insert records to a table based on click event of a button.

  1. First design a table with fields you want to capture using this form.
  2. Than design the unbound form where textbox are not linked to any table.
  3. Than write append query.
  4. On submit button, run the append query.

I have created file for example, but there is not option to upload the same.

Regards,
Sarfaraz Ahmed

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Is there a reason that you do not want a bound form?
    1,740 pointsBadges:
    report
  • Jenkinac
    I am using bounded text boxes because the tables that I want to drop the information in are already created. I just have no idea as to how to write the VB code to get the information to go to the directed table upon the click of the button.
    40 pointsBadges:
    report
  • Jwcurrier
    I think you are looking for something like the following vb code as an event handler. Private Sub Command6_Click() On Error GoTo Err_Command6_Click Dim sqlStmt As String sqlStmt = "insert into [yourtable] (field1, field2) select (" + Me.Text2.Text + ", " + Me.Text4.Text + ");" DoCmd.RunSQL sqlStmt Exit_Command6_Click: Exit Sub Err_Command6_Click: MsgBox Err.Description Resume Exit_Command6_Click End Sub
    30 pointsBadges:
    report
  • Jenkinac
    so if i have several different events that i need to occur upon clicking the button, i have to have an event handler for each? here is a snippet of one of the text boxes that need to be inserted into the database: Private Sub StudLname_Click() Dim StudLname As String StudFname = StudFname.Text If StudLname = "" Then MsgBox ("Please enter your last name") Else INSERT INTO End Sub ___________ is this how that part is suppose to be done? if so how do i define the table that the information should go to...like in the INSERT INTO statement?
    40 pointsBadges:
    report
  • Randym
    Jenkinac, Your explanation of why not use a bound form doesn't make sense to me. In case you don't understand the concept of a bound form, it is one where the form's record source is set to a table or query that exists in the Access database. Each object's control source is then bound to it's corresponding column in the table. Now the data manipulation (insert, update, delete) is automatically handled by the form. You do not need code to insert into the table or make updates, etc. Plus you can use the built in record navigation buttons to scroll through the records. If you want to make sure some fields are filled in before insert or update, you could use the form's before update event to check those fields and if anything is missing, use the cancel event method (docmd.cancelevent) to stop the update from happening. Now with that being said, if you still want to handle the data manipulation manually, your code as you show should be in the Submit button like you stated earlier. That one on_click event of the Submit button can check all of the fields and if all is ok, insert into the table.
    1,740 pointsBadges:
    report
  • Jenkinac
    About the bounded forms...I thought that the concept was as you explained. It will simply be placed in the table with the event of the submit button being pressed. When I asked my supervisor if this was the case, he simply said that my code would have a series of insert into's for each event. There's different tables that are connected together by primary keys and foreign keys, so I don't know if that would be an issue. For each bounded box my code looks similiar to this: Dim dbTheDB As Database Dim rsTheRS As Recordset Set dbTheDB = OpenDatabase("Internship.mdb") Private Sub CompAddress_Click() Dim companyAddress As String Set rsTheRS = dbTheDB.OpenRecordset("dbo_Companies", Dynaset) companyAddress = CompAddress.Text dbTheDB.Execute ("INSERT INTO dbo_Companies (CompAddress) VALUES (companyAddress)") End Sub ______ I'm not even sure if that works at all because this is my very first time coding in vb using access forms. Thanks alot for your patience and help!!!
    40 pointsBadges:
    report
  • Randym
    Ok, I didn't realize that you were inserting into different tables. I don't know if you are trying to manage all the tables in one unbound form or if the code you are showing is being done in the bound form. If the code is in a bound form to the same table as that code, then you don't need to do the code. The bound form will take care of everything for you . You don't need a button to submit. The record, whether a new record or an updated record, will be commited to the table automatically when you leave the form or move to another record. If you are trying manage all the tables in an unbound form, typically, you would have a separate form for each "master" table instead of trying to manage all the tables in one form. Then the form can be bound to the table and life is much easier. So if you have a company table. You would have a company form that would manage that table. Then for the student table, you would have the student form manage that table. You would set up the company before you would try to enter a student who is an intern with that company. I think this would be the better way to go. But the way you are showing, would work too for an unbound form. You would need to do some checking to make sure the record doesn't exist or you will get primary key errors, etc.
    1,740 pointsBadges:
    report
  • MarvinM80

    Hi Jenkinac,

    I have worked with forms that do similar things. I use both bound and unbound forms. To help clear this up, we all need to know a few basic things about your database. Could you please tell us:

    1. What data is being entered on the form?

    2. What table is the form bound to?

    3. Which of the controls on the form are bound to what fields in that table?

    4. What tables do the other data elements need to go to?

    5. What is the key that connects the tables? (like a record number or ID)

    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following