Solved

Trying to update a date field

Posted on 2014-02-17
12
271 Views
Last Modified: 2014-02-18
I'm trying to update a date field in an afterupdate event of a number field on a form.

Here is my code so far:

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblReady")
        RS.Edit
        RS!ReadyDate = Me.Me.txtGrowDaysLate + Tables!tblPlanted.PlantDate
        RS.Update

But I don't know how to have it update JUST the related record.   The criteria would be TextMNumber or txtMNumber on the form.

So I think it would be something like:

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblReady")
        RS.Edit
        RS!ReadyDate = Me.Me.txtGrowDaysLate + Tables!tblPlanted.PlantDate WHERE [TextMNumber = me.txtMNumber
        RS.Update


????
0
Comment
Question by:SteveL13
  • 5
  • 5
  • 2
12 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 39866233
rs.Edit should only work on current record as you recurse through the recordset.
So maybe:
Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblReady")
rs.movefirst
do while not rs.eof
        RS.Edit
        RS!ReadyDate = Me.Me.txtGrowDaysLate + Tables!tblPlanted.PlantDate
        RS.Update
rs.movenext
loop
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39866245
Of course, this is air code. Can you post a sample db?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39866449
You could use the FindFirst method to find the record for the ID you have.  Or, better technique would be to use a query that includes criteria and use that to open the recordset directly to the record you want to update.  Or, if the update is simple enough, you can just run an update query.

Looks like you are using a field from the current record on the form plus the value of a field in a second table (tblPlanted) to update a field in a third table (tblReady).   Is that correct?  How are the three tables related?  You'll need to provide the details of the three tables before someone can come up with a query that would work.

You should also know that what you are doing probably violates standard principles of normalization and rather than storing this value, you should be calculating it on the fly in your query.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:SteveL13
ID: 39867434
Using:

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblReady")
    RS.MoveFirst
    Do While Not RS.EOF
        RS.Edit
        RS!ReadyDate = Me.txtGrowDaysLate + PlantDate
        RS.Update
    RS.MoveNext
    Loop

Which has changed slightly from the original it is actually finally changing the ReadyDate.  But it changes it to 1/14/1900  ???  Does the line...

RS!ReadyDate = Me.txtGrowDaysLate + PlantDate

need to be changed somehow?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39867485
PlantDate is 0 (Dec 30, 1899)  so when you add 15 to it you get 1/14/1900.

Where is PlantDate coming from?  If it is in the recordset, use RS!PlantDate.  If it is in the form's RecordSource, use Me.PlantDate.  Only if it were a variable in code would you not qualify it by the object to which it belongs.

Keep in mind that using a recordset that opens the table requires Access to actually read all the records in the table.  That is why doing a query is so much more efficient especially if the field you are searching on is indexed.  

The OP never said whether his BE was ACE or SQL Server so I assume it is ACE.  Given that, it doesn't make much difference but with a SQL Server BE, it would make a HUGE difference if the table were large.

The only time I would loop through a recordset to update records is if I were going to update more than 40% of them and/or the process was too complicated to do in a query.
0
 

Author Comment

by:SteveL13
ID: 39867557
PlantDate comes from the recordset.  I tried

RS!ReadyDate = Me.txtGrowDaysLate + RS!PlantDate

but it still doesn't change the ReadyDate.

(There will only be about 300 records total in the table)
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39867567
Did you look at the data?  PlantDate is 0 based on your results.

Put a break in the code and examine PlantDate before you do the addition.
0
 

Author Comment

by:SteveL13
ID: 39867592
I didn't state the issue properly.  Sorry.  The ReadyDate is no longer 1/14/1900.  But if the ReadyDate  is for example, 3/2/14 and then to code runs and txtGrowDaysLate is "2", then the new ReadyDate should be 3/4/14.

??
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39867745
And what is it instead?

The code is correct.  Make sure the data is actually what you think it is.

Make sure the fields are defined correctly.  ReadyDate should be Date/Time and GrowDaysLate should be numeric (either integer or long integer)
0
 

Author Comment

by:SteveL13
ID: 39867782
BINGO!!!!  You did it.  GrowDaysLate was numeric but Single.  I changed it to integer and it works!!!  Thank you.

But help me understand why single wasn't working.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39867884
It sounds like Access is being picky about the data type.  Normally I would use the DateAdd() function since that is the controlled way to add something to a date/time and that might have raised an error instead of silently failing.  According to DateAdd(), the format for the number is Double.  I thought it would be integer.  But, Access appears to ignore fractions anyway.  Access probably allows a Double as the increment even though it doesn't make sense because the date itself is a Double precision number.  The integer represents the number of days since Dec 30, 1899 and the fraction is the time since midnight.
0
 

Author Comment

by:SteveL13
ID: 39868274
Well, anyway, thank you.  Much appreciated.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question