Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data not displaying consistently (2003)

    We started with a single MDB containing two tables (Existing_Vendors, New_Vendors), one form (frmNew_Vendors) and one query (qryExisting_Vendor_Names). The form is based upon the New_Vendors table. Within the form there is a combo box (cmboExistingVendor; the data source is qryExisting_Vendor_Names containing two fields: vendor name and vendor code) that allows the user to select an existing vendor name to match a corresponding new vendor (the purpose of this is to clean out new duplicate vendors by finding a match with an existing vendor). The combo box has two columns, vendor name and vendor code. The selected vendor name is saved to a field (ExistingVendorName) in the New_Vendors table. The vendor code from cmboExistingVendor is displayed on the form in a text box (txtExistingVendorCode) using the data source =cmboExistingVendor.colum(1).
    Everything worked as desired until we split the mdb and moved the tables to SQL Server. Using the same query and form and linking to the SQL tables, some of the vendor codes now display on the form and some do not (even when deleted and reselected). All display properly on the stand-alone MDB. We split the MDB to allow several people to work on the cleanup at the same time. They each have a local copy of an MDB with the form and query pointed via ODBC to the SQL tables. Hope this makes sense. Any idea on what is causing some of the data to not display on the form now? Any help would be greatly appreciated. Thank you. - Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data not displaying consistently (2003)

    1) Do the tables have primary keys, e.g. an Identity field (the equivalent of AutoNumber in Access)? When working with SQL Server tables, this is important.

    2) How many people will be entering/updating data simultaneously? With a central Access backend and individual Access frontends, you shouldn't have a problem with up to 10 simultaneous users, perhaps a bit more. So unless there will be lots of people updating simultaneously, there is no pressing need to use SQL Server.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data not displaying consistently (2003)

    Do the codes appear in the second column of the combo when the list is dropped down (You might need to make column widths of say 5,2 for this.

    This would let you know if the issue is with the data retrieval OR the text box control.
    Also do you see the codes if you view the query that is the row source of the combo
    Andrew

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data not displaying consistently (2003)

    Hans, Andrew:
    Sorry for the delay in response. Primary keys are setup on the tables mentioned. The codes do appear in the drop down. I can see all the codes if I view the data source for the drop down. The reason for initially not wanting to have an mdb backend was that there users (4) are in separate locations and 2 have slow wan connections. Thank you both for your help. ...- scott

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data not displaying consistently (2003)

    I hope that someone who has more experience with the combination of Access and SQL Server will have a suggestion.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data not displaying consistently (2003)

    So are you saying that...

    The codes appear OK in the query
    They also appear OK in the list that drops down

    BUT..

    Either the combo is empty when it finishes
    OR the calculated field that refers to a column of the list box is empty

    Also does this happen when the data is selected, or also when you scroll from record to record.

    It could be a refresh is needed due to a timing issue

    I assume that the combo box is not dependent upon other fields on the form.

    If you haven't solved this you could try putting the instruction

    Calcfield.requery in the On Current Event of the form.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •