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!