I'm with you until the very last part, where the second query looks at the output of the Find Unmatched Chemicals query. The second query looks for rows where the ChemicalSyn field is not blank and the ChemicalID field is blank. However, the results of the initial query don't include the ChemicalID field in the result. Should this field be added by the Find Unmatched Chemicals query?

Here's what I have so far, anyway:

qryDELETEImports:

DELETE tblImport.*
FROM tblImport;

qrySELECTUnmatchedChemicals:

SELECT tblImport.SampleID, tblImport.Depth, tblImport.SampleDate, tblImport.ParType, tblImport.ChemicalName
FROM tblImport LEFT JOIN tblChemSyn ON tblImport.ChemicalName = tblChemSyn.Synonym
WHERE (((tblChemSyn.Synonym) Is Null));

I have a form set up (frmImport) so the user can browse to a lab data file and click a button to import. All of the code that follows is within the command button Click event on that form:

Private Sub cmdImportSubmit_Click()

'Turn user prompt for query off
DoCmd.SetWarnings False

'Clear data from tblImports
DoCmd.OpenQuery "qryDELETEImports"

'Turn user prompt for query back on
DoCmd.SetWarnings True

'Import laboratory data fiile to a temporary table called tblImport
'On Error GoTo HandleError
DoCmd.TransferText acImportDelim, , "tblImport", txtImportFileLocation, True

'Clear the txtImportFileLocation from text box
txtImportFileLocation = Nothing

'Find unmatched chemical names that aren't in tblChemicalSyn
DoCmd.OpenQuery "qrySELECTUnmatchedChemicals"

End Sub

The code that controls the browse to dialog box is all in a module outside of the frmImport code.

You're good to here:

'Find unmatched chemical names that aren't in tblChemicalSyn
DoCmd.OpenQuery "qrySELECTUnmatchedChemicals"

What I was suggesting was opening a recordset on that query (actually, use the query SQL string to defined the recordset, not open the query) and then check for the existence of ANY reocrds. If even one exists, then open a different form on tblChemSyn that forces the user to match up new chemical names to ones used in the tblChecmical table. You will want to work out whether users are limited to the list values, or if they can add new chemicals (not synonyms) and set the Not In List values and events accordingly.

Then once you have that worked out, they can continue to move the data from the import table to the other tables.

Alternatively, you can just take in all the data, and then open a form on the records that were imported that do not have matching chemical names.



mmcdonal

Ah ha, I see now what you meant! I'm hitting a mental wall on setting up the second form that asks the user to match the unknown name to a ChemicalName and insert it as a Synonym. In thinking about this, I'll have to create another temporary table to merge the list of ChemicalNames from tblChemicals with the corresponding Synonyms from tblChemSyn for the form to allow a user to select the right ChemicalName from the list. This will probably need done with another query? Once the link is made between unknown chemical and ChemicalName What would be the most efficient way to pop up the form and then update tblChemSyn with the new synonym with attached ChemicalID? And can I make the form work as easily with multiple unknown chemical names that would need matched up with multiple ChemicalNames? I think this is a case where I'm stretching my knowledge about form interoperation and sql.

Again, I have to thank you for getting me this far...hours if not days have definitely been saved.

Here's what I have so far on the import, I've added the DefineRecordset sub to call the query to open the recordset:


Private Sub cmdImportSubmit_Click()

'Turn user prompt for query off
DoCmd.SetWarnings False

'Clear tblImports of data
DoCmd.OpenQuery "qryDELETEImports"

'Turn user prompt for query back on
DoCmd.SetWarnings True

'Import laboratory EDD to a temporary table called tblTempEDD
'On Error GoTo HandleError
DoCmd.TransferText acImportDelim, , "tblImport", txtImportFileLocation, True

'Clear the txtImportFileLocation from text box
txtImportFileLocation = Nothing

'Find unmatched chemical names in tblChemicalSyn
Call DefineRecordset

'Continue import procedure here

End Sub


Public Sub DefineRecordset()

Dim cnChemDB As ADODB.Connection
Dim rsSynonyms As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & CurrentProject.Path & "\ChemDB.mdb;"

Set cnChemDB = New ADODB.Connection
cnChemDB.Open strConnection

'Open recordset
Set rsSynonyms = New ADODB.Recordset
With rsSynonyms
    rsSynonyms.CursorType = adOpenStatic
    rsSynonyms.CursorLocation = adUseClient
    .LockType = adLockOptimistic
    rsSynonyms.Open "qrySELECTUnmatchedChemicals", cnChemDB
End With

'Check to see if recordset is empty
If rsSynonyms.BOF And rsSynonyms.EOF Then
    MsgBox "Message for testing - All chemical names are recognized." 'Remove msgbox once procedure is finished
'If the recordset is not empty and an unknown chemical name is present
Else
    Set Me.Recordset = rsSynonyms
End If

'Add synonym form controls to be inserted here?

End Sub

Sorry I don't have time today, but I will post more tomorrow. For the synonym update, just create an updateable query and pop open a datasheet. Alternatively, as I mentioned, you could push the unknown terms directly into the chemsyn table and pop open an updateable form filtered for only those records where ChemicalID = "" or IsNull(). Then flow on from the on close event of this form to finish the data import.



mmcdonal

No problem, I'm happy for the assistance whenever I can get it!

I've chosen to have the unrecognized chemical names added to tblChemSyn as new Synonyms and then require the user to associate them with a known ChemicalName or add the unknown chemical name as a ChemicalName. I have a form (frmSynonym) set up that pops up during import that displays the results of a query to find Synonyms in tblChemSyn with no ChemicalID in a listbox (lstUnrecognizedChemical). I also have a listbox on the form that displays the complete list of ChemicalNames from tblChemicals (lstKnownChemicals).

My plan is to require the user select an unknown chemical name from the first listbox and a ChemicalName from the second listbox, then on command button click have the ChemicalID corresponding to the selected ChemicalName from the second listbox added to tblChemSyn as the ChemicalID for the Synonym selected in the first listbox. If the unknown chemical name selected from the first listbox doesn't match a ChemicalName from the database, I'll have a check box that will branch things off so that the Synonym will be added to tblChemicals as a new ChemicalName with a new ChemicalID that will then need to be added to tblChemSyn as the ChemicalID for the Synonym. The form will then refresh itself and the listboxes will update (removing the unknown chemical that was just added to the database and showing any that are left to add).

I've got the form set up so it pops up when unknown chemicals are found, but I'm having a hell of a time trying to get the two listboxes matched up with their respective tables and getting the SQL to match and update everything.

I believe this is basically an equi-join when I get right down to it...but I can't figure out how to make queries look up the selected listbox values from frmSynonym, equate them to the values in tblChemicals and tblChemSyn and then update each respective table as necessary.

It seems to me that to get the list boxes to work, you set them both so they only allow one selection.

Then the columns in the tblChemical list box are PK, and Chemical name, the bound column is the PK.

Then in the list box that has the tblChemSyn information, you have PK and Synonym, bound to PK on that table.

Then you would do this on the On Click event of your match button:

Take the PK from each list box in a variable.
Close the form.
Do an update query on the PK from tblChemSyn list box selectio to set the value of the Chemical name field to the PK from the list box selection.
If no errors are returned, do a message box that says "[Chemical Name] successfully matched with [Synonym]"
Then start the query process again and check for empty fields, and if there is one still empty, open your form again.
Otherwise proceed with processing.

Does this help, or do you need the code?

mmcdonal

I think I have the import working now! I have to thank you again for all the assistance...I'd be in a real bind if it weren't for your help. One last thing - would you mind looking over my .mdb file to make sure I didn't miss anything or build in problems waiting to happen?

Sure. Rename the file with a ._db extension so it will get past the email filters. What issues are you concerned with?

mmcdonal

My primary concern is inappropriate or inefficient VBA and SQL. I have the mechanism working...but I'm not confident that I don't have memory waste or "errors waiting to happen" built into the procedures. I believe the queries are sound, but again...there may be a better way to do things than what I figured out. I'll send you a message through the forum message system and reply with the ._db file ASAP upon receiving your email address. Thanks again!