Solved

Trying to update a date field

Posted on 2014-02-17
12
268 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

12 Experts available now in Live!

Get 1:1 Help Now