Create Button to Submit Data

[mnewton367]

Can you have two Sources? What if I have a textbox that is =[Field1] & " - " & [Field2] & " - " & [Field3] but I also want that to send to a field of a table?



Can I do something like set it up so that by the time it receives it's last check through daily operations it then receives an ArchiveDate with today's date? I guess that would be one way to do it that would be cleaner. It would just require a script or something to have it input today's date into a field when checked as archived?

Where does DB lag come from? From too many tables, or too many records?

[ssanfu]

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.

Yep. the evils of cut and paste

Corrected code from post #2:

Private Sub YourButtonName_Click()   'change to your button name

    Dim db As DAO.Database
    Dim sSQL As String
    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.

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

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

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

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

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

        sSQL = "INSERT INTO Table1 ( Field5) VALUES ( '" & Me.TextBox3 & "');"
        db.Execute sSQL, dbFailOnError
        sSQL = "INSERT INTO Table2 ( Field5) VALUES ( '" & Me.TextBox3 & "';"
        db.Execute sSQL, dbFailOnError
        msgbox "Done"

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

        Me.TextBox1.SetFocus

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

End Sub

[June7]

Saving calculated data (data dependent on other data) is usually not advisable. Data can get 'out of sync'. Calculate when needed. Saving calculated data requires code (macro or VBA).

Why would you want to save those 3 fields as one concatenated value? This concatenation calc can be done when needed.

Yes, code could populate an ArchiveDate field based on some parameters encountered in daily operations. The real trick is figuring out what event to put code into.

Or this archive status could be calculated. If value is entered in last date field of a series of date fields, that would indicate record is 'complete' and in archive status. That date would serve as archive date.

Database performance can be affected by network, size of dataset, complexity of forms/reports.

[ssanfu]

I agree with June that an "ArchiveDate" field is the way to go. It also gives you a history of WHEN the data was archived.

Can you have two Sources? What if I have a textbox that is =[Field1] & " - " & [Field2] & " - " & [Field3] but I also want that to send to a field of a table?

No. One control has one control source.and the rule is (that a lot of people break) "one piece of data in one field".
This is the First Normal Form.

First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.

See http://www.tutorialspoint.com/dbms/d...malization.htm

You can have code that checks the fields and, if all dates are filled in, enters a date into the ArchiveDate field. I prefer to have to perform an action to set the Archive status....(I am kinda a control freak with data).

[mnewton367]

So first of all I just wanted to say thank you to everyone's help! I decided to look into your recommendations to avoid scripting and utilize table control source binding and bound my form to one single table. It's working great. I even figured out scripting pretty well and managed to script my save, undo, and exit buttons to do a little extra.

However~ I am looking into that recommendation to, instead of having separate tables (Work History, Work Current, Work Needed), have everything on one table. But what I'm wanting to do is create two checkboxes for each record in this table. Seeing as how I want them to move through a step-by-step subform process on the main page, I want to set it up so that the first subform (subtable, really) only lists the records with the neither checkboxes marked, but only shows the ability to mark the first checkbox. Then the second subform shows only records with the first checkbox marked, but only shows the ability to mark the second checkbox. Then after marking the second checkbox, that main page no longer shows the record in any subforms, however allows it to stay in that All-In-One table we talked about and be utilized for 'Archiving' later. I hope this makes sense! It's basically my idea on how to create a way for records in a single table to move through a process of subforms and finally become archived for historical purposes.

I guess to simplify, if that was too confusing... How can I make it so that a subform of a table on my main page only pulls records that have checkbox(es) marked or unmarked accordingly? I'm assuming this would be done with a little bit of VBA and a Query Wizard? Unless someone knows of a non-VBA way where I can set criteria for a record to show up?

Thank you again for everyone's help! You guys/gals are great!

[June7]

Set RecordSource to an SQL statement.

SELECT * FROM tablename WHERE checkfield1 = False And checkfield2 = False;

Don't have a control bound to checkfield2.

SELECT * FROM tablename WHERE checkfield1 = True And checkfield2 = False;

Don't have a control bound to checkfield1.