Query to increment value of a field

[CanWest]

I have a query that is used to collect a recordset from a table for printing receipts. The receipt number needs to match a pre printed form from the government. The receipt numbers are sequential

I need a query to populate the receiptnumber field sequencially starting ayt a number specified in a form. The form has a text box called txtStartingReceptNumber.

So if the number 5073 is in that text box I need the query to put that in to the ReceiptNumber field in the first record of the record set and then increase it by one for all of the remaining records. I have search and found a few solutions but none see to work for me

I have tried

Nz(DMax("[ReceiptNumber]","tbl_Activity"),5073)+1

in the update to parameter of the ReceiptNumber field. That put 5074 in every record. Close but no cigar. I know I have to replace the 5073 with the fully qualified name of the text box. This was just to try and get a working model.

Any help would be greatly appreciated.

 

[PNGBill]

I imagine this task could better be done by vba code.

The code will store the start number and then loop through a process that prints a receipt each time and increments the number by 1 as it loops.

Presumably it will know when to stop

The Function will take the number in the forms text box control as it's start number and maybe another number in another control as the number of receipts to print.

 

[CanWest]

I imagine this task could better be done by vba code.

The code will store the start number and then loop through a process that prints a receipt each time and increments the number by 1 as it loops.

Presumably it will know when to stop

The Function will take the number in the forms text box control as it's start number and maybe another number in another control as the number of receipts to print.

I am afraid that is way over my pay grade. I do not even know where to begin

 

[PNGBill]

May not be as hard as you think

If you have the Receipt Report working and the only issue is which record to print then it may be a short and simple code.

Is it possible to attach a stripped down copy of the database that doesn't have sensitive data in it ?

 

[CanWest]

May not be as hard as you think

If you have the Receipt Report working and the only issue is which record to print then it may be a short and simple code.

Is it possible to attach a stripped down copy of the database that doesn't have sensitive data in it ?

The issue is not wich records to print. The issue is to update a receipt number to each record that I am going to prnt

 

[PNGBill]

Do you have a table and will hold the rec number there ? and then print the rec or is the number just on the rec only ?

 

[CanWest]

Do you have a table and will hold the rec number there ? and then print the rec or is the number just on the rec only ?

It is on the pre printed receipt and I need to store it in the activities table where the record is stored.

 

[PNGBill]

You need an Update Query to change a field in the table to hold the receipt number in an existing record or an Append Query to add a record to the table with the receipt number.

I would still think a simple vba procedure can do this easily.

If you can paste the relevant sql I will try and supply the code to make this repeat and Append or Update for the number of times you require.

 

[CanWest]

You need an Update Query to change a field in the table to hold the receipt number in an existing record or an Append Query to add a record to the table with the receipt number.

I would still think a simple vba procedure can do this easily.

If you can paste the relevant sql I will try and supply the code to make this repeat and Append or Update for the number of times you require.

Ok let me try to explain again cause I think I did not do a good job. I have a query that shows a recordset based on two peices of criteria. The receipt number in these results is blank. I want update the receipt number field of each record starting at a value that has been entered in a form that is open. I have place this code

Nz(DMax("[ReceiptNumber]","tbl_Activity"),"Forms![frm_MainMenu]![txtStartReceiptNumber]")+1

in the update to property of the reciept number field in my query.

Let's assume that 5703 was entered in the form. When the query is run the value is set to 5704 for all records in the set instead of 5704 5705 5706 etc

I believ it needs a loop or something and I have absolutely no idea how to do this.

 

[PNGBill]

I need the query that collects the data with the empty rec number field.

You should be able to Copy and Paste to here.

If view the query as SQL and paste it within the code symbols above.

That query should provide enough info to setup some code to "loop" and update the empty field to the receipt number starting with the numb er entered on the form.

More story....

With the code you need to identify the records to be Updated - that is the query you mention.

Another query should then be able to loop through this recordset and Update the table with the receipt number being incremented by 1 each loop.

Do you always have a fixed number of receipts ?? or use the number of records returned with the first query to get the end ?

 

[CanWest]

Do you always have a fixed number of receipts ?? or use the number of records returned with the first query to get the end ?

No the number receipts will vary each time the query is run.

Here is the code

UPDATE tbl_Events INNER JOIN (tbl_Activity INNER JOIN tbl_Contacts ON tbl_Activity.ContactID = tbl_Contacts.ContactID) ON tbl_Events.EventID = tbl_Activity.EventName SET tbl_Activity.ReceiptNumber = Nz(DMax("[ReceiptNumber]","tbl_Activity"),"Forms![frm_MainMenu]![txtStartReceiptNumber]")+1
WHERE (((tbl_Events.EventID)=[Forms]![frm_MainMenu]![cboEventI]) AND ((tbl_Activity.TaxReceiptAmount)>0));

 

[PNGBill]

Thanks, I should have enough to recreate the database.

Suggest tblEvents (no space) has EventID as primary key and this is the join field on tblActivity rather then EventName.
EventName is only stored in tblEvents.

 

[PNGBill]

Here is a sample database in Access 2010.

Let me know if you need it in an earlier version.

Open the database and open frmUpdateReceiptNumbers

This is a simple solution and it is easy to add some bells and whistles to avoid user mistakes.

Main difference is I do not have spaces in table names or _ .

The VBA code is in the On click Event of the Command Button "click to Update Receipt Numbers"

It would be quite easy to copy and past this code to your own form and Edit as required.

Trust this assists

 

[PNGBill]

I just remembered. How do your decide what receipt number to allocate to what Activity ??

I sorted on tblActivity.ActivityID which ussually means in oder of the Activity although Date Order may be better ??
The Order (often important) could mean ths difference between &

 

[CanWest]

Here is a sample database in Access 2010.

Let me know if you need it in an earlier version.

Open the database and open frmUpdateReceiptNumbers

This is a simple solution and it is easy to add some bells and whistles to avoid user mistakes.

Main difference is I do not have spaces in table names or _ .

The VBA code is in the On click Event of the Command Button "click to Update Receipt Numbers"

It would be quite easy to copy and past this code to your own form and Edit as required.

Trust this assists

Sorry I forgot to mention that I am using Access 2003

 

[CanWest]

I just remembered. How do your decide what receipt number to allocate to what Activity ??

I sorted on tblActivity.ActivityID which ussually means in oder of the Activity although Date Order may be better ??
The Order (often important) could mean ths difference between &

The ActivityID is perfect. I will have to change the table references to have the _(underscore) in it because it would mean way to many changes to my project. I was told way back when to use that to simulate a space. Is there a reason not to do that cause if there is i won't do that in future projects.

 

[PNGBill]

Sorry I forgot to mention that I am using Access 2003

Database is 2002-2003 .mdb which may mean you are able to open ??

Here is a version in 2000 just in case.

 

[PNGBill]

The ActivityID is perfect. I will have to change the table references to have the _(underscore) in it because it would mean way to many changes to my project. I was told way back when to use that to simulate a space. Is there a reason not to do that cause if there is i won't do that in future projects.

I understand it is just easier to type. anything that allows your code to be easy to type and understand assists.
I interrupted the underscore as due to spaces but not the case.

Good luck with the form and code and trust it all makes sence.

VBA is a little scary until you give it a chance and there is a lot of assistance with code issues on the forum and via www search's.
I avoided it for too long.

I am still a "learner" and have to check older examples and research but it does open up a whole new way of achieving your database goals.

 

[CanWest]

Thanks

Would you be able to repost the database you created in Access 2003 format. I can not open the 2010 one.

Many Thanks

 

[PNGBill]

Did you try to open the 2nd file I attached ? That is saved as 2000 version