Need help on Run-time error 3265

[Solidbry]

Hi guys!

So I'm a little rusty on VBA programming last time I did some VB was back in my college days :/

Here's my code:

Public Sub TraiterSample()
 
   Dim ClefSampleRef As String 'Je suppose que c'est du texte
   Dim cptSample As Long
 
   Dim load_assays As dao.Database: Set load_assays = CurrentDb
   load_assays.QueryDefs("Empty_Result_temp").Execute
 
   Dim Populate_export As dao.Recordset: Set Populate_export = load_assays.OpenRecordset("Populate_export") 'rSample
 
   Dim Result_temp As dao.Recordset: Set Result_temp = load_assays.OpenRecordset("Result_temp", dbOpenDynaset) 'rResultat
 
   Do While Not Populate_export.EOF()
 
      If ClefSampleRef <> Populate_export![Sample_no] Then
         Result_temp.AddNew 'Ajoute un nouvel enregsitrement
         Result_temp![Sample_no] = Populate_export![Sample_no]
        Result_temp.Update
 
         ClefSampleRef = Populate_export![Sample_no] 'Note la nouvelle clef de référence
         cptSample = 0 'Reinitialise le compteur de valeur
      End If
 
     Result_temp.FindFirst ("[Sample_no]=""" & Populate_export![Sample_no] & """")
 
      If Not Result_temp.NoMatch Then
           Result_temp.Edit
           cptSample = cptSample + 1
          Result_temp.Fields("Au1_1ppm" & cptSample) = Populate_export![Au1_1ppm]  'Recopie la valeur dans le résultat dans la colonne voulue
         Else
           Error 5 'Cas impossible, soit il existait soit on l'a créé
      End If
 
      Populate_export.MoveNext 'Lit le sample suivant
   Loop
 
   Result_temp.Close: Set Result_temp = Nothing 'Ferme la source de données, libère la mémoire
   Populate_export.Close: Set Populate_export = Nothing 'Ferme la source de données, libère la mémoire
 
   Set load_assays = Nothing 'Libère la mémoire utilisé par l'objet db
End Sub

I'm having the error 3265 which says:" Item not found in this collection" on line:

Result_temp.Fields("Au1_1ppm" & cptSample) = Populate_export![Au1_1ppm]

Populate_export![Au1_1ppm] has a value in it so the problem must be with the .Fields thing. After browsing, I've found out that most of the time, this error is caused by a misspelled field name or a reference to a field that does not exist. I've checked everything related to this field name (Au1_1ppm which is btw gold values from lab assay certificates) and everthing is fine.

To wrap it up, this piece of code is going down my Populate_export query which is linked to a Excel sheet. If it find a re-assay for the same sample number (2 identical sample number with 2 values), it places the value of the re-assay in a specific field, get rid of the Doublon and put everything in my Result_temp table. That way, I only got one entry with all the values instead of having multiple entries for one sample number.

Do you guys have any idea as to why I'm having this freaking error ? Also, if there's a way to optimize that code I'm open to all suggestions. As I said, I'm back to being a rookie in this :P

Thx a lot

[DataMiser]

It has been ages since I have did anything with dao. It is severly outdated. I can not rembmer the code for opening a table in dao. Typically when opening a recordset you would use "select * from table" or select field1,field2,... from table"

The error indicates that the field you are referecing does not exist in the recordset you are referencing either because it does not exist, was not selected or is misspelled.

[Solidbry]

Thanks for the input

Can you give me an example of what the code should look like if I open my table with a SQL select ? I've seen some ado stuff but I'm pretty unsure about the exact syntax and if it need arguments or something.

Thanks alot!

[DataMiser]

SQL select works the same with DAO as well

"Select * from TableName" will return all fields from the given table or instead of * you can use a comma seperated list of the fieldnames you want to select and the order you want them selected in.

For a sample of ADO syntax try a quick google search there are tons of examples on the web.

[Solidbry]

Allright I should be able to figure something out.

Thx!

[WoF]

This "not found in collection" error appears most likely when a named field is not found in the fields collection.
I think the creation of the field name might be the problem.

Result_temp.Fields("Au1_1ppm" & cptSample) = ...

depending on the value in cptSample, the created fieldnames will be like
Au1_1ppm1
Au1_1ppm2
...
Au1_1ppm_10
and so on
When the error comes you could check what value is in the var cptSample and then look in the table if this field exists at all.

[Solidbry]

Hi guys!

Here's my corrected code:

Public Sub TraiterSample()
 
   Dim tFieldName As Variant: tFieldName = Array("Au1_1ppm", "Au2_1ppm", "Au1_2ppm", "Au1_3ppm") 'Défini les colonnes résulat
 
   Dim ClefSampleRef As String 'Je suppose que c'est du texte
   Dim cptSample As Long: cptSample = -1
 
   Dim load_assays As dao.Database: Set load_assays = CurrentDb
   load_assays.QueryDefs("Empty_Result_temp").Execute
 
   Dim Export_assays As dao.Recordset: Set Export_assays = load_assays.OpenRecordset("Export assays")
 
   Dim Result_temp As dao.Recordset: Set Result_temp = load_assays.OpenRecordset("Result_temp", dbOpenDynaset)
 
   Do While Not Export_assays.EOF()
 
      If ClefSampleRef <> Export_assays![Sample_no] Then
         Result_temp.AddNew 'Ajoute un nouvel enregsitrement
         Result_temp![Sample_no] = Export_assays![Sample_no]
         Result_temp![From] = Export_assays![From]
         Result_temp![To] = Export_assays![To]
         Result_temp![Cert_date] = Export_assays![Cert_date]
         Result_temp![Certif_no] = Export_assays![Certif_no]
         Result_temp![Lab_id] = Export_assays![Lab_id]
         Result_temp![Au1_1ppm] = Export_assays![Au1_1ppm]
         Result_temp![Au1_2ppm] = Export_assays![Au1_2ppm]
         Result_temp![Au1_3ppm] = Export_assays![Au1_3ppm]
        Result_temp.Update
 
         ClefSampleRef = Export_assays![Sample_no] 'Note la nouvelle clef de référence
         cptSample = -1 'Reinitialise le compteur de valeur
      End If
 
     Result_temp.FindFirst ("[Sample_no]=""" & Export_assays![Sample_no] & """")
 
      If Not Result_temp.NoMatch Then 'S'il y a un match
           Result_temp.Edit
           cptSample = cptSample + 1
          Result_temp.Fields(tFieldName(cptSample)) = Export_assays![Au1_1ppm] 'Recopie la valeur dans le résultat dans la colonne voulue
         Else
           Error 5 'Cas impossible, soit il existait soit on l'a créé
      End If
 
      Export_assays.MoveNext
   Loop
 
   Result_temp.Close: Set Result_temp = Nothing 'Ferme la source de données, libère la mémoire
   Export_assays.Close: Set Export_assays = Nothing 'Ferme la source de données, libère la mémoire
 
   Set load_assays = Nothing 'Libère la mémoire utilisé par l'objet db
End Sub

It is now compiling without any errors but it doesn't do exactly what I need so I'll explain what needs to be done with that code and maybe someone can give me some insights on how to make it happen.

I have a query named Export assays that has all my values in it. This query comes from a compilation of laboratory certificates containing a bunch of results representing gold values present in samples. My problem is that some samples are sometime getting re-tested to confirm the result. Samples are getting re-tested with either the same method or another method depending on the case. The methods are my Au1_1ppm Au2_1ppm Au1_2ppm and Au1_3ppm fields.

When a sample is re-tested, it triggers a new value but still has the same sample number. I can't have 2 entries with the same sample number and different values. I need to only get one entry with different values if that sample got re-tested. They should all be lined-up on the same row. Here are examples of the current situation versus what I need in the end:

Sample_no Au1_1ppm Au1_2ppm Au1_3ppm
sample1 2.5
sample1 2.1
sample2 5
sample3 8
sample3 10

The output of the code should be:

Sample_no Au1_1ppm Au2_1ppm Au1_3ppm
sample1 2.5 2.1
sample2 5
sample3 8 10

In words, sample1 got re-tested with the same method and had 2.1 instead of 2.5. sample2 is untouched. sample3 got an 8 but the geologist chose to re-test sample3 with a more precise method just to be sure and it returned 10. Note that the field Au2_1ppm doesn't exist in my Export assays query. I need to create it and populate it in the code. This is the hard part for me and I can't figure how to do this. I think I'm near but something is missing.

Once that everything has been sorted out, the code dumps the results in my results_temp table. I export this table in a text file and import it in my geology software.

Thanks alot guys

P.S. I don't know if I should start a new thread with this because it doesn't concern the error 3265 anymore

[dglienna]

You'd need to set up an array, with a slot for each test, along with max number of testable items in the program. 14x5, or 20x4?

Then, depending on the value of (0) you might have x(1)=8.5 x(2)=8.2 x(3)=0 x(4)=0

Then, add it like this:

if x(1)>0 Then
   Result_temp![Au1_1ppm] = Export_assays![Au1_1ppm]
End If

Test 1 and 2 would print, test 3 and 4 would not

[Solidbry]

Allright I'll try this.

Thx David