How to populate a textbox with a SQL Select statement

[JasonMann1979]

I'm trying to do the following.



I have a text box named Last_Check on a form.

On that form load I want to use the following SQL statement to populate that text box.

SELECT TOP 1 tbl_QA_Check_Sheets.Machine, tbl_QA_Check_Sheets.The_Date, tbl_QA_Check_Sheets.Time
FROM tbl_QA_Check_Sheets
ORDER BY tbl_QA_Check_Sheets.ID DESC;


I've tried everything I can think of and can't get it to work.

Thanks!

[pbaldy]

You can open a recordset on that SQL and get the value from there.

[June7]

AFAIK, doesn't work because can't populate a textbox with a query result.

Do a saved query object that returns the TOP 1 for each group then include that query in the form/report RecordSource

If joining query in the form or report RecordSource is not appropriate, an expression using DLookup against that query might serve.

[Dal Jeanis]

What is it that you actually want in the text box? You're returning three values...

It looks like you just want a value from the record with the MAX ID from tbl_QA_Check_Sheets.

You can easily run some VBA to acquire and load that value. The question is, what is that information doing for the user? If the user just needs to know the machine, date and time of the last check that anyone did on any machine, then put three text boxes up there, inactive so they can't be changed, and load the information either at load, on current, or at whatever events make sense.

On the other hand, if that information is there for a functional reason, then knowing a little more about how you plan to use it will help us give you more useful advice.

[JasonMann1979]

What is it that you actually want in the text box? You're returning three values...

It looks like you just want a value from the record with the MAX ID from tbl_QA_Check_Sheets.

You can easily run some VBA to acquire and load that value. The question is, what is that information doing for the user? If the user just needs to know the machine, date and time of the last check that anyone did on any machine, then put three text boxes up there, inactive so they can't be changed, and load the information either at load, on current, or at whatever events make sense.

On the other hand, if that information is there for a functional reason, then knowing a little more about how you plan to use it will help us give you more useful advice.

I'm trying to show the newest record added to a table. I need to show three fields; Machine, Date and time so I can show the user the last machine they checked on a date at a time. I thought I had it using DLast, but I found out a few days later that I wasn't actually getting the newest record from the table. This is what I had that I thought was working, but it seems to show a random record. Using my query above I was able to get the values I need, but I need to be able to show those values to the user on the form that they use. Thanks guys.


Private Sub Form_Load()




On Error GoTo Err_Handler
On Error Resume Next


Dim Mach As String
Dim DTE As String
Dim TME As String


Mach = DLast("Machine", "tbl_QA_Check_Sheets")
DTE = DLast("The_Date", "tbl_QA_Check_Sheets")
TME = DLast("Time", "tbl_QA_Check_Sheets")


Me.Last_Check = Mach & " on " & DTE & " at " & TME


Exit_Err_Handler:
Exit Sub


Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler



End Sub

[JasonMann1979]

Here is a screenshot of the form my users use to give you a better idea of what I'm trying to do.

[JasonMann1979]

I got it. I ended up using a Dlookup and pointing it to my query.

Private Sub Form_Load()




On Error GoTo Err_Handler
On Error Resume Next


Dim Mach As String
Dim DTE As String
Dim TME As String




Mach = DLookup("Machine", "qry_QA_Checks_Last")
DTE = DLookup("The_Date", "qry_QA_Checks_Last")
TME = DLookup("Time", "qry_QA_Checks_Last")


Me.Last_Check = Mach & " on " & DTE & " at " & TME


Exit_Err_Handler:
Exit Sub


Err_Handler:
MsgBox Err.Description
Resume Exit_Err_Handler


End Sub

[Dal Jeanis]

I was thinking something more like this, but that should work.

Dim IDMax AS Long
Dim Mach As String
Dim DTE As String
Dim TME As String

IDMax = Dmax("[ID]","[tbl_QA_Check_Sheets]")
Mach = DLookup("[Machine]", "[tbl_QA_Check_Sheets]","[ID]=" & IDMax )
DTE = DLookup("[The_Date]", "[tbl_QA_Check_Sheets]","[ID]=" & IDMax )
TME = DLookup("[Time]", "[tbl_QA_Check_Sheets]","[ID]=" & IDMax )

[pbaldy]

I was thinking of making 1 trip to the data instead of several:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT TOP 1 ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Textbox = rs!FieldName & ...

  set rs = nothing
  set db = nothing

[JasonMann1979]

I like the idea of one trip and no queires too. I am going to play around with the code above to see if I can get it to work. When I did this before I kept getting #name?

[Dal Jeanis]

I thought about that, too, but it seems a bit of overhead to build a recordset just for a single read.

My rule in this kind of situation is: Do (1) whatever works (2) that will be blatantly obvious to me when I read it a year from now.

[pbaldy]

I thought about that, too, but it seems a bit of overhead to build a recordset just for a single read.

I agree, but you're making 4 reads, not 1; each domain aggregate function reads the table separately. I'm not saying the difference will be huge; it may not even be noticeable. I just like to use the more efficient methods throughout my apps, on the assumption that many small gains will lead to an overall more efficient app.