By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,571 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,571 IT Pros & Developers. It's quick & easy.

Problems with Updating table

P: 4
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..

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


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAddParts_Click()
  2. '[Add part] button
  3.      Dim rs As Recordset
  4.      Dim rs1 As Recordset
  5.      Dim db As Database
  6.      Dim PrevItem As Integer
  7.      Dim sSQL As String
  8.      Dim sSQL1 As String
  9.  
  10.      Dim Counter As Integer
  11.      Me.Refresh
  12.  
  13.     sSQL = "SELECT * FROM qryDupCheck WHERE CLAIM_NO = '" & Me![txtCLAIM_NO] & "' AND CUSTOMER_NO = " & Me![CUSTOMER_NO] & ";"
  14.     Set db = CurrentDb()
  15.     Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
  16.     If rs.RecordCount = 1 Then
  17.  
  18.     sSQL = "SELECT * FROM qryDupCheck WHERE CLAIM_NO = '" & Me![txtCLAIM_NO] & "' AND CUSTOMER_NO = " & Me![CUSTOMER_NO] & ";"
  19.     DoCmd.OpenForm "frmDupClaim"
  20.     Forms![frmDupClaim].RecordSource = sSQL
  21.     Exit Sub
  22.     End If
  23.  
  24.      Set db = CurrentDb()
  25.      sSQL = "SELECT * FROM PRODUCT_INFO WHERE TRANSACTION_NO = " _
  26.                     & Me![TRANSACTION_NO] & ";"
  27.  
  28.  
  29.      Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
  30.      'If Not rs.EOF Then
  31.  
  32.  
  33.  
  34.      sSQL1 = "SELECT * FROM queRMA_PART WHERE TRANSACTION_NO = " _
  35.                     & Me![TRANSACTION_NO] & ";"
  36.  
  37.      Set rs1 = db.OpenRecordset(sSQL1, dbOpenDynaset)
  38.      If Not rs1.EOF Then
  39.  
  40.      PrevItem = 0
  41.  
  42.           rs1.MoveLast
  43.           'rs.MovePrevious
  44.           PrevItem = rs1![RMA_PART]
  45.           rs1.MoveLast
  46.  
  47.           'PrevItem = rs.RecordCount
  48.      Else
  49.           PrevItem = 0
  50.      End If
  51.  
  52.     'For counter = 1 To Me![txtQuantity]
  53.           rs.AddNew
  54.           rs!TRANSACTION_NO = Me![TRANSACTION_NO]
  55.           rs!RMA_PART = PrevItem + 1
  56.  
  57.           rs!CUST_PART_NO = Me![cboCustPartNo]
  58.           rs!MODINE_PART_NO = Me![cboModinePart]
  59.           rs!PART_NOTE = Me![Txtpartnote]
  60.           rs!DESCRIPTION = Me![txtDescription] 'new SAP DESCRIPTION
  61.           'rs!PART_DESC = Me![txtPartDesc]     'Old part description
  62.           rs!SAP_DESC_ID = Me![txtSAPDescID]
  63.           rs!GLOBAL_PROD_Grp = Me![txtGlobProdGrp]
  64.           rs!GLOBAL_PROD_TYPE = Me![txtGlobProdType]
  65.           rs!WRE_SAP_CODE = Me![txtWRECode]
  66.           rs!SAP_INDV_CODE = Me![txtSAPCode]
  67.           rs!RETURN_REASON = Me![txtReason]
  68.           rs!CLAIM_NO = Me![txtCLAIM_NO]
  69.           rs!DEALER_NO = Me![txtDealer_No]
  70.           rs!CHASSIS_NO = Me![txtChassisNumber]
  71.           rs!LEN_OF_SERV = Me![txtMileage]
  72.           rs!DELIVERY_DATE = Me![txtDeliveryDate]
  73.           rs!PROD_DATE = Me![txtBuildDate]
  74.           rs!FAIL_DATE = Me![txtFailDate]
  75.           rs!LOS_MEASURE = Me![txtLOS_MEASURE]
  76.           rs!REMARKS = Me![txtRemarks]
  77.           rs!MFG_DATE = Me![txtManufactureDate]
  78.           rs.Update
  79.      'Next counter
  80.      sSQL = "SELECT * FROM TRANSACTION WHERE TRANSACTION_NO = " _
  81.                     & Me![TRANSACTION_NO] & ";"
  82.      Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
  83.      If rs.EOF Then
  84.           rs.Close
  85.           Exit Sub
  86.      Else
  87.           rs.Edit
  88.           rs!NUM_PART = PrevItem + Counter - 1
  89.           rs.Update
  90.      End If
  91.      rs.Close
  92.      Me![subProductInfo].Form.Requery
  93.  
  94.  
  95. End Sub
  96.  
  97. Private Sub cmdClearAll_Click()
  98. '[Clear All]button;  Clear Part number info for next entry
  99.  
  100. Me![cboCustPartNo] = ""
  101. Me![cboModinePart] = ""
  102. Me![Txtpartnote] = ""
  103. Me![txtDescription] = ""             'new SAP DESCRIPTION
  104. 'rs!PART_DESC = Me![txtPartDesc]     'Old part description
  105. Me![txtSAPDescID] = ""
  106. Me![txtGlobProdGrp] = ""
  107. Me![txtGlobProdType] = ""
  108. Me![txtWRECode] = ""
  109. Me![txtSAPCode] = ""
  110. 'Me![txtReason] = ""
  111. Me![txtCLAIM_NO] = ""
  112. Me![txtDealer_No] = ""
  113. Me![txtChassisNumber] = ""
  114. Me![txtMileage] = ""
  115. Me![txtDeliveryDate] = ""
  116. Me![txtBuildDate] = ""
  117. Me![txtFailDate] = ""
  118. Me![txtLOS_MEASURE] = ""
  119. Me![txtRemarks] = ""
  120. Me![txtManufactureDate] = ""
  121.  
  122. End Sub
Jan 24 '08 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 25 '08 #2

P: 4
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?
Jan 25 '08 #3

P: 4
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!!
Jan 25 '08 #4

nico5038
Expert 2.5K+
P: 3,072
Glad I could help, success with your application !

Nic;o)
Jan 26 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.