CODEWITHSUNDEEP
sqldatabasems-access
maneesha
maneesha

Reputation: 685

Add an item from a query to a form

I'm on Access 2010.

I'm designing a database to help keep track of participants in a community program.

I have a table called ParticipantMaster that includes fields like ParticipantID, FirstName, LastName, Address, Phone, etc and an entry form built on this table.

I have a set of forms/tables that collect attendance data (with levels to show whether they attended PARTIAL, HALF, or FULL session)... the fields relevant to my question here include ParticipantID (from Participant Master), WorkshopID, Level.

From the attendance data I've created a query called Attendance_FullorHalf that counts how many Half or Full sessions each participant has attended. Fields include ParticipantID, CountStatus, FirstName, LastName. This query is working correctly.

Separate from attendance, I have a table and form called Interviews that logs every time I call a Participant. They only become eligible for interviews after their attendance Count > 2.

I want the attendance count to display on the interview logging form so I can see right there whether they are interview-eligible.

Maybe a text box that displays something like 

SELECT CountStatus from Attendance_FullorHalf 
WHERE ParticipantID (from Attendance_FullorHalf query) = ParticipantID (of the record I'm on in the interview form)

I know it's probably not a statement like that that I need -- I just wrote that to help explain my question.

So if I'm in the interview form, entering info on participant whose ID is 20-563 I want it to display on that form how many sessions participant number 20-563 has attended.

Hope my question is clear -- thank you so much!!

Upvotes: 0

Views: 116

Answers (1)

James Sinclair
James Sinclair

Reputation: 407

Surely in the forms oncurrent event you can write a query that pulls the attendance figures for that participants ID and then update a textbox with the result of the query.

Am I missing something here?

EDIT FOLLOWING FROM COMMENT BELOW:

create a text box on the interview logging form

create an onCurrent event for the form and choose code builder you should have a window that looks like below

Private Sub Form_Current()

End Sub

into this you'll need to write your code... something like this should give you a starting point to work from

Private Sub Form_Current()
    Dim rs as DAO.Recordset 'recordset object to hold the results of the sql query
    set rs = CurrentDB.OpenRecordset("INSERT YOUR SQL HERE")
    If rs.RecordCount > 0 Then 'we have a value for this participant
        txtBox.Text = rs![FIELDNAME] 'update the textboxes text value
    Else
        txtBox.Text = "0" 'else no value means 0
    End If
    set rs = nothing ' clean up the recordset after we've finished with it
End Sub

now the above is VERY VERY rough and dirty but the principle is fairly sound. open recordset, run your SQL against it, if the recordset now has results then update the textbox with those results.

hope that helps get you started

Upvotes: 1

Related Questions