Page 1 of 6 123456 LastLast
Results 1 to 15 of 80
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    Combo Box Autopopulation with an import button across databases


    Hi there, thanks for taking the time to read my post and help me out!

    I have to keep company data private so I will be using fake names.

    My company has an inventory which is separated to multiple front ends and 1 backend. We own a subsidiary/other part of the company that manufactures the parts we use and the chemistry data they use to manufacture each lot.
    Currently we manually enter that data into our forms. My task is to make a combo box that allows the editor to select a lot (Also known as "Heat" from here on out) and auto-populate the chemistry below. The catch is they also need to be able to edit/manually input the information as we order material from other companies as well. Our subsidiary has their access database in dropbox so I can use linked table manager to link to it no problem. I have copied all the databases to a local folder in order to get this feature working (Also using this as my first project to learn access as this database was built and maintained by someone who has passed. So I am a beginner.)

    Here are the names of the databased that I will be using to refer to:
    Subsidiary DB: MetalDB
    My companies backend: WorkDB_BE
    My companies frontend: WorkDB_FE

    With all that said here is what I currently have and the methodology I am using:
    The table with all the chemistry from MetalDB and The entire WorkDB_BE have been linked to WorkDB_FE.

    I created a subreport called chemistry junction to autopopulate the chemistry from the heat before importing it to WorkDB_BE.

    On the front end data entry form(Called Lots) inside the subreport I have a combo box with the source being a query(Heat Combo Box Query) that selects all of the heats from MetalDB. So that works fine and displays all the heats.
    The control source is the Heat field inside the subreport. (The backend and subsidiary database tables also have a heat field) I ran into an issue earlier that was causing the combo box to not update the field so it is set to, on exit, do an event procedure that contains the following code

    If Me.Dirty Then
    Me.Dirty = False
    End If

    The regular text box with a control source of Heat next to the combo box then updates and displays what I selected so I know that much works. Although none of the chemistry fields autopopulate.

    I was attempting to get an import button working with an append query so on the Chemistry Junction subreport is a button that is set to run a query called "Import Heat Button Query".
    The query is setup to insert into Chemistry Junction and it selects the heat and the first chemistry field(Only first because attempting to get it to work) and appends to their proper fields in the subreport.
    I have a criteria on the heat field set to "[Forms]![LOTS]![Chemistry Junction].[Form]![Heat]" Which I used the build tool to select from the loaded forms.

    At this point I should mention there are no relationships between these tables as all 3 (WorkDB_BE.LOTS, WorkDB_FE.Chemistry Junction, MetalDB.Heat) are all from separate databases.
    The primary keys of each lot are as follows:
    WorkDB_BE.LOTS = Autonum(To keep an ID on each lot)
    WorkDB_FE.Chemistry Junction = ID (Though I was swapping it to heat because I thought I could have it just take the heat from metaldb then I figured the ID could just copy over from the backend.)
    MetalDB.Heat = Heat (Cant change this one easily due to it coming from the other company.)

    My current issue is that when I click the import button it seems the criteria cannot find the heat that was selected from the combo box because it gives me the error of "Index or primary key cannot contain a Null value"

    I have gotten so close so many times and ran into several different issues. Any help is appreciated and if you can break it down for a simpleton to understand then I appreciate it even further!

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would suggest that you do not use Dropbox for your Access databases.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    When you say "subreport" do you mean "subform"? Is [Chemistry Junction] name of subform/subreport container control?

    Post code from the button click event. Post import query full SQL statement.
    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.

  4. #4
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by June7 View Post
    When you say "subreport" do you mean "subform"? Is [Chemistry Junction] name of subform/subreport container control?

    Post code from the button click event. Post import query full SQL statement.
    Yes I mean subform, apologies. Yes [Chemistry Junction] is the name of the subform. I do not know what you mean by container control.

    Code for button click event:
    Code:
    Private Sub Import_Heat_Click()
    On Error GoTo Err_Import_Heat_Click
    
    
        Dim stDocName As String
    
    
        stDocName = "Import Heat Button Query"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    
    Exit_Import_Heat_Click:
        Exit Sub
    
    
    Err_Import_Heat_Click:
        MsgBox Err.Description
        Resume Exit_Import_Heat_Click
        
    End Sub
    Code for Query:
    Code:
    INSERT INTO [Chemistry Junction] ( Heat, C )
    SELECT [HEATS-Master1].HEAT, [HEATS-Master1].C
    FROM [HEATS-Master1]
    WHERE ((([HEATS-Master1].HEAT)=[Forms]![LOTS]![Chemistry Junction].[Form]![Heat]));
    Note [HEATS-Master1].Heat is the table from MetalDB that contains the heats.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It appears that Chemistry Junction is name of both a Table anda sub Form.
    Most will advise you to NOT have names with embedded spaces.

    Do you have a graphic of your tables and relationships? With a BE, that's where this (relationship window) would be.
    As June mentioned, please provide complete SQL for query and events involved. Post the code directly in the thread.

    I would suggest that you post a copy of your database (FE and BE) with some sample "dummy data" so readers can see issues in context. Only enough records to highlight the issue is sufficient.

    Work with a copy of your production database(s). Don't do testing/manipulation of a production/operational system.

    Do you have a routine to Backup your database on a regular basis?

    Why are you using Access 2002?

  6. #6
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by mike60smart View Post
    Hi

    I would suggest that you do not use Dropbox for your Access databases.
    I don't really have a say. I am asking it to get moved to google drive at least. How would you suggest it be shared across companies?



    Quote Originally Posted by orange View Post
    It appears that Chemistry Junction is name of both a Table anda sub Form.
    Most will advise you to NOT have names with embedded spaces.

    Do you have a graphic of your tables and relationships? With a BE, that's where this (relationship window) would be.
    As June mentioned, please provide complete SQL for query and events involved. Post the code directly in the thread.

    I would suggest that you post a copy of your database (FE and BE) with some sample "dummy data" so readers can see issues in context. Only enough records to highlight the issue is sufficient.

    Work with a copy of your production database(s). Don't do testing/manipulation of a production/operational system.

    Do you have a routine to Backup your database on a regular basis?

    Why are you using Access 2002?
    You are correct. Chemistry Junction is the name of both a form and a table. Does that cause issues? I thought that's what the "[Forms]!" was for, to specify that it is a form.
    I could take pictures but I believe it would cause more confusion because there are a bunch of other tables and queries from the previous two people who worked on this database. They control other functions of the form as there is much that we need to enter. I am paying close attention to this forum so I can give any information requested. I am also afraid that photos may give away some information my boss is not comfortable with.

    I considered posting a copy of the FE and BE but there is a lot here and I don't want people to get confused. Also my boss told me that if I go that route that I should just hire someone to fix it that way they are contracted.

    I stated in the original that I am indeed working in a copy as I am also learning and the database is used on a daily basis so any changes need to be made sure they work before being added but thank you for taking the time to make sure anyways!
    The server it is hosted on is scheduled to make backups daily or weekly I believe. I am not the admin of the server. I am just being asked to learn the database to fill a vacancy.

    Quote Originally Posted by orange View Post
    Why are you using Access 2002?
    Money is the answer I was given haha. It works and is used across the entire company for the time being. And I am actually using 2000 but this site doesn't have that listed.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Chemistry Junction is the name of both a form and a table. Does that cause issues?
    It does not cause an issue directly. As long as you and the others working on this database understand which item you are referencing, then all is well. You mentioned not wanting to confuse readers, but you have 1 name for two different things.

    I thought that's what the "[Forms]!" was for, to specify that it is a form.
    Yes, Forms! indicates that a form name follows, but it doesn't mean that you have to use the name of the table on which the form is based.
    You could name the form (some common naming convention approaches)

    -ChemistryJunctionF or
    -frmChemistryJunction or
    -Form209...

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by orange View Post
    Chemistry Junction is the name of both a form and a table. Does that cause issues?
    It does not cause an issue directly. As long as you and the others working on this database understand which item you are referencing, then all is well. You mentioned not wanting to confuse readers, but you have 1 name for two different things.

    I thought that's what the "[Forms]!" was for, to specify that it is a form.
    Yes, Forms! indicates that a form name follows, but it doesn't mean that you have to use the name of the table on which the form is based.
    You could name the form (some common naming convention approaches)

    -ChemistryJunctionF or
    -frmChemistryJunction or
    -Form209...
    I will rename it ChemistryJunctionF right now. I honestly didn't even realize it until it was brought up.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you are attempting to use Access over a Wide Area Network then read the following:-

    https://www.devhut.net/ms-access-wan...-the-internet/
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by mike60smart View Post
    Hi

    If you are attempting to use Access over a Wide Area Network then read the following:-

    https://www.devhut.net/ms-access-wan...-the-internet/
    I appreciate the recommendation but as I stated I don't have much of a say. The company already has this setup and doesn't intend on upgrading it at the moment.
    Also according to the article you linked I should continue using MSAccess.

    We have a vpn that makes all the computers from every site (Except out subsidiary which is why it is on dropbox) have our database on a network drive.

    That being said I need help solving my current problem not looking for an alternative program.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Yes continue using Access but NOT using Dropbox or Google Drive. Access is not designed to work this way.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by mike60smart View Post
    Hi

    Yes continue using Access but NOT using Dropbox or Google Drive. Access is not designed to work this way.
    I will attempt to convince them to get it added to the server but at the moment that is not an issue because I am working with a local copy.

  14. #14
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    These are all great readings and I will keep them in mind for the future! Thank you!
    Do you happen to know if there are any like that on how to properly setup and use relationships? I am struggling to wrap my head around it even after several youtube videos.
    I also know that since I am linking tables from other databases that the relationship cannot enforce referential integrity which I learned the hard way after struggling and then finding that answer through google.
    Edit: I think what I really struggle with is primary keys and how relationships form.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Quote Originally Posted by Vita View Post
    Yes I mean subform, apologies. Yes [Chemistry Junction] is the name of the subform. I do not know what you mean by container control.
    A subform/subreport is created by installing a Subform/SubReport Container Control on a form or report. The container control has a Source Object property. This has the name of form or report the container holds.
    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.

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Macros, VBA, Autopopulation of Data
    By pdevito3 in forum Access
    Replies: 5
    Last Post: 08-13-2013, 01:39 PM
  2. Replies: 1
    Last Post: 08-09-2012, 04:19 PM
  3. Import Button
    By kowen091010 in forum Access
    Replies: 1
    Last Post: 12-15-2011, 04:32 PM
  4. Need help with code to enable autopopulation of form
    By bacarley in forum Programming
    Replies: 1
    Last Post: 11-29-2011, 11:54 PM
  5. Autopopulation using macros?
    By EvanRosenlieb in forum Forms
    Replies: 2
    Last Post: 06-16-2011, 01:57 PM

Tags for this Thread

Posting Permissions

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