error when selecting code from a database

[AAAndy]

error when selecting code from a database and trying to post it to a textbox
below is my code, the error occurs when reaching Me.txtvarExtensionSettingValueList.text = S
I am trying to post the queried info to the textbox txtvarExtensionSettingValueList but it will not allow me. Can anyone help?
below is my code




Private Sub cbovarReportTitle_AfterUpdate()
Dim iVal As String
iVal = Nz(Me.cbovarReportTitle.Value, "")


Dim S As String
S = "SELECT varExtensionSettingValueList from dbo_tbl_Custom_Subscription_Subscriptions where varReportTitle like '" & iVal & "'"


Me.txtvarExtensionSettingValueList.text = S
Me.txtvarExtensionSettingValueList.Requery
End Sub

[CJ_London]

not at all clear what the code is supposed to do, but a couple of comments:

if you are not using *, then use = rather than like

you can only refer to the text property if the control has the focus - and looks like it doesn't, focus is still with cbovarReportTitle. Do you mean to be assigning it to a rowsource of a combobox?

if you are trying to assign it to an unbound textbox to retrieve a value then that textbox's controlsource should be

=dlookup("varExtensionSettingValueList","dbo_tbl_C ustom_Subscription_Subscriptions"," varReportTitle = '" & [cbovarReportTitle] & "'")

however the use of dbo in a tablename implies a sql server backend, so not sure if dlookup will work

[ssanfu]

And a couple more comments...

Setting a variable (S) equal to a SQL string does not execute the SQL any more than setting a variable (S) equal to a string "100+20" will calculate (add) the numbers.
Therefore, as Ajax said, IF the text box had the focus, you would be setting the text property to the SQL string. The text property of a text box holds the uncommitted value - once the record is committed, the Value property holds the SQL string and the Text property is NULL (IIRC).

"Me.txtvarExtensionSettingValueList.Requery" does nothing - there is nothing to requery in a text box..

Use of "Like" - this does nothing because there is no wildcard before or after the variable. (Same as Ajax said, but worded different )

You could try this:

Private Sub cbovarReportTitle_AfterUpdate()
    Dim iVal As String
    Dim S As String
    Dim r As DAO.Recordset

    iVal = Nz(Me.cbovarReportTitle.Value, "")

    S = "SELECT varExtensionSettingValueList from dbo_tbl_Custom_Subscription_Subscriptions where varReportTitle = '" & iVal & "'"
    '    Debug.Print S
    Set r = CurrentDb.OpenRecordset(S)

    If Not r.BOF And Not r.EOF Then
        Me.txtvarExtensionSettingValueList.Text = S("varExtensionSettingValueList")
    End If

    r.Close
    Set r = Nothing
End Sub

[CJ_London]

If Not r.BOF And Not r.EOF Then
Me.txtvarExtensionSettingValueList.Text = S("varExtensionSettingValueList")
End If

Except drop the .Text

[orange]

AAAndy,

I see this is your first post. Welcome.
You will find that telling readers WHAT you are trying to accomplish in plain English, rather than How you have done something that isn't working as expected, you will improve communications and get more focused responses more quickly.

Good luck with your project.

[AAAndy]

All I was trying to do is lookup a variable in the database varExtensionSettingValueList and pass it into a text box called txtvarExtensionSettingValueList

The below code works great, thanks. I tried something similar before but it did not work. I was missing the [ ] at " & [cbovarReportTitle] & "'")

=dlookup("varExtensionSettingValueList","dbo_tbl_C ustom_Subscription_Subscriptions"," varReportTitle = '" & [cbovarReportTitle] & "'")


The below code gave an error even with the dropped .text
Private Sub cbovarReportTitle_AfterUpdate()
Dim iVal As String
Dim S As String
Dim r As DAO.Recordset

iVal = Nz(Me.cbovarReportTitle.Value, "")

S = "SELECT varExtensionSettingValueList from dbo_tbl_Custom_Subscription_Subscriptions where varReportTitle = '" & iVal & "'"
' Debug.Print S
Set r = CurrentDb.OpenRecordset(S)

If Not r.BOF And Not r.EOF Then
Me.txtvarExtensionSettingValueList = S("varExtensionSettingValueList")
End If

r.Close
Set r = Nothing
End Sub

[AAAndy]

I have about 10 different types of values I want to retrieve from the database one of them is a number intSubscriptionID and I want to put it into Me.txtintSubscriptionID. I added the code Me.txtintSubscriptionID = DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")
why would it give an error?
I also have some values that I want to retrieve into a combo box


Private Sub cbovarReportTitle_AfterUpdate()
Dim iVal As String
iVal = Nz(Me.cbovarReportTitle.Value, "")


Me.txtintSubscriptionID = DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")
Me.txtvarExtensionSettingValueList = DLookup("varExtensionSettingValueList", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")


End Sub

[CJ_London]

why would it give an error?

what error would that be? Could be a number of reasons - the error will tell you why

[AAAndy]

The error is

Run-time error '-2147352567 (80020009)':
You can't assign a value to this object

[CJ_London]

and I'm guessing the line highlighted is

Me.txtintSubscriptionID = DLookup("intSubscriptionID....

the error is telling you what the problem is which is my other guess that Me.txtintSubscriptionID is bound to an autonumber field (usually the primary key) and you can't assign values to autonumbers

[AAAndy]

and I'm guessing the line highlighted is

Me.txtintSubscriptionID = DLookup("intSubscriptionID....

the error is telling you what the problem is which is my other guess that Me.txtintSubscriptionID is bound to an autonumber field (usually the primary key) and you can't assign values to autonumbers

You are right. I can retrieve all the other values but not the primary key value intSubscriptionID

Is there anyway to retrieve it and display the value only without modifying it?

[CJ_London]

Is there anyway to retrieve it and display the value only without modifying it?

yes, as previously advised. In an unbound textbox put the following in the controlsource

= DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")

[AAAndy]

Is there anyway to retrieve it and display the value only without modifying it?

yes, as previously advised. In an unbound textbox put the following in the controlsource

= DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")

The below code gives that same error

Me.txtintSubscriptionID = DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")

[CJ_London]

for the third and final time

yes, as previously advised. In an unbound textbox put the following in the controlsource

= DLookup("intSubscriptionID", "dbo_tbl_Custom_Subscription_Subscriptions", " varReportTitle = '" & [iVal] & "'")

[AAAndy]

for the third and final time

Sorry I'm kind of new to access. I figured out what an unbound textbox is, but I'm still having problem with the controlsource code
I created a new unbound textbox with the name txtPrimaryNumber
attached picture is what I have for the control source which does not work. I have been researching this for a while. What would be the proper way?