Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can i make the FindFirst Method Work ?

Posted on 1998-07-15
1
Medium Priority
?
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
Helicopter earned 150 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

722 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