How to set result of SQL

[KentMorand]

I have what I think is a very easy question that was so easy I couldn't find the asnwer in the other posts. I have an SQL string that will return a single value. I need to know how to put this value into a text box. Here is the code

SQL = "Select distinct(TechLan) from tblTechName where TechName = '" & strTechName & "'"
Set rsRecord = db.OpenRecordset(SQL)

What owns the value of the SQL string? I tried assigning SQL to a text box but nothing at all happens... Any ideas and thanks in advance.

 

[lastout]

I might be misunderstanding you but it sounds like all you need to do is add to your code a line like:

Me.tbxname.ControlSource = SQL

If that doesn't work, it might be that you need your string SQL to be a global variable. As it is, I'm not sure where your variable is declared.

Oh and by the by, if SQL is a string you should probably follow a naming convention and call it something like strSQL or stSQL. It'll help you later.

 

[KentMorand]

Thanks for the help. I tried that and it runs through the code but it doesn't assign the value to the text box. I managed to dredge up an old project and found this piece of code.

SQL = "Select distinct(TechLan) from tblTechName where TechName = '" & strTechName & "'"
Set rsRecord = db.OpenRecordset(SQL)

If Not rsRecord.EOF Then
myvar = rsRecord!TechLan
End If

Me.txtTechID.SetFocus
txtTechID.Text = myvar

Now this assigns the value returned to the text box just like I wanted but I always get this error..

"Run time error '2115'

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving data in the field."

What is this error referring to? The text box in question is bound to a table so when I save the record it will go into the appropiate field. Other than that the textbox is just like any other textbox. Any other ideas? Again I more than appreciate any help at all with this.

 

[lastout]

Sorry to take so long in responding. I haven't come across that error before but I did find an MS Knowledge Base article (number 128195) that deals with it:

http://support.microsoft.com/default.aspx?scid=kb;en-us;128195

Hope it helps.

 

[Eupher]

From what event are you calling your code?

Ken S.

 

[KentMorand]

Thanks so much for all your help guys. I was able to determine the problem. I was assigning what was returned to mytextbox.text. When I tried assigning it to mytextbox.value it worked fine. Thanks again for the help.