Populate Text box from combo boxes

[Jontmke]

I want to populate a Text box with the results of a query where the parameters are gotten from 2 combo boxes.

The combo box row sources are this:

SELECT DISTINCT [200_Server_Jobs].[Day Started] FROM 200_Server_Jobs;

AND

 SELECT DISTINCT [200_Server_Jobs].[Hour Started] FROM 200_Server_Jobs;

I have a query that works fine:

SELECT Count([200_Server_Jobs].[Client]) AS QActivejobs
FROM 200_Server_Jobs
WHERE ((([200_Server_Jobs].[Day Started])<=([Forms]![jobsrunning]![CmbDay])) AND (([200_Server_Jobs].[Hour Started])<=([Forms]![jobsrunning]![CmbHour])) AND (([200_Server_Jobs].[Day Ended])>=([Forms]![jobsrunning]![CmbDay])) AND (([200_Server_Jobs].[Hour Finished])>=([Forms]![jobsrunning]![CmbHour])));

This query basically says: Give me what jobs started before the Day And Hour specified AND finished after. Or in other words, the total number of jobs running at that time.

I just don't know enough to get the results to not pop up in a query but to populate a textbox on the form. I understand it is something about putting the query in VB code but I am just a newbie in VB\Access.
I would appreciate any assistance.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[PHV]

Take a look at the DCount function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[originalxavier]

Dim Jobs as Integer
Dim FillTextBox as string

Jobs = DCOUNT("[Client]", "200_Server_Jobs", "[Day Started] _
 <= '" & Forms![jobsrunning]!CmbDay & "' AND '" (enter remaining critera) AND... AND ...)

[FillTextBox] = Jobs

In that example, FillTextBox is going to be the text box that you want to have filled in on the form. The only thing that you need to do is to figure out how to call the code above. For example, you can call it when the users tab into or click on the FillTextBox by pasting the above code into the On Got Focus event...

Let me know how this works out for you...

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning"
Rick Cook
----------------------------------------

 

[Jontmke]

OK, this is what I came up with:

Private Sub TxtJobResults_GotFocus()
Dim jobs As Integer
Dim TxtJobResults As String

jobs = DCount("[Client]", "200_Server_Jobs", "[Day Started]" _
 <= " & Forms![jobsrunning]!CmbDay & " And "[Hour Started]" _
 <= " & Forms![jobsrunning]!CmbHour & " And "[Day Ended]" _
 >= " & Forms![jobsrunning]!CmbDay & " And "[Hour Finished]" _
 <= " & Forms![jobsrunning]!CmbHour & ")

[TxtJobResults] = jobs

        
End Sub

Where the Text box is named "TxtJobResults". I do not get an error but I do not get any data in the box either. The other code I put in was this in the second combo box (CmbHour).

Private Sub CmbHour_Change()
Forms!jobsrunning!TxtJobResults.SetFocus
End Sub

I figured that once the user made a change in the combo box it would set the focus to the text box, then the On Got Focus routine would run. But I must have made an improper assumption.

Thanks for the codelet, I really am a novice at this. I actually am a server guy and this data is from our backup system.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[PHV]

And this ?
jobs = DCount("Client", "200_Server_Jobs", "[Day Started]<=" _
& Forms![jobsrunning]!CmbDay & " And "[Hour Started]<=" _
& Forms![jobsrunning]!CmbHour & " And "[Day Ended]>=" _
& Forms![jobsrunning]!CmbDay & " And "[Hour Finished]>=" _
& Forms![jobsrunning]!CmbHour)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244