Cmd Button to generate New Number on Close

[Joe via AccessMonster.com]

I have a client that is trying to put together a shipping log database that
will tracking work order's that were printed out several years ago. They have
not had the time to scan all the documents into thier system. So they are
going to ship the records off site. What I am doing is creating a form that
at the top as a field name "Box #", and a subform that the user will enter a
records and keep going for how ever many records are going into a box. The
box can hold anywhere from 50 to 75 records.

What I need is a button that when hit, it will save the records and create
the next available number and enter this number into the "Box #" field at the
top of the form and into all the records that are showing up in the current
subform view (Box # field). That way, it will be easier for the company to
find the record they are looking for in a particular box, instead of search
all the boxes.


Your help would be greatly appricated.

Joe

 

[John W. Vinson]

I have a client that is trying to put together a shipping log database that
will tracking work order's that were printed out several years ago. They have
not had the time to scan all the documents into thier system. So they are
going to ship the records off site. What I am doing is creating a form that
at the top as a field name "Box #", and a subform that the user will enter a
records and keep going for how ever many records are going into a box. The
box can hold anywhere from 50 to 75 records.

What I need is a button that when hit, it will save the records and create
the next available number and enter this number into the "Box #" field at the
top of the form and into all the records that are showing up in the current
subform view (Box # field). That way, it will be easier for the company to
find the record they are looking for in a particular box, instead of search
all the boxes.


Your help would be greatly appricated.

Joe

I would recommend NOT using the # character in the fieldname; it's a date
delimiter and nonstandard character. You presumably have a table of Boxes (I'd
use BoxNo as the primary key) related one to many to a table of Documents.

You could base a Form on the Boxes table, and a subform on the Documents
table, using BoxNo as the master/child link field. In the mainform's
Beforeinsert event you can put code:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!BoxNo = NZ(DMax("[BoxNo]", "[Boxes]")) + 1
End Sub

This will assign a box number one greater than the largest yet in the table
when a new record is created, and assign that number to each document assigned
on the subform.

 

[tina]

sounds do-able, but before we talk about getting the form to work the way
you want, what's the table(s) structure? i could base a form solution on a
guess at the table(s) and fields, but quicker in the long run if you provide
the information first. and while we're at it, explain your current
mainform/subform setup - what tables or queries are bound to the forms, etc.

hth