If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ************************************************** ************************************** |
#2
|
|||
|
|||
Edit/Add record in form from cmdButton
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 ************************************************** ************************** ************ |
#3
|
|||
|
|||
Edit/Add record in form from cmdButton
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 |
#4
|
|||
|
|||
Edit/Add record in form from cmdButton
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Move feild entries from form to form using global variables | JackCGW | General Discussion | 11 | November 14th, 2005 05:22 AM |
Access Mail Merge to Word.doc files ? | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 18th, 2005 06:31 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Form drops edited record-inserts new record instead | karenk10 | General Discussion | 0 | September 22nd, 2004 10:19 PM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |