Edit/Add record in form from cmdButton

Excel coder needs help with access:

Windows xp
Access 97

2 Forms from different tables - Teardown and Quality
1 standard link criteria between records - machine serial number
Teardown form has a command button that opens quality form using
standard link criteria.

What it does:
1. If there is a quality record with the same serial #, it opens the
form filtered to that record.

2. If there is no quality record with the same serial #, it asks the
user if they would like to copy over the machine data. If they answer
yes, it populates a few common fields that are sometimes the same data
and sometimes not.

all is good. until i found out that there will be more than one quality
report created per serial number and they want the ability to
automatically populate the fields for 2nd and 3rd, etc, etc, reports as
well.

What I need it to do:

1. If there is a quality record with the same serial #, prompt the user
with a message like this:

"There are x# of quality reports for this serial #. Would you like to
create a new report?
if vbYes - create new record in form, copying fields from Teardown
form.
if vbNo - filter to record(s) with serial #
************************************************** **************************************
Here is my current code -////////////////////////////////////////////
-doodle
************************************************** **************************************
Private Sub cmdViewQualityReport_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEnterQualityDataFromTeardown"

stLinkCriteria = "[G_Machine_SN]=" & "'" & Me![Machine_SN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.OpenForm stDocName

End Sub
************************************************** **************************************

Public Sub Form_Load()
Dim Answ1 As String
Dim txtFail As String
Dim stDocName As String
Dim stDocName_1 As String
'On Error Resume Next

stDocName = "frmEnterQualityDataFromTeardown"
stDocName_1 = "frmEnterTeardownData"

If IsNull([G_Machine_SN].Value) Then
Answ1 = MsgBox("Would you like to use the same failure
description in the quality report that you entered into the teardown
report?", _
vbYesNo, "Message From Adria...")

Select Case Answ1
Case vbYes
Me!DE_Failure_Descrip =
Forms!frmEnterTeardownData!FS_FailureDescrip
Me!G_MachineType =
Forms!frmEnterTeardownData!Machine_Type
Me!G_Machine_SN =
Forms!frmEnterTeardownData!Machine_SN
Me!G_Tech =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!DE_Tech =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech1 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech2 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech3 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech4 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech5 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech6 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!Cast_Num =
Forms!frmEnterTeardownData!Spindle_SN
Me!G_Unit_PN =
Forms!frmEnterTeardownData!Part_Number
Me!G_Max_RPM =
Forms!frmEnterTeardownData!Max_RPM
Case vbNo
Exit Sub
Case Else
Exit Sub
End Select
Else: Exit Sub
End If

End Sub

************************************************** **************************************

my first question would have to be "why are you copying duplicate data into
two different tables?"

as an Excel user, you may have fallen into the "flat file" design trap -
it's a trap because Access handles data very differently than Excel, and
designing relational data tables the way you would design Excel spreadsheets
will give you poor results in Access. suggest you post a description of your
table design. before commenting on the user interface (forms, etc), it makes
sense to verify that the basic structure of the database (tables and
relationships) is sound. usual format for posted table descriptions is

TableName
FieldName (primary key)
AnotherFieldName
AnotherFieldName
AnotherFieldName (foreign key from SomeOtherTableName)

a description of how the "teardown" and "quality" records relate machine
records, and to each other, will help a lot.

if you're not familiar with the terms "primary key" and "foreign key" in the
context of Access tables, recommend you read up on data normalization
principles and table relationships. see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

hth


"doodle" wrote in message
ups.com...
Excel coder needs help with access:

Windows xp
Access 97

2 Forms from different tables - Teardown and Quality
1 standard link criteria between records - machine serial number
Teardown form has a command button that opens quality form using
standard link criteria.

What it does:
1. If there is a quality record with the same serial #, it opens the
form filtered to that record.

2. If there is no quality record with the same serial #, it asks the
user if they would like to copy over the machine data. If they answer
yes, it populates a few common fields that are sometimes the same data
and sometimes not.

all is good. until i found out that there will be more than one quality
report created per serial number and they want the ability to
automatically populate the fields for 2nd and 3rd, etc, etc, reports as
well.

What I need it to do:

1. If there is a quality record with the same serial #, prompt the user
with a message like this:

"There are x# of quality reports for this serial #. Would you like to
create a new report?
if vbYes - create new record in form, copying fields from Teardown
form.
if vbNo - filter to record(s) with serial #

************************************************** **************************
************
Here is my current code -////////////////////////////////////////////
-doodle

************************************************** **************************
************
Private Sub cmdViewQualityReport_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEnterQualityDataFromTeardown"

stLinkCriteria = "[G_Machine_SN]=" & "'" & Me![Machine_SN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.OpenForm stDocName

End Sub

************************************************** **************************
************

Public Sub Form_Load()
Dim Answ1 As String
Dim txtFail As String
Dim stDocName As String
Dim stDocName_1 As String
'On Error Resume Next

stDocName = "frmEnterQualityDataFromTeardown"
stDocName_1 = "frmEnterTeardownData"

If IsNull([G_Machine_SN].Value) Then
Answ1 = MsgBox("Would you like to use the same failure
description in the quality report that you entered into the teardown
report?", _
vbYesNo, "Message From Adria...")

Select Case Answ1
Case vbYes
Me!DE_Failure_Descrip =
Forms!frmEnterTeardownData!FS_FailureDescrip
Me!G_MachineType =
Forms!frmEnterTeardownData!Machine_Type
Me!G_Machine_SN =
Forms!frmEnterTeardownData!Machine_SN
Me!G_Tech =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!DE_Tech =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech1 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech2 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech3 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech4 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech5 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!ID_Tech6 =
Forms!frmEnterTeardownData!Repair_Tech_Num
Me!Cast_Num =
Forms!frmEnterTeardownData!Spindle_SN
Me!G_Unit_PN =
Forms!frmEnterTeardownData!Part_Number
Me!G_Max_RPM =
Forms!frmEnterTeardownData!Max_RPM
Case vbNo
Exit Sub
Case Else
Exit Sub
End Select
Else: Exit Sub
End If

End Sub


************************************************** **************************
************

Tina -

It is not really a duplication of data. 40% of the time the data is the
same in the quality form. That is why I am prompting the user as to
whether or not they would like the data to be copied over.

Nonetheless, I posted my table design for both tables at the end of
this message. Basically, what I need is: (pseudo)

If quality record with same serial number does not exist, create new
report
-Ask user if they would like to use the same data
-If so, copy data, if not, open blank form
If quality record with same serial number exists then
-Tell user that x number of records exist, ask user if they would like
to create a new report ***
-If no, open form filtered to existing records
-If yes, ask user if they would like to use the same data
-If so, copy data, if not, open blank form

*** This is the part I am having trouble with. Coding whether or not
record exists. If I can figure that line out, I can pass the value and
write the rest myself.

Any help with that line would be appreciated.

-doodle

***********tblQuality***********
Report# Number (Long)
G_MachineType Text
G_Unit_PN Text
G_Machine_SN Text
G_Max_RPM Text
G_Tech Text
G_DateShipped Date/Time
G_ShippedTo Text
DE_Tech Text
DE_Failure_Descrip Memo
DE_FailureLocation Memo
ID_Taper Text
ID_Tech1 Text
ID_Run2 Text
ID_Tech2 Text
ID_Run12 Text
ID_Tech3 Text
ID_RearRun Text
IDClamp1 Text
ID_Clamp2 Text
ID_Tech4 Text
ID_Runoff Text
ID_Tech5 Text
ID_Notes Memo
ID_Tech6 Text
Cast_Num Text
Job_Num Text
ckExchangeRepair Number (Long)


***********tblTeardown***********
Report# Number (Long)
Contact_Date Date/Time
Customer Text
Machine_Type Text
Part_Number Text
Machine_SN Text
Order_Num Text
Repair_Tech_Num Text
Spindle_SN Text
Max_RPM Text
FS_Part_1 Text
FS_Service_Type_1 Text
FS_Part_2 Text
FS_Service_Type_2 Text
FS_Part_3 Text
FS_Service_Type_3 Text
FS_Part_4 Text
FS_Service_Type_4 Text
FS_Part_5 Text
FS_Service_Type_5 Text
FS_Part_6 Text
FS_Service_Type_6 Text
FS_Part_7 Text
FS_Service_Type_7 Text
FS_Part_8 Text
FS_Service_Type_8 Text
FS_Part_9 Text
FS_Service_Type_9 Text
FS_Part_10 Text
FS_Service_Type_10 Text
FS_PossibleCause_1 Text
FS_PossibleCause_2 Text
FS_PossibleCause_3 Text
FS_PossibleCause_4 Text
FS_PossibleCause_5 Text
FS_FailureDescrip Memo
FS_Modifications Memo
FS_Recommendations Memo
ckGen1 Text
ckGen2 Text
ckGen3 Text
ckGen4 Text
ckGen5 Text
ckGen6 Text
ckGen7 Text
ckGen8 Text
ckGen9 Text
Name Type
ckGen10 Text
ckGen11 Text
ckGen12 Text
ckGen13 Text
ckGen14 Text
ckGen15 Text
ckGen16 Text
ckHousing1 Text
ckHousing2 Text
ckHousing3 Text
ckHousing4 Text
ckHousing5 Text
ckHousing6 Text
ckHousing7 Text
ckHousing8 Text
ckHousing9 Text
ckHousing10 Text
ckHousing11 Text
ckHousing12 Text
ckHousing13 Text
ckHousing14 Text
ckHousing15 Text
ckHousing16 Text
ckHousing17 Text
ckHousing18 Text
ckHousing19 Text
ckHousing20 Text
ckHousing21 Text
ckHousing22 Text
ckDrawbar1 Text
ckDrawbar2 Text
ckDrawbar3 Text
ckDrawbar4 Text
ckDrawbar5 Text
ckDrawbar6 Text
ckDrawbar7 Text
ckDrawbar8 Text
ckDrawbar9 Text
ckDrawbar10 Text
ckDrawbar11 Text
ckDrawbar12 Text
ckDrawbar13 Text
ckDrawbar14 Text
ckDrawbar15 Text
ckDrawbar16 Text
ckDrawbar17 Text
ckDrawbar18 Text
ckDrawbar19 Text
ckDrawbar20 Text
ckMotor1 Text
ckMotor2 Text
ckMotor3 Text
ckMotor4 Text
ckMotor5 Text
ckMotor6 Text
ckMotor7 Text
ckMotor8 Text
ckMotor9 Text
ckMotor10 Text

your tables design does not follow normalization rules, and indeed more
closely resembles an Excel "flat" file than relational data tables, but
since you indicated you'll welcome only comments on the "record exists" line
of code, i won't comment further.

as for checking for existing records in a table before you open a form,
suggest you use the DCount() function, setting the criteria argument to
filter on whatever field(s) will identify the record(s) you want to count.
something along the lines of

If DCount(1, "TableName", "TextFieldName = '" & Me!ControlName _
& "' AND NumberFieldName = " & Me!ControlName) 0 Then

the above expression shows the syntax for a field of Text data type, and a
field of Number data type. it also assumes that the code will run in the
form that contains the value(s) you want to check against. suggest you read
up on the DCount() function in Access Help, to better understand how the
arguments work.

hth


"doodle" wrote in message
oups.com...
Tina -

It is not really a duplication of data. 40% of the time the data is the
same in the quality form. That is why I am prompting the user as to
whether or not they would like the data to be copied over.

Nonetheless, I posted my table design for both tables at the end of
this message. Basically, what I need is: (pseudo)

If quality record with same serial number does not exist, create new
report
-Ask user if they would like to use the same data
-If so, copy data, if not, open blank form
If quality record with same serial number exists then
-Tell user that x number of records exist, ask user if they would like
to create a new report ***
-If no, open form filtered to existing records
-If yes, ask user if they would like to use the same data
-If so, copy data, if not, open blank form

*** This is the part I am having trouble with. Coding whether or not
record exists. If I can figure that line out, I can pass the value and
write the rest myself.

Any help with that line would be appreciated.

-doodle

***********tblQuality***********
Report# Number (Long)
G_MachineType Text
G_Unit_PN Text
G_Machine_SN Text
G_Max_RPM Text
G_Tech Text
G_DateShipped Date/Time
G_ShippedTo Text
DE_Tech Text
DE_Failure_Descrip Memo
DE_FailureLocation Memo
ID_Taper Text
ID_Tech1 Text
ID_Run2 Text
ID_Tech2 Text
ID_Run12 Text
ID_Tech3 Text
ID_RearRun Text
IDClamp1 Text
ID_Clamp2 Text
ID_Tech4 Text
ID_Runoff Text
ID_Tech5 Text
ID_Notes Memo
ID_Tech6 Text
Cast_Num Text
Job_Num Text
ckExchangeRepair Number (Long)


***********tblTeardown***********
Report# Number (Long)
Contact_Date Date/Time
Customer Text
Machine_Type Text
Part_Number Text
Machine_SN Text
Order_Num Text
Repair_Tech_Num Text
Spindle_SN Text
Max_RPM Text
FS_Part_1 Text
FS_Service_Type_1 Text
FS_Part_2 Text
FS_Service_Type_2 Text
FS_Part_3 Text
FS_Service_Type_3 Text
FS_Part_4 Text
FS_Service_Type_4 Text
FS_Part_5 Text
FS_Service_Type_5 Text
FS_Part_6 Text
FS_Service_Type_6 Text
FS_Part_7 Text
FS_Service_Type_7 Text
FS_Part_8 Text
FS_Service_Type_8 Text
FS_Part_9 Text
FS_Service_Type_9 Text
FS_Part_10 Text
FS_Service_Type_10 Text
FS_PossibleCause_1 Text
FS_PossibleCause_2 Text
FS_PossibleCause_3 Text
FS_PossibleCause_4 Text
FS_PossibleCause_5 Text
FS_FailureDescrip Memo
FS_Modifications Memo
FS_Recommendations Memo
ckGen1 Text
ckGen2 Text
ckGen3 Text
ckGen4 Text
ckGen5 Text
ckGen6 Text
ckGen7 Text
ckGen8 Text
ckGen9 Text
Name Type
ckGen10 Text
ckGen11 Text
ckGen12 Text
ckGen13 Text
ckGen14 Text
ckGen15 Text
ckGen16 Text
ckHousing1 Text
ckHousing2 Text
ckHousing3 Text
ckHousing4 Text
ckHousing5 Text
ckHousing6 Text
ckHousing7 Text
ckHousing8 Text
ckHousing9 Text
ckHousing10 Text
ckHousing11 Text
ckHousing12 Text
ckHousing13 Text
ckHousing14 Text
ckHousing15 Text
ckHousing16 Text
ckHousing17 Text
ckHousing18 Text
ckHousing19 Text
ckHousing20 Text
ckHousing21 Text
ckHousing22 Text
ckDrawbar1 Text
ckDrawbar2 Text
ckDrawbar3 Text
ckDrawbar4 Text
ckDrawbar5 Text
ckDrawbar6 Text
ckDrawbar7 Text
ckDrawbar8 Text
ckDrawbar9 Text
ckDrawbar10 Text
ckDrawbar11 Text
ckDrawbar12 Text
ckDrawbar13 Text
ckDrawbar14 Text
ckDrawbar15 Text
ckDrawbar16 Text
ckDrawbar17 Text
ckDrawbar18 Text
ckDrawbar19 Text
ckDrawbar20 Text
ckMotor1 Text
ckMotor2 Text
ckMotor3 Text
ckMotor4 Text
ckMotor5 Text
ckMotor6 Text
ckMotor7 Text
ckMotor8 Text
ckMotor9 Text
ckMotor10 Text