getting Value of text box into string

[jms318]

Im in a form trying to get the value of a text box called txtGroupID
that contains a number ID of the group thats currently selected and convert it to a string for my sql where clause

if i manually set the ID in the where clause the other selection box loads the right data but i need this to be based on whats in the text box for dynamic use.
I guess I'm confused on how to get a string value of whats in a text box on a form

Any help would be most appreciated

I've surrounded the code that is giving me problems the *********

Private Sub Form_Load()
    
    Dim DB As Database
    Dim rst As Recordset
    Dim fld As String
  
    
    Set DB = CurrentDb()
'****************************
    Set fld = Me.txtGroupID.Text ' what im tring to get so on the load _ 
                                               ' and other functions for the where clause
    
 '***************************
    ' load the Query.
    Set rst = DB.OpenRecordset("SELECT tblGroupEmployeeLinks.GroupID, tblGroups.[Group Name], " _
                & "tblGroupEmployeeLinks.EmployeeID, tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.Clock " _
                & "FROM tblGroups INNER JOIN (tblEmployee INNER JOIN tblGroupEmployeeLinks ON tblEmployee.[SSN] = " _
                & "tblGroupEmployeeLinks.[EmployeeID]) ON tblGroups.[ID] = tblGroupEmployeeLinks.[GroupID]" _
                & "WHERE tblGroupEmployeeLinks.GroupID = " & fld & ";")
    
    Do While Not rst.EOF
    
        Me.lstEmpInGrp.AddItem rst![FirstName] & "  " & rst![LastName] & "   " & rst![Clock]
        rst.MoveNext
    Loop
    
End Sub

 

[JANR]

Set fld = Me.txtGroupID.Text

Don't use the text property, the control has to have focus for it to work.

Set fld = Me.txtGroupID

JR

 

[CBrighton]

You can drop the set and me too.

The following would work fine:

fld = txtGroupID

 

[vbaInet]

You can drop the set and me too.

The following would work fine:

fld = txtGroupID

To ambiguate or disambiguate

It is common practice and adviseable to do the latter for reference issues. Yes it would work but apart from the advantage of having Intellisense there are other reference advantages and one should get used to disambiguating.

 

[CBrighton]

Fair enough.

I'm self-taught in Access & VBA so my solutions work but aren't necessarily the "best way".

 

[vbaInet]

Fair enough.

I'm self-taught in Access & VBA so my solutions work but aren't necessarily the "best way".

Most of us on here are self taught (from what I gathered)

Good to have you on here by the way!