how to reference a column in a table

[jguscs]

update query

I want to take some string data (typed by the user in a text box) and save it to all row entries of ONE column in a specific table.
Is this possible?
I'm assuming a loop is necessary from 1 to COUNT of the table's rows and then I would have to designate which column of the active row would receive the data somehow...

 

[SforSoftware]

You could do that with an UPDATE query. For 'Change into' you refer to the field on your form. When you don't add criteria, de field will be updated for each record.

Luck,
Bert

 

[jguscs]

Alright, I'll try that... I don't have much experience with queries, but what you described sounds pretty simple.
On the other hand, is there a way to do this without a query?

Maybe this should be a separate post, but I have a second question:
How can I read the value from a specific cell of a table into a text box on my form?

 

[SforSoftware]

You can do it without a query, by looping through the table with a recordset, but that's a lot slower than a query. Why don't you want it in a query?

You can read a value from a table/query ie with DLookUp (well documented in Help)

 

[Pat Hartman]

How can I read the value from a specific cell of a table into a text box on my form

- Don't make the mistake of thinking tables are just like spreadsheets because they have a similar "look" when a table is viewed in datasheet view.

want to take some string data (typed by the user in a text box) and save it to all row entries of ONE column in a specific table.

- sounds like you are trying to duplicate data. This is not necessary in properly normalized tables. Store the value in the correct table and get it whenever you need to by joining to the table where it is stored.

 

[jguscs]

SforSoftware, could you help me write the update query? The only query I've ever used is an SQL (shown below) used to fill a list box with the names of tables in my database.
So, actually I don't know what you mean by using 'Change into.'
Any help would be appreciated.

strSQL = "SELECT Name FROM MSysObjects WHERE Left(Name,4)<>'MSys' AND Type=1 AND Name <> 'ObjectTypeCodes' "

 

[jguscs]

update query

OK, I've been working on my update query.
I have a feeling that it's close, but I have no idea (and it's not actually updating the table).
Any help?
(I have to grab the table name from a list box... that explains the For...Next)

Dim varItem As Variant
Dim SaveToTable As String
For Each varItem In Me.LstTables.ItemsSelected
SaveToTable = Me.LstTables.ItemData(varItem)
Next varItem

strSQL = "UPDATE SaveToTable SET SaveToTable.[Client Name] = Me.ModTxtName;"

 

[SforSoftware]

Hi, I'm not sure what you really want to do... Maybe you can explain something more?
What do you have in your form and where do you want to put it?

Did you think about Pat's second remark?


When you want to create an update query: first create something with the build-in query-builder. You can change the query-type via menu Query. Then change to SQL-view to see how's the SQL of that query.
Than you can 'translate' it into VBA, for working with variables (you then have to think about the right quotes and so on)

 

[jguscs]

What I'm trying to do, SforSoftware, is take info from a text box (on my form called Switchboard) and store it into every row of a particular column in my table (the name of which varies). As I do more and more research, I've found that people generally would recommend doing this via an "update query."
My problem is that I have (had) never heard of an update query, and have extremely limited experience with SQL.
So, I'm guessing at it as I go.
Any help would be appreciated.

As for Pat's remark, I really have no idea. Obviously he is an expert and most of the things he says are over my head, so I wouldn't be able to contradict what he said.
To that regard, I have a feeling I don't want to modify my original tables any more than I need to because my database needs to import and export the tables that the users put in as though nothing happened to them (with the exception of obvious modifications that the user would perform himself).

 

[SforSoftware]

Ok,

Then try this:

Dim varItem As Variant 
Dim SaveToTable As String 
Dim strSQL As String

For Each varItem In Me.LstTables.ItemsSelected 
  SaveToTable = Me.LstTables.ItemData(varItem) 

  strSQL = "UPDATE " & SaveToTable & " SET " & SaveToTable & ".[Client Name] = '" & Me.ModTxtName & "'"
Next varItem

Be sure to take the quotes arount Me.ModTxtName (first single, than double. Afther modtxtname: double, single, double)

 

[jguscs]

It's not working, but I see the points you are making...

OK, so I need to declare strSQL as a String variable? Done.
Are you missing the ending
&";"
to the SQL?
Are you sure single quotes need to go between the Me.ModTxtName?

 

[SforSoftware]

Sure you've to declare de strSQL. But now I read it back and see that it can't work, because we don't give the command to run the SQL

Will this work?

Dim varItem As Variant 
Dim SaveToTable As String 

For Each varItem In Me.LstTables.ItemsSelected 
  SaveToTable = Me.LstTables.ItemData(varItem) 

  DoCmd.RunSQL "UPDATE " & SaveToTable & " SET " & SaveToTable & ".[Client Name] = '" & Me.ModTxtName & "'"
Next varItem

If Me.ModTxtName is a textvalue, then the single quotes must be there. If not, tell me what type of value it is...
The ";" is not necessary in VBA, but you may use it if you want.

 

[jguscs]

Me.ModTxtName is the control name for the text box that contains the information that is to be stored to the table.
So, to answer your question... No, Me.ModTxtName is not a text value, but the contents of it is a text value...
When I compile, I get the error "Argument not optional." and it points to .ModTxtName

 

[jguscs]

Nevermind, the problem was that I forgot to drop the = between the strSQL = "UPDATE...

Now that that's gone, it's giving me a general "syntax error in SQL statement" error.

DoCmd.RunSQL "UPDATE " & SaveToTable & " SET " & SaveToTable & ".[Client Name] = '" & Me.ModTxtName & "'"

 

[SforSoftware]

Try this:

Dim varItem As Variant 
Dim SaveToTable As String 
Dim strSQL As String

For Each varItem In Me.LstTables.ItemsSelected 
  SaveToTable = Me.LstTables.ItemData(varItem) 

  strSQL = "UPDATE " & SaveToTable & " SET " & SaveToTable & ".[Client Name] = '" & Me.ModTxtName & "'"
  Debug.Print strSQL
Next varItem

Run this code and check what's printed in the Immediate-window. Can you copy that here?

Is the code running from the form with the textbox "modtxtname"?

 

[SforSoftware]

Can you post a part of the DB here (attached)? Then I'll see what to do...

 

[jguscs]

I would, except that the code you gave me:
strSQL = "UPDATE " & SaveToTable & " SET " & SaveToTable & ".[Client Name] = '" & Me.ModTxtName & "'"
Debug.Print strSQL
isn't doing anything new.
Actually, it's not doing anything at all.
What's the Debug.Print supposed to do? Open a new window? Or print out a hard copy?

 

[SforSoftware]

Debug.Print will 'print' the value of strSQL into the Immediate-window (you can open that by pressing Ctrl+G). Then you can see exactly how the string has been interpreted.

 

[jguscs]

Interesting! Ctrl-G. OK, Here is the result of the latest attempt to run the SQL:

UPDATE Test Table SET Test Table.[Client Name] = 'Test Name'

 

[jguscs]

I tried setting up the SQL code so that the "interpretation" would look like this

UPDATE [Test Table] SET [Test Table].[Client Name] = 'Test Name'

but that didn't help.
Anything wrong with the interpretation that you can see?
I'll try putting in the
;
at the end.