Solved How to update a table field via VBA

[Marshall Brooks]

I have a simple table in my front end - 1 record, two fields. Used for the version date.

I can read the values via:
strFE = Nz(ELookup("[fe_version_number]", "[tbl-fe_version]", "[ID] =1"), "") & ""

How can I write to the field.

I saw:

Access VBA Tables - Update, Count, Delete, Create, Rename, Export

This tutorial will teach you how to work with Access Tables using VBA. Access VBA Tables To start we will demonstrate the simple commands for working with

www.automateexcel.com

and tried:

DoCmd.SetWarnings (False)
DoCmd.RunSQL "Update tbl-fe_version SET tbl-fe_version.fe_version_number = Format(DueDate, 'mm/dd/yyyy') WHERE (((tbl-fe_version.ID)=1))"

But it didn't seem to work, and I also saw:

Update field in a table using VBA ?

I need to update a field in a table based on what another field is Now_month Count 1 1 1 2 1 3 1 4 1 5 2 1 2 2 What I need to do is insert incremental values in...

www.access-programmers.co.uk

Which I haven't tried yet, but seems complicated for only two values to be changed.

Thanks in advance!

 

[XPS35]

Try

DoCmd.RunSQL "Update tbl-fe_version SET tbl-fe_version.fe_version_number = #" & Format(DueDate, 'mm/dd/yyyy') & "# WHERE (((tbl-fe_version.ID)=1))"

 

[Marshall Brooks]

That didn't work either. I got a syntax error until I changed 'mm/dd/yyyy' to double-quotes. Then the syntax error went away, but the table value did not update.

Another solution - this is code behind a button on an unbound form, so I could just bind this form to the table and add two text boxes to the form and set .visible = No. and then just say "Me.tbox_fe_version_number.value = DueDate", but I was expecting it to just be something simple, along the lines of:
currentdb.tbl-fe_version.fe_version_number.value = DueDate

 

[Marshall Brooks]

The second link works for me - i.e.

    Dim db As Database
    Dim r As Recordset
    Set db = CurrentDb
    Set r = db.OpenRecordset("tbl-fe_version")
    r.MoveFirst
    r.Edit
    r.Fields("fe_version_number") = DueDate
    r.UPDATE
    Set r = Nothing
    Set db = Nothing

 

[Gasman]

If it is bound to the table, you do not even need the controls.
I used to update several fields that way.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

I used to keep an amendment log.

 

[Marshall Brooks]

I do have a very odd follow-up question. I'm not sure it matters for my purposes, but it still somewhat bothers me ...

The fe_version_number field in my table is set up as a date/time field. If I click the little calendar beside it to manually update it, and put in last weeks date, it will show as m/d/yyyy - i.e. 9/7/2023.

The DueDate variable is the output of a custom pop-up datepicker that I use and is a string function with the format of mmmm d, yyyy. So for the above, it assigns DueDate as "September 7, 2023".

Format(DueDate, "m/d/yyyy")
CDate(DueDate)
Format(CDate(DueDate), "m/d/yyyy")
all display as 9/7/2023 when I display them in a msgbox.

But when I use:
r.Fields("fe_version_number") = CDate(DueDate)
(or any of the other variants) and I look at the table, it shows the value as dd-mmm-yyyy. - i.e. 07-Sep-2023.

Why?

 

[theDBguy]

What is your Regional Settings?

 

[Marshall Brooks]

Not sure where to find the regional settings, but I would think if it were regional settings related, the format should be the same in the table.

I.e. if I manually entered the date in the field and it changed to 9/7/2023 and I told it to format it to 7-Sep-2023 and it changed to 9/7/2023, I would suspect regional settings.

When it manually saves as 9/7/2023 and then I tell it to save through VBA as 9/7/2023 and it saves as 07-Sep-2023, I'm confused.

 

[theDBguy]

Regional Settings play a major role. Your example date of 9/7/23 could become ambiguous depending on regional settings. Just as a test, try out a non-ambiguous date.

 

[Marshall Brooks]

I understand what you mean - the example date could be either September 7 or July 9th.

Fortunately - this database is only used in one region so all computers accessing it should have similar regional settings, and my point was the dates are formatted two different ways on my own computer.

 

[theDBguy]

I understand what you mean - the example date could be either September 7 or July 9th.

Fortunately - this database is only used in one region so all computers accessing it should have similar regional settings, and my point was the dates are formatted two different ways on my own computer.

One thing that's worth remembering is that dates in Access tables are stored differently as they are displayed. In other words, formatting has nothing to do with how the data is stored. If you use VBA to store a literal date, the format settings may read it differently. Or, if you use a literal date in VBA, your Regional Settings may cause Access to store the data differently (if the date was ambiguous); hence, my suggestion to try an unambiguous date to see if you actually have a problem.

PS. It's also quite possible I am misinterpreting your issue, since I can't see it in action, and trying to imagine what's happening by simply reading your description of the situation could lead to misinterpretation.

 

[Marshall Brooks]

In that case, I'm probably okay. I'll try to post screenshots soon.

 

[The_Doc_Man]

Getting differences in dates (based on regional settings) can also come from the origin of the date request. If you build a query that gets the date from the Access FE this might be different from a query that does a PASSTHRU operation and asks for the date in the passed-through query that is executing on the BE host. Those two systems CAN have different regional settings. Just something to check.

 

[Marshall Brooks]

In this case EVERYTHING is coming from my computer and the front end.

Screenshots:

Format when I clicked the little calendar pop-up in the table, which I circled.

Format when I save the value to the table using VBA code which takes my datepicker output and should convert it to 9/6/2023.

 

[Pat Hartman]

But when I use:
r.Fields("fe_version_number") = CDate(DueDate)
(or any of the other variants) and I look at the table, it shows the value as dd-mmm-yyyy. - i.e. 07-Sep-2023.

Why?

Dates are stored internally as double precision numbers NOT TEXT STRINGS. Think about the confusion that would cause. Think about the language issues it would cause. Think about your own confusion.

I think the origin date for SQL Server is 1/1/1900. For Jet/ACE it is 12/30/1899. Word and Excel use the same scheme but I'm not sure what their origin dates (the 0.0 value) are.

if the BE is Jet/ACE:
0.0 = 12/30/1899 midnight
0.5 = 12/30/1899 noon
1.75 = 12/31/1899 06:00 PM
-2.5 =12/28/1899 noon

NEVER convert a date using Format() unless you actually want to convert it to a string.

The tricky thing you need to understand if your standard date format is not the US standard of mm/dd/yyyy is that if you are creating SQL in VBA so the date is a string, you MUST format your date as mm/dd/yyyy or the unambiguous yyyy/mm/dd to avoid the confusion caused by ambiguous values such as 2/3/2023. Is this Feb 3 or Mar 2? Depends on your local default format.

If you are seeing dates formatted differently in different situations, you either have format properties set or code that is affecting the display. No matter how the date is formatted, it is still stored as a double precision number so that all calculations can be done without having to ever convert a date from one string format to another.

If you Format() a date, it becomes a string and therefore, it will sort and compare like a string.

Apr 22, 2023 sorts prior to Jan 1, 2024 because A is < J.

 

[Marshall Brooks]

@Pat Hartman - Thanks - that helps a lot ...

Basically I don't need to format the date to anything since it will be stored in the table as a date.

If you are seeing dates formatted differently in different situations, you either have format properties set or code that is affecting the display.

I considered format properties being set to make it happen, and if it was consistent and ignored my formatting statement I wouldn't worry about it. I'm not understanding why the formatting is different when all I am doing is viewing the table and updating the table by different methods, but I'm not going to worry more about it. (But I will take out the steps that format the dates for the table).

Apr 22, 2023 sorts prior to Jan 1, 2024 because A is < J.

Funny you should mention that. I had a similar mistake when I first tried automatic updates to the front end. IIRC, it was something where the previous version of the database was September 8, XXXX and the new version was September 10, XXXX. At the time, I was doing a string compare and 8 > 1 so nothing happened.

Initially, I changed the backend date to September 9, XXXX.

Then for the next FE revision, I change the FE comparison code to "If CDate(FE version in FE table) > CDate(FE Version in BE Table) Then Update".

If I had it to do over, I'd use numerical versions like Bob Larson originally suggested, if for no other reason than that it makes it easier if you have to release several FE versions on the same day.

 

[Pat Hartman]

(But I will take out the steps that format the dates for the table).

You're welcome. Good move When you are debugging, it is IMPERATIVE that you see the actual table values, NOT the formatted value. People make the mistake all the time of using Now() to log a date when they really mean Date() and then when they see the times, they change the format and think the time is gone but it's not. It is interfering with every single date compare you do. This can easily drive you crazy when you are looking at a table to try to figure out why certain records are excluded or included and don't recognize that you are not seeing the actual stored value.

It is best to not set any formats at the table level. I know this is inconvenient because it is nice if when you add SalesTax to a form that it shows the percent sign and two decimal places. But, that is the price you pay. See the ACTUAL value of a column or save a miniscule amount of time as you are building a form or report. It would be nice if we had the option to add a "default display format" value that would propagate to formatting on forms and reports but not for queries and tables. Don't hold your breath though.