473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

269 256MB
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
  3.  
  4. Private db As DAO.Database
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  9.  
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String, strCustNo As String, strOrderNo As String
  12.  
  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
  52.  
  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
  63.  
  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
  68.  
  69.     'Requery subform to update
  70.  
  71.     Me.txtScanCapture = Null
  72. End Sub
  73.  
Attached Images
File Type: jpg ReceivingScreen.jpg (89.8 KB, 290 views)
Dec 7 '16 #1
7 1349
PhilOfWalton
1,430 Expert 1GB
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.

Phil
Dec 8 '16 #2
jforbes
1,107 Expert 1GB
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
DanicaDear
269 256MB
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
jforbes
1,107 Expert 1GB
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
DanicaDear
269 256MB
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
jforbes
1,107 Expert 1GB
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
  2. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Recalc
  3. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Refresh
  4. Me.subfrmBOX_RECEIVING.Form.DATE_BOX_RETURN.Requery
  5.  
  6. Me.subfrmBOX_RECEIVING.Repaint
  7. Me.subfrmBOX_RECEIVING.Recalc
  8. Me.subfrmBOX_RECEIVING.Refresh
  9. Me.subfrmBOX_RECEIVING.Requery
  10.  
  11. Me.Repaint
  12. Me.Recalc
  13. Me.Refresh
  14. Me.Requery
Dec 9 '16 #7
DanicaDear
269 256MB
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

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

Similar topics

12
by: Corey Burnett | last post by:
I have a client that has a split database (front-end/back-end). They are also using Access security - MDW file. The front end MDE file, the back end MDB file, and the MDW file are all located on...
2
by: AAOO (Sean) | last post by:
When I try to access the back end of a split database, I get the error message, that it's not trusted by access, and that I should move it to my computer, or an accessible network location? What...
7
by: DanZaMan | last post by:
Just a query about how clever access is, I'm using access 97. When using a "split" database with a separate front-end and with the data file on a separate server on the network how does access...
2
by: teju | last post by:
Hello, I have to display all the data from the database in tables in ASP. I am able to get the data but the problem is of displaying one to many relations.Like below Title1 has two records but how...
7
by: FNA access | last post by:
I have a mainform that has a subform in the detail section and a subform in the footer section.(Both subforms are in datasheet view) When I click the navigation button to create a new record on the...
11
by: dba | last post by:
Have been displaying data from database using html for some time but just recently trying to display data back to "form". Can't find answer. <form method="post" action="<?php echo $PHP_SELF;?>">...
9
by: whitep8 | last post by:
Hi All, The following script will display the grid, with its headers but will not display any data. Im sure ive made a mistake in the syntax somewhere but i can see the wood for the trees. Ive...
5
mseo
by: mseo | last post by:
hi, I have mainform with two cmdbuttons: 1- Save 2- Add Detail the mainform has subform which is set to invisible, if I click Add Detail the subform appears, if not and user clicked Save the...
5
by: rhonda6373 | last post by:
Good morning, I have a client that has sent me a database that is already split. I have not worked with a split database before. He wants me to make the changes and then re-import data from...
3
by: Shawn29316 | last post by:
I have a split database and I need to replace one of the data tables in the back end weekly. I'd like to have the user who gets the new data file each week to replace the old file but I don't really...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.