How do I set-up a counter code and impliment an update query when counter hits its mark

[aquabp]

I have to track physical receipts. Currently Scanning them in does not help me.

The Receipt Table holds one receipt number per line along with other info and a PrnJr number. This PrnJr number is a page identifier and is updated to each record by the module placed in an update button. Two receipts fit on one report page. The report breaks page at this number. I physically tape the receipts to this page and put them in a book…. Accountant is happy!

The module is basically a big if than statement. So basically if a date has <= 2 records, assign a PrnJr Number to those two receipt records and print the report, tape the receipts onto the page, put them in the book. Using mostly an update query, it works.

The problem comes when the receipt count for the day is >2. Currently I stop the code and manually update the PrnJr Number.

I am at a loss at how to solve this issue. From what I have researched I think I need to have a counter that counts up to two add the PrnJr number then add PrnJr+1 and add it to the next two records until all the records for that day are accounted for. All the examples I have tried that I have seen on line I have not gotten to work. I am not sure how to start this.

Any help or direction anyone can give me would greatly be appreciated!

[CJ_London]

I am not familiar with the terms ' tape the receipts onto the page, put them in the book.'

suggest provide some sample data and an example of what you want as a result.

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...d.php?t=282740


@Aquapb - please look at this link http://www.excelguru.ca/content.php?184

[aquabp]

Did not know they were connected. Read the article. Thanks.

[aquabp]

A receipt is a physical piece of paper you get from a vendor, shop keeper etc. These pieces of paper are called receipts and according to the IRS they need to be kept track of in such a way that they can be identified from the system to the binder and from the binder to the system. The term " tape the receipt onto the page" refers to the physical act of using scotch tape and connecting the receipt to the paper which has the PrnJr Number at the top of its page. The PrnJr number id's the binder that the receipt is located connecting the system to the binder. The book is a physical book or binder that holds these PrnJr pages. In larger companies these receipts are called Purchase orders and once received and entered into the system are put into files, that are identified, and those files are put into storage boxes, which in turn are put into storage rooms pending independent audits.

I will add an example of the results I need. The question I am asking is how to get there.

Also, in reading your link I saw that I should link a post to if I am posting to another form. Can I do that after the post was posted on the two forums or will this create another post?

[CJ_London]

Can I do that after the post was posted on the two forums or will this create another post?

don't worry about it - the other forum is now aware

With regards your question, suggest post the table designs you have and their relationships. If you haven't got that far, put some example data into excel and post that

[ssanfu]

The module is basically a big if than statement. So basically if a date has <= 2 records, assign a PrnJr Number to those two receipt records and print the report, tape the receipts onto the page, put them in the book. Using mostly an update query, it works.

The problem comes when the receipt count for the day is >2. Currently I stop the code and manually update the PrnJr Number.

Would you also post the code?

[aquabp]

Ok, Here is the code. It has changed from original code. It works except for the select statement. Currently have a select statement hard coded with the date. The inner join statement below it errors out. Error says Syntax error in Join statement. I'm a beginner so the code is not pretty I apologize a head of time.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst400_ReceiptS As DAO.Recordset
Dim rst104_Date As DAO.Recordset
Dim rst105_NRec As DAO.Recordset
Dim strSQL As String
Dim intI As Long
Dim ICount As Integer
Dim SN As Long


    DoCmd.RunMacro "475MD01MC01test"      ' Update 104_Date table with selected date. Date formated into MSort "yymm" & 0001+1


    Set db = CurrentDb()
    Set rs = db.OpenRecordset("475_MD01RF01Q01") 'open the recordset for use (table, Query, SQL Stateme
    
With rs 'W1
    If .RecordCount > 0 Then 'Ensure there are records. The next 2 line determine the number of returned records IT1
        rs.MoveFirst
        rs.MoveLast 'This is required otherwise you may not get the right count
        ICount = rs.RecordCount 'Determine the number of returned records
            
    Do While ICount > 0   'Not .BOF
        strSQL = "SELECT * From 400_ReceiptS WHERE Date= #11/5/15# AND JrLoc=0"  'This works but cannot control the Date selection.
        'strSQL = "SELECT * From [400_ReceiptS] INNER JOIN [400_ReceiptS] ON [400_ReceiptS].Date=[104_date].date where JrLoc=0"
        
        Set [rst400_ReceiptS] = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        
           If rst400_ReceiptS.EOF Then Exit Sub    'If the recordset is empty, exit.
                SN = GetGST()
                intI = SN
                With rst400_ReceiptS
                Do Until ICount = 0
                If ICount = 0 Then Exit Sub
                .Edit
               ![JrLoc] = intI
               ![JrPrnt] = -1
         
               .Update
               .MoveNext
               intI = intI + 1
               
        Loop
End With


   rst400_ReceiptS.Close
   dbsGLE475_RecLogTest.Close


   Set rst400_ReceiptS = Nothing
   Set dbsGLE475_RecLogTest = Nothing


   Exit Sub


ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
 Loop
    End If 'IT1
End With 'W1
End Sub

[CJ_London]

dates need to be expressed in US format (mm/dd/yyyy) so not sure if your date is 5th Nov or 11th May - but also you need full year - 2015

Date is a reserved word so change it to something more meaningful

learn to use the code tags (the # button) to preserve insetting - not insetting your code makes it difficult to read - and many people won't waste their time trying to decipher it

[aquabp]

Ok, I think I fixed the formatting. Sorry. As far as the date I assume you are looking at the 11/5/15 in the code. If so, this line actually works. I did change the date names on the two tables as well as in the code but still got the same error. The line below it is what I am trying to fix. I get a syntax error on the join statement. It debugs and highlight's the Set line below it. I have added the prefix RST in front of the table names, removed the brackets but still get the error message. So, the first line the DoCmd.RunMacro "475MD01MC01test" asks for a date and then updates it to a table called 104_Date. This date value now called VDate is what I want to link to the 400_ReceiptS table to pull out a specific date to be updated.

strSQL = "SELECT * FROM rst400_ReceiptS INNER JOIN rst400_ReceiptS ON [rst400_ReceiptS].RDate = [rst104_date].Vdate where JrLoc=0"
Set [rst400_ReceiptS] = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) 'This is were the debug pops to when I get the error, "Syntax error in JOIN operation.

The current active line will run the code but I would have to go in and change the date each day. Thanks for your input.

[June7]

When there are multiple tables in query, prefix the * wildcard with object names.

SELECT rst400_ReceiptS.*, rst104_date.*

However, AFAIK cannot Set a recordset with an SQL statement that references recordsets, certainly not an SQL statement that references the object variable being set.

SQL statement must reference table or query objects. Maybe you need to remove the rst prefixes in the SQL statement.

Also, the [] are not appropriate with VBA variables.

If you want the date parameter to be dynamic, use a form for input of value then reference the control and concatenate into SQL string, like:

"SELECT * FROM tablename WHERE [datefield]=#" & Me.tbxDate & "#;"

[ssanfu]

In looking at the code, I see many errors and logic bombs........ here are a few of my comments.


This

    rs.MoveFirst
    rs.MoveLast    'This is required otherwise you may not get the right count
    ICount = rs.RecordCount    'Determine the number of returned records

the above statements should be in the following order

    rs.MoveLast    'This is required otherwise you may not get the right count
    ICount = rs.RecordCount    'Determine the number of returned records
    rs.MoveFirst   'move back to the top of the recordset

'-----------------------------------------------------------

This is an endless loop. "ICount" will never get to zero. "ICount" is an integer but is never decremented.

With rst400_ReceiptS
    Do Until ICount = 0   
       If ICount = 0 Then Exit Sub   
       .MoveNext
    Loop
End With

'-----------------------------------------------------------


I cut out a lot of the statements to focus on the control statements

Public Sub xxold()
 

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("475_MD01RF01Q01")    'open the recordset for use (table, Query, SQL Stateme

    With rs    'W1
        If .RecordCount > 0 Then    'Ensure there are records. The next 2 line determine the number of returned records IT1
            ICount = rs.RecordCount    'Determine the number of returned records

            Do While ICount > 0   '<<-ICount will never get to zero
                Set [rst400_ReceiptS] = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

                If rst400_ReceiptS.EOF Then Exit Sub    'If the recordset is empty, exit.
                 'this is a very poor way to exit a sub. All open record sets are not closed and not destroyed
                 ' the dB object is not destroyed either


                With rst400_ReceiptS
                    Do Until ICount = 0   '<<-ICount will never get to zero. "ICount" is an integer but is never decremented.

                        If ICount = 0 Then Exit Sub   '<<-ICount will never get to zero. "ICount" is an integer but is never decremented.
                        'this is a very poor way to exit a sub. All open recordsets are not closed and not destroyed
                        ' the dB object is not destroyed either

                        .MoveNext

                    Loop
                End With

                Exit Sub
                'exits without ensuring all open recordsets are closed and destroyed
                ' the dB object is not destroyed either

ErrorHandler:      'error handler is inside a loop. Plus, there is no "On Error" statement at the top of the sub
                MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
            Loop
        End If    'IT1
    End With    'W1
End Sub

Note that in the above code, the recordset "rs" never moves off of the current record (no rs.MoveNext or rs.MovePrevious)

[aquabp]

Hi June7, ok made that change to an unbound box on the form. However still do not understand why it wouldn't work on a single line table like 104_Date. Got punctuation error this time but it didn't tell me the error nor could I figure out what it was. I played with moving the From around and the inner join table. So I included it in here in hopes you could shed some lite. At least direct me to an article or two that could explain this.

strSQL = "SELECT from 400_ReceiptS *, 104_Date * INNER JOIN 104_date ON 400_ReceiptS.RDate = 104_date.Vdate where JrLoc=0" ' Getting Punctuation error

strSQL = "SELECT * from 400_ReceiptS where JrLoc=0 and Rdate=#" & Me.VDateB & "#;" this worked! Thanks!!!

[June7]

The first SQL should be:

"SELECT 400_ReceiptS.*, 104_Date.* FROM 400_ReceiptS INNER JOIN 104_date ON 400_ReceiptS.RDate = 104_date.Vdate WHERE JrLoc=0;"

http://www.w3schools.com/SQl/default.asp

Use Access query builder to help get correct SQL syntax.

[aquabp]

Thanks June7! Love the link!!! ok so I get an error that says," syntax error in query expression '400_ReceiptS.date = 104_Date.dat'."
It debugs out to the next line, Set [rst400_ReceiptS] = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Something to work on! Will see if it is in the link you sent me. Thanks again! One line closer!!