double data entry with msgbox function?

[jhwagner]

I need to use double data entry with an MS Access database. I have
read many arguments and reasons against this on this group but I have
to do this. I have seen various tips on how this can be done including
the use of queries but this hasn't worked satisfactorily. I have two
people who know Access just barely enough to enter the data so what I
really want is something like the msgbox function to pop up a window
whenever the person doing the second round of data entry types in
something that doesn't match the first round of entry.

1. Would it be easiest to use two tables and two forms? If so, is
there anything special I need to do to the second table/form?

2. Would it be possible to use a msgbox that makes the comparison
between the two values and alerts the person entering the second round
of data that their entry doesn't match AND lets them either accept
their new entry and override the previous entry or cancel their entry
and use the value entered in the first round.

If this will work, can someone please help me with the programming.
For argument sake, lets say I have table1, form1, table2, form2 and the
first varialbe I need to check is AGE.

Thanks for any help.

[Lauren Quantrell]

The way I see it, the biggest issue (if you must do this sort of thing)
is matching record 1 in table1 with record 1 in table2. The form/msgbox
part is pretty easy. Does every row of data to be entered include a
mandatory ID field? If yes, then it's pretty easy. If no, not so easy
and very problematic.

Example:
User1 enters John Smith - Age 25 in table 1
User2 enters John Smith - Age 27 in table 2

Is this an error or are there two (or more) different John Smiths?

But ...

User1 enters John Smith - ID# 101 - Age 25 in table 1
User2 enters John Smith - ID# 101 - Age 27 in table 2

Application checks ID#101 in Table1 with ID#101 in Table2 and reports
the error.

The code to do this is pretty straight forward.

lq

[Lauren Quantrell]

The way I see it, the biggest issue (if you must do this sort of thing)
is matching record 1 in table1 with record 1 in table2. The form/msgbox
part is pretty easy. Does every row of data to be entered include a
mandatory ID field? If yes, then it's pretty easy. If no, not so easy
and very problematic.

Example:
User1 enters John Smith - Age 25 in table 1
User2 enters John Smith - Age 27 in table 2

Is this an error or are there two (or more) different John Smiths?

But ...

User1 enters John Smith - ID# 101 - Age 25 in table 1
User2 enters John Smith - ID# 101 - Age 27 in table 2

Application checks ID#101 in Table1 with ID#101 in Table2 and reports
the error.

The code to do this is pretty straight forward.

lq

[PC Datasheet]

Could you explain WHY you have to do this with an example and let's go from
there.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

<jh******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I need to use double data entry with an MS Access database. I have
read many arguments and reasons against this on this group but I have
to do this. I have seen various tips on how this can be done including
the use of queries but this hasn't worked satisfactorily. I have two
people who know Access just barely enough to enter the data so what I
really want is something like the msgbox function to pop up a window
whenever the person doing the second round of data entry types in
something that doesn't match the first round of entry.

1. Would it be easiest to use two tables and two forms? If so, is
there anything special I need to do to the second table/form?

2. Would it be possible to use a msgbox that makes the comparison
between the two values and alerts the person entering the second round
of data that their entry doesn't match AND lets them either accept
their new entry and override the previous entry or cancel their entry
and use the value entered in the first round.

If this will work, can someone please help me with the programming.
For argument sake, lets say I have table1, form1, table2, form2 and the
first varialbe I need to check is AGE.

Thanks for any help.

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:ka****************@newsread2.news.atl.earthli nk.net...

Could you explain WHY you have to do this with an example and let's go from
there.
Could you ??
If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
************************************************** ******
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

Please Steve, do contact a psychiatrist ...

Arno R

[jhwagner]

First off, the reason I have to do this is because it is stipulated in
our grant that we will do double data entry. I have worked at three
different universities and it has always (over 20 years) been standard
practice for this type of thing when the research involves the
medical/health field.

More importantly, to answer Lauren's question, YES I do have a unique
ID for each and every record so your example . . .

User1 enters John Smith - ID# 101 - Age 25 in table 1
User2 enters John Smith - ID# 101 - Age 27 in table 2

.. . . is exactly what I have (sorry I didn't specify this to begin
with).

I'm glad it's easy but I seem to be striking out with it. This is the
first time I have had to set this up in Access.

[Lauren Quantrell]

Can you give an example of how many columns of information need to be
checked for each row. For example:
-----COLUMNS: LastName FirstName Age FavoriteFood HairColor
ROW = ID#101 Smith John 25 Bananas Blue

Does every column have to be macthed?
Example: Table1 Row101 FavoriteFood = Bananas must be checked against
Table2 Row101 FavoriteFood = Bananas

Is Table1 ALWAYS the first data entry or is it possible that sometimes,
the user will enter on Table2 first?

Does Table1 exist in the same database as Table2 or are they in
different databases?

If there are not too many columns, you could use DLookup after the
AfterUpdate of each control on the form that writes to Table2.

[jhwagner]

In all there are 110 columns and they start this way:

COLUMNS: ID DateEntry Staff Age Gender Marital T1SSQ1
T1SSQ2
ROW1: 101 Doe Smith 25 2 1
1 2

Most columns need to be matched (all but a few are NUMERIC).

Table1 is ALWAYS the first data entry

Table1 and Table2 are tables within the same database
< If there are not too many columns, you could use DLookup after the
< AfterUpdate of each control on the form that writes to Table2.

I suppose I would like to know how to do it this way but if there is
another way I would be interested in knowing what that is also.

Thank you very much for your help.

[PC Datasheet]

If you have 110 fields in your tables, there's a high probability that the
design of the tables is incorrect. Typically large tables in a relational
database max out around 25 to 30. The design of your tables may be the start
of your problem here but assuredly will give rise to larger future problems.
To begin solving your database, would you consider redesigning your tables
first?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

<jh******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

First off, the reason I have to do this is because it is stipulated in
our grant that we will do double data entry. I have worked at three
different universities and it has always (over 20 years) been standard
practice for this type of thing when the research involves the
medical/health field.

More importantly, to answer Lauren's question, YES I do have a unique
ID for each and every record so your example . . .

User1 enters John Smith - ID# 101 - Age 25 in table 1
User2 enters John Smith - ID# 101 - Age 27 in table 2

. . . is exactly what I have (sorry I didn't specify this to begin
with).

I'm glad it's easy but I seem to be striking out with it. This is the
first time I have had to set this up in Access.

[jhwagner]

For the sake of argument then, lets say there are 4 tables with 25 to
30 fields in each.

[PC Datasheet]

For each set of matching tables, ideally you would want the same field names
in each table. In the least, you would want the fields in the second table
in the same order as the first table. Once you have your tables set up
correctly, do some reading in the Help file on Recordsets, RecordsetClone,
TableDef, Fields Collection and Field. For each set of matching tables you
will need code to compare the corresponding values in Table2 vs Table1 and
when they don't match raise a message that they don't match and provide a
means to edit the value in either table.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


<jh******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

For the sake of argument then, lets say there are 4 tables with 25 to
30 fields in each.

[jhwagner]

I have the same field names in each table
I have the fields in the second table in the same order as the first
table

It appears you have restated my intention above so you must have a good
idea of what I want to do. However, it should be possible using msgbox
function so I would like to try it that way. This is where my problem
has been.
PC Datasheet wrote:

For each set of matching tables, ideally you would want the same field names
in each table. In the least, you would want the fields in the second table
in the same order as the first table. Once you have your tables set up
correctly, do some reading in the Help file on Recordsets, RecordsetClone,
TableDef, Fields Collection and Field. For each set of matching tables you
will need code to compare the corresponding values in Table2 vs Table1 and
when they don't match raise a message that they don't match and provide a
means to edit the value in either table.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!


<jh******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

For the sake of argument then, lets say there are 4 tables with 25 to
30 fields in each.

[PC Datasheet]

Yes, your code wopuld use the elements previously mentioned and you would
loop through the fields in Table2 comparing each field to its corresponding
field in Table1 and when they don't match you would use the msgbox function
to raise the message that they don't match and ask if the user wants to edit
one of the tables.
If Msgbox("Value in Field in Table2 Does Not Match Value in Field In
Table1.Do you Wish To Edit One Of The Tables?",vbYesNo,"Field Values Don't
Match!") = vbYes Then ....
You would then use the Msgbox function again:
If MsgBox("Click Yes To Edit Table2 And No To Edit Table1",vbYesNo,"Which
Table Do You Want To Edit?") = vbYes Then
<Code to edit Table2>
Else
<Code To Edit Table1>
End If

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Contact me.
<jh******@gmail.com> wrote in message
news:11********************@g43g2000cwa.googlegrou ps.com...

I have the same field names in each table
I have the fields in the second table in the same order as the first
table

It appears you have restated my intention above so you must have a good
idea of what I want to do. However, it should be possible using msgbox
function so I would like to try it that way. This is where my problem
has been.
PC Datasheet wrote:

For each set of matching tables, ideally you would want the same field
names
in each table. In the least, you would want the fields in the second
table
in the same order as the first table. Once you have your tables set up
correctly, do some reading in the Help file on Recordsets,
RecordsetClone,
TableDef, Fields Collection and Field. For each set of matching tables
you
will need code to compare the corresponding values in Table2 vs Table1
and
when they don't match raise a message that they don't match and provide a
means to edit the value in either table.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling?
Need
room reservations scheduling? Need employee work scheduling? Contact me!


<jh******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

> For the sake of argument then, lets say there are 4 tables with 25 to
> 30 fields in each.
>

[StopThisAdvertising]

"PC Datasheet" <no****@nospam.spam> schreef in bericht news:kV***************@newsread2.news.atl.earthlin k.net...

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

So now you are advertising three times in the same thread ... you are making progress ...

These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
************************************************** ******

Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R

[rkc]

jh******@gmail.com wrote:

I have the same field names in each table
I have the fields in the second table in the same order as the first
table

It appears you have restated my intention above so you must have a good
idea of what I want to do. However, it should be possible using msgbox
function so I would like to try it that way. This is where my problem
has been.

What is the data entry scenario?

Does one person enter the data twice in two different forms?

Does one person enter the data on one day and another person
enter the data on another day.

Do two people enter the same data at the same time?

Basically you're looking for a 100% match between two records
entered in two identical tables or forms. A msgbox has no way of
doing that so a query or code is needed somewhere in the process.

When depends on what the heck you are actually trying to do and how.

[Lauren Quantrell]

If you MUST do this and Table2 is ALWAYS populated after Table1 as you
state, then you can do this on a field by field level.

This assumes Table1 and Table2 are identical (column (field) names)
This also assumes as you said that every row has an ID field

Form1 is bound to Table1
Form2 is bound to Table2

The original entry to Table1 is ALWAYS made from Form1
The second-entry to Table2 is AlWAYS made from Form2

In the AfterUpdate event of EVERY field on Form2 you'll need code to
check the value of the fieldname column in Table1:
Dim myValue as Long
'(you mentioned these were all numbers. If not, make it: myValue as
String but you'll have to deal a little differently with quote marks in
the dlookup and strSQL statements)

Dim myFieldName as string
Dim msg, style, title, response
Dim strSQL as String

myFieldName = Screen.ActiveControl.Name

myValue = DLookUp(myFieldName,"Table1","[ID]=" & Me!ID)

If Me(myFieldName).Value <> myValue Then

msg "Yo Dude! You entered conflicting friggin data!" & vbcrlf & _
"Ease off on the typing little man." & vbcrlf & _
"A previous value of: " & myValue & " was entered by the dude who
did this first." & vbcrlf & vbcrlf & _
"Do you want to overwrite the previous value?" & vbcrlf & _
"Click YES use your new value and overwrite the previous value." &
vbcrlf & _
"If you are a total lamer or if you have had too much to drink
recently, DO NOT CLICK YES!" & vbcrlf & _
"Do you want to store your new value?"

style = vbYesNo + vbQuestion + vbDefaultButton1
title = "WHOA THERE COWBOY"
response = MsgBox(msg,style,title)

If Response = vbYes Then

'user has chosen to overwrite the existing value

strSQL = "INSERT INTO Table1 (" & myFieldName & ") VALUES ("
& Me(myFieldName).Value & " WHERE Table1.ID + " & Me!ID & ")"
DoCmd.RunSQL strSQL

MsgBox "You have successfully overwritten the hard work of
one of your colleagues." & vbcrlf & _
"So, you think you're better than they are? You had better be
right or there will be hell to pay for this! We know where you live
princess...", vbExclamation, "New Data Stored"

Else 'user has chosen not to overwrite the existing value in
Table1 so make the field blank and try again

Me(myFieldName).Value = Null
Me(myFieldName).SetFocus

End If

End If
Now, there are a few more things to consider:
If someone pastes data instead of typing it into the field...
If the fields will except NULL values
Probably a bunch more if I had to do this myself...

There are other approaches to this. One would be to compare it on a
record by record basis after avery field is typed, but it sounds like
that's not the way you were leaning.

I hope this helps.
lq

[Tim Fierro]

"Lauren Quantrell" wrote in message

If Me(myFieldName).Value <> myValue Then

msg "Yo Dude! You entered conflicting friggin data!" & vbcrlf & _
"Ease off on the typing little man." & vbcrlf & _
"A previous value of: " & myValue & " was entered by the dude who
did this first." & vbcrlf & vbcrlf & _

.....and the rest of your Message Box display type;

Had me laughing my ass off. I thought I was going to spew this orange juice
all over my monitor. :-)

I have something crazy like this in my app so I prevent myself from making a
major change to a field. It asks me if I "really want to update the master
record?". Upon the click of YES, I do, another box pops up; "Are you really
sure? There is no turning back."

Then if I still press a YES, it pops up a final time. "OK, YOU ARE TELLING
ME TO CHANGE ALL THESE RECORDS WITH THIS CHANGE. I WILL NOT TELL YOU AGAIN,
SO PRESS YES ONLY IF YOU ARE TOTALLY SURE."

Something along those lines. :-)
Tim