![]() ![]() Why do you have a table with both Material/Material Description & Component/Component Description fields? Using an UPDATE query would prevent that & do the update in one stepĪlso whichever method you use, your UNION query may be creating duplicate records. MsgBox "Error " & Err.Number & ": " & Err.DescriptionHowever, repeatedly emptying tables & repopulating them will cause database bloat AS PartDescriptionįROM Master_MATERIAL) AS C ", dbFailOnError SELECT DISTINCT C.MfrPartNumber, C.PartDescription If there are no records in the record set, there will be an error.Ĭurrentdb.Execute "DELETE * from tblPartMain", dbFailOnErrorĬurrentDB.Execute "INSERT INTO tblPartMain ( MfrPartNumber, PartDescription ) You are trying to set the variable "strTable" to a value, but "strTable" was never declared (no Dim statement)/ Also, "strTable" is never used in the code, so why is it there? Should delete this line.Ĥ) Good programming practice is to test for records BEFORE trying to use the record set. Should delete this line.ģ) Compile error - Variable not defined. You are trying to open a recordset named "rsParts", but "rsParts" was never declared (no Dim statement). Missing the error handling codeĢ) Compile error - Variable not defined. Set rsParts = db.OpenRecordset(strSQL) ' 2ġ) Compile error - the Label "EH" is not defined. Option Explicit '<-EVERY module should have this line at the top Sub test() '<< the name I used for this routine RstTarget! = sPartDescriptionĬode: Option Compare Database '<-EVERY module should have this line at the top SPartDescription = rstSource!PartDescription Set rstTarget = db.OpenRecordset("tblPartMain", dbOpenDynaset) StrSQL = strSQL & " FROM Master_SAP_MATERIAL) AS C " StrSQL = strSQL & " UNION SELECT Component AS MfrPartNumber," StrSQL = strSQL & " FROM Master_SAP_MATERIAL" StrSQL = strSQL & " FROM (SELECT Material AS MfrPartNumber, AS PartDescription" StrSQL = "SELECT MfrPartNumber, PartDescription" Is this the correct VBA or is there a better way to update the table with the SELECTed records I believe I followed the steps you outlined and got this to work. ![]() For me, it is quicker to use the create query wizard to get the "bones" of the query, then modify it. If you know SQL very well, you can just type in the SQL. I make any changes i need, then I can copy the SQL and paste it into VBA to be able to use the update query in code. I will typically create a SELECT query (with criteria), execute it to see if the correct records are returned, then change the query to an update query and add values to update the field(s) to and add criteria. Switch to SQL view to see what the SQL now looks like. You can switch to design view and add criteria in the "Criteria" row. This query would update ALL records because there is not any criteria to limit records. Now you have 2 field that would be updated. Add a value to the row in the grid that is "Update To". Switch back to design view and add another field. Switch to "SQL" view to see what the SQL for the update query would look like. In design view, go to the menu at the top "Query Tools", and change the query to an "Update Query".Īdd a value to the row in the grid that is "Update To". So now you have a working SELECT query (1 field). If the query executes and returns records, great. Execute it to see if records are returned. In a test dB, create a SELECT query that has 1 field, using the Query design grid. Something that might help you learn how to create update queries. Once you have done this, you would use eitherĭoCmd.RunSQL "." or CurrentDB.Execute "." Then create an UPDATE query based on the SAVED UNION query - you can use the query designer window for this part Instead run the UNION query first & save it You are trying to use a UNION query as your record source which I doubt will work You aren't setting the 2 fields MfrPartNumber, PartDescription to anything - what do you want them to be updated to?Ģ. Also, can someone share the VBA code I need to execute this query.įROM (SELECT MfrPartNumber, PartDescriptionįROM Catalogs) There appear to be several issues with your update queryġ. I am not sure what is wrong but I am very new to this. Here is the SQL to update the table but it is not working. I want to create an Update Query that I want to execute via a Command Button on my form. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |