Results 1 to 5 of 5

Form in Split DB Cant Query record unless i close and re open the form, or click refresh.

  1. #1
    Bricktop44 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Location
    Los Angeles, CA
    Posts
    2

    Question Form in Split DB Cant Query record unless i close and re open the form, or click refresh.

    Hi.



    I have a simple Split DB with 2 tables and 5 data entry type forms. They are used to record test results of digital cameras. 1 main table (TestData) to hold serial numbers and test results, 1 look-up table to map error codes to error descriptions, and 5 forms. There are 5 test stations and 1 form to record the respective test results from that station. The database is split with the back end residing on a network share and the front end forms distributed to each local PC at the 5 test stations. My problem seems to be with the forms not seeing new records entered at other stations. For Example:

    Station 1 enters the new cameras Serial number [SN], Model and condition into the form as a new record, saves and then the form resets and waits for the next new SN to be entered in. Then on to station 2 which has a combo-box that looks up SN in the table (TestData) and displays the record values in various text boxes on the form. Record your test result, save then on to station 3, etc, etc.

    If i enter a new SN record at Station 1 and then walk to Station 2 and look-up that same SN i just entered it does not find the SN. I have to either click "Refresh" from the ribbon or i have to close the form and re open it.

    This error happens in 2 ways. Either i get the usual "This item cant be found in the list", Or, i get no "not in list" error at all and the combo-box does its look-up function BUT no record data displays in the corresponding text box fields below as they should. When that happens i see the tables first record SN value (as in the first record ever in the table) flash briefly it its respective text box and then disappear. No sign of the SN value i just searched. Model and Condition values do not display either. Now , i can look in the linked table and see the new SN in the table, but the combo-box cant seem to locate it until i close the form and re open. I used the combo box wizard to have it look up the SN i need and then I used VB to then have it reset its value to "" once the record for the SN i search appears.

    I have tried using re-query on the combo-box with little success. All stations are wired Ethernet, no security or traffic issues. If i select a SN value from the combo-box that's already been in the table before the form loaded i have no issues. If i type in an SN value that has just been entered in by a previous station, AFTER the the form has loaded, the Combo-box does reset to "" but no records display below in their text boxes. Again, it all goes away once i close and re open the form.

    That was a lot, sorry. was trying to be as detailed as i could. I don't think this is a problem with any of my code because it all works great with existing values. I'm leaning more towards something with the database being split and the form not updating, something like that. Your help is appreciated.

    Thanks,

    Brick.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,398
    This is an unbound combobox for input of filter criteria?

    A requery of the combobox should show the new item listed.

    What method did you use to requery the combobox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Bricktop44 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Location
    Los Angeles, CA
    Posts
    2
    Combo is unbound, i used option 3 of the combo wizard - Find a record based on the value i select. Simple SELECT statement for the Row Source (SELECT [TestData].[SN] FROM TestData.

    When i tried Re query, i used the Combo's before update event (Me.Combo86.Requery) and got errors about having to save the field first before a re query.

    I have noticed something else when the error behavior type is my combobox query firing but showing no results below. When i start typing the SN value for the combo to query it starts to auto fill it for me. So that suggests that the combo IS seeing the new values in the table, right? So in that case there would be no need to requery the Combo, but why oh why doesnt the record values display? This is making me crazy.

    I can reproduce the error everytime:

    Form 1 is open on laptop , form 2 is open on desktop. Enter new record SN1 on Form 1, save it. Immediately move to form 2 and type in SN1 to the combo box. Auto type fills in SN1 as i type i hit enter and attempt the lookup of SN1. The combobox resets to empty as it should, but i see no record data for SN1 on the rest of the form. I close Form 2, re open. Type in SN1 to the combobox again and hit enter, now all works as it should. i save the record on form 2 and the form resets and waits at the combobox for my next lookup value.
    Now, if go back to form 1 and enter a new record of SN2, save and try to look up SN2 on form 2 the issue changes to "item not found in the list". Does not autofill, does not fire the lookup and reset to "", nothing.

    If this wasn't a split DB i wonder if i would still have this issue?

    Thank you so much.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,398
    Is the combobox LimitToList property set to Yes? If you are pulling the RowSource list from the data table, not a lookup table, are you seeing duplicates? Might use: SELECT DISTINCT SN FROM TestData;

    However, that event won't help the users at other stations. Sure, the AfterUpdate event could requery the combobox for the user that just entered the value but does nothing for the combobox in db copy at another station.

    Also use the combobox GotFocus event to Requery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    Quote Originally Posted by Bricktop44 View Post

    ...If this wasn't a split DB i wonder if i would still have this issue...
    Yes you would! Using the GotFocus event of the Combobox to do its Requery, as June7 suggested, should solve the problem.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. close a form if record is open and being edited
    By Ruegen in forum Programming
    Replies: 9
    Last Post: 08-15-2014, 01:36 AM
  2. Replies: 2
    Last Post: 05-11-2012, 11:52 AM
  3. Replies: 1
    Last Post: 05-10-2012, 09:32 AM
  4. Replies: 1
    Last Post: 05-03-2012, 02:25 PM
  5. Replies: 11
    Last Post: 01-26-2012, 01:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums