Solved

How can i make the FindFirst Method Work ?

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Update Query 1 20
Solved: How can I prevent deletions from a form in datasheet view? 5 27
IIF help, YN field 7 22
Dirty form - conditional formatting 5 17
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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