Solved

Trying to update a date field

Posted on 2014-02-17
12
269 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 34

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
 

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 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 34

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 34

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now