Solved

Trying to update a date field

Posted on 2014-02-17
12
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 37

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
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.

 

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 37

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 37

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 37

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 37

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Balance After Payment 12 58
ms access 2013 - getting a list of software from a user's computer 4 46
SUBFORM on ACCESS 2013 8 30
Tabbed form question 5 17
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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