form based on query - criteria change at runtime ??

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

I have a form where the user can insert payment for an invoice - it selects
the correct invoice# as i set it in the query the form is based on - fine so
far.

now when user selects register 'reminder' to write a reminder - and then
selects for some reason the payment form, I would like that the current
invoice# is already selected on the payments form.

I've tried various things but do not seem to be on the right track ....
maybe someone can point me into the right direction ?

TIA
Gina
 
What I would recommend is that you create a filter for that form in the
properties window, under the data tab. Create the filter to be based upon
the invoice number field in the payments form. So, depending upon your field
and form names the filter would look something like this:
reminder_invoice_number= forms!payments!payment_invoice_number

I don't know if you use this reminder form for any other purpose but what
you have described here. If you do use it for something else, you would need
to learn how to use VBA to tell the form to open with the filter on or off,
depending upon why you are opening it. Let me know if you would need help
with that.

Andrea
 
Thanks Andrea for your answer.

Well .. I only use the remineder form to write the reminders but when it
comes to users mind that a customer has already paid but the payment has not
been registered I thought it might be useful when a user then clicks on the
page tab and the payment form is then the 'active' one the corresponding
record to the one in the reminders form should already be 'there' ready for
any further input.

For now the record is filtered by a query together with a fields criteria
from the main form. (that I wanted to keep anyway ) ... but in addition when
a user goes back to page 3 (frmPayment) the criteria should be the invoice#
of page 4 .....

I removed the criteria but then the first of all records is shown ....

this is part of what I got on Register1_Change - but doesn't work ....

Select Case Me!Register1.Value

Case 3
Me.Modal = True
Form_frmInvoiceForPayment.Filter = "Invoice# = " &
Form_frmForReminder.Invoice#
Form_frmInvoiceForPayment.Filter = True
'Form_frmInvoiceForPayment.Requery
'Form_frmInvoiceForPayment.Refresh
Form_frmInvoiceForPayment.Repaint

End Select

the invoice# is shown correctly when stepping through the code ... but the
form is loaded/opened together with the register pages containing other
forms ... if I can get that across what I mean - think it's bit confusing
....

I definitely think I would need some vba and it would be very kind if you
have some links where some samples are shown.
thanks a lot !!

Gina
 
Gina,

I’m sorry but I am having a difficult time following what you are
describing. I’m going to take a stab though at what you may be going for.
If you want to have the frmPayment
automatically find the correct invoice number when it becomes active but not
filter out the other records, what you need to do is use VBA to control the
bookmark of the form.
Here is an example of code that allows you to find the record that you want
displayed in a form and then telling the form to display it:

Private Sub frmPayment_current()
' Find the record that matches a control on another form.
Dim rec As Object

‘ Sets the recordset object to be a clone of the form’s underlying recordset
Set rec = Me.Recordset.Clone
‘ Using the clone of the form’s recordset, finds the record that matches the
invoice # on Page 4
rec.FindFirst "[invoice_number] = " & forms!frm_page4!invoice_number
‘ if a match for the invoice # is found, sets the form’s bookmark to be the
same as the bookmark for the clone recordset
If Not rec.EOF Then Me.Bookmark = rec.Bookmark
rec.close
End Sub

You would put this in the page 3’s current event, under the form’s properties.

Is this what you are looking for?

Andrea
 
Sorry, Andrea for not answering straight away .....
had to do some other things as well ...

had a thought with a fresh morning brain .... and changed the recordsource
for the payment form to be a certain sql statement with criteria reminders
invoice# (in case the reminder tab was before selected by the user )
otherwise use other invoice# (that one from main form) ....

I had to totally get rid of the hard coded access query in the properties of
the payment form ...

when it finally worked I was (and still am) very convinced there MUST be an
easier way ...

well yes .... I think I was looking for that recordset clone thing ....

big thanks for your help and ... for understanding my awkward explanations !

Gina
 
Back
Top