Creating a log, need help auto populating worksheet numbers

[snipe]

Hey guys,

Our salespeople get 25 worksheets handed out to them at a time, along with a log. A snip of this log is pictured. Once the salesperson has used all of his/her 25 issued worksheets, he turns in the log with the blank fields all filled in, and gets another batch of tickets. This log is then keyed into a massive excel sheet that is exclusive to each store.

Currently this log is created in excel, but we want to move it into a centralized access db that contains all info from all stores. The "Sales worksheet #" is the serial number that is pre-printed on the worksheet. The "Simplex #" is the number that will get stamped onto it once that worksheet is used to write up a sale. You will notice that worksheet numbers are always in sequence. The only exception is when a box of the logs runs out, and they open a new box.



Here is what I am trying to create:

-A master table that will hold every stores checkout log info (created)
-A form for a clerical to enter the data from a filled out checkout log (easy to create)
-A form to produce the checkout log that is pictured above (problem child)

For the last item, I want the form to be quick and simple. Pick a store, then salesperson from a combo box (easy to do). Then pick the work sheet range(s). That is the part that I have no idea how to do. Once the store, salesperson, and range(s) are selected, the form will print. It doesn't have to look identical as the one above, but it needs to have the same general idea.

Hope I was clear enough on what I am trying to accomplish.

Also, I need the ability to be able to choose multiple ranges of worksheet numbers, for when a salesperson gets, say, the last 15 tickets from a box of worksheets, and the first 10 from a new box. The box's worksheet ranges are labeled on the outside (annoying, I know), so the last ticket from a box will never be sequential with the first ticket from a new box. Make sense?

[June7]

Options for creating the printed sheet with pre-filled SalesWorksheetNo:

1. create records and print report of selected records

2. some advanced and tricky code to print sequential numbers on report without having to establish records

Selecting multiple discontinuous ranges for data entry procedure just needs more code.

[snipe]

Dealing with the multiple discontinuous isn't too big of an issue. I could always just do multiple log sheets for those psuedo-rare situations.

We will go through roughly 120,000 worksheets a year. We you recommend doing option 1 or 2?

[June7]

Since you will need the records eventually for the data entry, assuming each record will always have data entered, maybe number 1 - and it might be easier.

[snipe]

Ok so what is the best way to make a ~500,000 record autonumbered table, starting at 500,000?

[June7]

Here is one.

For i = 500000 to 999999
CurrentDb.Execute "INSERT INTO tablename (WorksheetNum) VALUES(" & i & ")"
Next

[snipe]

Thanks June, that worked great!

Next, I need to push data to that table for when a salesperson checks out worksheets. I have made this form:

And the master table looks like this:


So what is the code to append the store, issue date, salesperson (the code next to the name is what will be used) to the records where SerialNumber is between the 2 numbers on the form? I am assuming we are doing a recordset update?

[snipe]

Actually, you have answered this one for me already

"If not a bound form, can run an UPDATE SQL (examples for text, date, number fields).

CurrentDb.Execute "UPDATE tablename SET DO1='" & Me.tbxDO1 & "', DO2=#" & Me.tbxDO2 & "#, DO3=" & Me.tbxDO3 & " WHERE datefield=#" & Me.DelDate & "#""

Let's see if I can get it to work.

[snipe]

 CurrentDb.Execute "UPDATE T_checkout_master set store=" & Me.SelectStore & ", Salesperson='" & Me.EmpCode & "', DateIssued=#" & Me.IssueDate & "# WHERE SerialNumber BETWEEN " & Me.SerialFrom & " AND " & Me.SerialTo & ""

Works like a charm. How do I put line breaks in there though? I keep getting a "unexpected end of statement" error when I try.

 CurrentDb.Execute "UPDATE T_checkout_master set store=" & Me.SelectStore & ", Salesperson='" _    
& Me.EmpCode & "', DateIssued=#" & Me.IssueDate & "# WHERE SerialNumber BETWEEN " _
& Me.SerialFrom & " AND " & Me.SerialTo & ""

That was my attempt.

[June7]

I would have thought that should work. However, I always do: & _
Don't forget space between & and _

Store and SerialNumber actually a number type?

[snipe]

Still doesn't work.

Yes, both store and SerialNumber are number types.

[June7]

If the line starts or ends with literal text, must precede or follow with " mark.

CurrentDb.Execute "UPDATE T_checkout_master set store=" & Me.SelectStore & ", Salesperson='" & _
Me.EmpCode & "', DateIssued=#" & Me.IssueDate & "# WHERE SerialNumber BETWEEN " & _
Me.SerialFrom & " AND " & Me.SerialTo

[snipe]

Gotcha. Appreciate the help! Solved!