Progamming Check box condition

[Gus]

Hello,

Thank you for taking the time to help me with my question.

I have a form called CheckOutAgreementRequestForm with a check
box(completed). I want to see if I you can help me create code that will
assign the agreement(Agr_Num) to the User(User_ID) requesting the agreement
in the table tblAgr_Check_Out when the Check box is marked true.

The form is tide to tblAgr_Check_Out_Request containing attributes
(ChkOut_Request_ID "PK",User_ID, Agr_Num, FFIS_Agr_Num, Date_Time_Requested,
Date_Time_Completed, RegUnit,
Completed, NotFound)

One other table is called tblAgr_Check_Out this table has attributes
(ChkO_ID"PK", Date_Checked_Out, User_ID, Agr_Num)

The two tables are sharing a relationship with is tblUsers(User_ID,
User_FName, User_LName) by User_ID.

The table tblAgr_Check_Out_Request is looking up the Agr_Num from the
tblAgr_Check_Out users are able to select the Agr_Num where these agreement
numbers are stored.

Any help would be greatly appreciated.

-Gus

 

[Michel Walsh]

Sure, how is easy, but WHEN (WHERE in your code, in which event) you will
do it is unknown to me, from what you supplied.

HOW to do it can be:

DoCmd.RunSQL "INSERT INTO tblAgr_Check_Out(Date_Checked_Out, User_ID,
Agr_Num)
VALUES( Date( ), FORMS!formName!UserID,
FORMS!formName!Agr_Num) "



where you replace the formName with the real form name. I assumed the
controls are UserID and Agr_Num. I also assumed the primary key of
tblAgr_Check_Out is an autonumber, so we don't have to specify it in the
VALUES list (and in the matching list of fields following tblAgr_Check_Out
in our SQL statement).




Vanderghast, Access MVP

 

[Gus]

Thanks Michael.

If I understand (help me out I am beggining to understand SQL, so I may be
way off) the code you suggest would insert into the Check out
tblAgr_Check_Out (Date_Checked_Out, User_ID, Agr_Num) based on what is on the
Form(AllUserAgrRequestsNotCompleted).

The event would be at the point in which a user checcks that a request has
been completed and thus the Agr_Num assigned to them (UserID). I do not want
the information entered into the tblAgr_Check_Out as a new record, but rather
as an update to tblAgr_Check_Out (Date_Checked_Out, User_ID, Agr_Num) based
on my Form (AllUserAgrRequestsNotCompleted).

I tried the code and I get errors due to a type conversation failure, due to
violations failures.

I am not sure if I mentioned that the Agr_Num(an actual Agreement/Contract
Number) already exists in the tblAgr_Check_Out actually my form
(AllUserAgrRequestsNotCompleted) is looking up the Agr_Num from this table so
that users can select any Agr_Num already existing in this table and which
could be assigned to another UserID or the Default UserID(16) when Agreement
is not assigned to a user.

Hope this helps clarify and helps me give you more information so that you
can suggest more ideas. Thank you very much.

Have a great day.

 

[Michel Walsh]

Then you can run an UPDATE query instead of the proposed INSERT query. You
can try the update query in the query design editor (don't forget to include
the appropriate criteria to update ONLY the record you want to update). You
can make your tests in the query designer and once it works, cut and paste
it inside the double quote of:

DoCmd.RunSQL " ... "



To make your tests, in the query designer, have your form open and the
controls filled with values.


Vanderghast, Access MVP

 

[Gus]

Thank you Michel.

I will try and let you know what happened.