none
compile Error "Argument not Optional" RRS feed

  • Question

  • I am trying to catalogue my photograph files in a data base.  The Photograph table has the following Fields:

    FileID; YearID; MonthID; Folder; Subfolder; Hyperlink to the file path.

    I have a form with these fields on it.

    I'm trying to write code behind a button to insert a new record so that it falls in chronilogical order, and have written the following code:

    Dim NewYear as long; NewMonth As long

    NewYear = InputBox("Enter Year.", "Add Year")
    NewMonth = InputBox("Enter Month ID.", "Add Month ID")

    DoCmd.GoToRecord acDataForm, "Photographs", acLast
    If Me.YearID > NewYear Then
        Me.FileID = Me.FileID + 1
        Do
            DoCmd.GoToRecord acDataForm, "Photographs", acPrevious

            Me.FileID = Me.FileID+1

        Loop Until me.YearID <= NewYear

    End If

    I'm using similar code in other databases without problems, but with this one I keep getting Me.YearID highlighted and a compile error message saying "Argument Not Optional".

    Can somebody please show me what I'm missing?

    Thanks

    Sunday, February 5, 2017 9:48 AM

All replies

  • Can you please post the full procedure

    Private ...

    ....

    End ...

    And then state which line is highlighted in yellow when you debug your code.  This will greatly help us help you.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com 
    MS Access Tips and Code Samples: http://www.devhut.net



    Sunday, February 5, 2017 12:17 PM
  • As you are adjusting the numbers they clearly have no semantic significance, so why store the sequential numbers at all?  You can compute them at runtime in a query.  You'll find examples of row numbering and ranking queries as RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the following query numbers transactions by date:

    SELECT COUNT(*) AS RowNumber, T1.TransactionDate, T1.CustomerID, T1.TransactionAmount
    FROM Transactions AS T1 INNER JOIN Transactions AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
     AND (T2.TransactionDate<=T1.TransactionDate)
    GROUP BY T1.TransactionDate, T1.TransactionID, T1.TransactionAmount, T1.CustomerID;

    If we first create a query, QryTemp, to simulate your table by including YearID and MonthID columns rather than specific dates:

    SELECT YEAR(TransactionDate) AS YearID,
    MONTH(TransactionDate) AS MonthID, *
    FROM Transactions;

    We can then amend the first query above to number the rows chronologically:

    SELECT COUNT(*) AS RowNumber,T1.YearID, T1.MonthID, T1.CustomerID, T1.TransactionAmount
    FROM QryTemp AS T1 INNER JOIN QryTemp AS T2
    ON (T2.TransactionID<=T1.TransactionID
    OR T2.YearID & FORMAT(T2.MonthID,"00")<>T1.YearID & FORMAT(T1.MonthID,"00"))
    AND (T2.YearID & FORMAT(T2.MonthID,"00")<=T1.YearID & FORMAT(T1.MonthID,"00"))
    GROUP BY T1.YearID, T1.MonthID, T1.TransactionID, T1.TransactionAmount, T1.CustomerID;

    Note how the autonumber primary key TransactionID is used to act as a tie breaker in the case of two or more transactions being on the same date in my original table, on the same year/month in the QryTemp query which simulates your table.  So, in your case you'd replace the FileID column with an autonumber primary key if you don't have such a column already:

    When inserting a new row into your table via a bound form you then simply need to insert the year and month, and the sequential numbers will be computed in the query, taking account of any new rows inserted wherever they might fall in the chronology

    You don't actually need to return sequential numbers to maintain a chronological order, of course; you can simply return an ordered result table with a query, e.g.

    SELECT YearID, MonthID, CustomerID, TransactionAmount
    FROM QryTemp
    ORDER BY YearID, MonthID, TransactionID;

    Unlike the query which numbers the rows this will return an updatable result table, so could be used as a form's RecordSource.  If you want to return an updatable result table which includes row numbers you can do so by calling the VBA DCount function; my demo includes examples of this.


    Ken Sheridan, Stafford, England

    Sunday, February 5, 2017 1:16 PM
  • Hi cclaKR,

    can you tell me on which line you got that error.

    you are using Me.YearID 2 times in code above.

    If Me.YearID > NewYear Then

    or

      Loop Until me.YearID <= NewYear

    I agree with Daniel  Pineault that you need to post a full code so that we can try to test.

    also you need to check the suggestion given by Ken Sheridan regarding fileid and row numbering.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 3:28 AM
    Moderator
  • Thank you for the help everybody, but I was able to come up with the solution myself. I basically insert the new record at the correct date position, which means I have to increase the Record ID by 1 using the following code:-

    Dim RecID As long

    RecID = Inputbox("Enter the record number prior to the insertion point.")

    docmd.gotorecord, Acdataform,"Form1",Aclast

    Do

    RecordID = RecordID +1

    docmd.gotorecord ACDataform,"Form1", Acprevious

    loop until me.RecordID <= recID

    recID = me.RecordID

    docmd.gotorecord acdataform,"Form1",AcNewRecord

    me.RecordID = recID+1

    Sunday, February 12, 2017 1:23 PM
  • Hi cclaKR,

    from your last post , I can see that you find the solution by yourself.

    we are happy that your issue is solved now.

    but this thread is still open.

    please try to mark your last post as an answer.

    so that we can close this thread. if you do not mark then this thread will remain open.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 13, 2017 7:39 AM
    Moderator