DESPERATE: Need help with SQL! (1 Viewer)

[pbaldy]

Wow, talk about small worlds. My daughter lives in Bury St Edmunds. Visited her a few months ago; it's a lovely area.

 

[The_Doc_Man]

If you don't have it copied any other way, the reference to the contents of a control on an ACTIVE form would be

Forms("formname").Controls("ControlName")

And when you use the syntax above, you are using the default for the item selected, which is .Value - so you might use

"UPDATE [Dummy Table] SET [Line Rating] = '" & Forms("formname").Controls("controlname") & "' WHERE [Sub Station] = 'crosby'"

The trick is, of course, to use the ' marks to quote text so that Access doesn't try to translate the resultant string, and you use & marks to force concatenation of the value in question.

Finally, if you are in a context where Me actually works and the variable name Rating exists, using Me.Rating is not necessary because (just plain) Rating is adequate. And if you are calling a "common" subroutine where Me does NOT work, Me.Rating is undefined.

Personally, I would make it a two step process by putting a ".LostFocus" routine behind the control in question and have it store the value in your Rating variable, complete with any mode conversions and editing as required. Like, for instance, if it is a string you might wish to trim the string.... Rating = Trim$([controlname])

After that event has fired, your rating is available until you go change it and tab out of the control again.

 

[chriscwilson]

Thanks Paul, it is a lovely area, I sometimes work in London but live in the country. Best of both worlds! Good to know you have a connection with Bury St Edmunds.

 

[vbaInet]

Thanks Paul, it is a lovely area, I sometimes work in London but live in the country. Best of both worlds! Good to know you have a connection with Bury St Edmunds.

... then there's me with a connection with London Three of us on one thread. I wonder if The_Doc_Man has some connection!!

 

[pbaldy]

I'm hoping to make another trip later this year. I'll try to remember to post something here so we can have an AWF meeting at the nearest pub!

 

[chriscwilson]

Hey, great idea - best beer in the UK here!!

 

[vbaInet]

Great stuff Paul!

 

[abhisheknag89]

Sorry for the confusion guys. I'm working with a Report. On this Report there is a textbox and a button. When the button is clicked, I wish to grab the value in the textbox and update certain records in a table using that value.

The problem is that even when I type a value into the textbox, access seems to read it as being empty.

I tried Doc Man's suggestion of creating a subroutine on LostFocus and stored the textbox contents into a variable. I then tried using this variable to run the UPDATE statement but that gave me the UPDATE syntax error.

I also attempted vbaInet's Msgbox Nz test for that variable and found the dialog box that pops up is blank (meaning there is some value stored, im guessing) but I can't seem to use it in my SQL query. Here's the what I'm doing:

Private Sub rtg_LostFocus()

doit = Me.rating

End Sub
---------------------------------------------
Private Sub Command25_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()

MsgBox Nz(doit, "Contains nothing")


DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] =" & doit & " WHERE [Sub Station] = Enter_substation_name"

End Sub


Arghh.

 

[pbaldy]

Can you post the db, or a representative sample?

 

[boblarson]

I'm working with a Report. On this Report there is a textbox and a button. When the button is clicked, I wish to grab the value in the textbox and update certain records in a table using that value.

USE A FORM, not a report. A report doesn't have this functionality. In 2007/2010 you can have a button on a report, you can click it and do something but you can't type something into a text box on it and expect it to act like a form.

 

[abhisheknag89]

I do believe bob is right. I never had any issues with grabbing values from textboxes when i was working with forms, but I guess reports really don't have that functionality.

Thanks so much all of you for your time, wisdom and patience! I think I'll have to settle for having the user be prompted for the values that would have otherwise been entered in a textbox.

 

[vbaInet]

Reports aren't used for such basically. Stick to using forms for data entry/capture and reports for presenting data.

 

[abhisheknag89]

Yup. By the way: when I prompt the user to enter the parameter value, if the user clicks cancel then my program runs into an error. Can I assign an event to the "cancel" button to, say, close the report?

 

[vbaInet]

It would be useful to know the error message?

 

[abhisheknag89]

Hahah you're right.

Run-Time error '3270'"
Reserved Error

I guess access doesn't really know how to proceed if the user chooses to cancel.

 

[pbaldy]

It would also be helpful to see the code you're using. I believe clicking cancel on an input box will return a zero length string (""), so your code needs to be able to handle that.

 

[abhisheknag89]

The code I'm using is:

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = Enter_Rating_Value WHERE [Sub Station] = Enter_substation_name"

Which prompts the user to enter the values for Line Rating and then Sub Station.

Zero length string, eh. I guess if I know where it is stored I can set an if condition to make the program do something when it sees a (").

 

[pbaldy]

I don't personally like that method. You're relying on what is in essence the error thrown by Access when it doesn't know what that text represents to gather user input. As you've found, it made you lose control of the situation. Look at InputBox in VBA help. I'll let you play with the actual code, but the puesdo code for what you want is:

Variable = InputBox(...)
If Len(Variable) > 0 Then
run your SQL using the variable

 

[vbaInet]

You could also consider using a form and textboxes then get the values from the textboxes. I think Paul has some examples on his site.