Assigning variable to a text box

[Nevy]

Hi, I want to write a piece of code.

Instead of writing "Me.cboOffice.Value" to refer to the textbox, can I assign a variable to it?

I tried to assign it to a string-variable, but It gave me an error.

Thanks.

 

[ghudson]

Dim x as String
x = "Test"

cboOffice = x

 

[modest]

Dim x as String
x = "Test"

cboOffice = x

I think that's backwards.

Dim strVar As String

strVar = Me.cboOffice.Value

 

[Nevy]

So what's the difference between:

strVar = Me.cboOffice.Value

and

set strVar = Me.cboOffice.Value

Because I'm getting a compile error: "ByRef argument by type mismatch".

This happens when I send the value to a function.

 

[Mile-O]

Post your full code

 

[Nevy]

This is part of the code, it's going to be used to create a query that is used by the report.

Private Sub btnGetReport_Click()
' Declare variables
    Dim dbs As DAO.Database
    Dim qdf1 As DAO.QueryDef
    Dim qdf2 As DAO.QueryDef
    Dim varItem1 As Variant
    Dim strNamesCriteria As String
    Dim strModelCriteria As String
    Dim strFrMonth As Variant
    Dim strToMonth As String
    Dim strFrYear As String
    Dim strToYear As String

' Get the database and stored query
    Set dbs = CurrentDb()
    Set qdf1 = db.QueryDefs("Q1")
    Set qdf2 = db.QueryDefs("Q2")
        strToMonth = Me.cbToMonth.Value
        strFrMonth = Me.cbFromMonth.Value
        strToYear = Me.cbToYear.Value
        strFrYear = Me.cbFromYear.Value

' Loop through the selected items in the list box and build a text string
    For Each varItem1 In Me!lstNames.ItemsSelected
        strNamesCriteria = strNamesCriteria & ",'" & Me!lstNames.ItemData(varItem1) & "'"
    Next varItem1
    
' Loop through the selected items in the list box and build a text string
    For Each varItem2 In Me!lstNames.ItemsSelected
        strModelCriteria = strModelCriteria & ",'" & Me!lstNames.ItemData(varItem2) & "'"
    Next varItem2
    
' Remove the leading comma from the string
    strNamesCriteria = Right(strNamesCriteria, Len(strNamesCriteria) - 1)
    strModelCriteria = Right(strModelCriteria, Len(strModelCriteria) - 1)
     
' Get's the SQL string
    If (strFrMonth = strToMonth) And (strFrYear = strToYear) Then
        qdf1 = getSQLstr(strModelCriteria, strNamesCriteria, strFrMonth, strFrYear)
        ' Apply the new SQL statement to the query
        qdf1.SQL = strSQL1
    Else
        qdf1 = getSQLstr(strModelCriteria, strNamesCriteria, strFrMonth, strFrYear)
        qdf2 = getSQLstr(strModelCriteria, strNamesCriteria, strToMonth, strToYear)
        ' Apply the new SQL statement to the query
        qdf1.SQL = strSQL1
        qdf2.SQL = strSQL2
    End If
' Empty the memory
    Set dbs = Nothing
    Set qdf1 = Nothing
    Set qdf2 = Nothing

End Sub
'*******
' Build the new SQL statement incorporating the string
'*******
Private Function getSQLstr(a As String, b As String, c As String, d As String) As String

    Dim strSQL As String
    Dim strLgnA As String
    Dim strLgnB As String
    Dim strLgnC As String

 

[modest]

Don't use set in this case.

"Set" is used to bind an object to a variable of the same object type. You're getting an error because it is not the same object type. The variable is used as a pointer to that object.

Dim objTextBox As Object
Set objTextBox = Me.TextBoxName

you can now change Me.TextBoxName's value 2 ways:
Me.TextBoxName.Value = "new value" or
objTextBox.Value = "new value
They both reference the same textbox

However, when Set uses the "New" keyword, it creates a whole new object.



The helpfile explains this pretty well:

Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created. More than one object variable can refer to the same object. Because such variables are references to the object rather than copies of the object, any change in the object is reflected in all variables that refer to it. However, when you use the New keyword in the Set statement, you are actually creating an instance of the object.

 

[modest]

When you're just working with the value, you don't need the object (just what's inside).

So
Dim strValue As String
strValue = Me.cboOffice.Value

This stores what's inside the text area of the ComboBox to a string variable. If you wanted to change what's inside the text area using code you would do:
Me.cboOffice.Value = "something else"

or if the combobox has focus:
Me.cboOffice.Text = "something else"


---just play with it and you'll soon understand.

Hope this helps,
Modest

 

[Nevy]

I see, thanks.