Combine & Search Querys for Report??

[mfleming]

Hello.

I'm have a database setup for our wedding. I have a form to enter RSVP responded (checkbox), RSVPInvited(##), RSVPAttending(##).

1st question.

1. I want to be able to disable the RSVPResponded(Checkbox) after I close down the form so I don't accidently uncheck it the next time I come into the form.
b) How do I add a running total to my report of the numbe of people responded?

2nd Question.

2. I want to make a validation rule in the form to check and make sure when I input the RSVPAttending(##) it is equal to or less than RSVPInvited(##).
b) How do I make a running total of RSVPAttending and RSVPInvited and add that to a form?

Question 2-b Addition:

I can make two querys one having all the FirstName, LastName, RSVPResponded, RSVPInvited, RSVPAttending, but I can't add the this "SumInvited: Sum(Addresses!RSVPInvited" ect. But If I create a new query from Address.Table I can enter "SumInvited: Sum(Addresses!RSVPInvited)" ect. and it comes out with the totals.

The only problem is I don't know how to combine both querys with these values to use in a Report?



I tried putting this into validation for the RSVPAttending:
=[RSVPAttending]=[RSVPInvited] Or [RSVPAttending]<[RSVPInvited]

But it doesn't work.

3rd question.
How to I add a search to my forms so I can input characters into the LastName spot and it will come close to the location of the person. (ie. just like when u use your address book in Outlook).



Thanks

Matt

 

[Trevil]

Re question 1:
Private Sub RSVPResponded_BeforeUpdate(Cancel As Integer)
Dim i As Integer
If Me.RSVPResponded = False Then
i = MsgBox("are you sure you want to uncheck this?", vbYesNo, "Uncheck this?")
If i = vbYes Then
Exit Sub
Else
Cancel = True
End If
End If
End Sub


You say in 1.b "running total to my report" Do you mean report or FORM?

Re Question 2, there are two ways to do this. Both involve the 'After Update' event for the RSVPAttending:

a. Run a query like the following and check if attending exceeds invited.
SELECT Sum(Wedding.RSVPInvited) AS Invited, Sum(Wedding.RSVPAttending) AS Attending
FROM Wedding;

or b. when form opens, run query to get # invited and save in a form variable (or in a text box in the form header or footer). Then after each update (or in the Before Update if you want to prevent) check your running total + this value against the # invited.


2b: You could have the after update event run your query to get counts and just set text boxes to latest results.
i. e.
Dim rs As DAO.Recordset
Dim sSQL as String
sSQL = "SELECT Sum(Wedding.RSVPInvited) AS Invited, Sum(Wedding.RSVPAttending) AS Attending
FROM Wedding;"
Set rs = currentdb.openrecordset(sSQL)
If rs.recordcount > 0 then
me.txtInvited = rs!Invited
me.txtAttending = rs!Attending
end if
rs.close
set rs = nothing

I don't understand question 2b

Question 3 -- if last name is bound to your recordset, you need another textbox (or better yet, a ComboBox based on list of names!) in the form header to allow a search.



"Have a great day today and a better day tomorrow!

 

[mfleming]

Thanks for all your help.

I can't get the search to work.

It works fine in my RSVP form, but then I tried to copy that into my Address form and it displays the names in the search but it doesn't update/goto that record.

(p.s. when I add new combo box to Address FORM I don't have the option to find records when it comes up with the three options in the combo box wizard)

Thanks

Matt

 

[PHV]

The Form must have a RecordSource (ie must be bound) to have the 3rd option in the combobox wizard.

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

 

[Trevil]

Using a combobox to search:
Private Sub cboSearchName_AfterUpdate()
Me.txtInviteeName.SetFocus
DoCmd.FindRecord cboSearchName, , True, , True
End Sub





"Have a great day today and a better day tomorrow!