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

[June7]

Are the field names RDate and VDate or date and dat?



As already noted, the [] characters are not appropriate for VBA variables.

[CJ_London]

thought you said you have fixed your naming

Thanks June7! Love the link!!! ok so I get an error that says," syntax error in query expression '400_ReceiptS.date = 104_Date.dat'."

[aquabp]

Hi Sanfu!! Thank you for your observations.
So, conceptually I think you are telling me that the code is a mess. I agree. As it stands the code gets to the end result, but I do not know why.

So here is the problem again. I have to physically account for receipts I post to the system. Currently two receipts fit on an 8.5x11 sheet of paper. Receipts are like what you get from the grocery store.
If the code runs properly the 400_ReceiptS table when completed would look like the following:

Receipt          date            JrLoc         JrID       
 1                11/5/15      1511001      1           
 2                11/5/15      1511001      2
 3                11/5/15      1511002      1

Sorry put code wrapping on it to keep format so ignore the code: label this is what the table is suppose to look like if the code runs properly.

JrLoc ID’s Location and page number the receipts are located. The JrID identifies if they are in the left or right column of the report. The report has the JrLoc in upper right corner and JrID 1 receipt printed in column 1 and Jr ID 2 receipt printed in the right column. Once these reports print the receipt is taped to the paper in the receipt column that matches.
How do I get there? I rest the rs lines like you suggested. Past that I need some help.

1. You pointed out several times that ICount = rs.RecordCount was not counting or moving. What did you mean and how do I fix it?
2. Conceptually I thought it would be a nested variety of loops.
   
   1. Do Until x = 0 à thought this would be the recordset
      
      1. Do Until y = 2 à add a JrLoc value for the next two records on the third record a new JrLoc would be used.
         
         1. Do until z = 1+1 à add a JrID to each line. If line was 1 id=1 if 2 lines one line =1 the other equals 2.
      
      
   2. I never did the z value yet because I was having so much trouble getting the other part to run.

Any help or articles would greatly be appreciated!

[aquabp]

Hi Ajax, This referred to post 13. Collecting the date from an unbound box on the form worked which I stated following that statement. Also on the post was the inner join statement that I was still having issues with. June7 showed me the errors of my ways!

[ssanfu]

So, conceptually I think you are telling me that the code is a mess.

Ummm...Well....... yes. That would pretty much sum it up.

1. You pointed out several times that ICount = rs.RecordCount was not counting or moving. What did you mean and how do I fix it?

ICount is a VBA variable. You set ICount = rs.Recordcount .. Good so far.
You then use "Do While ICount > 0"
But nowhere in the code is there a statement like

ICount = ICount -1

So ICount will NEVER get to zero.


I'm guessing JrLoc = Journal Location? And JrID = Journal ID?

JrLoc ID’s Location and page number the receipts are located.

How??

The JrID identifies if they are in the left or right column of the report.

Where does JrID come from? Does 1 = Left column and 2 = Right column? Or 1 = Top and 2 = Bottom??

How do I get there?

Not sure. I still don't understand how things are put together in your dB.

Once these reports print the receipt is taped to the paper in the receipt column that matches.

You print a report, then tape the actual receipt to the report??

Maybe you would post your dB? Just a few records - scrub/change sensitive data.

[aquabp]

GLEZip.zip
Ok the database is attached I think. When you open it the dropdown shows some records from November. Go to the forms section and open test form as well. This is where I have been working this code. The Work in progress buttons will update the first form that popped up. There are two tables in that form. The receipt table and the RecLog Table. The JrID is used in a crosstab query that appends this table. Currently, The JrID column is manually updated so any date with three or more records you will have to change the id. See 11/5/15. Currently you will have to change the JrID for 11/9/15 to look like those in 11/5/15.

To run the code again for the same date replace the JrLoc for that date with 0 and uncheck the JrPrnt box.

To see a report run the report numbered 4 on the first form. Key in 1511 when asked.
Thanks again for your time and insight!

[ssanfu]

I spent a lot of time looking at your dB. I am still very confused.

I think you are having some problems because there are some basic things wrong.
-Naming: object names should only be letters , numbers and sometimes the underscore. NO spaces, punctuation or special characters. You have used spaces and a slash on table "400_ReceiptS".
-Should never start a object name with a number. Should never ever use just a number for a field name.

-There is still a field named "Date" in the table "475_RecLog".
-I never saw a field named "JrID"
-The top two lines of EVERY module should be

Option Compare Database
Option Explicit

- Missing or wrong declarations in the routines.
-There are two modules that have the same code "MOD01RF01" and "MOD01RF01 o". This is causing a conflict.
-There are 3 identically named functions in 3 different modules. 2 are in form modules and 1 is in a standard module. I commented out the 2 in form modules. You should NEVER name a module the same name as a procedure. I changed the module name from "GetGST" to "modGetGST".

-I modified the code for the button "Work In Progress Test". I converted the macro "475MD01MC01test" to VBA code. (I never use macros). I wanted to see what was happening. If you want to use the macro, uncomment the run macro line and comment out the lines between the lines with '**** at the beginning.

-The code for the sub "Sub WIPTest_Click()" works (as far as I can tell ). JrLoc and JrPrint are updated.
There are two records in table "400_ReceiptS" for 11/12/2015. Should they have the same JrLoc number? (ie all receipts for the same day should have the same JrLoc number? Or each receipt has a different JrLoc number?


Attached is the modified dB

[aquabp]

Hi Ssnafu!, Thank you for you time and effort! That answered several questions about counters and incorporating them into vba! So I do have a couple of questions:

1. I changed the CntSort to JrId in the new table but did not change it here. Sorry for the confusion. Attached is a PDF of the report we are building.
0300RecJrR01.pdf

The JrLoc is the page identifier in a three ring binder. The Binder is id'd by year and month, 1511 = 2015 November. The page is the whole JrLoc = 1511009
One page can hold up to two receipt hard copies. So the code needs to give one JrLoc to every two records per day. The CntSort value of either 1 or 2 puts the code in either the left column or right column of the page. This is done with a crosstab query that is appended to the 475_RecLog table. The report is actually generated from this table.

So for one date there may be multiple JrLoc but with no more than two records per JrLoc. If the JrLoc has two records the CntSort field needs to be updated so that one record has a number 1 and the other has a number 2. See the records for 11/5/15. If the attached report does not work run #3 report with the date 11/5/15.

2. Is there no way to link the date from the 104_Date table in the statement below? I would like to try and avoid the unbound field. I have used these before. They usually cause issues at some point.

strSQL = "SELECT Receipt, Rdate, JrLoc, JrPrnt from 400_ReceiptS where nz(JrLoc,0) =0 and Rdate = #" & JRDate & "#;"



Thanks again for your advice time and patients!

[aquabp]

Ok, So, I played around with the original code I wrote taking notes from Ssanfu. Here is the updated code. The Code Counts to 2 and updates those two records with SN value which is the JrLoc and CS which is thee CntSort or JrID value.

At this point I realized that all the variables need to be basically reset so I ended the loop and run the MaxR query again below it. The code loops through the first loop again after the query update. The CntSort Updates but the JrLoc does not. I looked at the 105_RNec table and it has been updated.

How do I get the SN variable to go out and get the new NRec value from the 105_NRec table again? I put the SN = getGST() code below this query and I get the -1111 error in the field.

When I run the code I get this error message:
Error Number: 3021
Error Source: opRecExample
Error Description: No Current record.

'open record set
    strSQL = "SELECT Receipt, Rdate,  JrLoc, CntSort, JrPrnt  from 400_ReceiptS where nz(JrLoc,0) =0 and Rdate = #" & JRDate & "#;"
    '            Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)       'open the recordset for use (table, Query, SQL Statement)


    With rs    'W1
        If .RecordCount > 0 Then            'Ensure there are records. The next 2 line determine the number of returned records IT1
            .MoveLast                       'This is required otherwise you may not get the right count
            ICount = .RecordCount           'Determine the number of returned records
            .MoveFirst                      'move to the first record


            SN = GetGST()
            If SN = -1111 Then
                MsgBox "Invalid 'JR Loc' number"
            Else


            
            Do While .RecordCount > 0
            CS = 1
            Cntr = 0
                Do Until Cntr = 2 ' While Not .EOF
                    .Edit
                    !JrLoc = SN
                    !JrPrnt = -1
                    !CntSort = CS
                    .Update
                    .MoveNext
                    CS = CS + 1
                    Cntr = Cntr + 1
                
                Loop
                
                    'Once counter gets to 2 then everything needs to get rest. Need a new SN number for the next page, need to rest CS and Cntr
                    '475F03Q04_NRec
                    strSQL = "Delete [105_NRec].NRDate, [105_NRec].MSort, [105_NRec].MaxJrLoc, [105_NRec].FirstJrLoc, [105_NRec].NRec FROM 105_NRec;"
                    '    Debug.Print strSQL
                    db.Execute strSQL, dbFailOnError
            
                    '475F03Q06_MaxR
                    strSQL = "INSERT INTO 105_NRec ( NRDate, MSort, MaxJrLoc, FirstJrLoc, NRec )"
                    strSQL = strSQL & "SELECT [475F03Q05_MaxR].VDate, [475F03Q05_MaxR].MSort, [475F03Q05_MaxR].MaxJrLoc,"
                    strSQL = strSQL & " IIf([MaxJrLoc]=0 Or [MaxJrLoc] Is Null,[104_Date].[MSort] & '001',[MaxJrLoc]) AS FirstJrLoc,"
                    strSQL = strSQL & " IIf([MaxJrLoc]>0,[MaxJrLoc]+1,[FirstJrLoc]) AS NRec"
                    strSQL = strSQL & " FROM 475F03Q05_MaxR;"
                    '    Debug.Print strSQL
                    'SN = GetGST()
                    'If SN = -1111 Then
                    '    MsgBox "Invalid 'JR Loc' number"
                
    db.Execute strSQL, dbFailOnError
            
        'Else
           'MsgBox "Oops - No records!!"
            'End If
            Loop
            
            End If
            End If    'IT1
    End With    'W1

[aquabp]

Ok, just figured out the issue. I had the SN = getGST() outside the loop. I brought it in and it works!! I still get the error message:
Error Number: 3021
Error Source: opRecExample
Error Description: No Current record.

So I think it is because the loop does not know where to go once it runs out of records. Doesn't the error handler suppose to catch that?

[ssanfu]

Is there no way to link the date from the 104_Date table in the statement below? I would like to try and avoid the unbound field. I have used these before. They usually cause issues at some point.

Yes, but someplace you have to enter/select a date. Where would that be?

I still get the error message:
Error Number: 3021
Error Source: opRecExample
Error Description: No Current record.

So I think it is because the loop does not know where to go once it runs out of records. Doesn't the error handler suppose to catch that?

The error handler IS catching that; you have gone past the last record in the recordset (past the end of the file) so yo get the error message.

You have a line

Do While .RecordCount > 0

HOW is the record count of the record set ever going to change unless you change the criteria???
You cannot increment or decrement rs.RecordCount!!!


--------------------------------------------
I took the dB I sent you and removed all but the TEST form. Then I modified the code to add in CntSrt.
I added a button to open a query to the date in the box. In my testing, I used the dates 11/12/2015 and 11/13/2015.
The date 11/12/2015 has 2 records and the date 11/13/2015 has 3 records.
The third button clears JrLoc, JrPrnt and CntSrt.

Set the date to 11/12/2015,
View the records (should be 2), then
Click the WIPTest button.
View the records again (2nd button). The JrLoc and CntSrt fields should be updated.

Now change the date to 11/13/2015.
View the records (should be 3), then
Click the WIPTest button.
View the records again (2nd button). The JrLoc and CntSrt fields should be updated and incremented.



Trace through the code for button "WIPTest".

[aquabp]

Hi Ssanfu,

That makes sense I think. So basically I was putting the CS counter inside the if than statement it was counting?

Also the line you changed from, "Do Until Cntr = 2 Then" to "Do Until Cntr > ICount"
Is there a way to view the records of these statements? Is there a code where I can display or store them to see how the statement reacts?
If I can see the results of the statement I understand it better.


[QUOTE=ssanfu;302588]Yes, but someplace you have to enter/select a date. Where would that be?

Answer: In the beginning of the code. Set the data and the right hand never leaves the 10 key.


So I think that does it! I'll take my learnings and transform the crosstab query into a sqr statement so that the code can update the reclog table and we should be good to go.

Thanks again for your help!!!

[aquabp]

How do I mark this as being completed?