Searching Across Two Tables For A Record

[Brenda]

Curious to know if anyone could provide help for the below code.

I have a form called Members, a table called Members and a table called
Members_Banned. New members are entered through the Members form are stored
in the Members table and banned members are in Members_Banned table through
non use of a form. I had the Members form and Members table successfully
working with everything inbetween the ** notations below (these are not part
of the code but used here just to direct attention) omitted. I had a need to
separate to another table for banned members and I added this code to search
across two tables and display information into the members form when a record
is found. Omit the code and all works well as it should. If there is a
duplicate record the code will erase the new record entry attempt and display
the existing record once message of duplicate record found message is
acknowledged. If there is no duplicate record the new member data is stored
in the Members table.

When I run the code with the newly added code between the ** below I get to
the point to where the code finds the records in the Members table and
displays information as it should but when it searches but whe the record is
in the Members_Banned table it gives me just the message saying a record
exists and clears the entry but does not show me the record. I can only
assume this is because the form is tied to the Members table as a data source.

I feel there is a simple bit of code I am missing that would allow this.
Using the form I need all information ( new ) to dump into the Members table
and nothing into the Members_Banned table. That table is solely for keeping
information from the past and is not updatable through the use of a form. I
just need the code to go there and display the information that is in the
table into the Members form.

Thanks in advance!

Brenda


Private Sub State_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.State_ID.Value
stLinkCriteria = "[State_ID]=" & "'" & SID & "'"

'Check Members table for duplicate State ID number
If DCount("State_ID", "Members", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

** 'Check Banned Members table for duplicate State ID number
ElseIf DCount("State_ID", "Members_Banned", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
** Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[Arvin Meyer [MVP]]

Before you worry about the code, you should fix the design flaw which has 2
tables doing the work of 1. Adding a single Yes/No field to the Members
table will suffice to ban a member. Your form can change recordsources to
queries that show or hide the banned members, more easily than trying to use
2 tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Curious to know if anyone could provide help for the below code.

I have a form called Members, a table called Members and a table called
Members_Banned. New members are entered through the Members form are
stored
in the Members table and banned members are in Members_Banned table
through
non use of a form. I had the Members form and Members table successfully
working with everything inbetween the ** notations below (these are not
part
of the code but used here just to direct attention) omitted. I had a need
to
separate to another table for banned members and I added this code to
search
across two tables and display information into the members form when a
record
is found. Omit the code and all works well as it should. If there is a
duplicate record the code will erase the new record entry attempt and
display
the existing record once message of duplicate record found message is
acknowledged. If there is no duplicate record the new member data is
stored
in the Members table.

When I run the code with the newly added code between the ** below I get
to
the point to where the code finds the records in the Members table and
displays information as it should but when it searches but whe the record
is
in the Members_Banned table it gives me just the message saying a record
exists and clears the entry but does not show me the record. I can only
assume this is because the form is tied to the Members table as a data
source.

I feel there is a simple bit of code I am missing that would allow this.
Using the form I need all information ( new ) to dump into the Members
table
and nothing into the Members_Banned table. That table is solely for
keeping
information from the past and is not updatable through the use of a form.
I
just need the code to go there and display the information that is in the
table into the Members form.

Thanks in advance!

Brenda


Private Sub State_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.State_ID.Value
stLinkCriteria = "[State_ID]=" & "'" & SID & "'"

'Check Members table for duplicate State ID number
If DCount("State_ID", "Members", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

** 'Check Banned Members table for duplicate State ID number
ElseIf DCount("State_ID", "Members_Banned", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
** Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[Brenda]

Arvin,

I have that capability now but in a diferent from. I have a drop down box
that will label each member either approved, banned, or rejected. The
problem is that this database is begun anew annually. After each year the
approved members database is purged to begin a new year with zero records.
This means new membership numbers are assigned to keep track of the amount of
members. Something had to be done with the years past banned members, thus I
created another table dedicated to them. If the banned members from years
past were included in the new members database, the Dcount function will not
work to be accurate. DCount is all I know to do for counting records.
During the course of the year if a banned member comes in and tries to rejoin
in the same year he was banned his data in the members table and would be
found by the code written. If he comes in the next year say 2010 but was
banned in 2009 or 2008 his data needs to be in the banned_members table so as
not to interfere with the current years applicants or records. I hope I
explained this correctly. What will work best in this situation for me and
what little I know about access to make this work is having two tables for
which I can search each and display data from each. Thanks for taken the
time to looking at this post.

Before you worry about the code, you should fix the design flaw which has 2
tables doing the work of 1. Adding a single Yes/No field to the Members
table will suffice to ban a member. Your form can change recordsources to
queries that show or hide the banned members, more easily than trying to use
2 tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Curious to know if anyone could provide help for the below code.

I have a form called Members, a table called Members and a table called
Members_Banned. New members are entered through the Members form are
stored
in the Members table and banned members are in Members_Banned table
through
non use of a form. I had the Members form and Members table successfully
working with everything inbetween the ** notations below (these are not
part
of the code but used here just to direct attention) omitted. I had a need
to
separate to another table for banned members and I added this code to
search
across two tables and display information into the members form when a
record
is found. Omit the code and all works well as it should. If there is a
duplicate record the code will erase the new record entry attempt and
display
the existing record once message of duplicate record found message is
acknowledged. If there is no duplicate record the new member data is
stored
in the Members table.

When I run the code with the newly added code between the ** below I get
to
the point to where the code finds the records in the Members table and
displays information as it should but when it searches but whe the record
is
in the Members_Banned table it gives me just the message saying a record
exists and clears the entry but does not show me the record. I can only
assume this is because the form is tied to the Members table as a data
source.

I feel there is a simple bit of code I am missing that would allow this.
Using the form I need all information ( new ) to dump into the Members
table
and nothing into the Members_Banned table. That table is solely for
keeping
information from the past and is not updatable through the use of a form.
I
just need the code to go there and display the information that is in the
table into the Members form.

Thanks in advance!

Brenda


Private Sub State_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.State_ID.Value
stLinkCriteria = "[State_ID]=" & "'" & SID & "'"

'Check Members table for duplicate State ID number
If DCount("State_ID", "Members", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

** 'Check Banned Members table for duplicate State ID number
ElseIf DCount("State_ID", "Members_Banned", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID " &
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
** Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[Arvin Meyer [MVP]]

You can still use DCount with the banned field. Like:

DCount("MemberID", "tblMembers", "Banned = 0")

DCount is still not the best, or fastest method of getting a count. Use a
recordset based on a query. If you're not sure how, post all the relevant
names and I'll write the code for you.

Our computer society has a membership database which shows Active members (a
reverse of your Banned) and their due date for their dues. It is very easy
to find a member, or to get his status or the status of all current members,
past members, etc. All of that is, and should remain in 1 table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

I have that capability now but in a diferent from. I have a drop down box
that will label each member either approved, banned, or rejected. The
problem is that this database is begun anew annually. After each year the
approved members database is purged to begin a new year with zero records.
This means new membership numbers are assigned to keep track of the amount
of
members. Something had to be done with the years past banned members,
thus I
created another table dedicated to them. If the banned members from years
past were included in the new members database, the Dcount function will
not
work to be accurate. DCount is all I know to do for counting records.
During the course of the year if a banned member comes in and tries to
rejoin
in the same year he was banned his data in the members table and would be
found by the code written. If he comes in the next year say 2010 but was
banned in 2009 or 2008 his data needs to be in the banned_members table so
as
not to interfere with the current years applicants or records. I hope I
explained this correctly. What will work best in this situation for me
and
what little I know about access to make this work is having two tables for
which I can search each and display data from each. Thanks for taken the
time to looking at this post.

Before you worry about the code, you should fix the design flaw which has
2
tables doing the work of 1. Adding a single Yes/No field to the Members
table will suffice to ban a member. Your form can change recordsources to
queries that show or hide the banned members, more easily than trying to
use
2 tables.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Curious to know if anyone could provide help for the below code.

I have a form called Members, a table called Members and a table called
Members_Banned. New members are entered through the Members form are
stored
in the Members table and banned members are in Members_Banned table
through
non use of a form. I had the Members form and Members table
successfully
working with everything inbetween the ** notations below (these are not
part
of the code but used here just to direct attention) omitted. I had a
need
to
separate to another table for banned members and I added this code to
search
across two tables and display information into the members form when a
record
is found. Omit the code and all works well as it should. If there is
a
duplicate record the code will erase the new record entry attempt and
display
the existing record once message of duplicate record found message is
acknowledged. If there is no duplicate record the new member data is
stored
in the Members table.

When I run the code with the newly added code between the ** below I
get
to
the point to where the code finds the records in the Members table and
displays information as it should but when it searches but whe the
record
is
in the Members_Banned table it gives me just the message saying a
record
exists and clears the entry but does not show me the record. I can
only
assume this is because the form is tied to the Members table as a data
source.

I feel there is a simple bit of code I am missing that would allow
this.
Using the form I need all information ( new ) to dump into the Members
table
and nothing into the Members_Banned table. That table is solely for
keeping
information from the past and is not updatable through the use of a
form.
I
just need the code to go there and display the information that is in
the
table into the Members form.

Thanks in advance!

Brenda


Private Sub State_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.State_ID.Value
stLinkCriteria = "[State_ID]=" & "'" & SID & "'"

'Check Members table for duplicate State ID number
If DCount("State_ID", "Members", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID "
&
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

** 'Check Banned Members table for duplicate State ID number
ElseIf DCount("State_ID", "Members_Banned", _
stLinkCriteria) > 0 Then
'Undo duplicate entry attempt
Me.Undo
'Message box warning of duplication
MsgBox "Membership history exists in the database for State ID "
&
SID & "!" _
& vbCr & vbCr & "You will now been taken to the record for
detailed information!", _
vbInformation, "Membership History Found!"
'Go to record of original State ID Number
rsc.FindFirst stLinkCriteria
** Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub