Help with Form

[Ray67]

Hello


I have a table with payments collected every month for different providers. I would like my sure to view all payment by provider in a form. The user would conformed that all payments are correct. If one payment is not correct than he would check off a box. Once user is done and closes the form a new form would open only if a box was check off. When the new box opens it shows the data that was check off. Can this be done???




Thank you

[June7]

Seldom encounter something that can't be done. Will need code.

Use form/subform arrangement to view provider and related payments.

Need a field in payment record for the check box. Why would another form need to open?

[Ray67]

The form would be used to record any payments with error or payments that didn't make it . So the new form will have more fields. Example Reason for error, Comments.

[June7]

The trick is determining if any records on first form have been checked. Use VBA code to open the second form with filter criteria in WHERE argument, something like this might work:

Me.RecordsetClone.FindFirst "[error field name]=True"
If Me.RecordsetClone.NoMatch = False Then
DoCmd.OpenForm "secondformname", , , "[providerID]=" & Me.Provider & " AND [error field name]=True"
End If
DoCmd.Close acForm, Me.Name

[Ray67]

Can i use Data type Yes/No?? Should i put this VBA code in query SGL??? Everytime the user opens the first form the user needs patameter to just open one provider at a time. The patameter would have three fields Provider name, Date From and Date To. How can i set this up in the query that is linked to the first form ????

[Ray67]

Query name is Q_ReconciliationList
First form name is F_Reconciliationlistform(this is the one that is linked to query) How would i be able to link this one to the query????
Second form name is F_Fee Settlement Reconciling this form will have a sub form
Name for the parameter from is F_ReconciliationForm

I'm not sure if this would help.

Thank you

[Ray67]

Sorry But i got another question. When the second form open (F_Fee Settlement Reconciling) i want it to transfer information from the first form. The first form has the following fields that i would like it to transfer to the second form Provider, Receipt number,and Amount. So the information on the second form would be store in a new table.

Thank You

[June7]

How is the first form opened? Do you want it to open filtered to a specific Provider name, Date From, Date To combination or filter the recordset after the form opens?

I am not understanding data structure and form behaviors. Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Ray67]

I want the first form to open to specific filtered Provider name Date from and date to.

[June7]

I already showed one way, the WHERE argument in DoCmd.OpenForm. Another is query input parameter prompts in the form RecordSource.

Or you can build filter capability into the form with query parameters that refer to unbound combobox controls as input. Review http://datapigtechnologies.com/flash...tomfilter.html

[Ray67]

Reconciliationzip.zip

June 7

Here is a copy of my project. Please help me.

Thank You

[June7]

You stated: "If one payment is not correct than he would check off a box."

Which box on which form?

I suggest you change the RowSource for the Provider combobox to: SELECT T_DocInfo.ID, T_DocInfo.[Provider Name] FROM T_DocInfo;

[Ray67]

Thank you for your reply.

If one payment is not checked off than after closing form, new form would open. When new form opens than some of the fields data would transfer.
Your suggestion on the on the rowsource worked great.



Thank you

[June7]

Well, that sounds the opposite of your original statement. You want form to open with records for provider if any one of 4 checkboxes NOT checked?

You want a record created in T_FeeSettlementReconciling2 for the invoice numbers from the first form? Probably won't be easy.

I am not really understanding this project's purpose and what you are trying to do.

[Ray67]

Yes it is opposite form my original statement. SORRY. This is what I’m trying to do. In access my users enter all payments that have been collected for the month. Now at the end of the month we get fee settlement (just like a bank statement showing all payments). We need to compare fee settlement and access. The user would open F_ReconciliationList for the month (showing only payments for that month); compare both fee settlement and access. All payments that match the user would check off and if one payment doesn't match than user wouldn't check off. After user is done matching all payments than he would close the form and if one payment is not check off than F_Fee Settlement Reconciling would open. Now i would like for some off the data to be transfer from F_ReconciliationList to F_Fee Settlement Reconciling once the form opens Provider name, Date, Receipt number and Amount.