Problems with Updating table

[AlexNunley]

I've adopted a moderately sized (65k records) active use database (Access 2000, Windows XP). One of the most commonly used forms is whats called the RMA generation field, used to add claim information to a table on an item-by-item basis. the form is pretty straight forward, but is in need of updating.

In talking with some of the users its been decided to add a few fields to the form. Digging through the guts, I find commands, the cmdAddParts and cmdClearAll. All these commands do is 1. Check for duplicates against items already in the table 2. Open the Recordset 3. Write to the recordset based on the values entered in the form (and calculate the item number) and 4. the cmdClearAll clears all the fields for entry of the next part.

Pretty straight forward all in all. The problem is there is a field that isnt used much, the Return_Reason field, form text box txtReason, which isnt cleared when the clear all button is clicked. It never has been as it wasnt even in the clear all command. Add it, and it breaks the command; when you go to add another part, it wont save it to the table properly. Example: Enter 1 part, click Add, it adds and is displayed at the bottom of the form. Click Clear all, all boxes clear, enter another, and click Add, it thinks for a moment, and then nothing.

I've asked the person who wrote all the code in the first place, she says its my changes that affect it, though the production version, the original, doesnt work either. Its just a field that was not used much, so its never been noticed before. Now, with all the additions, there are many more fields. If any of them are added to the clear code, it does not work. I cannot figure this out for the life of me. I've poured over it for a week now, its such simple code, I cant see why it doesnt work, why it works for some fields, not all...

Any tips would be Greatly appreciated

A.

The code, in all its glory, is below..

-------------------------

Private Sub cmdAddParts_Click()
'[Add part] button
     Dim rs As Recordset
     Dim rs1 As Recordset
     Dim db As Database
     Dim PrevItem As Integer
     Dim sSQL As String
     Dim sSQL1 As String
 
     Dim Counter As Integer
     Me.Refresh
 
    sSQL = "SELECT * FROM qryDupCheck WHERE CLAIM_NO = '" & Me![txtCLAIM_NO] & "' AND CUSTOMER_NO = " & Me![CUSTOMER_NO] & ";"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    If rs.RecordCount = 1 Then
 
    sSQL = "SELECT * FROM qryDupCheck WHERE CLAIM_NO = '" & Me![txtCLAIM_NO] & "' AND CUSTOMER_NO = " & Me![CUSTOMER_NO] & ";"
    DoCmd.OpenForm "frmDupClaim"
    Forms![frmDupClaim].RecordSource = sSQL
    Exit Sub
    End If
 
     Set db = CurrentDb()
     sSQL = "SELECT * FROM PRODUCT_INFO WHERE TRANSACTION_NO = " _
                    & Me![TRANSACTION_NO] & ";"
 
 
     Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
     'If Not rs.EOF Then
 
 
 
     sSQL1 = "SELECT * FROM queRMA_PART WHERE TRANSACTION_NO = " _
                    & Me![TRANSACTION_NO] & ";"
 
     Set rs1 = db.OpenRecordset(sSQL1, dbOpenDynaset)
     If Not rs1.EOF Then
 
     PrevItem = 0
 
          rs1.MoveLast
          'rs.MovePrevious
          PrevItem = rs1![RMA_PART]
          rs1.MoveLast
 
          'PrevItem = rs.RecordCount
     Else
          PrevItem = 0
     End If
 
    'For counter = 1 To Me![txtQuantity]
          rs.AddNew
          rs!TRANSACTION_NO = Me![TRANSACTION_NO]
          rs!RMA_PART = PrevItem + 1
 
          rs!CUST_PART_NO = Me![cboCustPartNo]
          rs!MODINE_PART_NO = Me![cboModinePart]
          rs!PART_NOTE = Me![Txtpartnote]
          rs!DESCRIPTION = Me![txtDescription] 'new SAP DESCRIPTION
          'rs!PART_DESC = Me![txtPartDesc]     'Old part description
          rs!SAP_DESC_ID = Me![txtSAPDescID]
          rs!GLOBAL_PROD_Grp = Me![txtGlobProdGrp]
          rs!GLOBAL_PROD_TYPE = Me![txtGlobProdType]
          rs!WRE_SAP_CODE = Me![txtWRECode]
          rs!SAP_INDV_CODE = Me![txtSAPCode]
          rs!RETURN_REASON = Me![txtReason]
          rs!CLAIM_NO = Me![txtCLAIM_NO]
          rs!DEALER_NO = Me![txtDealer_No]
          rs!CHASSIS_NO = Me![txtChassisNumber]
          rs!LEN_OF_SERV = Me![txtMileage]
          rs!DELIVERY_DATE = Me![txtDeliveryDate]
          rs!PROD_DATE = Me![txtBuildDate]
          rs!FAIL_DATE = Me![txtFailDate]
          rs!LOS_MEASURE = Me![txtLOS_MEASURE]
          rs!REMARKS = Me![txtRemarks]
          rs!MFG_DATE = Me![txtManufactureDate]
          rs.Update
     'Next counter
     sSQL = "SELECT * FROM TRANSACTION WHERE TRANSACTION_NO = " _
                    & Me![TRANSACTION_NO] & ";"
     Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
     If rs.EOF Then
          rs.Close
          Exit Sub
     Else
          rs.Edit
          rs!NUM_PART = PrevItem + Counter - 1
          rs.Update
     End If
     rs.Close
     Me![subProductInfo].Form.Requery
 
 
End Sub
 
Private Sub cmdClearAll_Click()
'[Clear All]button;  Clear Part number info for next entry
 
Me![cboCustPartNo] = ""
Me![cboModinePart] = ""
Me![Txtpartnote] = ""
Me![txtDescription] = ""             'new SAP DESCRIPTION
'rs!PART_DESC = Me![txtPartDesc]     'Old part description
Me![txtSAPDescID] = ""
Me![txtGlobProdGrp] = ""
Me![txtGlobProdType] = ""
Me![txtWRECode] = ""
Me![txtSAPCode] = ""
'Me![txtReason] = ""
Me![txtCLAIM_NO] = ""
Me![txtDealer_No] = ""
Me![txtChassisNumber] = ""
Me![txtMileage] = ""
Me![txtDeliveryDate] = ""
Me![txtBuildDate] = ""
Me![txtFailDate] = ""
Me![txtLOS_MEASURE] = ""
Me![txtRemarks] = ""
Me![txtManufactureDate] = ""
 
End Sub

[nico5038]

Did you check the datatype you're using for the field txtReason in the target table ?
When it's numeric use just a zero ("0" without quotes!) or Null instead of "" to initialize the field.

Nic;o)

[AlexNunley]

Did you check the datatype you're using for the field txtReason in the target table ?
When it's numeric use just a zero ("0" without quotes!) or Null instead of "" to initialize the field.

Nic;o)

Thanks for the reply!

Looked at what you suggested, the field is not numeric, rather its a memo......
......
Looked further into this, found a few fields that are cross typed... I will fix them, and see if this works...

Is there anything to be done with Memo fields special? or is "" okay?

[AlexNunley]

You're the best. I went through each field after the one wasn't it.. Found three that were mismatched...

Actually, I ended up just making them all be <field> = Null , no matter if they were text or date or what have you... it seems to work, added a dozen dummy parts with no faults...

GOLD STAR FOR YOU!!

[nico5038]

Glad I could help, success with your application !

Nic;o)