How to access components in the forms detail section (1 Viewer)

[Jygge]

Hello! I am a beginner in VBA, but I developed quite a bit in Delphi many years ago. With the help of ChatGPT, I have now tried to create an application in MS Access. In doing so, I have encountered an issue when trying to access components in the form's detail section. I get a runtime error in Form_Current when I try to access components in the detail section using aktivitetID = Me!TxtAktivitetsId. VBA says that "The expression contains a field, control, or property name that the database cannot find." I have found that this applies to all components in the detail section. Am I doing something wrong, or is this a general limitation in Access?

 

[CJ_London]

Nothing special about the detail section.

You could do with understanding the difference between a dot and a bang ( . v ! ) . Dots early bind, bangs late bind and are generally used to reference recordset fuelds

try using me. Or don’t use it at all

Or perhaps you have not declared activateID as a variable? Or you have but it conflicts with a field or control name

 

[Gasman]

is this a general limitation in Access?

 

[The_Doc_Man]

Am I doing something wrong, or is this a general limitation in Access?

Generally, when you know the name of something and it is currently open (such as when you are designing/building a form), you use Me.item-name (because you knew the name). The compiler will look up the list of known names and resolve the address immediately.

 

[LarryE]

aktivitetID = Me!TxtAktivitetsId

Why are you attempting to set a numeric field to text? It appears that aktivitetID is a Primary or Foreign Key. This makes no sense, even if you did use Me.TxtAktivitesId. And if you attempt to do that, ACCESS will not allow you to set a numeric field to text anyway.

We had better have a look at your design to see what tables you have and how they relate to one another. If your design is flawed from the start, then you will continue to have issues.

If you attach your database file, perhaps we can get you going on the right foot.

 

[Jygge]

I understood from your response that there must be some other issue. The problem was that I regenerated the form's bound table in the form's open method. Once I removed that routine, all addressing worked as expected. However, I don't fully understand why this happens.

The reason I became uncertain about the ability to reference fields in the detail section is that I was reading a thread on a forum related to what I’m trying to solve. I have a table in the database called Activities, which contains information about various activities planned for the year. In the current form, I want to visualize the information so that you can see the activities and color markings for the weeks during the year when they are scheduled to take place. The detail section consists of the text field Activity on the far left and then all the weeks of the year in a row.

The function for coloring the weeks is shown below. However, it only results in the last activity’s week and color being applied to all activity records. It checks how many weeks there are per year, which can be 52 or 53. Then, it goes through and colors the weeks during which the activity is scheduled to take place.

Sub FargsattaVeckor()
Dim ctl As Control
Dim i As Integer
Dim maxVeckor As Integer
Dim veckovarde As Variant
Dim aktivitetID As Long

' Hämta antal veckor för det aktuella året
maxVeckor = GetISOWeeksInYear(Me!LstAr.Value)

' Hämta AktivitetId för den aktuella posten
aktivitetID = Me.TxtAktivitetsId

' Färgsätt veckorna för den aktuella posten i formuläret
For i = 1 To maxVeckor
Set ctl = Me("EtkAktivitetV" & i)

' Hämta värdet för den aktuella veckan från temp_Veckoplanering
veckovarde = DLookup("Vecka" & i, "temp_Veckoplanering", "AktivitetsId = " & aktivitetID)

' Färgsätt veckofältet baserat på värdet
If Not IsNull(veckovarde) Then
ctl.BackColor = veckovarde
Else
ctl.BackColor = vbWhite ' Standard bakgrundsfärg
End If

' Sätt textfältet till en tom sträng
ctl.Value = ""
Next i
End Sub

 

[The_Doc_Man]

The problem was that I regenerated the form's bound table in the form's open method.

I don't understand this statement. Can you amplify your explanation on that point?

As a side note: If you are changing the form's .RecordSource in the Form_Open routine, some of the bindings can become misdirected. If that actually IS what you are doing, your problem could be that you need to do a Me.Refresh AFTER you diddle with the form's data source.

Form.Open event (Access)

Office VBA reference topic

learn.microsoft.com

The following quote from that article is relevant to what I'm saying.

When you open a form based on an underlying query, Microsoft Access runs the underlying query for the form before it runs the Open macro or event procedure.

The recordset is therefore established BEFORE the _Open event code is called by Access. So if you then CHANGE the recordset in some way in the _Open event code, the original recordset was already in place. You have to force Access to take note of any changes you make to that recordset.

 

[Jygge]

Thank you for the valuable input. I feel that I have some catching up to do. The reason I initially chose to regenerate the table was that I had an idea to adapt the table to the weeks of the year. That is, the first field’s week would be named Week 1 Dec 30 - Jan 5, the second week Week 2 Jan 6 - Jan 13, and so on, based on how it looks for a particular year.

I later realized that it’s sufficient to just change the headers for the fields in the form header instead, and to have a consistent table as the data source with 53 fields for weeks named Week1, Week2, etc. However, if I had insisted on having a design with changing the data source, then a Me.Refresh would have been the solution.

 

[Gasman]

I do not think that is the correct approach.
I would have a field for date, and from that work out week/year.
That is how I constructed my diabetes db.
Everything is in as a date, and I sum for week/month and year.

 

[Uncle Gizmo]

and to have a consistent table as the data source with 53 fields for weeks named Week1, Week2, etc.

It sounds like your issue might stem from using a table structure that violates normalization principles, especially with separate columns for each week of the year. This design can cause problems in Access, particularly when dynamically changing the form's data source.

A more effective approach would be to normalize your data into a table with three columns:

ActivityID, WeekNumber, and WeekValue.

This structure will make it easier to manage your data and should resolve the issues you’re facing with form controls.

If you need help transitioning your data to this format or adjusting your form, feel free to ask!

A good start might be to view my YouTube Video where I helped another user about six years ago.....

Excel in Access - Transpose years

 

[Gasman]

Have a read of these links in this post.

Solved - Normalisation

Micron on here posted a few links on another site, the first one being for Normalisation, which Excel users to come to Access have never heard of. He also posted a good few other links. Rather than post to his original post (as it is on another site) anymore, I am posting them here and so will...

www.access-programmers.co.uk

 

[Jygge]

It’s possible that you’re right. I’ll have to think about how to proceed. Thank you for taking the time.