Solved

How can i make the FindFirst Method Work ?

Posted on 1998-07-15
1
216 Views
Last Modified: 2012-06-27
I'm coding a search method in VBA to search in my databases's tables.  Here's my code :

Private Sub Ajouter_Article_Click()

    Dim dbspointeur As Database
    Dim rstpointeurEmprunts As Recordset
    Dim rstpointeurDetails As Recordset
    Dim rstpointeurArticles As Recordset
    Dim strcritere As String
    Dim strcritere2 As String
    Dim strReq As String
   
    Set dbspointeur = CurrentDb
   
    strReq = "SELECT * FROM Articles ORDER BY [Numéro d'entrée d'Article];"
    Set rstpointeurArticles = dbspointeur.OpenRecordset(strReq, dbOpenSnapshot)
   
    strReq = "SELECT * FROM Emprunts ORDER BY [No Emprunt];"
    Set rstpointeurEmprunts = dbspointeur.OpenRecordset(strReq, dbOpenSnapshot)
   
    strReq = "SELECT * FROM [Détails Emprunts] ORDER BY [No Détail];"
    Set rstpointeurDetails = dbspointeur.OpenRecordset(strReq, dbOpenSnapshot)
     
   
   strcritere = "[Section] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Section] & " And [Sous-Section] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Sous-Section] & " And [Type d'Information] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Type d'Information] & " And [Numéro de Volume] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![No de Volume] & " And [Disponibilité] = True"

    strcritere2 = "[Section] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Section] & " And [Sous-Section] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Sous-Section] & " And [Type d'Information] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Type d'Information] & " And [Numéro de Volume] =" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![No de Volume] & " And [Date Retour] = Null"
   
    rstpointeurArticles.FindFirst (strcritere)
    rstpointeurDetails.FindFirst (strcritere2)

   
    If (rstpointeurArticles.NoMatch = True) Then
        MsgBox "Can't rent book"
       
    ElseIf (rstpointeurDetails.NoMatch = False) Then
        MsgBox "Can't rent book ! "
                 
         
        Else
             
            rstpointeurArticles![Disponibilité] = False
           
            If NombreFoisClique = 0 Then
           
                With rstpointeurEmprunts
                    .AddNew
                    ![No Membre] = Forms![Nouvel Emprunt]![No Membre]
                    ![No Employé] = Forms![Nouvel Emprunt]![No Employé]
                    ![Date Départ] = Date
                    .Update
                End With
                rstpointeurEmprunts.Close
                NombreFoisClique = 1
               
                With rstpointeurDetails
                    .AddNew
                    ![No Emprunt] = Forms![Nouvel Emprunt].[No Emprunt]
                    ![Section] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Section]
                    ![Sous-Section] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Sous-Section]
                    ![Type d'Information] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Type d'Information]
                    ![Numéro de Volume] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![No de Volume]
                    ![Date Retour] = Null
                End With
           
                rstpointeurDetails.Update
                rstpointeurDetails.Close
               
               
            Else
           
                With rstpointeurDetails
                    .AddNew
                    ![No Emprunt] = Forms![Nouvel Emprunt].[No Emprunt]
                    ![Section] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Section]
                    ![Sous-Section] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Sous-Section]
                    ![Type d'Information] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Type d'Information]
                    ![Numéro de Volume] = Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![No de Volume]
                    ![Date Retour] = Null
                    .Update
                End With
                rstpointeurDetails.Close
             
            End If
        End If
   


End Sub

The structure of the table "Articles" is : No Article, Section, Sous-Section, Type d'Information, Numéro de Volume, Titre, Auteur , ...

The structure of the table "Détails emprunts" is : No Detail, No Emprunt, Section  , Sous-Section, Type dINformation, Numéro de Volume, Date Retour

The Structure of the table "Emprunts" is : No Emprunt, No Membre, No Employé, Date Départ.

As you can see , this procedure is for a library, to rent a book and enter the transaction in tables.
However , my "FindFirst" method gives me error 3070, that it doesn't recognize the value of my form's fields as a valid field name or expression.

How can i make this FindFirst Work ?
0
Comment
Question by:soleil_18
1 Comment
 
LVL 8

Accepted Solution

by:
Helicopter earned 50 total points
ID: 1976755
If your fields are text fields they must have a '.....' surrounding them


e.g.

"[Section] ='" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Section] & "'  And [Sous-Section] ='" & Forms![Nouvel Emprunt]![Sous-Formulaire des Articles a Emprunter]![Sous-Section]& "'"

assuming these 2 form fields are text

I suggest you break the code just before the find first statement and have a look at the value of strcriteria to make sure the string makes sense.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question