Combo Box Autopopulation with an import button across databases

[Vita]

What is it you don't understand about JOIN?

When using the designer to build, if there is no link line connecting tables, will have to manually add it by clicking on key field and dragging to corresponding field of other table.

So, first build a SELECT query that has JOIN and WHERE clauses - make sure appropriate record(s) are retrieved - then switch to UPDATE to complete. Then look at SQLView to see statement.

The syntax I provided was per a site I reviewed https://www.geeksforgeeks.org/sql-update-with-join/. I did not test it.

There are many online tutorials for SQL.

I tried looking at a few tutorials and I got this made up but it says it has a syntax error in UPDATE and then it proceeds to highlight FROM

UPDATE TblMastertubeSET TblMastertube.Heat = TblHeatsMaster.Heats, TblMastertube.C = TblHeatsMaster.C,
 FROM TblMastertube 
     INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats
WHERE TblMastertube.Heat IN ([Forms]![FrmMastertube]![ImHeatCbo]);

[June7]

Again, updating same field that tables are linked on makes no sense. Although, it should work but is just unnecessary.

Need space between TblMastertube and SET. Did you use query builder Design View?

Okay, I did a test and the syntax I got from Access doesn't use FROM:

UPDATE TblMastertube
INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats
SET TblMastertube.C = TblHeatsMaster.C
WHERE TblMastertube.Heat = [Forms]![FrmMastertube]![ImHeatCbo];

If you used the query builder Design View as instructed, should have gotten a functional SQL.

[mike60smart]

Try a SPACE before the word SET

TblMastertubeSET

[orange]

Here is structure of an UPDATE query using Inner Join from my database that works as expected. Hope you caan use the structure to test/resolve your issue.

UPDATE AnimalCapture INNER JOIN AnimalLocs 
ON AnimalCapture.AnimalId = AnimalLocs.AnimalId 
SET AnimalLocs.CaptureDateX = AnimalCapture!CaptureDate;

[Vita]

Again, updating same field that tables are linked on makes no sense. Although, it should work but is just unnecessary.

Need space between TblMastertube and SET. Did you use query builder Design View?

Okay, I did a test and the syntax I got from Access doesn't use FROM:

UPDATE TblMastertube
INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats
SET TblMastertube.C = TblHeatsMaster.C
WHERE TblMastertube.Heat = [Forms]![FrmMastertube]![ImHeatCbo];

If you used the query builder Design View as instructed, should have gotten a functional SQL.

I was using the link you sent earlier and typing it manually. This site. Specifically this code:

UPDATE Geeks1  SET col2 = Geeks2.col2,  
col3 = Geeks2.col3  
FROM Geeks1  
INNER JOIN Geeks2 ON Geeks1.col1 = Geeks2.col1   WHERE Geeks1.col1 IN (21, 31);

Where Geeks1 is TblMastertube (Since thats the one I want the update to be written to) and Geeks2 being TblHeatsMaster.
I tried what you wrote and it comes up with a blank result/null value.

As for the space before SET, I had one its just when I copy paste the code for some reason it removes the first linebreak. Happened earlier in the forum too.
Also I am unsure what you mean by updating the same field. I followed the guide from that site.

Here is structure of an UPDATE query using Inner Join from my database that works as expected. Hope you caan use the structure to test/resolve your issue.

UPDATE AnimalCapture INNER JOIN AnimalLocs 
ON AnimalCapture.AnimalId = AnimalLocs.AnimalId 
SET AnimalLocs.CaptureDateX = AnimalCapture!CaptureDate;

Thank you. I will try this.

[June7]

Your SQL is updating Heat field while also using Heat field in table link. The example you show does not do that.

Why are you not building with Design View?

[Vita]

I was using a mix of SQL and Design view. I just remade it in Design view from scratch and this is what I get now.






Here is the code it writes:

UPDATE TblMastertube, TblHeatsMaster 
SET TblHeatsMaster.Heats = [TblMastertube]![Heat], TblHeatsMaster.C = [TblMastertube]![C]WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

I tried linking the heat fields in the relationship boxes above like so:

but then the result becomes null/blank (Would have attached picture but 5 image limit.
and here is the change in code when I link them:

UPDATE TblMastertube 
INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats 
SET TblHeatsMaster.Heats = [TblMastertube]![Heat], TblHeatsMaster.C = [TblMastertube]![C]WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

[June7]

Does SELECT TblMastertube.* INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats; show records?

Then test the filter
SELECT TblMastertube.* INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

If that works, then switch to UPDATE.

Again, don't update Heats or Heat field. Aren't you supposed to UPDATE TblMastertube table?

[Vita]

Does SELECT TblMastertube.* INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats; show records?

Then test the filter
SELECT TblMastertube.* INNER JOIN TblHeatsMaster ON TblMastertube.Heat = TblHeatsMaster.Heats WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

If that works, then switch to UPDATE.

Again, don't update Heats or Heat field. Aren't you supposed to UPDATE TblMastertube table?

This only works if ImHeatCbo has a value that is equal to an existing record in TblHeatsMaster and TblMastertube.
Otherwise it returns null/empty. I still attempted to swap it over to update and it was telling me that I can't use an asterisk in update. It also made no sense when transferred over to an update because it was trying to pull information from TblMastertube and update to TblMastertube.

Again, don't update Heats or Heat field. Aren't you supposed to UPDATE TblMastertube table?

I don't understand what you mean. I am attempting to filter it so that the heat field and chemistry gets updated.
So I want TblMastertube.Heat to be updated.

Here's an example:

I have an ID (that could be blank but for sake of example I am going to say has an old heat) that has an old/wrong heat number
In TblMastertube:
ID: 707
Heat: A1007
C: .0192
ImHeatCbo = Heat: A1010

TblHeatsMaster:
Heat: A1010
C: .026

Upon hitting the ImHeatBttn:
TblHeatsMaster: TblMastertube: Result: TblMastertube (ID: 707)
Heat: A1010 ----> Heat: A1007 = Heat:A1010
C: .026 ----> C: .0192 = C: .026
Example was being funky so I hope you understand but it is overwriting/updating


Which is why the SET function is made to set TblHeatsmaster.Heats = TblMastertube.Heat
with the critera being set to filter for the heat selected in the combo box. (

WHERE (((TblHeatsMaster.Heats)=[Forms]![FrmMastertube]![ImHeatCbo]));

[June7]

Which is why the SET function is made to set TblHeatsmaster.Heats = TblMastertube.Heat
with the critera being set to filter for the heat selected in the combo box. (

That would replace value in TblHeatsmaster.Heats with value from TblMastertube.Heat. Isn't that reverse of what you really want?

If I now understand correctly, you want the TblMastertube.Heat number A1007 to disappear altogether and be replaced with TblHeatsmaster.Heats number A1010? Then linking on Heat fields will NOT work. And there are two filter criteria involved. Update only TblMastertube record A1007 and pull data from only TblHeatsmaster A1010. I think this will require VBA opening a recordset object. Have comboboxes to select TblHeatsmaster.Heats and TblMastertube.Heat

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.cbxHeatsMaster & "'"
CurrentDb.Execute "UPDATE TblMastertube SET Heat = '" & rs!Heats & "', C=" & rs!C & " WHERE ID=" & Me.cbxHeatTube

Will you want to update every field in TblMastertube record or just some fields?

Does TblMastertube have any dependent related tables?

[Vita]

That would replace value in TblHeatsmaster.Heats with value from TblMastertube.Heat. Isn't that reverse of what you really want?

If I now understand correctly, you want the TblMastertube.Heat number A1007 to disappear altogether and be replaced with TblHeatsmaster.Heats number A1010? Then linking on Heat fields will NOT work. And there are two filter criteria involved. Update only TblMastertube record A1007 and pull data from only TblHeatsmaster A1010. I think this will require VBA opening a recordset object. Have comboboxes to select TblHeatsmaster.Heats and TblMastertube.Heat

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.cbxHeatsMaster & "'"
CurrentDb.Execute "UPDATE TblMastertube SET Heat = '" & rs!Heats & "', C=" & rs!C & " WHERE ID=" & Me.cbxHeatTube

Will you want to update every field in TblMastertube record or just some fields?

Does TblMastertube have any dependent related tables?

I know it has been a while I got swamped with other aspects of work. I don't know if you want to still help but I figured I should continue here so others can reference this in the future if need be.

I want it to update whatever ID I am on.

Current:
ID:763
Heat: A1007
C: .0192
Combobox = A1010

After button press:
ID:763
Heat: A1010
C: .026

So it updates the heat for that specific ID to equal the heat selected via the combobox.

Also I understand what you mean by the SET now and have flipped it.

[June7]

Did you try suggested code?

[Vita]

Did you try suggested code?

I did not because I am unfamiliar with record set. VBA is the program that opens when I go to edit an event procedure correct?
Do I just open the ImHeatBttn Event Procedure and add the code there?

[June7]

Yes, that is the VBA editor.

Yes, button Click event is suitable.

[Vita]

Yes, that is the VBA editor.

Yes, button Click event is suitable.

I tried the code (Had to add a end parenthesis on the end of set because it was saying it expected one to close the selection) and it says " Compile error: User-defined type not defined" while highlighting

rs As DAO.Recordset

(Also note that in the full code I do have "Dim" in the front of this but VBA was only highlighting this section)

When I was googling recordset I saw that the Microsoft documentation says it only applies to 2013. Is this perhaps something I don't have in the 2000 version?

Full code here:

Private Sub ImHeatBttn_Click()On Error GoTo Err_Import_Click


    Dim stDocName As String
    
    
    stDocName = "QupdImHeat"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.cbxHeatsMaster & "'")
    CurrentDb.Execute "UPDATE TblMastertube SET Heat = '" & rs!Heats & "', C=" & rs!C & " WHERE ID=" & Me.cbxHeatTube
    Me.Requery
Exit_Import_Click:
    Exit Sub


Err_Import_Click:
    MsgBox Err.Description
    Resume Exit_Import_Click
End Sub