[RESOLVED] Write textbox value to field in table with VBA coding

[lilmark]

What is the syntax to write code from a text box to a specific field in a table?

I have an access form with TextBox1. The user enters their name. Then, they click the command button at the bottom.

The vba code I have in the command button click event is as follows....

[tblInfo]![fldName] = [Forms]![frmMain]![TextBox1]

However, I get an error saying it can't find the field specified in my expression, but I do have the field listed and spelled correctly in the table.

Yes, I know it'd be easier to just Bind the textbox to the field and have it write directly to the field that way. But, my form is more complicated and that way will not work the way I have it set up which is why I am taking this round-about way. If you go some posts down on the list you can see that no one has responded to my other question which is why I am trying this method.

[salvelinus]

I've never heard of updating a table in that way. Even if it worked, it would set every record's field to that value. You need to specify which record(s) you want to update.

Dim strSQL as String
'note the single quotes
strSQL = "UPDATE tblInfo SET fldName = '" & Me.TextBox1.Text & "' WHERE fldFoo = somevalue"
DoCmd.RunSql strSQL

[lilmark]

Well, I guess it's just my vb 6.0 coding that's got me thinking the way I first said.

What is fldfoo? is that supposed to be the unique identifier so it knows which record to point to?

How would I right the sql statement if I wanted to updated multiple fields/textboxes the same way?

And, here's more info, just in case it clears some of it up. I have a subform pointing to the same table. On the subform I have a textbox linked to the primary key just so I can see which record it's currently on. As soon as the user types info into the subform the textbox linked to the primary key populates with 68 for example letting me know it's on the new record 68.

Now, if those earlier text boxes on the main form are not bound when they hit the command button wouldn't it put the values of those unbound text boxes into the record 68 since it's currently pointing there as they entered the new data in the subform?

[salvelinus]

fldFoo is any field that uniquely identifies the record(s) you want to update. If a primary key, it would update only one record. If a field where records share the same somevalue (like 20 records where fldFoo = January = somevalue), then all those records with fldFoo matching somevalue will be updated. You may have to put somevalue outside the quotes like the textbox.text, depending on where you're getting somevalue from.
If your put code like above to update record 68 with the values in the unbound textboxes, then it would update the record. You may have to do a requery on the subform to display them right away, though.
Admittedly it's been a few years since I worked with VB 6.0, but I've still never seen the syntax in your original post.

[Al42]

How would I right the sql statement if I wanted to updated multiple fields/textboxes the same way?

Either multiple SQL commands or make the Where clause include all the records you want to update. Which way you do it depends on your data and the update criteria.

... Where fldIndex = 68 ...

would update one record.

... Where fldFoo = 'Administrator' ...

could update many records.

As far as many fields:

strSQL = "UPDATE tblInfo SET fldName = '" & Me.TextBox1.Text & "', fld2Name = '" & Me.TextBox2.Text & "' ... WHERE fldFoo = somevalue"

And, here's more info, just in case it clears some of it up. I have a subform pointing to the same table. On the subform I have a textbox linked to the primary key just so I can see which record it's currently on. As soon as the user types info into the subform the textbox linked to the primary key populates with 68 for example letting me know it's on the new record 68.

Now, if those earlier text boxes on the main form are not bound when they hit the command button wouldn't it put the values of those unbound text boxes into the record 68 since it's currently pointing there as they entered the new data in the subform?

No, the SQL statement isn't looking at the subform to figure out which records to update, it's looking at the Where clause.

[lilmark]

Admittedly it's been a few years since I worked with VB 6.0, but I've still never seen the syntax in your original post.

The code isn't from 6.0. It's from access/vba. I just meant that I'm using vba coding and using templates normally used in vb 6.0. Basically intermixing the two. I dunno ok! It's thursday and I just want my paycheck!

[lilmark]

I tired using the following code, but it still won't do the update

strSQL = "UPDATE tblAccounts SET User_Define_3a = '" & Form_frmBldrResiNewAcct.User_Define_3a & "' WHERE IncidentNumber = " & Form_subfrmBldrSvc.txtIncidentNum
    DoCmd.RunSQL strSQL

After it excute the above command a pop-up window appears saying it's about to update 0 row(s). are you sure? yes/no

it only updates the bound textboxes on the subform. it does not update the unbound textbox on the main form which is specified in the above sql statement.

However, on the WHERE if i set it equal to a specific number such as WHERE IncidentNumber = 100 it will update that textbox into that field on record 100 which means the statement works. The problem seems to be is that I can't get it to point to the current record that is being inputed and displayed on the subform. My guess is as they enter data into the subform area and the textbox on the subform is displaying incident number 121 since that's the new record the user is putting data into....when the command button is hit it searches for the textbox value which is 121 in the table, however, it hasn't been updated into the table yet so it can't locate 121 since it only goes up to 120.

This means I need update the table first before having it run the SQL command...requery I'm guessing? But if I do that won't it move to a new record which means the textbox incident number will now be blank...

Unless I store the value in a variable first so it won't matter if it updates. I'll give it a shot!! Thanks guys you and the snicker bar got my brain rolling. If it doesn't work I'll be reposting here.

[lilmark]

It worked! I put txtIncidentNumber.requery before the SQL command statement and now the SQL statement was able to find the newest data currently being entered since the requery updated the latest entry to the table before the sql command could search for it!

If what I just said makes sense than you're smarter than I am cause I have no idea what I just said.

I've been fighting with this issue all week and it's finally over.
Score:
Programmer - 1
Program - 0

All I can say is nudy bar this weekend.

Scratch the above! It didn't work, but I found out how to make it work. Before it runs the sql coding I returned focus to the main form
The Email command button is on the subform. So, once I set focus to the textbox on the main form the subform loses focus and thus finishes writing the code into the table and when it runs the sql coding next to search for the incidint number it is now able to locate it on the table and write the unbound boxes on the main form to those fields on that record

Form_frmMain.textbox1.setfocus

[bluewater9]

Hi Lilmark,
I have a similar problem need to be done. The difference is that I don't have a subform. I guess this will make the things simpler. But I am a VBA blind. All I can understand at this moment is that you need to update the table first to get the newest data before running SQL command.

But I am a bit confused that you are trying to transfer the data from the form to the table. I hope I can get further instruction from you at this point. Thanks in advance.