Update a Table Field from Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Form "Form1", which has 2 field:
Date1 and Text1

On Command Button Click event from the Form1 screen I like to do the
following job :
Select All the records from Table "Table1", where JobDate = Forms!Form1!Date1
and update all the records which is filtered - VStatus = Forms!Form1!Text1

Please advise.
 
On Command Button Click event from the Form1 screen I like to do the
following job :
Select All the records from Table "Table1", where JobDate =
Forms!Form1!Date1 and update all the records which is filtered -
VStatus = Forms!Form1!Text1
Click to expand...

This has not been tested, but should be close. In any case I assume (or,
rather, I very much hope) you have fictionalised your object names.

private sub cmdUpdate_Click()

' this is string for Jet databases; change it if you are on
' SQL Server/ adp/ etc
const c_strFormat = "\#yyyy\-mm\-dd\#"

dim strSQL as string

' change this if you are using ADO
dim db as Database

' create the command. Note there is no testing for null
' or invalid entries in the text boxes.
strSQL = "UPDATE Table1" & vbNewLine & _
"SET VStatus = """ & me!Text1 & """" & vbNewLine & _
"WHERE JobDate = " & Format(CDate(me!Date1)), c_strFormat)

' remove this line once you are happy that the thing is working
MsgBox strSQL

' good habit to use a variable for the db handle, even if it's
' not strictly neccessary
set db = currentdb()

' don't forget to trap any errors happening here!
db.execute strSQL, dbFailOnError

End Sub


Hope that helps


Tim F
 
Sorry Sir, I was unable to make it by your advised code. I dont know why its
shows error. In additional I understand that my question and specially the
object names was not clear to you. Therefore, just to make you understand my
question well, please note the below :

I want to update all records of my Table ("ModiOT") from the Form
("UPSForm") on Command Button ("Click13") Click Event : For fast reference I
will try in a look of coding :

UPDATE Table!ModiOT!Status2=Forms!UPSForm!TextUps
WHERE Table!ModiOT!OtDate=Forms!UPSForm!DateUps

Also note that I have already selected : Microsoft DAO 3.6 object library.
And on Form TextUps is a text type data field and DateUps is a date type
field.

Please advise.
 
Sorry Sir, I was unable to make it by your advised code. I dont know
why its shows error.
Click to expand...

What error, in which line?
I want to update all records of my Table ("ModiOT") from the Form
("UPSForm") on Command Button ("Click13") Click Event : For fast
reference I will try in a look of coding :
Click to expand...

UPDATE ModiOT
SET Status2= Forms!UPSForm!TextUps
WHERE OtDate=Forms!UPSForm!DateUps

first of all, SQL does not recognise the Tables!ModiOT syntax, and
furthermore knows nothing about forms or controls. You have to build the
command literally in bits:

strSQL = "UPDATE ModiOT" & vbnewLine & _
"SET Status2 = """ & forms!upsform!textups & """" & vbnewline & _
"WHERE OtDate = " & format(forms!upsform!dateups, c_strFormat)

msgbox strSQL

and the msgbox will reveal something like this:

UPDATE ModiOT
SET Status2 = "Eric"
WHERE OtDate = #2004/12/31#

which is what you (and Jet) need to see. If you get something else, post
back here.

Hope that helps


Tim F
 
Sir, Thank you so much. The codes worked, I Wrote the same code before, but
the changes now is the Form full address. The final code which worked , which
you advised was :

Private Sub Label5_Click()
Dim strSQL As String
Dim db As Database
Set db = CurrentDb()
Const c_strFormat = "\#dd\-mm\-yyyy\#"

strSQL = "UPDATE ModiOT" & vbNewLine & _
"SET Status2 = """ & Forms!FormUps!TextUps & """" & vbNewLine & _
"WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat)

MsgBox strSQL
db.Execute strSQL, dbFailOnError
End Sub

Now I have Two additional question about it , that If I will have the
condition to add AND , for example :
WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat) AND EmpName =
Forms!FormUps!Text6
how to Add AND situation, I mean Additional Crtieria to it.

Secondly if the situation will be Between ..... AND .....
WHERE OtDt = BETWEEN" & Format(Forms!FormUps!DateUps, c_strFormat) AND OtDt
= Forms!FormUps!DateSec

If i get the two additional query solved, then in future, I can work on all
three situation if required as both related and generally situations are
there to work for this.

Regards.
 
Const c_strFormat = "\#dd\-mm\-yyyy\#"
Click to expand...

Nonononononono.... this is not a valid Jet format. You can use either one
of these:-
USA type \#mm\/dd\/yyyy\#
or ISO type \#yyyy\-mm\-dd\#

but british dates will _not_ be recognised correctly. Google on this group
for discussions of this ad nauseam... :-)
Now I have Two additional question about it , that If I will have the
condition to add AND , for example :
WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat) AND
EmpName = Forms!FormUps!Text6
how to Add AND situation, I mean Additional Crtieria to it.
Click to expand...

Easy, just splice it into the WHERE clause.

Secondly if the situation will be Between ..... AND .....
WHERE OtDt = BETWEEN" & Format(Forms!FormUps!DateUps, c_strFormat) AND
OtDt = Forms!FormUps!DateSec
Click to expand...

Okay:- I am going out on a limb here to say NEVER use BETWEEN with DATES.
If you can really, really, guarantee that you swear that there are no time
values in your dates, then it works, but it's a rare promise. This

WHERE MyDate BETWEEN #2000-01-01# AND #2000-01-02#

will _omit_ this value: #2000-01-02 00:10#

You can cast the datetime into an integer and use something like this:

WHERE DateValue(MyDate) BETWEEN #etc etc#

or get real (sic) and use proper comparisons:

WHERE #2000-01-01# <= MyDate AND MyDate < #2000-01-03#

note the careful use of the inequalities!

In short Acess/ VB/ Jet datetime values are pretty useful, but do need
careful handling. The bugs from careless use can be very hard to track
down, and are not helped by some sloppy design from the Access team; but
they are all predictable and avoidable.

Hope that helps


Tim F
 
Sir, Thank you so much for your valuable advise, which helped me to gain more
knowledge about access programming.
Regarding the Date Format, I changed to dd-mm-yyyy, as the table has the
same format as well the Form Field where I update the data is also in the
dd-mm-yyyy. Therefore, I kept in the same format mode, so that there should
be no problem for the code to run.

I am not very good in VB coding. I was again unable to add the second
criteria on the Form, I typed the below and it gives error :
Private Sub Label4_Click()
Dim strSQL As String
Dim db As Database
Set db = CurrentDb()
Const c_strFormat = "\#dd\-mm\-yyyy\#"

strSQL = "UPDATE ModiOT" & vbNewLine & _
"SET Status2 = """ & Forms!FormUps!TextUps & """" & vbNewLine & _
"WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat) & "AND
Namee = Forms!FormUps!Text6"
'Note : Form Text6 is the Text field as Well as the Table Field Namee is
also a text field
MsgBox strSQL
db.Execute strSQL, dbFailOnError
End Sub

I was not able to do the BETWEEN....AND criteria also :
Keep an example that Two dates field will be on the Form!FormUps in the
dd-mm-yyyy format, I will Type dates first on both the field and then will
Click the Command button to execute the code. Lets take two field as
Between Forms!FormUps!DateUps AND Forms!FormUps!DateEnd
After sorting it will update as same as above which we have written.

I request you to kindly add these line condition just below the above code,
so that I can paste on my form and can continue to complete.

very best regards.
 
Regardless of what your regional settings have set the short date format to,
you cannot use dd/mm/yyyy in SQL statements (or, more accurately, you cannot
successfully use it for the first 12 days of each month, as Access will
insist on interpretting it as mm/dd/yyyy. Once dd is 13 or higher, Access
will grudgingly accept the date as dd/mm/yyyy format)

Dates aren't stored with any format: they're 8 byte floating point numbers
where the integer part represents the date as the number of days relative to
30 Dec, 1899, and the decimal part represents the time as a fraction of a
day. Therefore, using mm/dd/yyyy will not have any adverse impact on your
application.

For more information, take a look at llen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have in my September 2003 column for Pinnacle Publication's Smart Access.
You can download a copy of my column (along with the accompanying database)
at http://members.rogers.com/douglas.j.steele/SmartAccess.html
 
Regarding the Date Format, I changed to dd-mm-yyyy, as the table has
Click to expand...

Doug has picked up this point -- you simply don't have a choice about
presenting dates to the Jet engine.
strSQL = "UPDATE ModiOT" & vbNewLine & _
"SET Status2 = """ & Forms!FormUps!TextUps & """" & vbNewLine & _
"WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat) & _
"AND Namee = Forms!FormUps!Text6"
'Note : Form Text6 is the Text field as Well as the Table Field Namee
is also a text field
MsgBox strSQL
Click to expand...

If you look at the message box, you'll spot a number of errors:

you need a space before the "AND ";

I strongly suspect that Namee is spelt wrong;

you need to substitute the _value_ of Forms!FormUps!Text6 rather than
just referring to its name;

Try this:

strSQL = "UPDATE ModiOT" & vbNewLine & _
"SET Status2 = """ & Forms!FormUps!TextUps & """" & vbNewLine & _
"WHERE OtDt = " & Format(Forms!FormUps!DateUps, c_strFormat) & _
" AND Namee = """ & Forms!FormUps!Text6 & """;"

By the way, Text6 is a really lame name to give your fields or controls --
you will find your life much easier if you give them names that mean
something. For a different reason, it's a bad idea to use words like "Name"
for fields because the word is reserved by VB or SQL or both. The same
applies to Text, Date, Integer, Count and so on. They are legal, and you
can get away with it, but they can create really hard bugs if you slip up.
I was not able to do the BETWEEN....AND criteria also :
Click to expand...

If you think it's safe to use BETWEEN AND with dates (and I explained above
that I don't agree), then the syntax is easy enough:

strSQL = "SELECT etc..... " & _
"WHERE OtDt BETWEEN " & _
Format(CDate(Forms!FormUps!DateUps), c_strFormat) " AND " & _
Format(CDate(Forms!FormUps!DateEnd), c_strFormat)
After sorting it will update as same as above which we have written.
Click to expand...

No sorting going on here, as far as I can tell. You need an ORDER BY clause
for that.
 
Back
Top