Userform TextBox Value used in a SQL

[dubbglubb]

Hello Everybody,

Having a spot of bother with SQL and DAO. If this belongs in another
forum please tell me, but since I'm working in word, here I am.

I've created a Userform (GymyDoc) in Word with a TextBox (txtOurRef).
I'd like the user to be able to enter a -document number -e.g 84171,
and return text into the other TextBoxes.


Curently I can get is to work only by hardcoding the number into the
SQL eg:


Code:
--------------------

Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb], [State], [Zip], [Nata] FROM [tblGymy]" _
& "WHERE [Document No] = 84171 ")


--------------------


Ideally I'd replace the number with a variable


Code:
--------------------

varOurRef = txtOurRef.Text

--------------------


But I just can't get the syntax right. I get get various errors. This
version uses the full reference to the txtOurRef, but I'd like to
replace with varOurRef if I can.


Code:
--------------------


Dim dbEML As Database, rstJob As Recordset


Private Sub cmdGetDatabase_click()


Set dbEML = OpenDatabase("F:\ADMINISTRATION\DATABASE\EML DATABASE\EMLAIR.MDB")


Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb], [State], [Zip], [Nata] FROM [tblGymy]" _
& "WHERE [Document No] = '" & Forms!GymyDoc!txtOurRef.Text & "'")


FillFields

End Sub


Private Sub FillFields()

txtClient.Text = rstJob.Fields("Client")
lblNata.Caption = rstJob.Fields("Nata")
txtSuburb.Text = rstJob.Fields("Suburb")
txtClientState.Text = rstJob.Fields("State")
txtPostCode.Text = rstJob.Fields("Zip")

End Sub


--------------------


Any Ideas?

Cheers

 

[Peter Jamieson]

If you're trying to replace /this/

Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb],

[State], [Zip], [Nata] FROM [tblGymy]" _

& "WHERE [Document No] = 84171 ")

then the SQL interpreter is expecting a number rather than a text
string, so try leaving out the single quotes, i.e.

Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb], [State],
[Zip], [Nata] FROM [tblGymy]" & _
" WHERE [Document No] = " & Forms!GymyDoc!txtOurRef.Text)

However, that will only work if

Forms!GymyDoc!txtOurRef.Text

just returns the string

84171

or whatever. If there's anything else in there you will need to strip it
out.


Peter Jamieson

http://tips.pjmsn.me.uk

Hello Everybody,

Having a spot of bother with SQL and DAO. If this belongs in another
forum please tell me, but since I'm working in word, here I am.

I've created a Userform (GymyDoc) in Word with a TextBox (txtOurRef).
I'd like the user to be able to enter a -document number -e.g 84171,
and return text into the other TextBoxes.


Curently I can get is to work only by hardcoding the number into the
SQL eg:


Code:
--------------------

Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb], [State], [Zip], [Nata] FROM [tblGymy]" _
& "WHERE [Document No] = 84171 ")


--------------------


Ideally I'd replace the number with a variable


Code:
--------------------

varOurRef = txtOurRef.Text

--------------------


But I just can't get the syntax right. I get get various errors. This
version uses the full reference to the txtOurRef, but I'd like to
replace with varOurRef if I can.


Code:
--------------------


Dim dbEML As Database, rstJob As Recordset


Private Sub cmdGetDatabase_click()


Set dbEML = OpenDatabase("F:\ADMINISTRATION\DATABASE\EML DATABASE\EMLAIR.MDB")


Set rstJob = dbEML.OpenRecordset("SELECT [Client], [Suburb], [State], [Zip], [Nata] FROM [tblGymy]" _
& "WHERE [Document No] = '" & Forms!GymyDoc!txtOurRef.Text & "'")


FillFields

End Sub


Private Sub FillFields()

txtClient.Text = rstJob.Fields("Client")
lblNata.Caption = rstJob.Fields("Nata")
txtSuburb.Text = rstJob.Fields("Suburb")
txtClientState.Text = rstJob.Fields("State")
txtPostCode.Text = rstJob.Fields("Zip")

End Sub


--------------------


Any Ideas?

Cheers

 

[dubbglubb]

Thanks Peter,

I'm still a little off on the Syntax. As per your sugestion I tried


Code:
--------------------

" WHERE [Document No] = " & Forms!GymyDoc!txtOurRef.Text)

--------------------


Syntax error. So I tried:


Code:
--------------------

"WHERE [Document No]= "& Forms!GymyDoc!txtOurRef.Text"")

--------------------


Then:


Code:
--------------------

"WHERE [Document No] = " & Forms!GymyDoc!txtOurRef.Text & "")

--------------------


This last one brings up run-time error 424, "Object Required"

Any Ideas?

Cheers,

 

[Doug Robbins - Word MVP]

If you put a MsgBox Forms!GymyDoc!txtOurRef.Text in your code, what does it
return?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Peter Jamieson]

Forms!GymyDoc!txtOurRef.Text

needs to be qualified in some way, but precisely how to do that depends
on what is running the code. If your code is Word VBA, you may have to
open the Access object to get the value. If your code is Access VBA, I
guess you would be OK. I don't think you can reference the form's value
from DAO, at least not directly.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[dubbglubb]

Forms!GymyDoc!txtOurRef.Text

needs to be qualified in some way, but precisely how to do that
depends
on what is running the code. If your code is Word VBA, you may have to
open the Access object to get the value. If your code is Access VBA, I
guess you would be OK. I don't think you can reference the form's
value
from DAO, at least not directly.

I'm using a UserForm in Word VBA, to access an Access database, both of
which are office 2003.

The textbox (txtOurRef) is in a Word VBA userform. So can I convert
that value to a variable (varOurRef) and use that in the SQL?

Cheers

 

[dubbglubb]

If you put a MsgBox Forms!GymyDoc!txtOurRef.Text in your code, what does
it
return?

Thanks Doug,

The Message box returns that same error so I guess vba can't interpret
it.
I thought I had to put it in that format so that -SQL- could interpret
it as per this msdn article:

'Build SQL Statements That Include Variables and Controls'
(http://msdn.microsoft.com/en-us/library/aa189798.aspx)

Problem is, that article uses only a date as it's example

 

[Peter Jamieson]

If the form is in the current document/template I think you should be
able to use

GymyDoc.txtOurRef.Text

The Forms! syntax has more to do with the Access VBA environment AIUI.

Peter Jamieson

http://tips.pjmsn.me.uk