?
Solved

Trying to update a date field

Posted on 2014-02-17
12
Medium Priority
?
275 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 38

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 38

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 38

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 38

Accepted Solution

by:
PatHartman earned 2000 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 38

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

765 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