Getting all #Name? (1 Viewer)

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
I think this is where the Form can't see the data. But It should be.. shouldn't it?
There's various text boxes and their control source are a field in tblMain4.
Code:
sql = "Select * From tblMain4"
Form_Form4.Datasheet2.Form.RecordSource = sql
DoCmd.OpenForm "Form4"
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:28
Joined
Oct 29, 2018
Messages
16,572
A #Name? error usually points to a typo. Double-check the names of your objects.
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
If I enter the table name (tblMain4) into the Forms RecordSource Property it works
If I copy the sql into the query builder the query words
And copying the sql into the Forms RecordSource Property also works
But Form_Form4.Datasheet2.Form.RecordSource = sql doesn't.
Should it ?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:28
Joined
Oct 29, 2018
Messages
16,572
If I enter the table name (tblMain4) into the Forms RecordSource Property it works
If I copy the sql into the query builder the query words
And copying the sql into the Forms RecordSource Property also works
But Form_Form4.Datasheet2.Form.RecordSource = sql doesn't.
Should it ?
All depends on where that code is placed and which form you're trying to update. For example, is it a subform?
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
Yes it is. The Form is "Form4" and the SubForm is "Datasheet2". Datasheet2 has the bound controls.
I get to the On Load event of Datasheet 2 (and this seems to run twice. Why???) and
MsgBox Me.Parent.RecordSource = tblMain4
MsgBox Me.RecordSource = Nothing


I'm very puzzled. Can't figure out what's going on. This is SOOOO frustrating !
Is Form_Form4.Datasheet2.Form.RecordSource = sql correct ? There's no error message.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:28
Joined
Feb 28, 2001
Messages
20,857
The syntax and semantics of the statement are both correct when viewed in isolation. However, two thoughts come to mind.

First, if all of the sub-form's controls are bound (which in this discussion context, it seems they would have to be), verify the .ControlSource for each of those controls having the #Name error.

However, you are dealing with a sub-form. The other thing that hits me is that the sub-form loads FIRST, then the main form. Is there a parent/child relationship between the main and sub forms? IF there is an active relationship linking something on the sub to something on the main, the main ain't there yet. So at the time the sub would load, that linkage from the main hasn't been instantiated quite yet, which means that the sub can't exploit the relationship to find the records in question, and "can't find the field" IS one of the possible meanings of "#name".

I'm not going to guarantee I'm right 'cause I don't know if that relationship is there - but it would explain the problem easily enough.
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
Thanks docMan, it's nice to get some reason. I'm so disappointed that "hacks" make things work e.g. I can only set the recordsouce now by passing sql via OpenArgs. And the boolean that Arne advised for the Sub Form On Current NOT to run first needs Form Tag field to restore it when it loses it's value. Neither seem correct and it bugs me! Why is the subForm allowed to run first, it totally messes things up. Is there a rock solid way to prevent this ?
Is there a Parent/child relationship? I don't know, not one that I have made. I created a new Form via Ribbon-Create-Form Desisn and dragged in the other Form. If I just dragged the SubForm control onto the Form, I had all kinds of problems.
I take it No Parent/child should exist. Can I determine that somehow? Aplogies for so many questions...
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:28
Joined
Sep 21, 2011
Messages
9,062
You set the form recordsource AFTER you open a form.?
You appear to be doing it before?
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
Thanks Gasmans, that seemed like a Eureka moment, but it's still not right. The sql isn't working properly e.g. Select * From Table only shows one record. If I add a Where criteria, it shows nothing. But if I use the same sql to make a query - the query shows what it should.
Might I inadvertently have some setting that's limiting it?
However putting Select * From Table into the sub Form RS Property shows all records.
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
I think a good idea is a mockup to demonstrate. Open Form1 and hit enter in the text box. It should show all records but there's only one.
Thanks for any help !
 

Attachments

  • FormTest.zip
    618.7 KB · Views: 14

Gasman

Enthusiastic Amateur
Local time
Today, 05:28
Joined
Sep 21, 2011
Messages
9,062
Well I cannot tell you why...yet, but the same happens if you just open form4.?
When you try and select a source for that control, nothing shows, yet the table name appears to be correct for the form?

Creating a new datasheet form does the same thing, one record, but continous form shows all?

Seems it is all down to your syntax?

I just used
Code:
Form_Form4.RecordSource = "Select * From tblMain4"
and I get all 47950 empty records?
 

Minty

AWF VIP
Local time
Today, 05:28
Joined
Jul 26, 2013
Messages
8,823
What is the actual name of your form? is it really Form_Form4 ?

The reason for asking is that access prefixes all the internal code modules for forms with Form_ so it may not be a very wise choice of naming convention. You may in fact be referencing another instance of the form by using that format.

Edit: I've been following the various threads about the processing of these forms and sub-forms, and can't help thinking you are developing an AI-controlled super sledgehammer to crack a small peanut?
You seem to be making a lot of hoops to jump through for something that should be, I suspect, pretty straightforward?
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:28
Joined
Sep 21, 2011
Messages
9,062
@Minty,
No, it is just called Form4
However it's source was tblMain4 to start with ? :(

The key appears to be remove the form source from the form and save with no recordsource? :unsure:
 
Last edited:

mike60smart

Registered User.
Local time
Today, 05:28
Joined
Aug 6, 2017
Messages
964
I think a good idea is a mockup to demonstrate. Open Form1 and hit enter in the text box. It should show all records but there's only one.
Thanks for any help !
Hi
When you hit the Enter Key it should actually open the Form "Datasheet2"
 

Attachments

  • FormTest.zip
    535.8 KB · Views: 6

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:28
Joined
Feb 28, 2001
Messages
20,857
@kirkm - As for the "double tap" on the routine, can't say anything with certainty but I found a reference to the effect you described that seems highly relevent.

First, mechanically speaking: When you have a form and alter its .Recordsource, that forces a .Requery and that triggered response might in turn trigger other actions. You are loading the .Recordsource in code which (I believe) occurs after Access does what IT is going to do. So at that point, .Recordsource evalution has occurred. And you make it occur again.


In the linked article above, look at the NOTE in the REMARKS section.

Here is another reference from a few years ago that deals with the multi-tap, and it seems to confirm that it is the reload of the .Recordsource that is the culprit.


It offers a discussion about how one might control this effect but there is commentary below the main article that expresses some question about their solution. I just skimmed that part, but it might not hurt to look more into it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:28
Joined
Oct 29, 2018
Messages
16,572
I think a good idea is a mockup to demonstrate. Open Form1 and hit enter in the text box. It should show all records but there's only one.
Thanks for any help !
Hi. I just had a few minutes to check it out.
 

Attachments

  • FormTest.zip
    623.2 KB · Views: 13

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
Thank you DBGuy, that's doing just as it should now. I can see the changes you made but don't follows exactly how it works.
That's fixed the recordsource issue. Looks like a very different approach on a subform as opposed to a Form.
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
DocMan, that link (has a geat title) was informative. He obviously knows the problem, but didn't help much. I don't have any filters or tabs, tried not setting source object (that caused other issues later on) and the boolean variable is useless as it can change so the current even never runs. Can you not have a variable that NEVER changes (on it's own) and can be seen in any module or Form?
I'm thinking maybe don't use a subForm at all. A grid might be better.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:28
Joined
Feb 28, 2001
Messages
20,857
You can, indeed, have a variable that never changes on its own. If you have a general module and you are not dealing with certain oddball data types, you can declare a PUBLIC CONST and that constant will be visible throughout your project. The constant has to be a simple data type (string is allowed; SINGLE, DOUBLE, INTEGER, LONG, and BOOLEAN are allowed. Never tried others but they should work just as well. But you can't have a User-Defined Type and declare a constant for it.

The trick is to figure out WHY a variable changes when you didn't think you were referencing it. That usually requires a pretty thorough search of all VBA code for the variable name. That includes checking for subroutines using the variable as an input but ByRef rather than ByVal. Then if you use the referenced input in the left side of an assignment, you miss the actual modifying reference.

You also need to watch for anything that would lead to a code RESET operation, 'cause that usually trips up variables. (Won't trip up constants.)
 

kirkm

Registered User.
Local time
Today, 17:28
Joined
Oct 30, 2008
Messages
1,324
A PUBLIC CONST can't be changed. So that won't work. It has to be False first, then True and never change back to false.
I know where it changes... a double-click event in the SubForm

Code:
Private Sub Title_DblClick(Cancel As Integer)
Debug.Print bolInitialized  'Is True
    Cancel = True
    PlayFile "a"
Stop 'confirmed bolInitialized is Nothing
End Sub
This sub is in a module so 'Filter" works
Code:
Sub PlayFile(Side)

    Dim Audio As Variant, Msg

    Select Case Side
        Case "a"
            Audio = Filter(IIf(Form_Form1.Filetype = 1, mp3s, flacs), Form_Datasheet.Prefix & "a", True)
        Case "b"
            Audio = Filter(IIf(Form_Form1.Filetype = 1, mp3s, flacs), Form_Datasheet.Prefix & "b", True)
    End Select

    Select Case UBound(Audio)
        Case 0
            Msg = fHandleFile(CStr(Audio(0)), 1)
        Case Else
            'Build PlayList
    End Select
End Sub
After it returns from Playfile am I wrong to expect bolInitialized to be restored? Does going to a module from a Form reset the code?
fHandlerFile can be remmed out, makes no difference so that isn't affecting it.
I wondered about making PlaytFile a function to return True, but - here again, I want to avold 'making things work' and instead have them work as they should.
 

Users who are viewing this thread

Top Bottom