Open Records Search

  • Thread starter Thread starter dbl
  • Start date Start date
D

dbl

Hi I have a form called Incident Data which records all the details of our
claims, is it possible when updating the field "Bodyshop", the on lost focus
event function searches for any other claims that are still open with a
matching Registation Number. The search would be carried out on the field
"RegistationNumber" and the field "FileClosed" which is a tick box giving
True or False to verify if the record is closed or open.

If it can be done I would then want it to produce a small pop up form with
the CustomerID's of all the open records that matched the registration
number.

Any help on how I go about setting it up would be very much appreated.

Bob
 
Hi,

Firstly, I think this sort of code could go in the after update event,
rather than the lost focus. This way you can tab past the field without
changing the data and the code won't trigger a second time.

I would then open a form (for example called frmPopOtherCustomers), and set
it's recordsource to use the variables from the Incident Data form.

Something like

Sub After_update Bodyshop


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPopOtherCustomers"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

and then maybe in the on open event of the frmPopOtherCustomers, you could
set the recordset

On_Open frmPopOtherCustomers

Me!Recordsource = "SELECT * FROM tblCustomers " _
& "WHERE RegistationNumber Like [Forms]![Incident Data ]![RegistationNumber]
AND [Forms]![Incident Data ]![FileClosed] = False"

You may also want to handle the occurance of no data somehow.

Only my ideas, code syntax may also be incorrect, and I'm by far no expert.
Hope this helps.
 
Thanks Dylan I have now sorted it out.

Bob
Dylan Moran said:
Hi,

Firstly, I think this sort of code could go in the after update event,
rather than the lost focus. This way you can tab past the field without
changing the data and the code won't trigger a second time.

I would then open a form (for example called frmPopOtherCustomers), and
set
it's recordsource to use the variables from the Incident Data form.

Something like

Sub After_update Bodyshop


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPopOtherCustomers"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

and then maybe in the on open event of the frmPopOtherCustomers, you could
set the recordset

On_Open frmPopOtherCustomers

Me!Recordsource = "SELECT * FROM tblCustomers " _
& "WHERE RegistationNumber Like [Forms]![Incident
Data ]![RegistationNumber]
AND [Forms]![Incident Data ]![FileClosed] = False"

You may also want to handle the occurance of no data somehow.

Only my ideas, code syntax may also be incorrect, and I'm by far no
expert.
Hope this helps.

--
Dylan Moran - Melbourne Australia


dbl said:
Hi I have a form called Incident Data which records all the details of
our
claims, is it possible when updating the field "Bodyshop", the on lost
focus
event function searches for any other claims that are still open with a
matching Registation Number. The search would be carried out on the
field
"RegistationNumber" and the field "FileClosed" which is a tick box giving
True or False to verify if the record is closed or open.

If it can be done I would then want it to produce a small pop up form
with
the CustomerID's of all the open records that matched the registration
number.

Any help on how I go about setting it up would be very much appreated.

Bob
Click to expand...
Click to expand...
 
Thanks Dylan that works fine.

Regards Bob
Dylan Moran said:
Hi,

Firstly, I think this sort of code could go in the after update event,
rather than the lost focus. This way you can tab past the field without
changing the data and the code won't trigger a second time.

I would then open a form (for example called frmPopOtherCustomers), and
set
it's recordsource to use the variables from the Incident Data form.

Something like

Sub After_update Bodyshop


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPopOtherCustomers"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

and then maybe in the on open event of the frmPopOtherCustomers, you could
set the recordset

On_Open frmPopOtherCustomers

Me!Recordsource = "SELECT * FROM tblCustomers " _
& "WHERE RegistationNumber Like [Forms]![Incident
Data ]![RegistationNumber]
AND [Forms]![Incident Data ]![FileClosed] = False"

You may also want to handle the occurance of no data somehow.

Only my ideas, code syntax may also be incorrect, and I'm by far no
expert.
Hope this helps.

--
Dylan Moran - Melbourne Australia


dbl said:
Hi I have a form called Incident Data which records all the details of
our
claims, is it possible when updating the field "Bodyshop", the on lost
focus
event function searches for any other claims that are still open with a
matching Registation Number. The search would be carried out on the
field
"RegistationNumber" and the field "FileClosed" which is a tick box giving
True or False to verify if the record is closed or open.

If it can be done I would then want it to produce a small pop up form
with
the CustomerID's of all the open records that matched the registration
number.

Any help on how I go about setting it up would be very much appreated.

Bob
Click to expand...
Click to expand...
 
Back
Top