Create Button to Submit Data

[mnewton367]

Alright, hello everyone. I'm Active Duty and looking to create a Access Database to better organize some of the things for my shop.

Basically I have a form where information is entered to multiple different areas via dates, combo-boxes, text boxes, etc. Eventually the entire form gets filled out. But for other reasons I can not source the form to the table I want the information to go, PLUS I want this information to go to more than one table (All tables will have the same fields).

So I want to have a button where it only takes the information and distributes it to these Tables after being clicked, then clears the form and exits. A verification message would be nice before it does this, asking "Are you sure you want to submit?" If someone could walk me through how to do this, I'm new to access -completely-, I'm teaching myself how to use it... The idea would be something like this:

[Verify Boxes are filled out] ifYes - Continue / ifNo - "Please fill out all information"
[Verify Message] ifYes - Go /ifNo - Don't Go

[Form][TextBox1] --> [Table1][Field1]
[Form][TextBox1] --> [Table2][Field1]
[Form][cmbText1] --> [Table1][Field1]
[Form][cmbText1] --> [Table2][Field1]
[Form][DateBox1] --> [Table2][Field1]
[Form][DateBox1] --> [Table1][Field1]


[Form][TextBox2] --> [Table1][Field1]
[Form][TextBox3] --> [Table1][Field1]
[Form][TextBox3] --> [Table2][Field1]

[ClearEntireForm]

[ExitForm]
[RefreshDatabase]

I'm assuming I would put this on the button's OnClick?
Sorry I'm new to Access. Everything I've done I'm teaching myself how to do. The biggest road block is that I'm not familiar with the Access Script Language AT ALL, but I am familiar with script. I need a Access Script For Dummies book :P So if anyone wants to explain some things to me that would be really helpful, that way I can better understand how the script for the above to happen was wrote, and manipulate that in the future to do different things that way I don't have to keep coming here to get people to write script.

I understand it's a bit contradicting to how Access was built to work, but believe me when I say there is a reason and method to my madness. Due to how many years of records need to be filed, I don't want to have people creating unnecessary records that lag the database.

-------------------------------------------------------
Site Note:

Would it make any difference if I was storing the 'Historic Records' on 'Table2' (Basically my History Table) on a external database? Or would that just lag it more not having it all on one database?

Do i have to do anything special to make sure it knows every time the button is pressed to create a new record? Or is that kind of a given by Access? Mainly because this form will be submitting data to these tables, each form submit will be an entire record, and other forms will be submitting records to these tables. So is anything extra needed in the code to say create new record before submitting data to these tables?

[ssanfu]

(All tables will have the same fields).

You realize this is not a normalized structure. You are creating redundant data that can (and probably will) get out of sync.
Would you describe why you think this is the way to go?

Using unbound forms and controls is not that unusual. A lot of programmers do that. You have much better control of what happens, but the trade off is that you have to write ALL of the code.

As far as the code, it would be several append SQL statements.

Private Sub YourButtonName_Click()  '<<- change this 

    Dim db As DAO.Database
    Dim sSQL As String
    Set db = CurrentDb
    Dim Msg, Style, Title, Response
    Msg = "Are you sure you want to submit?"    ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Data Entry"    ' Define title.

    ' Display message.
    Response = msgbox(Msg, Style, Title)
    If Response = vbYes Then    ' User chose Yes.

        sSQL = "INSERT INTO Table1 SET Field1 = '" & Me.TextBox1 & "';"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 SET Field1 = '" & Me.TextBox1 & "';"
        db.Execute sSQL, dbFailOnError

        sSQL = "INSERT INTO Table1 SET Field2 = '" & Me.cmbText1 & "';"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 SET Field2 = '" & Me.cmbText1 & "';"
        db.Execute sSQL, dbFailOnError

        sSQL = "INSERT INTO Table1 SET Field3 = #" & Me.DateBox1 & "#;"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 SET Field3 = #" & Me.DateBox1 & "#;"
        db.Execute sSQL, dbFailOnError

        sSQL = "INSERT INTO Table1 SET Field4 = '" & Me.TextBox2 & "';"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 SET Field4 = '" & Me.TextBox2 & "';"
        db.Execute sSQL, dbFailOnError

        sSQL = "INSERT INTO Table1 SET Field5 = '" & Me.TextBox3 & "';"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 SET Field5 = '" & Me.TextBox3 & "';"
        db.Execute sSQL, dbFailOnError

        msgbox "Done"

    Else    ' User chose No.
        msgbox "You selected don't Save!"
    End If

    'clear form
    Me.TextBox1 = Empty
    Me.cmbText1 = Empty
    Me.DateBox1 = Empty
    Me.TextBox2 = Empty
    Me.TextBox3 = Empty

    Me.TextBox1.SetFocus

End Sub

[Missinglinq]

While there are developers that regularly use Unbound Forms, in Access, doing so really does away with the primary reason for using Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms, as Steve said, the developer has to write code for everything, even the most mundane tasks!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end; doing so:

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

[mnewton367]

You realize this is not a normalized structure. You are creating redundant data that can (and probably will) get out of sync.
Would you describe why you think this is the way to go?

Using unbound forms and controls is not that unusual. A lot of programmers do that. You have much better control of what happens, but the trade off is that you have to write ALL of the code.

Yes, I realize. Basically the form is very intricately created in order to provide the user of the form the easiest time possible. (It's setup is likely over-complicated due to my ignorance of Access, but it gets the job done and looks better than the simplicity. It's also not terrible, if I do say so myself.) In doing so I can't source the Form to one table, but this is irrelevant because I need the information to go to two places. The reason I want it to go to two places is because one is a current table that utilizes all of the Form's input information, and the other is a History table that just takes the 'Who did it' and 'What did they do' of the form and archives it to be used later when we want to see who did what and how much they've done. The current table is used on a daily basis though and that information goes to the subTable of the main menu.

Also my reasoning for going over intricate with creation to make the form usage simpler and easier, harder to mess up if you will, for the users is because we work with A LOT of information that is very vital and important. I want to make sure everything gets entered properly, cleanly, and not overflowing with unnecessary entries. My work center has 0 knowledge of Access databases, so it'd be very easy for them to mess something up, so I'm just taking necessary precautions. Also because this database is meant to last far longer than I'll be around for it, and therefore I need that level of structure to maintain even in my absence, and that's best done through a high level of lockdown to prevent mishaps.

Do you have any better way of doing this? I don't plan on making the entire database this intricate code maze of fanciness, but after much review I decided that this form, which is utilized the most, would be easier scripted. I'm not too familiar with the usage of Queries and when they become necessary, though, so if you have a pointer there please share. Let me know if you need more information.

Also, looking over your code, is there anywhere that you know of that is a good teaching tool for this language? I can somewhat understand the code from reading it, but I'd never be able to write it, especially because of things like "Dim db As DAO.Database"; I don't know exactly what it's function is, why it's used, what it means, etc. If I knew commands, terms, and format, then I might be able to give some of it a go myself. Any recommendations?

While there are developers that regularly use Unbound Forms, in Access, doing so really does away with the primary reason for using Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms, as Steve said, the developer has to write code for everything, even the most mundane tasks!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end; doing so:

• You can create an EXE file which gives total protection to your code/design
• You can distribute the db to PCs without a copy of Access being on board
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

Understood! I promise that most of my DB is utilizing that ease which Access creates for it's users and creators. But in this instance I honestly concluded that this might be the easiest method. Though that simply could be because I'm not extremely experience with Access, I've been teaching myself. See my above bit for more information, perhaps. But again, I really think that setting the forms to run this code would be easier. And just the forms, not coding the entire DB.

[ssanfu]

My code example is an approximation of what you described. It might not be exactly what you want. Names like "Table1" and "Field1" leave a lot to be desired. And it makes it difficult for you to try and translate the code to your table/field names.

AS far as learning VBA programming, there are tons of sites to help with that.

Start here http://www.accessmvp.com/strive4peace/VBA.htm

Here is a YouTube video: https://www.youtube.com/watch?v=njIdr47QEmQ


Good luck with your project. Post back if you run into problems.

[mnewton367]

sSQL = "INSERT INTO Table1 SET Field1 = '" & Me.TextBox1 & "';"

Thank you for all of your help so far. I won't have opportunity to play around with this until I go into work on Monday, but I had a quick question. After watching some videos and getting an idea for the format VBA uses and how it identifies things... Can you elaborate this line some? My knowledge of scripting tells me that all you did was set a string. Where is the command that executes the action of taking the data from one field and placing it into the other? How does this break down exactly?

[ssanfu]

I always set an SQL string to a string variable because I can see what the SQL string looks like by using the Debug statement. The Debug statement prints the SQL to the immediate window - you can see if the SQL is formed correctly.

       sSQL = "INSERT INTO Table2 SET Field1 = '" & Me.TextBox1 & "';"  '<- SQL string
       '  Debug.Print sSQL        '  <- prints the SQL string to the immediate window
       db.Execute sSQL, dbFailOnError     ' <- executes the SQL string (action query)

The single quote (') is how you can "comment out" statements. It is like the REM statement in IBM BASIC (from the '80s)

[mnewton367]

db.Execute sSQL, dbFailOnError

So I'm getting a 'Syntax error' on this line and I've messed around with it a bunch and I just can't figure out why.

Also...

"INSERT INTO Table1 SET Field1 = '" & Me.TextBox1 & "';"

So just to verify, this line of text is going to take whatever a person inputs into a textbox on my form and send that to a Field of another Table that the form IS NOT linked to, correct? I was looking up the command INSERT INTO and it usually involves a 'VALUES' or 'FROM', etc., and this one has a 'SET', and none of them have an "=" in it, at least the examples don't. So I was just curious how you came up with this line and how it worked exactly.

[June7]

Steve must have been typing too fast. Yes, he seems to have absent-mindedly mixed INSERT and UPDATE syntax and got stuck in that rut in follow up posts.

I also do not understand the choice for unbound form. If you want to provide db for analysis, follow instructions at bottom of my post.

[mnewton367]

I can't provide my DB but perhaps I can try to explain it?

So for the sake of keeping it short, I currently have 2 tables that I'm working with and 1 form. The tables are [Work Issues] and [Work History]. You enter a bunch of information into this form and upon completion (I want them to be able to verify all of the fields and make sure they are all filled in) you submit the form and it takes all the information and sends it to [Work Issues], but ALSO sends just a few of the fields to [Work History]. So basically it will take Dates, Data, Workers, Initials, Numbers, etc and send it all to [Work Issues] but it will only take Dates and Workers, for instance, and send that to [Work History].

There might be a better way to do that besides coding to send the information manually to places, but again I'm utterly new to Access. I got tired of our old database system being outdated and slow so I have literally taught myself Access from scratch over the past few months by trial and error/messing around. I could be naive to the abilities of setting up a Query Control Source or something that I'm unaware of? Because I'm currently under the impression you can only bind a textbox field on a form to ONE Table, and ONE field in said Table?

Hope this elaborated further. Let me know if you have some knowledge that might help me. Layman's Terms please!

[June7]

Why are Dates and Workers in two tables? This appears to be duplication of data.

If WorkIssues and WorkHistory have a 1-to-many relationship, need primary/foreign key fields to associate related records. Then perhaps use a form/subform arrangement for data entry.

Advise no spaces in naming convention.

[mnewton367]

What do you mean by 1-to-many relationship and primary/foreign keys?

I put these two different tables because the [Work History] table is going to archive all the work so that people can pull up what all they've done and when. The [Work Issues] has far more information and is used for daily operations where the records go through a work process before being completed and removed. (They're only removed because they were already archived upon creation)

[June7]

I would not have an 'archive' table. The one table can be queried to 'pull up what all they've done and when'. If you want to tag records as 'archived', 'completed', or whatever, then have a field to serve that purpose. Those records can be excluded from viewing in 'daily operations' by filter criteria.

This permits use of bound form and eliminates all that messy, complicated code.

[mnewton367]

But if I have one table with a bunch of records on it from the past couple years, then how can I filter all of that to just show what I want to be shown in daily operations and only once the work is complete does it go to archives? Using checkboxes? I can't go by date because sometimes stuff has to stay on there for weeks.

[June7]

A form RecordSource can be a table, query, or SQL statement.

SELECT * FROM WorkIssues WHERE ArchiveDate Is Null;

or

SELECT * FROM WorkIssues WHERE IsComplete = False;

I prefer the date field because it is more informative than a Yes/No field.