Texbox.value = Query (1 Viewer)

[sargon]

I can't figure out what's wrong with this SQL statement. I need to show on texbox Name&Surname...

Private Sub Form_Current()

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT Old.EmpD, Emp.Name, Emp.Surname, Old.Place FROM Emp INNER JOIN Old ON Emp.EmpID = Old.EmpID WHERE (((Emp.Name) Is Not Null) AND ((Old.Place)=[Forms]![Structures]![SiteID]) AND ((Old.Function)="2") AND ((Old.UpTo) Is Null));"

Set rs = CurrentDb.OpenRecordset(strSQL)
Text22.Value = rs

End Sub

 

[Minty]

You need to specify what fields you want from the recordset:

Me.Text22 = rs.Fields("Name") & " " & rs.Fields("Surname)

But this is a pretty long winded way of doing this.

Do you not get an error trying to open that recordset?

 

[cheekybuddha]

You will probably have to concatenate the value of [Forms]![Structures]![SiteID] as well.

Also, the double quotes around "2" will mess up your VBA string. Is this field actually a text datatype, or is it numeric?

Can the query return more than one record?

Perhaps adjust:

Private Sub Form_Current()

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT Emp.Name & ' ' & Emp.Surname " & _
         "FROM Emp " & _
         "INNER JOIN Old "
         "  ON Emp.EmpID = Old.EmpID " & _
         "WHERE Emp.Name Is Not Null " & _
         "  AND Old.Place = " & [Forms]![Structures]![SiteID] & _
         "  AND Old.Function = 2" & _
         "  AND Old.UpTo Is Null;"

With CurrentDb.OpenRecordset(strSQL)
  Me.Text22 = .Fields(0)
  .Close
End With

End Sub

 

[ebs17]

What's being done here is playing pinball, wiggling the lamb's tail. Much too restless, and when a few real tasks come along, it's waiting for errors.

Why aren't Name and Surname simply included in the form's RecordSource?

Or is this just an exercise to pass the time and for your own amusement?

 

[Gasman]

Might want to Debug.Print your SQL string before trying to use it?

 

[sargon]

What's being done here is playing pinball, wiggling the lamb's tail. Much too restless, and when a few real tasks come along, it's waiting for errors.

Why aren't Name and Surname simply included in the form's RecordSource?

Or is this just an exercise to pass the time and for your own amusement?

I am working with a database that I did not create and that I do not fully understand.

On the current form, the operations at a work point are displayed, and my boss wants the name of the person in charge to appear on this form, which he obtains from Old as a subform of Emp.

 

[ebs17]

the operations at a work point are displayed

You know that a form has a RecordSource. This can also use a select query instead of a table. In a selection query you can link several tables and thus combine the contents of several tables into records of the query. This means you would have your information in the form immediately and without any further action via VBA.

The point to note here: using a query instead of a table may lose updatability. So you would have to decide for yourself whether you just want to LOOK at the data provided in the form or whether you need to do additional editing. For the second case, there are corresponding strategies for implementation, which always depend on the required way of working in the application.

Note on the above code solution:
Form_Current is a very dynamic event. Especially in connection with the main form and subform, it can be triggered multiple times by a trigger. Due to the interaction of several forms and several VBA codes used (with Requery & Co.), uncontrollable situations can arise quickly and easily. I would therefore recommend using Form_Current very sparingly.