There is some pretty extensive VBA code in this database, all of which was developed for me through Bytes (specifically extensive help from NeoPa, to give credit where it is certainly due—I’ll go to my grave in awe of that man!). Anyway, I split the databases and so far I’ve only found one problem. Need to see if someone can help me figure this out.
Background: we test and reuse rubber gloves (safety items) and customers return old gloves in the same box we shipped new gloves to them in. So, Box of new gloves ships. Same box with old gloves returns. We track both shipping and receiving in the database. Reuse plastic boxes over and over.
I have a main form (frmBOX_RECEIVING) which contains a subform (subfrmBOX_RECEIVING). The main form contains an unbound field (txtScanCapture) where you enter a Box number, and through the code, the program sets the date_box_returned to “today.” The subform is linked by the CUST_NUM, which is queued and shown on the mainform in another unbound field because the database knows what customer is attached to what box.
Each time you enter a BOX_NUM, the resulting customer for that box and ALL their boxes (usually between 1 and 5 boxes per customer) displays on the subform. The problem is, now that I’ve split the database, while the updated return date DOES UPDATE as intended, it does not *always* SHOW on the subform. So the date will be updated in the table (the truly important part) but the DATE_BOX_RETURN field shows up blank on the subform (user information only, which freaks them out when it's missing because they don't understand it's still updating in the table). This only happens sometimes. That’s what makes it so strange. I am unable to connect any reasoning to it. It probably happens 15% of the time. The other 85% it displays correctly.
To double check that I hadn’t gone slap crazy, I imported the tables back in (as opposed to being linked), and POOF it worked perfectly again. There has to be something in this code that prevents the display of updated data due to my tables being linked as opposed to being in the actual database. I hope one of you whizzes can spot it quickly. I cannot.
One thing I remembered: If I punch in the box number again, it will show on the subform correctly. I can also press enter a time (or two or three times) and eventually it will show on the subform. (???)
I attached a picture of the form with an example of a box not displaying the date correctly. (This is a fake example because the last box I entered "1028" is shown correctly but this is what it would look like if I had just entered "841" and it is showing empty where it SHOULD show the date). Just want to be clear. :-)
PS. I have the exact same problem in my Shipping form, which is set up almost identically. If I can figure out the problem here, I’ll apply the solution to the other form.
Could this be because in the first few lines I reference CurrentDB but the tables are not in the "current" db? Laugh WITH me, not at me, if that was a dumb suggestion. Haha.
Any takers? Can I get a whiz? LOL!!
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- Private db As DAO.Database
- Private Sub Form_Open(Cancel As Integer)
- Set db = CurrentDb
- End Sub
- Private Sub txtScanCapture_AfterUpdate()
- Dim strSQL As String, strCustNo As String, strOrderNo As String
- Select Case Len(Me.txtScanCapture)
- Case 3, 4
- 'Box
- 'Is box registered in database?
- If DCount("[BOX_NUM]", _
- "[tblBOX]", _
- "[BOX_NUM]='" & Me.txtScanCapture & "'") = 0 Then
- 'Box does not exist in DB
- MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
- Else
- Me.txtScan_Box_Num = Me.txtScanCapture
- 'Box exists.
- 'Set received date=now
- strSQL = "UPDATE [tblBOX] " & _
- "SET [DATE_BOX_RETURN]=Date() " & _
- "WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
- DoCmd.SetWarnings (False)
- DoCmd.RunSQL strSQL
- DoCmd.SetWarnings (True)
- Me.subfrmBOX_RECEIVING.Requery
- 'Use the box_num to obtain the cust_num & order_num from tblBOX
- 'but I don't know how to store the values to use in the .FindFirst below
- strSQL = "SELECT * " & _
- "FROM [tblBOX] " & _
- "WHERE [BOX_NUM]='" & Me.txtScanCapture & "'"
- With db.OpenRecordset(strSQL, dbOpenSnapshot)
- If .RecordCount = 0 Then
- MsgBox "There is no record of this box shipping"
- 'Do whatever you want to handle this case
- Else
- Me.txtScan_Box_Num = !BOX_NUM
- strCustNo = !CUST_NUM
- strOrderNo = !ORDER_NUM
- Me.tb_Cust_Num = strCustNo
- Me.Max_ORDER_NUM = strOrderNo
- End If
- Call .Close
- End With
- End If
- 'Update the DATE_RET in tblOrders where necessary
- strSQL = "([CUST_NUM] = '" & strCustNo & "') AND " & _
- "([DATE_BOX_RETURN] Is Null)"
- If DCount("*", "[tblBOX]", strSQL) = 0 Then
- strSQL = "UPDATE [tblORDERS] " & _
- "SET [DATE_RET] = Date() " & _
- "WHERE ([CUST_NUM] = '" & strCustNo & "')" & _
- " AND ([ORDER_NUM] = '" & strOrderNo & "')"
- Call db.Execute(strSQL)
- End If
- Case Else
- 'Some sort of error or user error
- MsgBox "Box Numbers can only be 3 or 4 digits."
- End Select
- 'Requery subform to update
- Me.txtScanCapture = Null
- End Sub