Datasheet wont populate

[TopGun1979]

Hi Guys

I am developing a programme to run off MS ACCESS, and eventually MS ACCESS Runtime, however, I do you a problem. I have created a form with 3 subforms on it. I have populated 2 of these subforms successfully, now I am trying to populate the 3rd subform which is a new form (frm_WeightBrowser) which contains its own subform (3rd level subform called frm_WeightGrid). This subform is a datasheet which I want to populate dynamically. I am trying to populate simply for now, and using the following code:

Public Sub LoadRecords(ID As Integer)
    Dim SQL As String
 
    SQL = "SELECT * FROM [Weight]"
    Me.Form.RecordSource = SQL
    Me.Requery
End Sub
 

This code resides on the datasheet form (frm_WeightGrid), but it just won't work. I also tried

Me.Form.Recordsource = "Weight"
Me.RecordSource = "Weight"
Me.RecordSource = SQL
 

Can someone please help me out here. I cannot see what I am doing wrong.

[Seth Schrock]

How are you calling your code? It isn't in any event, so you will need to call it from an event procedure.

[TopGun1979]

I am calling it from one of the other 3 subforms with the following code:

...
    Form_frm_TabPageControl.l_WeekNo.Caption = Nz(DMax("[WeekNo]", "[Weight]", "[ID] = " & ID), 0)
    Form_frm_Weight.GetInfo ID, Form_frm_TabPageControl.l_WeekNo.Caption
    Form_frm_WeightGrid.LoadRecords ID
End Sub
 

I do know it is working properly, as I tested by returning a simple msgbox in the frm_WeightGrid.

[Seth Schrock]

When you say it doesn't work, what does it do? Do you get an error message or does nothing happen or does it return unexpected results?

[TopGun1979]

Nothing happens, no results, no error msg, not even column names. There should be data as the "Weight" table holds 290 Records in it.

[zmbd]

"3rd level subform called frm_WeightGrid"
Is the issue yes?
Is this bound to the 2nd level via parent/child fields?

[Seth Schrock]

Are you using code to bind the controls in the subform to the record source? When you say "not even column names", it really makes me wonder what you are trying to do as changing a record source doesn't automatically create column names. That would have to be changed manually. The more I think about your question, the more I'm realizing that more information is needed to understand what you are trying to do.

[TopGun1979]

Hi zmbd, none of my forms are bound to the parent form. They are all unbound

[TopGun1979]

Hi Seth

Is there a way i can send you my accdb file and you can have a look and see what I am missing?

[Seth Schrock]

Are the controls within your third subform (the one that is having the problem) bound?

[TopGun1979]

Hi Seth,

Nothing is bounded, if I could attach the screen prints, I could show you the forms. But here is the basic layout if it helps:
Main Form: frm_TabPageControl
frm_TabPageControl Sub Forms:
1. frm_CustomerBrowser (Contains a listbox and combobox, everything works fine)
2. frm_Weight (Contains mostly text boxes with data, that is defined by a selection from frm_CustomerBrowser. A routine in frm_CustomerBrowser calls a routine in frm_Weight which populates these text boxes. Everything here works fine)
3. frm_WeightBrowser (Contains only a rectangle for the background and a subform inside)

frm_WeightBrowser subform is frm_WeightGrid which is a simple data sheet, which is suppose to return data depending on the selection from frm_CustomerBrowser. This data can be fetched from the table called "Weight" based on a column called "ID". This is what is not working.

As you can see, I have no need for this form to be bounded. I pass the ID to this form, and all I want to do is run a query that says:

"SELECT * FROM [Weight] WHERE [ID] = " & ID
 

I hope this helps. Thanks in advance.

[zmbd]

In the form you are having issues with:
In one of the controls that you want to have the data from
"SELECT * FROM [Weight] WHERE [ID] = " & ID
displayed in...
Open the properties for that control
On the data tab
What is the entry for "Control Source" ?

[TopGun1979]

This is access 2010 so there is no control source, but here are the settings on the Data tab for the frm_WeightGrid form (the datasheet):
Record Source:
Recordset Type: Dynaset
Fetch Defaults: Yes
Filter:
Filter on Load: Yes
Order By:
Order By On Load: Yes
Wait for Post Processing: No
Data Entry: No
Allow Additions: Yes
Allow Deletions: Yes
Allow Edits: Yes
Allow Filters: Yes
Record Locks: No Locks

I must also add, that I did try putting simply "SELECT * FROM Weight" in the Record Source and it still did not work.

[zmbd]

I was inquiring about the control on the form (i.e. a textbox or combobox, etc....) not the form itself.
All controls will have a "control source" as they are either bound, undbound, or calculated.

[TopGun1979]

Hi Guys

I actually have found a solution. It looks like Datasheets are fussy, but the following link helped me get to a solution: