lookup control number on form save

[jwmo9tt]

Hello,

Two tables; controlNum and requisiteInfo.

ControlNum table is pre-populated based on numbers given by a third party. When requisiteInfo form is filled in and saved, I need to pull the next unused (controlnum.assigned is not true) control number from the controlnum table and assign it to the controlnum field in the requisiteInfo table. I also need to update a flag (controlnum.assigned) in the controlnum table for the appropriate record, and allow the user to confirm the control number assignment.

I am used to writing sql statements but an having a hard time getting my head around the recordset syntax in the save button click event.

[June7]

Think I would set up a form/subform arrangement. Main form is the controlNum table and subform is requisiteInfo. Then I would open the main form to the next available controlNum record. As soon as data is entered into any field of the subform the controlNum value will populate to the controlnum field in requisiteInfo.

[jwmo9tt]

Thank you for the quick reply. In the subform arrangement how would I control the multi-user problem with assigning the same control number during simulataneous access? I have a flag in the control num table called opened which I plan on updating when open, and then selecting the next available control number by filtering out any with opened = true and is assigned = true. I guess locking would do the same thing but I am not sure.

[June7]

I did not consider multi-user environment. Doubt record locking would prevent two users opening the same controlNum record and adding requisiteInfo records. Would have to immediately set the opened or assigned fields to true and save this edit when the form opens. This also raises the issue of what if user wants to abort?

No matter if the controlNum is assigned at record initiation or close, there is risk of conflict in multi-user environment. Can just hope to minimize it.

If you want to get the controlNum when record is committed then can use DMax function to capture the next available value.
= DMax("controlNum", "controlNum","Opened=False And Assigned=False")

Does user need to confirm the controlNum assignment or just if they want to commit the new requisiteInfo record?

[jwmo9tt]

yes, and no. don't need to confirm just get a confirmation that it was assigned. I will be talking to the client about the abort scenario because I had the same question, but I'd like to proceed anyway. My real issue is structuring the syntax of the on_click event to pull the controlnum. I can't get my recordset syntax to work. Here is the requirement that I received from the client (law firm). I have everything done except the controlnum assignment piece. Turnaround was / is short and I just can't get the assignment thing figured out. Usualy I work in SQL and I am rusty with the whole access recordset idea.

begin requirements...

Currently, we issue a “Control No” for each Petition we are going to appeal. Clark County has given us a series of numbers we can use (sequentially) to complete all of our Petitions. Right now, each employee comes to me with the requisite information and then I assign the “Control No.” I then, periodically, provide the Clark County Assessor with a listing of the issued Control Nos. and all of its respective information.

Can we quickly program a database to assign the Control No. after a person has input the requisite information and clicked “save” or some other “confirming” button indicating the Control No. should be assigned? Can the data input person receive a confirmation (merely affirming the assigning of the Control No. by displaying the Control No….maybe something they could print)? Can we then print out a report from that database for all numbers assigned from ‘x’ date to ‘y’ date showing the Control No. assigned and its requisite information (1-4 below) sorted in Control # order? [Keep in mind, there will be multiple people entering data at the same time and potentially hitting “save/confirm” at the same second.]

The requisite information is as follows:
1. Lead Parcel No. (11 digit number)
2. Secured or Supplemental Roll (could be a drop box or two check boxes where one of the two must be checked for a number to be assigned)
3. Multiple Parcels (Yes or No… as simple as “Y” or “N”)
4. Field where all Parcel numbers can be entered (via a cut and paste from an excel file) and is required if #3 above is “yes.”

Note: the above #1-4 is the requisite info for Clark County but I would also want the below #5 so that I can generate a report from the program to link the information with our file/case management program.

5. Matter Number

[June7]

Don't need recordset to get the next control num. The DMax function I showed can do that. Include it in some event (button click) to set the value of the requisiteInfo controlNum field. This same event can present a message box pop up that informs user what control number was just assigned, this would not be a printable feature, that would require printing a form or report. This should happen so fast that user conflict is unlikely.

And yes the report you describe is possible.

I suggest that you disable the X close button on forms and control closing form with command buttons.