ERROR IN CODING

[expand]

Hi,

Can someone tell me what is the problem in the below coding. In fact, i have a table called parametres with fields, Famille, Libelle, Code, numerique and another table called, STATION_CR_POS with fields strstationPCISacc, NUM_STATION_CR_POS. In the table parametres, the field numerique must be incremented which is not working. The code is :

Function Numéro_STATIONCRPOS() As Integer

Dim BdD As Database
Dim F, P As Recordset

Dim D, T As String
Dim C, Z, N As Integer

On Error Resume Next

Screen.MousePointer = 11

Set BdD = CurrentDb
Set F = BdD.OpenRecordset("Select * From STATION_CR_POS Order By idstation", dbOpenDynaset)
Set P = BdD.OpenRecordset("Parametres", dbOpenTable)
P.Index = "Famille_Libelle"

P.Seek "=", "Facture", "NUM_STATION_CR_POS"
Select Case P.NoMatch
Case False
D = P("Code")
N = P("Numerique")
Case True
MsgBox "Numéro de Facture Non Trouvé..." & Chr(10) & "Abandon de la Procédure", vbCritical + vbOKOnly, "Fichier Paramètres hebdo"
Exit Function
End Select

C = 0

F.MoveFirst
Do While F.EOF = False
Select Case F("strstationPCISacc")
Case C
Case Else
N = N + 1
Select Case N
Case Is < 1000000
Case Else
D = Chr(Asc(D) + 1)
N = 1
End Select
C = F("strstationPCISacc")
End Select
F.Edit
T = Format(N, "00000000")
F("NUM_STATION_CR_POS") = T
' F("DatFact") = Now
F.Update
F.MoveNext
Loop

F.Close

P.Edit
'P("Code") = D
P("Numerique") = N
P.Update
P.Close

Screen.MousePointer = 0

Numéro_STATIONCRPOS = True

End Function


Thanks to help.

Regards,

Shaheen.

 

[Remou]

You have a line:

On Error Resume Next

This makes it impossible to debug code as it will always run to the end. Furthermore, it is a line that should be avoided, it is very rarely necessary. You need an error trap:

On Error GoTo HandleError

For example, and a chunk at the end to deal with various errors (look this up), although this line, too, should be commented out for debugging.


This:
Dim F, P As Recordset
Does not work as you might think. You are not declaring F and P as recordsets, you are only declaring P. In addition, it is usually safest to explicitly declare the recordset as DAO.

Dim F as DAO.Recordset, P As DAO.Recordset

You will also need to correct the declarations for D, T and C, Z, N.

You appear to have incorrect syntax for Seek. In Access 2000 it says:

recordset.Seek KeyValues, SeekOption

Where KeyValues is an array.

I would suggest you use instead something on the lines of:

    'Assuming NUM_STATION_CR_POS is numeric
    strSQL = "SELECT Code, Numerique FROM Parametres " _
      & "WHERE Facture =" & NUM_STATION_CR_POS
    
    Set P = BdD.OpenRecordset(strSQL)
    
    If P.NoMatch Then
        MsgBox "Numéro de Facture Non Trouvé..." & Chr(10) & "Abandon de la Procédure", vbCritical + vbOKOnly, "Fichier Paramètres hebdo"
        Exit Function
    Else
        D = P("Code")
        N = P("Numerique")
    End If

However, for this to work NUM_STATION_CR_POS will need to have a value, which I think comes from the station table.


You say:
Screen.MousePointer = 11

This is more normally:
DoCmd.Hourglass True

And do not forget to set it off, when you are finished:

DoCmd.Hourglass False

The rest of the code also needs to be examined, but I am going to stop for some feedback, because I am not exactly sure what you want to do. I suspect you want to look up the parameters for each station as you move through the table, but in that case, the message "Numéro de Facture ... " becomes a nuisance. Please make a few notes on what you want to do.

 

[expand]

Thanks Remou. So nice of you.

In fact, I will work on the above and keep you informed when finished.

Regards,

Shaheen.