Originally Posted by
June7
Cannot use Me. reference in query objects. It is valid only in VBA. It is shorthand for the form or report name the code sits behind. The code I suggested would be in an event procedure such as a button Click or combobox AfterUpdate.
Still unclear to me how these tables should work together - what data needs to be pulled from where and saved where. Maybe a junction table is not need because data is not in a many-to-many relationship but a master and child table might be needed for a one-to-many relationship. Really should provide sample data even if you can't provide a db file - can build tables directly here in post. Go to the Advanced post editor and you will have some table building tools available. Can also copy/paste from Access table then edit with the table tools.
I don't think it was me who advised to change table naming but I agree with it.
Well I am glad you agree with it and I apologize for changing them and confusing you.
So TblMetalDBHeats is our subsidiaries table. They manufacture the parts so they input the Heat and Chemistry into that table for record keeping and their own inventory purposes.
Main table is our table where a team of people use the Main form to input newly arrived stock so that we can use a separate form(name is irrelevant) to find it in our warehouse.
The chemistry data consists of 26 fields I believe. (Not every field is filled so I have "allow zero length" set to "yes" so that nulls can be copied in. I wanted to use an if statement to check but this version only has IIF and I didnt know where to put it)
Up until now they enter every field manually. My objective is to make a dropdown list with all the heats so that they can click which one they want and then a import button for confirmation.
This will save time and reduce human error.
The following is what I originally did before reading online that a junction was the proper way to do it.
So I created a combobox [FsubCboImHeat] that has a row source of the query named [QlkpImHeatCbo] and has an "On exit" event procedure with the following code
Code:
If Me.Dirty Then
Me.Dirty = False
End If
Which is supposed to force the table to update. I got the code from another forum.
and then I created the button next to it named "ImHeatBttn" using the button wizard to have it run the query [QappImHeatBttn].
So the SQL linked to the button is as follows:
Code:
Private Sub ImHeatBttn_Click()
On Error GoTo Err_ImHeatBttn_Click
Dim stDocName As String
stDocName = "QappImHeatBttn2"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_ImHeatBttn_Click:
Exit Sub
Err_ImHeatBttn_Click:
MsgBox Err.Description
Resume Exit_ImHeatBttn_Click
End Sub
I still have all this code and can just delete [TblChemJunc] and [ChemistryJunctionF] and then change the query targets.
I had a separate issue using that original system though.