Solved

How can i make the FindFirst Method Work ?

Posted on 1998-07-15
1
212 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now