Probably an easy answer - Form and update question

[Andy Proctor]

I hope there is an answer out there....

I have a simple database structured like this (non relevant tables and
fields omitted)

Members table
memberID
memberFname
memberLname
memberNokID
memberDob

Next of Kin table
nokID
nokFname
nokLname
nokAddress
nokTown
nokCounty

memberID and nokID are primary keys and autonumber for the tables.
memberNokID is a foriegn key in the members table for the nokID in the
next of kin table.

I then have a single form where users enter data for the member and
the next of kin (also there are school, class, doctor etc but not
relevant to this issue)

What I am trying to achieve is when a user types in the address line
it checks the nextofkin table to determine if the address is already
in the database and if so populate the values from that address into
the other fields. I can do this with some code for the beforeupdate
event:

Extract >>
strAddress = [nokAddress]
strLookup = DLookup("nokAddress", "nextofkin", "nokAddress='" &
strAddress_ & "'")

I can test this for a match and populate the other fields on the form
ok with
If (Not IsNull(strTown)) Then Me![nokTown] = strTown
Of course what i really need to do is to insert the nokID of the
existing record into the memberNokID value for the members table, that
way the same address only appears once in the table. (no duplication)

When I try the code

If (Not IsNull(intNOK)) Then Me![memberNokID] = intNOKID

I get error 3341 and a message stating the current field must match
the join key nokID.

I am stumped and have got into a mess trying to fix this one! Could it
be something to to with positioning the current (focussed?) field or
am I going about this the completely wrong way. I DONT want to use a
combo box with a noinlist event, but a way to automatically test if
the Next of Kin is already listed and use the listing as a reference
in the members table to avoid duplication in the next of kin table.

It is possible that > 1 member have the same next of kin and also that
there are > 1 last names for next of kin which are the same. The only
unique thing is the address line.

Full code I'm using is:

Private Sub nokAddress_BeforeUpdate(Cancel As Integer)
Dim strLookup, strAddress, strTown, strCounty, strPostcode As String
Dim intNOKID As Integer
Dim updRecord As Byte

strAddress = [nokAddress]
strLookup = DLookup("nokAddress", "nextofkin", "nokAddress='" &
strAddress & "'")
If strAddress = strLookup Then

updRecord = MsgBox("A member next of Kin at this address is
already listed. Use the existing data?", vbOKCancel, "Record
Modification")
If updRecord = vbCancel Then
Cancel = True
End If
intNOKID = DLookup("nokId", "nextofkin", "nokAddress='" &
strAddress & "'")
strTown = DLookup("nokTown", "nextofkin", "nokAddress='" &
strAddress & "'")
strCounty = DLookup("nokCounty", "nextofkin", "nokAddress='" &
strAddress & "'")
strPostcode = DLookup("nokPostcode", "nextofkin",
"nokAddress='" & strAddress & "'")
If (Not IsNull(intNOK)) Then Me![memberNokID] = intNOKID
If (Not IsNull(strTown)) Then Me![nokTown] = strTown
If (Not IsNull(strCounty)) Then Me![nokCounty] = strCounty
If (Not IsNull(strPostcode)) Then Me![nokPostcode] =
strPostcode

End If

End Sub
Thanks in advance for any suggestions.

Andy Proctor

[rkc]

"Andy Proctor" <ap******@hotmail.com> wrote in message
news:dd**************************@posting.google.c om...

I hope there is an answer out there....

I have a simple database structured like this (non relevant tables and
fields omitted)

Members table
memberID
memberFname
memberLname
memberNokID
memberDob

Next of Kin table
nokID
nokFname
nokLname
nokAddress
nokTown
nokCounty

memberID and nokID are primary keys and autonumber for the tables.
memberNokID is a foriegn key in the members table for the nokID in the
next of kin table.

I then have a single form where users enter data for the member and
the next of kin (also there are school, class, doctor etc but not
relevant to this issue)

What I am trying to achieve is when a user types in the address line
it checks the nextofkin table to determine if the address is already
in the database and if so populate the values from that address into
the other fields. I can do this with some code for the beforeupdate
event:

Extract >>
strAddress = [nokAddress]
strLookup = DLookup("nokAddress", "nextofkin", "nokAddress='" &
strAddress_ & "'")

I can test this for a match and populate the other fields on the form
ok with
If (Not IsNull(strTown)) Then Me![nokTown] = strTown>
Of course what i really need to do is to insert the nokID of the
existing record into the memberNokID value for the members table, that
way the same address only appears once in the table. (no duplication)

When I try the code

If (Not IsNull(intNOK)) Then Me![memberNokID] = intNOKID

I get error 3341 and a message stating the current field must match
the join key nokID.

I am stumped and have got into a mess trying to fix this one! Could it
be something to to with positioning the current (focussed?) field or
am I going about this the completely wrong way. I DONT want to use a
combo box with a noinlist event, but a way to automatically test if
the Next of Kin is already listed and use the listing as a reference
in the members table to avoid duplication in the next of kin table.

It is possible that > 1 member have the same next of kin and also that
there are > 1 last names for next of kin which are the same. The only
unique thing is the address line.

Full code I'm using is:

Private Sub nokAddress_BeforeUpdate(Cancel As Integer)
Dim strLookup, strAddress, strTown, strCounty, strPostcode As String
Dim intNOKID As Integer
Dim updRecord As Byte

strAddress = [nokAddress]
strLookup = DLookup("nokAddress", "nextofkin", "nokAddress='" &
strAddress & "'")
If strAddress = strLookup Then

updRecord = MsgBox("A member next of Kin at this address is
already listed. Use the existing data?", vbOKCancel, "Record
Modification")
If updRecord = vbCancel Then
Cancel = True
End If
intNOKID = DLookup("nokId", "nextofkin", "nokAddress='" &
strAddress & "'")
strTown = DLookup("nokTown", "nextofkin", "nokAddress='" &
strAddress & "'")
strCounty = DLookup("nokCounty", "nextofkin", "nokAddress='" &
strAddress & "'")
strPostcode = DLookup("nokPostcode", "nextofkin",
"nokAddress='" & strAddress & "'")
If (Not IsNull(intNOK)) Then Me![memberNokID] = intNOKID
If (Not IsNull(strTown)) Then Me![nokTown] = strTown
If (Not IsNull(strCounty)) Then Me![nokCounty] = strCounty
If (Not IsNull(strPostcode)) Then Me![nokPostcode] =
strPostcode

End If

End Sub

So you have a single form based on a query that joins the Members
and NextOfKin tables? What's the query look like? What happens
if you enter nothing but a value for memberNokID in a new record
and move out of the textbox for the field?

I'm guessing the rest of the fields are filled in automagically.

[Andy Proctor]

>

So you have a single form based on a query that joins the Members
and NextOfKin tables? What's the query look like? What happens
if you enter nothing but a value for memberNokID in a new record
and move out of the textbox for the field?

I'm guessing the rest of the fields are filled in automagically.

Thanks for the reply. Yes you are correct. (query posted below) If I
just do that then yes the fields get filled in as normal and all is
ok, but that's just using id numbers and not the address. I am not
sure if the way I'm addressing the solution is valid though as I've
made no progress.

What would be the way to get the data entry checked against the
nextofkin table on the address line? Maybe I do need to have a combo
box after all, ??

Here is the query for the form: (this is the full query including the
fields/tables I left out of the original post)

SELECT [members].[memberID], [members].[memberFname],
[members].[memberLname], [members].[memberDob], [members].[memberSex],
[members].[memberDateJoined], [members].[memberNokID],
[members].[memberMedical], [members].[memberSchoolId],
[members].[memberDoctorId], [members].[memberStandard],
[members].[memberDocName], [nextofkin].[nokID],
[members].[memberClassId], [nextofkin].[nokTitle],
[nextofkin].[nokFname], [nextofkin].[nokLname],
[nextofkin].[nokAddress], [nextofkin].[nokTown],
[nextofkin].[nokCounty], [nextofkin].[nokPostcode],
[nextofkin].[nokPhoneNo], [nextofkin].[nokMobileNo],
[nextofkin].[nokEmail], [schools].[schoolName],
[schools].[schoolAddress], [schools].[schoolTown],
[schools].[schoolCounty], [schools].[schoolPostcode],
[schools].[schoolPhoneNo], [doctors].[doctorSurgeryName],
[doctors].[doctorAddress], [doctors].[doctorTown],
[doctors].[doctorCounty], [doctors].[doctorPostcode],
[doctors].[doctorPhone], [classes].[classType], [classes].[classDay],
[classes].[classTime], [standards].[memberStandard] FROM standards
INNER JOIN (schools INNER JOIN (nextofkin INNER JOIN (doctors INNER
JOIN (classes INNER JOIN members ON
[classes].[classId]=[members].[memberClassId]) ON
[doctors].[doctorId]=[members].[memberDoctorId]) ON
[nextofkin].[nokID]=[members].[memberNokID]) ON
[schools].[schoolId]=[members].[memberSchoolId]) ON
[standards].[memberStandardId]=[members].[memberStandard];
Any help gratefully received! I think that I'm not seeing the wood for
the trees and that the answer is there but I'm not seeing it.

Thanks

Andy

[rkc]

"Andy Proctor" <ap******@hotmail.com> wrote in message
news:dd**************************@posting.google.c om...

So you have a single form based on a query that joins the Members
and NextOfKin tables? What's the query look like? What happens
if you enter nothing but a value for memberNokID in a new record
and move out of the textbox for the field?

I'm guessing the rest of the fields are filled in automagically.
Thanks for the reply. Yes you are correct. (query posted below) If I
just do that then yes the fields get filled in as normal and all is
ok, but that's just using id numbers and not the address. I am not
sure if the way I'm addressing the solution is valid though as I've
made no progress.

What would be the way to get the data entry checked against the
nextofkin table on the address line? Maybe I do need to have a combo
box after all, ??

Here is the query for the form: (this is the full query including the
fields/tables I left out of the original post)

<snip query>
Any help gratefully received! I think that I'm not seeing the wood for
the trees and that the answer is there but I'm not seeing it.

I can't tell you how to re-work your query so that your problem
goes away. I can tell you that if you enter the address in an unbound
textbox and then do the lookup that the sympton goes away. But it
only goes away if none of the other bound fields have been entered
before hand.

You might want to try breaking up your query and using one or more
subforms to do your entry.

[Andy Proctor]

> I can't tell you how to re-work your query so that your problem

goes away. I can tell you that if you enter the address in an unbound
textbox and then do the lookup that the sympton goes away. But it
only goes away if none of the other bound fields have been entered
before hand.

You might want to try breaking up your query and using one or more
subforms to do your entry.

Thanks for the help. I think there was a flaw in my fundemental design of
the forms to be honest. I have messed around with a few today and the nokID
was already allocated to the form record before I check the data so when
trying to set the nokID value no wonder there is an error!! duh! Stupid boy!

I have to reorganise my structure to try and strip out the unique part of
the data..i.e. the address info into a separate table I think, and then I
can check on that. Thats the theory anyway. When I check on it I can use the
notinlist event I hope. I'm going to search the group now to see if i can
find a way to trigger the search on say only after the first 2 characters of
the address are entered as then if the user enters 34 as the house number,
if any other 34's exist is will look for them.

Any thoughts?

Thanks

Andy