Question about binding controls (1 Viewer)

[kirkm]

I've been setting Datasheet Forms Record source to a query then each fields control source via the dropdown in Properties - Data Tab.

Now I'm wondering if instead you can set Form record souce to the actual sql string insted of the query name, and not need the query. But then the control source dropdown doesnt let me select a field. It's empty. I can type it in, but it doesn't work.

So is this idea wrong, there must be an actual query ?

 

[theDBguy]

You can use an SQL statement and the dropdown should show you the available fields still.

 

[Isaac]

You should be able to use a sql string there. I would say it's more common to do this via code than it is to type literal sql directly into it, but I just created a new form and typed into the recordsource: select * from tablename, (hit enter), and from that point forward, the Fields list was accurate...as was the Controlsource dropdown for any textbox.

Double check the sql to see if it parses correctly?

 

[kirkm]

I'm setting the record source in code before opening the Form. Is that wrong?

Form_frmzqryUSA.RecordSource = sql
 DoCmd.OpenForm "frmzqryUSA", acFormDS

 

[theDBguy]

I'm setting the record source in code before opening the Form. Is that wrong?

Form_frmzqryUSA.RecordSource = sql
DoCmd.OpenForm "frmzqryUSA", acFormDS

Maybe. Perhaps it's backwards. Try it the other way. As in:

DoCmd.OpenForm "FormName"...
Forms!FormName.RecordSource=...

Just a thought...

 

[theDBguy]

But wait... If you're assigning the record source via code, how can you expect to see the fields from the dropdown in design view.

I am lost...

 

[Isaac]

And either way, I believe that you should not expect to see those changes 'saved' as a designer, unless you first open the form in acDesign view and change the property and save and close it. Something totally different.

 

[The_Doc_Man]

Isaac and theDBguy are right. If you define the .RecordSource dynamically at run-time, you are at least implying that there is no .RecordSource in place. If so, there is nothing for any of the Access internal automation to find during design phase.

You can still do this dynamically, perhaps during a Form_Load event, but it will not be enough to just define the .RecordSource at that time. You will also have to go through the controls to set each .ControlSource or .RowSource (as appropriate for the control type.)

 

[kirkm]

Thanks very much one and all it's working great.

Private Sub Form_Load()
    DoCmd.MoveSize 6000, 3000, 12 * 1440, 6 * 1440
    ' docmd.MoveSize r,d,w,h
    If Nz(Me.OpenArgs) > "" Then
        Me.RecordSource = Me.OpenArgs
        Me.Artist.ControlSource = "Artist"
        Me.Title.ControlSource = "Title"
        Me.Label.ControlSource = "Label"
        Me.Year.ControlSource = "Year"
    End If
End Sub

Do I need "Me" or With Me - End With be a better method or ok how it is ?

 

[MajP]

The with end with. Is just short hand. Makes no difference. The compiler could care less. I personally find it easier to read without the extra code block, especially since VBA does not auto block.

 

[The_Doc_Man]

The only purpose of a "WITH" is to save you typing. To add "With Me" and "End With" adds 15 characters. Adding 5 x Me. adds 15 characters. For writing the code it's a "wash" and for executing code it will make zero difference.

However, for those controls like "Artist" and "Title" and "Label" and "Year" I have a couple of comments.
A. Year is dangerous names because it is the name of a VBA function, so there is a question about "naming conflicts" to be researched and resolved.

B. IF the names of the controls do not match the names of the fields, but you are in code running on the form containing the controls, you can omit the Me. prefix. If the names of the controls match the names of the fields, rename the controls like, instead of "Artist" use "txtArtist" (if that is in a textbox). Then you don't need the Me. at all.

 

[Isaac]

And I am an odd duck that actually feels the with blocks make code just ever-so-slightly less readable because you have to keep glancing back at the top to remind yourself what the parent item is. and i am obsessed with readability. BUT that does not mean I don't declare the heck out of variables appropriately--(and that's how I shorten my typing rather than with blocks). It makes more sense when I explained it here
not that anyone cares, lol
edit - oops, i now see majp's post, maybe i am not that odd after all

 

[kirkm]

Fair enough. Thanks. Will leave it how it is.

 

[MajP]

BUT that does not mean I don't declare the heck out of variables appropriately--(and that's how I shorten my typing rather than with blocks). It makes more sense when I explained it here not that anyone cares, lol

That is how I would do it.

 

[Galaxiom]

I'm setting the record source in code before opening the Form. Is that wrong?

Form_frmzqryUSA.RecordSource = sql
DoCmd.OpenForm "frmzqryUSA", acFormDS

Because the form is not already in the Forms Collection, the first line will cause a nameless instance of the form to be loaded into the Forms Collection. The second line will load a separate instance. Objects should not be referred to by their module.

 

[Isaac]

Because the form is not already in the Forms Collection, the first line will cause a nameless instance of the form to be loaded into the Forms Collection. The second line will load a separate instance. Objects should not be referred to by their module.

Can you expound on that a little? I'm in the habit of doing just that, sometimes. Why is it harmful & what would be the appropriate alternative?

 

[kirkm]

> Objects should not be referred to by their module.
I'll try and so that (thats the "Form_" bit ?) but suspect it's happened a lot as never been taught otherwise and generally go with what works.

Unrelated but you guys will know, The form is showing e.g. 42 records and a verticle scroll bar. But I can scroll way past the last record to where there's nothing showing on the Form. What sets the scope or range for the scrollbar ?

 

[Galaxiom]

> Objects should not be referred to by their module.
I'll try and so that (thats the "Form_" bit ?) but suspect it's happened a lot as never been taught otherwise and generally go with what works.

Always refer to the form via the Forms Collection unless it is from itself (Me), its Parent or Child.

If you use the Form_ reference and the form is already open it will use the form so it usually works. If it isn't open it will add a hidden nameless instance. You can see this if you Count the Forms Collection or enumerate its items.

 

[MajP]

The form is showing e.g. 42 records and a verticle scroll bar. But I can scroll way past the last record to where there's nothing showing on the Form.

Can you post a screen shot? Sounds a little strange, but depending on how the form is set up may be a limitation. FYI, Access provides basically no control of the scrollbar, so modifying and scrolling usually involves the windows API.

 

[MajP]

@Galaxiom,
You keep saying nameless

it will add a hidden nameless instance

What do you mean by that? Do you mean not unique? All form instances of a form class share the same name, so it definitely has a name it is just not unique.