Problem Updating Table

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

I have a form that is populated by a query and all but three boxes are
bound to that query. The first unbound box (TxtLvChk) is a manual
input from the user. The second unbound box (LVAppWho) contains the
user's name from previous form and the third (LvAppDate) contains the
current date. The idea being that each record is to be reviewed and
approved/disapproved by the user who opened the form and then update
the table. I tried the following update statement and the results were
mixed. While it would post the approval box to every record, it would
only post the approver and current date to the first record.
Anyone got any ideas?

DoCmd.RunSQL "Update Leavesheet" _
& " SET LeaveSheet.LvAppWho = '"
&Forms!frmLvHrApproval!TxtLvAppr & "'," _
& " LeaveSheet.LvAppDate = #" & Forms!frmLvHrApproval!TxtCurDte
& "#," _
& " LeaveSheet.LvApp = " & Forms!frmLvHrApproval!TxtLvChk _
& " WHERE LeaveSheet.UserName = '" &
Forms!frmLvHrApproval!LvUserName & "'" _
& " AND LeaveSheet.TDate = #" & Forms!frmLvHrApproval!TxtTDate
& "#;"
 
Joel said:
I have a form that is populated by a query and all but three boxes are
bound to that query. The first unbound box (TxtLvChk) is a manual
input from the user. The second unbound box (LVAppWho) contains the
user's name from previous form and the third (LvAppDate) contains the
current date. The idea being that each record is to be reviewed and
approved/disapproved by the user who opened the form and then update
the table. I tried the following update statement and the results were
mixed. While it would post the approval box to every record, it would
only post the approver and current date to the first record.
Anyone got any ideas?

DoCmd.RunSQL "Update Leavesheet" _
& " SET LeaveSheet.LvAppWho = '"
&Forms!frmLvHrApproval!TxtLvAppr & "'," _
& " LeaveSheet.LvAppDate = #" & Forms!frmLvHrApproval!TxtCurDte
& "#," _
& " LeaveSheet.LvApp = " & Forms!frmLvHrApproval!TxtLvChk _
& " WHERE LeaveSheet.UserName = '" &
Forms!frmLvHrApproval!LvUserName & "'" _
& " AND LeaveSheet.TDate = #" & Forms!frmLvHrApproval!TxtTDate
& "#;"
Click to expand...

What are LvUserName and TxtTDate? You explain the values that you want
to set, but not the values you are using for your criteria.

One thing you could try doing is to simply write this as a select query
(without the Set portion).

Then, run the select query while you have the form open and the values
you want to set filled in.

Are you getting the results in the query that you expect? Is it one
record or multiple records?

This should help you break it into parts and see where the error might be.
 
Thanks for the reply Duncan. What I have is a unbound form that is
loaded by a query. Each of the retrieved records must be
approved/disapproved. The LvUserName and TxtTDate fields are unbound
fields on the form. The LvUserName field contains the User's Name who
logged in on a previous form (data passed from one form to another) and
the Date field is a reflection of the current date. I want to post the
user's name and the current date to each record that has been reviewed.
My issue is that the approval gets correctly posted to each record in
the table but the UserName and TxtTDate fields don't.
 
Joel said:
Thanks for the reply Duncan. What I have is a unbound form that is
loaded by a query. Each of the retrieved records must be
approved/disapproved. The LvUserName and TxtTDate fields are unbound
fields on the form. The LvUserName field contains the User's Name who
logged in on a previous form (data passed from one form to another) and
the Date field is a reflection of the current date. I want to post the
user's name and the current date to each record that has been reviewed.
My issue is that the approval gets correctly posted to each record in
the table but the UserName and TxtTDate fields don't.
Click to expand...

Well the SQL looks to be correct, so it's a matter of tracking down the
individual bits of data and making sure that they are correct.

Try setting a breakpoint on the statement, and then issuing the
following commands in the immediate windows
? Forms!frmLvHrApproval!TxtLvAppr
? Forms!frmLvHrApproval!TxtCurDte
? Forms!frmLvHrApproval!LvUserName
? Forms!frmLvHrApproval!TxtTDate

Do you get values for each of those four items? You should, since you
are using two of them to set values, and two of them as criteria.

Check in your Leavesheet table that LvAppWho and LvUserName are text,
and that UserName and TDate are Dates.

Let's see what that gets you.
 
Back
Top