Using bound + unbound controls on a form to add data to a table

[Bcanfield83]

Hello,
I have a form in my database that is used to submit requests by a small team of individuals. It's setup as a standard 'single form' with record selectors at the bottom to scroll through the various requests.
The form is bound to a query that essentially just pulls over any "open" requests from the table (tblDevelopment_Tracking). Within the table, there's a EDDT_Date_Submitted and EDDT_Submitted_By field which are intended to hold the date in which the request was submitted, and userID of the person who submitted it (respectively).



The 2 text boxes referenced in the code (txtSubmittedOn, txtSubmitter) are unbound controls.. Upon form load, they populate the person's UserID (txtSubmitter) and Today's Date (txtSubmittedOn). These are not actually visible on the form, but I made sure they are populated with the correct info before setting the visible property to "No".

For some reason, I'm having trouble figuring out how to populate those fields via the unbound controls.. I have the below code currently, which is tied to a "Submit" button.. But those two fields (EDDT_Date_Submitted, EDDT_Submitted_By) are still showing to be blank even when the code executes without an error message.

Dim strSQL As String
Dim strDate As String
Dim strSubmitter As String


strDate = Me!txtSubmittedOn
strSubmitter = Me!txtSubmitter


'Insert SQL - inserts records into tblDevelopment_Tracking
strSQL = "INSERT INTO tblDevelopment_Tracking (EDDT_Submitted_By, EDDT_Date_Submitted)" & _
         "VALUES ('" & strSubmitter & "', '" & strDate & "')"


DoCmd.SetWarnings False


'Execute the SQL string above
DoCmd.RunSQL strSQL

[davegri]

Why are the controls unbound? Make them bound just like all the other textboxes on the form.

[kd2017]

Why are you using unbound controls?

You don't need the insert SQL statement. Simply need me!eddt_submitted_by=me.txtSubmitter

That said I see two issues with your SQL. First you've surrounded the date with quotes instead of hash tags.
Second your submitting the userID as a string, not a number, is that correct? I would usually expect the primary key field like userID to be an AutoNumber, in which case you don't surround a number datatype with quotes in SQL strings.

strSQL = "Insert Into tblDevelopment_Tracking (EDDT_Submitted_By, EDDT_Date_Submitted) VALUES (" & Me.txtSubmitter  & ", #" & Me.txtSubmittedOn & "#);"

Which should evaluate to look like

strSQL = "Insert Into tblDevelopment_Tracking (EDDT_Submitted_By,  EDDT_Date_Submitted) VALUES (1234, #12/25/2017#);"

It should NOT look like this:

strSQL = "Insert Into tblDevelopment_Tracking (EDDT_Submitted_By,   EDDT_Date_Submitted) VALUES ('1234', '12/25/2017');"

[Bcanfield83]

Thanks - so the reason I was trying to use *unbound* controls for those fields is because my intent was to have them autopopulate whenever a new record is entered in the form, and not visible.
So using "Submitted By" as an example - it's a text box that autopopulates with the person's UserID when they go into the form to add a new record. But if the text box is bound to the "EDDT_Submitted_By" field in the related table, then wouldn't that field get overwritten if somebody went into the form and toggled through the existing records?
By default, the form opens up to a new record, but the record selectors are still visible at the bottom.. so if I was the one logged into the database, the "Submitted By" control would autopopulate with my UserID.. and then if I clicked on the record selectors to view previously submitted records, the "Submitted By" text box would keep autopopulating with *my* UserID - even if those previous records were submitted by a different person.

[Micron]

If those fields are bound, the data would remain unchanged because you'd use the form NewRecord property to run code and put the values in. This would occur only on a new record.

Regardless if you use that method or any other code, whenever you automatically populate bound controls you must ensure data validation takes place lest you either a) unintentionally save records with only the pre-populated fields filled in or b) raise errors when the record doesn't pass the validation requirements you've set in code or on the table itself. This could happen when any of the actions that commit a record are invoked, such as closing the form or navigating off the record.

[davegri]

As Micron suggests,
In the form_Current event:

If Me.NewRecord then
    txtSubmittedOn = date()
    txtSubmitter = wherever the data is
end if

[kd2017]

I THINK I understand your motivation. You want a default value for new records?

Instead of the unbound controls you can set the default value property of the *bound* controls. Me.EDDT_Submitted_By.DefaultValue = userID
Now when scrolling through the records the control will display the actual value in the recordset unless it's a new record, in which case it displays the default value and autopopulates the new record with the default value. DefaultValue will have no effect on your existing records.

No need for extra unbound controls and code at all.

[Bcanfield83]

Thanks! I will use the suggestions provided.
As usual it sounds like I was making things far more complicated than need be..