Changing an SQL table value, etc. (1 Viewer)

[mjwillyone]

Friends,

I was not sure where best to post this question (here or on the sql database area) but I thought I would send it here seeing that I have had no replies when I sent to the forms forum.

I use an accounting program that runs off an SQL database. In an attept to save considerable time in the creation of checks and electronic transfers, I have created an Access database that will be used to change pending checks or create new ones in the SQL database. Typically, we have a number of recurring checks that are saved in the system. These are then posted as pending checks. It is the pending check table that is being viewed by my Access database.

Each check has a separate check reference number. This is not the same as the check number. This reference number is held in a header table. Each distribution line of the check (and there can be any number of these) has a occurance number. This occurance number is held in a check detail table. A check to an insurance company, for instance, would have one check reference number, but many occurance numbers. Each occurance number represents one employee insurance premium amount. The check reference number is found in both tables and is the link used to match the distribution lines of the check to the check header.

Every time a new check is created, it looks at the dbo_p01parm table in the SQL database. In this table there are the Codetype and CodeValue fields. The last check number used by the system will be found in the Nbr1 field of where the CodeType is "CONTROL" and the CodeValue is "CHECKS." This means that if I want to add a new check through the Access database I need to:

1. change the value in the Nbr1 field of the CHECKS CodeValue by incrementing it by one
2. use this new number as the check reference number on the new check

I am in need of help in doing this.


Also, in order to add distributions to the new check or to other checks that are not new, I need to goto the dbo_p01parm table again. I will need to look for the Codetype and CodeValue fields again, but I need the value in the Nbr1field where the CodeType is "CONTROL" and the CodeValue is "NEXTOCC." I will need to do the following:

1. change the value in the Nbr1 field of the NEXTOCC CodeValue by incrementing it by one
2. automatically drop this new number into the occur field of my subform as an occurrance number for the new distribution line.


Can anyone help me in doing these steps??

Thanks in advance for your help,
Mike

 

[FoFa]

I think your best bet would be to develop a stored proceedure on the SQL side to handle the getting and updating of these numbers, this will give the lest amount of time the number will be in limbo. The SP can return the number you need plus handle all the dirty work quickly.

 

[mjwillyone]

Thanks

Dear FoFa,

Thanks for the response. Now, onto the big question . . . how do I create the stored procedure. I have little of any experience with this.

Thanks,
Mike