When open a form to update a table record.

  • Thread starter Thread starter DazedConfused
  • Start date Start date
Hi Gary,

From your description, I understand that you'd like to realize the two tasks.

According to your questions, I answer them respectively as below:

To the first question, we can use DAO to loop the record and compare the date in the [dd] field. If the date is
current date, we then perform a update to the field
Code: 
:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command1_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset, FLD As DAO.Field
Dim i As Integer

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblcheck")

i = 0 'The order of the field [dd] in the table

RS.MoveFirst
Do Until RS.EOF
If (Year(RS.Fields(i).Value) = Year(Now()) And Month(RS.Fields(i).Value) = Month(Now()) And Day
(RS.Fields(i).Value) = Day(Now())) Then
DoCmd.SetWarnings False
DoCmd.RunSQL " Update [tblcheck] Set [code] = [code] + 1 where dd = #" & RS.Fields(i).Value & "#"
DoCmd.SetWarnings True
End If
RS.MoveNext
Loop

Set RS = Nothing
Set DB = Nothing

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

To the second question, we can simply use Docmd.RunSQL to update the [code] field if we click the
Command0 button:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command0_Click()

DoCmd.SetWarnings False
' If condition Then
DoCmd.RunSQL " Update [tblcheck] Set [code] = [code] + 1 "
DoCmd.SetWarnings True

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Does that answer your question? Please apply my suggestions above and let me know if this helps resolve
your problem. If there is anything more I can do to assist you, please feel free to post it in the group

Best regards,

Billy Yao
Microsoft Online Support
 
Think Billy!!

"Billy Yao [MSFT]" said:
Hi Gary,

From your description, I understand that you'd like to realize the two tasks.

According to your questions, I answer them respectively as below:

To the first question, we can use DAO to loop the record and compare the
Click to expand...
date in the [dd] field. If the date is
current date, we then perform a update to the field
Code: 
:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command1_Click()

Dim DB As DAO.Database
Dim RS As DAO.Recordset, FLD As DAO.Field
Dim i As Integer

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblcheck")

i = 0 'The order of the field [dd] in the table

RS.MoveFirst
Do Until RS.EOF
If (Year(RS.Fields(i).Value) = Year(Now()) And[/QUOTE]
Month(RS.Fields(i).Value) = Month(Now()) And Day[QUOTE]
(RS.Fields(i).Value) = Day(Now())) Then
DoCmd.SetWarnings False
DoCmd.RunSQL " Update [tblcheck] Set [code] = [code] + 1 where[/QUOTE]
dd = #" & RS.Fields(i).Value & "#"[QUOTE]
DoCmd.SetWarnings True
End If
RS.MoveNext
Loop

Set RS = Nothing
Set DB = Nothing

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

To the second question, we can simply use Docmd.RunSQL to update the [code] field if we click the
Command0 button:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Command0_Click()

DoCmd.SetWarnings False
' If condition Then
DoCmd.RunSQL " Update [tblcheck] Set [code] = [code] + 1 "
DoCmd.SetWarnings True

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Does that answer your question? Please apply my suggestions above and let me know if this helps resolve
your problem. If there is anything more I can do to assist you, please[/QUOTE]
feel free to post it in the group
Click to expand...
 
Hi,
I have two form question.
1.) I want to update a table(tblcheck) record when open the form(packing)
if the table field(dd) not equal to today date then update it to today date
and update field(code) to "1"
otherwise do nothing.

2.) Each time when I click on a button, it can update the table(tblcheck)
field(code)+1,

Thank for help!!

Gary
 
Hi Gary,

I'm not sure here "Think" means. Do you mean you need my additional assistance, or I have answered your
questions respectively?

Thanks,

Billy Yao
Microsoft Online Support
 
Sorry Billy,

My mistake, I just want to thank for your help!!

"Billy Yao [MSFT]" said:
Hi Gary,

I'm not sure here "Think" means. Do you mean you need my additional
Click to expand...
assistance, or I have answered your
 
Hi Billy,

Base on your code, each record field(code) I can +1,
but now I have another question, if I want the table(tblcheck) field(code)
can change like this base on another table(tbldata), how to chane the code,

Example1 if table(tbldata) field(cc)=15 then
table(tblcheck) before:
code
3
4
5
table(tblcheck) after:
code
16
17
18

Example2 if table(tbldata) field(cc)=99 then
table(tblcheck) before:
code
3
4
5
6
table(tblcheck) after:
code
100
101
102
103

Please help again!!
Thank!

Gary

"Billy Yao [MSFT]" said:
Hi Gary,

I'm not sure here "Think" means. Do you mean you need my additional
Click to expand...
assistance, or I have answered your
 
Thanks Gary,

It's my pleasure to be of assistance! Also, thank you for posting in the group.

Have a nice day,

Billy Yao
Microsoft Online Support
 
Hi Gary,

Thanks for your furhter question.

I will look into the issue and get back to you Monday next week, with a full testing and accurate code. Thanks in
advance for your patience and cooperation!

Have a nice weekend,

Billy Yao
Microsoft Online Support
 
Hi Gary,

Sorry for the delay.

From your descriptions, I understood that you need the new_cc = original_cc
+ 15 - 2. Does this hit your business logic requirement? If so, I think you
could write the codes in the following way:
''''
Private Sub Command0_Click()
Dim DB As DAO.Database
Dim RS2 As DAO.Recordset, FLD2 As DAO.Field

Dim j As Integer

Set DB = CurrentDb()
Set RS2 = DB.OpenRecordset("tbldata")

j = 1 'The order of the filed [cc] in the table tbldata

RS2.MoveFirst
Do Until RS2.EOF
If ((RS2.Fields(j).Value) = 15) Then ' I don't know whether it need
judgement here,
' you could remove it if you don't need this kind of judgement:)
DoCmd.SetWarnings False
DoCmd.RunSQL "Update [tblcheck] Set
Code: 
 = [code] - 2 + " &
RS2.Fields(j).Value
DoCmd.SetWarnings True
RS2.MoveLast
End If
RS2.MoveNext
Loop


Set RS2 = Nothing
Set DB = Nothing

End Sub
''''

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Back
Top