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

I split a database and now I have a sneaky issue with a subform not displaying data

P: 256
I had a beautifully working database and due to some upcoming upgrades, I thought it would be helpful if I split it into a front end and back end.
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
  1. Option Compare Database
  2. Option Explicit
  4. Private db As DAO.Database
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String, strCustNo As String, strOrderNo As String
  13.     Select Case Len(Me.txtScanCapture)
  14.     Case 3, 4
  15.         'Box
  16.         'Is box registered in database?
  17.         If DCount("[BOX_NUM]", _
  18.                   "[tblBOX]", _
  19.                   "[BOX_NUM]='" & Me.txtScanCapture & "'") = 0 Then
  20.             'Box does not exist in DB
  21.             MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  22.         Else
  23.             Me.txtScan_Box_Num = Me.txtScanCapture
  24.             'Box exists.
  25.             'Set received date=now
  26.             strSQL = "UPDATE [tblBOX] " & _
  27.                      "SET    [DATE_BOX_RETURN]=Date() " & _
  28.                      "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  29.             DoCmd.SetWarnings (False)
  30.             DoCmd.RunSQL strSQL
  31.             DoCmd.SetWarnings (True)
  32.             Me.subfrmBOX_RECEIVING.Requery
  33.             'Use the box_num to obtain the cust_num & order_num from tblBOX
  34.             'but I don't know how to store the values to use in the .FindFirst below
  35.             strSQL = "SELECT   * " & _
  36.                      "FROM     [tblBOX] " & _
  37.                      "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
  38.             With db.OpenRecordset(strSQL, dbOpenSnapshot)
  39.                 If .RecordCount = 0 Then
  40.                     MsgBox "There is no record of this box shipping"
  41.                     'Do whatever you want to handle this case
  42.                 Else
  43.                     Me.txtScan_Box_Num = !BOX_NUM
  44.                     strCustNo = !CUST_NUM
  45.                     strOrderNo = !ORDER_NUM
  46.                     Me.tb_Cust_Num = strCustNo
  47.                     Me.Max_ORDER_NUM = strOrderNo
  48.                 End If
  49.                 Call .Close
  50.             End With
  51.         End If
  53.         'Update the DATE_RET in tblOrders where necessary
  54.         strSQL = "([CUST_NUM] = '" & strCustNo & "') AND " & _
  55.                  "([DATE_BOX_RETURN] Is Null)"
  56.         If DCount("*", "[tblBOX]", strSQL) = 0 Then
  57.             strSQL = "UPDATE [tblORDERS] " & _
  58.                      "SET [DATE_RET] = Date() " & _
  59.                      "WHERE ([CUST_NUM] = '" & strCustNo & "')" & _
  60.                      "  AND ([ORDER_NUM] = '" & strOrderNo & "')"
  61.             Call db.Execute(strSQL)
  62.         End If
  64.     Case Else
  65.         'Some sort of error or user error
  66.         MsgBox "Box Numbers can only be 3 or 4 digits."
  67.     End Select
  69.     'Requery subform to update
  71.     Me.txtScanCapture = Null
  72. End Sub
Attached Images
File Type: jpg ReceivingScreen.jpg (89.8 KB, 241 views)
Dec 7 '16 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 1,430
I am having similar problems with forms not updating until you click in a field a few times. You & I don't seem to be the only people with this problem. This only appears to have happened within the last month or so, and I am suspicious this is a new bug.

Interesting to see if anyone else is reporting a similar problem, and if so, can we tie it to a Windows or Office update.

Dec 8 '16 #2

Expert 100+
P: 1,107
I would copy or move line 32:
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmBOX_RECEIVING.Requery
to line 63 and see if your problem goes away.

There is something a bit magical at how and when Access decides to refresh Form data. It gets even more erratic and un-predictable when tables are linked. So I don't typically rely on Access' automatic refresh capabilities, especially when data is being updated in VBA.
Dec 8 '16 #3

P: 256
Sad to report that moving the Requery to line 63 didn't help. I had tried adding it (while also keeping it where it was to start with) to line 70 before I wrote this post with no success. I know one solution that definitely works-- put my tables back directly into the database. I will await any other suggestions before I go there.

I thought splitting was a great idea. Sad to know it causes some issues.

jforbes--thank you for your time. I am always appreciative to those who try to help me. Super grateful!
Dec 8 '16 #4

Expert 100+
P: 1,107
The shortcut key for requery is {F5}. If you click in the field that is missing information and hit {F5} does Access then display the correct information?
Dec 8 '16 #5

P: 256
Oh my goodness! Yes, pressing {F5} does force the updated display!! {What a nice thing to know.} What does this tell us, besides that it isn't re-querying properly via the VBA?
Dec 8 '16 #6

Expert 100+
P: 1,107
I think it means that Access will get the latest information if you explicitly tell it to.

I'm not 100% sure how your Form and SubForm are put together. It looks like there is a MainForm named frmBOX_RECEIVING and a SubForm named subfrmBOX_RECEIVING. I'm guessing that they aren't linked in any manner. I'm guessing that the RecordSource for subfrmBOX_RECEIVING is a Query that either has some criteria that refers back to the MainForm (frmBOX_RECEIVING) or is strictly Date/Data driven.

I would attempt a few more ways to refresh the Form first, and if they don't work, then maybe we should dig into the RecordSource for the SubForm. I would try putting some of these (or all of them) at the end of txtScanCapture_AfterUpdate():
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Repaint
  3. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Refresh
  4. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Requery
  6. Me.subfrmBOX_RECEIVING.Repaint
  7. Me.subfrmBOX_RECEIVING.Recalc
  8. Me.subfrmBOX_RECEIVING.Refresh
  9. Me.subfrmBOX_RECEIVING.Requery
  11. Me.Repaint
  12. Me.Recalc
  13. Me.Refresh
  14. Me.Requery
Dec 9 '16 #7

P: 256
I tried many alternatives from above and I got an error and debug on all but the .Requery line. I tried putting the lines where you suggested as well as in a couple of other places. When I got the additional .Requery line to execute, the problem still occurred.

The subform and mainform are linked by the way.

I think right now I am going to put my tables back in my database and move forward. I am afraid that I may have more problems in the future, and I am just not experienced enough to efficiently address the complications. There is no reason why I cannot have it the way it was--without the front/back end split. If you are anxious to continue troubleshooting I will do it, but I don't want to waste anyone's time more than what is necessary. :-) Thank you so very much jforbes. (Am I giving up to early??)
Dec 12 '16 #8

Post your reply

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