Combo Box Autopopulation with an import button across databases

[June7]

Sorry about missing paren.

And sorry did not notice age of Access version you are using. Unfortunately, yes, DAO was not default back then and probably have to set a Library reference in the VBA editor or use late binding.

Another alternative would be to use ADO recordset.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.cbxHeatsMaster & "'", CurrentProject.Connection
CurrentDb.Execute "UPDATE TblMastertube SET Heat = '" & rs!Heats & "', C=" & rs!C & " WHERE ID=" & Me.cbxHeatTube

Why are you opening query QupdImHeat?

[Vita]

Sorry about missing paren.

And sorry did not notice age of Access version you are using. Unfortunately, yes, DAO was not default back then and probably have to set a Library reference in the VBA editor or use late binding.

Another alternative would be to use ADO recordset.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.cbxHeatsMaster & "'", CurrentProject.Connection
CurrentDb.Execute "UPDATE TblMastertube SET Heat = '" & rs!Heats & "', C=" & rs!C & " WHERE ID=" & Me.cbxHeatTube

I suppose I should note down to research what "DAO" and "ADODB" are because I have no idea.
I ran the current code and get a "Compile error: Method or data member not found" On "Me.cbxHeatsMaster". I assume, based on the cbx, that this is supposed to reference the combobox so I changed the name to be "ImHeatCbo" (Thats the name of the combo box).
Then I run into the same error but on the "rs!C". I am not sure what "rs" stands for, otherwise I would have attempted to fix this too.
I also believe it is going to run into an issue searching for "Me.cbxHeatTube" but I am not sure what 'HeatTube' That is supposed to reference.

Why are you opening query QupdImHeat?

That is what the button wizard did. Clicking the button should run QupdImHeat which should then update the fields of the selected MainID. Is that not how I should have it? (Note: My thought process originally included append rather than update so it was click to append the information to the fields but as I later learned append creates a new record rather than update an existing.)
Should I make it so the combobox runs QupdImHeat on change? Or did you have a different way you would recommend?

As last time, here is the current code running

Private Sub ImHeatBttn_Click()On Error GoTo Err_Import_Click


    Dim stDocName As String
    
    
    stDocName = "QupdImHeat"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tblHeatsmaster WHERE Heats = '" & Me.ImHeatCbo & "'", CurrentProject.Connection
    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

Edit: Could you also explain why you have things being set to apostrophe? I am trying to understand this code.

[June7]

rs is the recordset object variable.

No, the VBA code is supposed to update record without use of a query object.

However, I have figured out how to do this with a single SQL statement and eliminate recordset.

In a query object:

UPDATE tblMastertube, (SELECT Heats, C FROM [tblHeatsmaster] WHERE Heat=[enter Heats value]) AS Q
SET tblMastertube.Heat = [Q].[Heats], tblMasterbtube.C = [Q].C
WHERE (((tblMastertube.Heat)=[enter Heat value]));

[enter Heats value] and [enter Heat value] will trigger pop input prompts. These can be replaced with references to controls on form for selection of Heats and Heat values.

In VBA this would be like:

CurrentDb.Execute "UPDATE tblMastertube, (SELECT Heats, C FROM [tblHeatsmaster] WHERE Heats='" & Me.cbxHeats & "') AS Q " & _
                            "SET tblMastertube.Heat = [Q].[Heats], tblMastertube.C = [Q].C " & _
                            "WHERE tblMastertube.Heat='" & Me.cbxHeat & "'";

[Vita]

rs is the recordset object variable.

No, the VBA code is supposed to update record without use of a query object.

However, I have figured out how to do this with a single SQL statement and eliminate recordset.

In a query object:

UPDATE tblMastertube, (SELECT Heats, C FROM [tblHeatsmaster] WHERE Heat=[enter Heats value]) AS Q
SET tblMastertube.Heat = [Q].[Heats], tblMasterbtube.C = [Q].C
WHERE (((tblMastertube.Heat)=[enter Heat value]));

[enter Heats value] and [enter Heat value] will trigger pop input prompts. These can be replaced with references to controls on form for selection of Heats and Heat values.

In VBA this would be like:

CurrentDb.Execute "UPDATE tblMastertube, (SELECT Heats, C FROM [tblHeatsmaster] WHERE Heats='" & Me.cbxHeats & "') AS Q " & _
                            "SET tblMastertube.Heat = [Q].[Heats], tblMastertube.C = [Q].C " & _
                            "WHERE tblMastertube.Heat='" & Me.cbxHeat & "'";

I attempted to set both [enter Heats value] and [enter Heat value] to [Forms]![FrmMastertube]![ImHeatCbo] so that it would use whatever was selected in the combo box but it still prompts me for them?

Here is the code inside the Query:

UPDATE tblMastertube, [SELECT Heats, C FROM [tblHeatsmaster] WHERE Heat=Forms![FrmMastertube]![ImHeatCbo]]. AS Q SET tblMastertube.Heat = [Q].[Heats], tblMasterbtube.C = [Q].CWHERE (((tblMastertube.Heat)=[Forms]![FrmMastertube]![ImHeatCbo]));

Edit: it is probably also worth mentioning that the query keeps turning the parenthesis into brackets which brings up an error so I turn them back into parenthesis and it says UPDATE has a syntax error. Odd quirks of office 97.

[June7]

No, each input needs to be reference different combobox.

One combobox is to select Heats value from tblHeatsmaster.

One combobox is to select Heat value from tblMastertube.

Your query syntax does not quite follow my example. Please follow it very carefully. Should not be a dot after [ImHeatCbo] before AS. That dot is probably causing the query to be confused.

Need a space between C and WHERE.