[RESOLVED] Yet another SQL issue

[MethadoneBoy]

Hi guys,

I'm having difficulty in getting text from a textbox on a form. What I'm trying to do is use a textbox for completing an SQL statement.

For example, say the textbox name is txtMonth. I have a String variable called insert

Dim insert as String
 
insert = txtMonth.txt
 
sqlStmt = "INSERT INTO <Tablename> VALUES ('" & insert "');"

There are no syntax errors or errors of any kind in the SQL statment, so it must be a problem getting the text from the textbox.

Any suggestions?

Thanks

[dannymking]

What Applicaiton is this being performed on? Which Control? and Which Event?

[dee-u]

Try...

Dim insert1 as String
 
insert1 = txtMonth.txt
sqlStmt = "INSERT INTO Tablename VALUES('" & insert1 "');"

[MethadoneBoy]

What Applicaiton is this being performed on? Which Control? and Which Event?

It's being written as part of an Access Form. The code is called when a button (CmdDoit) is clicked.

Try...

Dim insert1 as String
 
insert1 = txtMonth.txt
sqlStmt = "INSERT INTO Tablename VALUES('" & insert1 "');"

Tried that and still no success. Thanks for the help, though.

[Ecniv]

Silly question but are you executing the sql statement on a command object??

[dannymking]

My point exactly.. which is why the initial questions..

You cannot reference the text value of an control without setting the focus first.. the default is value.. I'm surprised it's not throwing an error about the set focus..

So the code should be

Dim insert1 as String
insert1 = txtMonth
sqlStmt = "INSERT INTO Tablename VALUES('" & insert1 "');"

I presume there is more code following this to actually execute the query..

Try that and post back if still not resolved

[techgnome]

Sorry danny, but that has got to be one of the lamest things I have ever heard.... 1) IT is possible to get the text value of a control w/o setting focus... 2) While it's not necessary to use .Text... it's a good idea. Lastly, no one said anything about set focus errors. Where did that come from?

Methadone - A couple of questions and a comment for you:
Comment first: What you have done in your first post is akin to saying "Doc I hurt. It must be my appendix, because my leg bleeds just fine." You've said there's a problem and you've seemed to diagnosed it yourself, yet you never said what the symptoms are.

HOW do you know there's a problem getting the text? Do you get an error? Does the update even happen? HOW are you executing the query? Have you done a msgbox on insert and sqlStmt? What are their values? What does sqlStmt look like after putting it all together (but before executing)?

Tg

[dannymking]

1) IT is possible to get the text value of a control w/o setting focus...

In an Access Database it is not possible...

A simple MsgBox from a command button that returns the Text property of a Textbox in an Access Form returns the following error message...

Run-Time error '2185'

You can't reference a property or method for a control unless the control has the focus.

and I quote

It's being written as part of an Access Form. The code is called when a button (CmdDoit) is clicked.

The value propery of a control on an Access Form can be referred to without setting the focus to the control...

Lastly, no one said anything about set focus errors. Where did that come from?

I said

I'm surprised it's not throwing an error about the set focus..

Remember This is Access and not VB..

[Ecniv]

Beg to differ

Quote:
Originally Posted by Techgnome
1) IT is possible to get the text value of a control w/o setting focus...

In an Access Database it is not possible...

Do not use the .text with access forms. no need for the focus.



It will only not work if you are in the text box and fire a before update on it, because the value is not set yet. But then the .text works and you have the focus... so if wouldn't make the error you posted. Uh well. Try n see?

[techgnome]

Well I'll be.... Humble pie, one please, a la mode if possible.... it's still the lamest thing I've ever heard of (well, actualy it makes the top 10, a former co-worker will always retain spots 1-5 at least, but that's another matter)... then again, MS isn't not exactly the poster child of consistancy.

Still, shouldn't this have thrown an error (focus or not) -- insert = txtMonth.txt

.txt????

Tg

[dannymking]

I'll give you that one.. didn't spot the .txt thing.

A Compile error will be returned stating "Method Or Data member not found".. Adding the e in will return Runtime Error '2185' taking the .txt out will result in a (hopefully) working command button click...

Why is it still lame...

[techgnome]

Because that makes .Text inconsistant with VB.... but I guess Access has always done it's own thing...

Tg

[dannymking]

Yeah.. tis true..

Went from Excel VBA to VB then to Access VBA and then to .Net...

Lack of Text being default caught me out and I ended up setting the focus to the control and then back again..

Until I actually read the Help File

[MethadoneBoy]

What you have done in your first post is akin to saying "Doc I hurt. It must be my appendix, because my leg bleeds just fine." You've said there's a problem and you've seemed to diagnosed it yourself, yet you never said what the symptoms are.

Noted.

HOW do you know there's a problem getting the text? Do you get an error? Does the update even happen? HOW are you executing the query? Have you done a msgbox on insert and sqlStmt? What are their values? What does sqlStmt look like after putting it all together (but before executing)?

I've managed to resolve the issue since I posted this query. Well, I didn't resolve it, truth be told, just found a way around it.

TO answer your questions, I initially thought there was a problem getting the text because, I would first of all enter the text (I did actually set the focus to the textbox) then click a command button which would feed that text into the sql String, which would in turn extract data from a table. No data was being received. I added another textbox to the form to see if text was being assinged to the "insert" variable - it was. So there must be something wrong with the sql String. For some reason it wasn't extracting data.

I managed to solve this part of the problem by breaking the String into smaller parts as opposed to one long statement eg:

sqlStr = "SELECT <col1>, <col2>, "
sqlStr = sqlStr & "<col3>, <col4>, "

..... you get the idea.

This solved the problem I had encountered, but I'm not sure why. If anyone could explain why one long String of an sql Statement wouldn't work as opposed to a String built up, I'd appreciate it.

[dee-u]

I wouldnt find any difference with a long sql statement with a breaked one, I usually just put in one line all my sql statements and encountering no problem with it, if you could post that long sql statement of yours then maybe we could check for any syntax errors...

[dannymking]

An Access query string can be upto 64,000 characters long.. It would be better to see what your actual sql string was, as things like spaces in field names need to be surounded by square brakets...

[MethadoneBoy]

An Access query string can be upto 64,000 characters long.. It would be better to see what your actual sql string was, as things like spaces in field names need to be surounded by square brakets...

I don't think there were any syntax errors as once I split the String down, it worked fine - I hadn't made any fundamental changes to the text. I had field names enclosed in square brackets and everything seemed in order.

I'm a little hesitant about posting the names of the tables and fields here as they're work-sensitive. I just wanted to see if anyone knew offhand why a long String would fail and a component String wouldn't.

Thanks to everyone who helped, though!