By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Populate a datasheet with querystring and extract data from datasheet

P: 6
I am trying to pass a query string from the main form to subform to display records in datasheet view.I tried using the following code in the form_load() event to populate the subform(Child0)

Me.Child0.Form.Recordsource=strSql (strSql is a query string which has a join on 2 tables)

But I got the following error,"The expression you entered refers to an object that is closed or does not exist".Pls let me know the solution for this.

2)How can I extract data from the subform in datasheet view on double clicking a record?I want to populate textfields and comboboxes with this data.

Thanks,
Savita
Sep 1 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
I am trying to pass a query string from the main form to subform to display records in datasheet view.I tried using the following code in the form_load() event to populate the subform(Child0)

Me.Child0.Form.Recordsource=strSql (strSql is a query string which has a join on 2 tables)

But I got the following error,"The expression you entered refers to an object that is closed or does not exist".Pls let me know the solution for this.

2)How can I extract data from the subform in datasheet view on double clicking a record?I want to populate textfields and comboboxes with this data.

Thanks,
Savita
Hi, Savita.

1) I can guess your main form is in Datasheet view. This case Access does not load subform when main form is being loaded, any attempts to refer to subform properties/methods will fail. Subform will be loaded as soon as it will be expanded.

2) In datasheet view Form_DblClick event will be fired when you doubleclick on record selector or anywhere on form empty space. If you expect the same on doubleclick behaviour for the whole record then you should handle DblClick event for all form controls. When you handle event, controls are populated with current record values, no difference to single form view.

Anyway your question is unclear. Plz clarify what do you actually want to do.
Sep 1 '07 #2

P: 6
Thanks.Let me make things more clear.

The issue is that I am enhancing existing code .There is a form with some textfields and comboboxes.I select those values,click a search button to display records in the listbox.There is a function which builds a SQL dynamically and populates the listbox.

I am trying to replace the listbox with datasheet.On double-clicking each record in listbox , a different set of textfields and combo boxes get populated with the data in the clicked record.I can use these fields to edit data and save it in the table.

I need to implement the same functionality by replacing listbox with datasheet.Pls let me know how this can be done.

Savita
Sep 2 '07 #3

P: 6
I tried associating DblClick event with the subform(child0) with the foll. code.But the event Chil0_DblClick gets fired just once when I double click on the datasheet for the first time. If I double click again it doesn't get fired.Basically,in the Child0_DblClick event,I want to extract the data from each column in the datasheet view for the selected row when it is double clicked and Display this data in textfields.Pls help.

Private Sub Form_Load()
With Me.Child0
If .Form.OnDblClick = "" Then
.Form.OnDblClick = Child0_DblClick

End If
End With
End Sub



Private Function Child0_DblClick() As String
Dim i As Integer
MsgBox "dblclk"
'i = Me.Child0.Form.CurrentRecord
Child0_DblClick = ""
End Function
Sep 2 '07 #4

FishVal
Expert 2.5K+
P: 2,653
I tried associating DblClick event with the subform(child0) with the foll. code.But the event Chil0_DblClick gets fired just once when I double click on the datasheet for the first time. If I double click again it doesn't get fired.Basically,in the Child0_DblClick event,I want to extract the data from each column in the datasheet view for the selected row when it is double clicked and Display this data in textfields.Pls help.

Private Sub Form_Load()
With Me.Child0
If .Form.OnDblClick = "" Then
.Form.OnDblClick = Child0_DblClick

End If
End With
End Sub



Private Function Child0_DblClick() As String
Dim i As Integer
MsgBox "dblclk"
'i = Me.Child0.Form.CurrentRecord
Child0_DblClick = ""
End Function
Hi, Savita.

Actually you have a completely wrong approach.
I guess all the code above is in the main form module.
So what is happenning:
.Form.OnDblClick = Child0_DblClick
fires Child0_DblClick routine once on the form load and set main form OnDblClick property to what is being returned, this case "". :)

Additionally according to subform name I can guess that SubformControl.SourceObject is set to table/query. This doesn't allow to handle subform events.

I suggest you the following:
  • create a subform as a separate form and embed it to the main form
  • set the subform OnDblClick property to [Event Procedure] and place event handler code to the subform module

Plz let me know whether you've succeded in this before we will proceed with coding.
Sep 2 '07 #5

P: 6
Thanks.The issue got resolved.I am opening a new form in datasheet layout from the main form now which opens the new form in a new window.Instead,is there a way to embed the second form in the main form itself?
Sep 2 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Thanks.The issue got resolved.I am opening a new form in datasheet layout from the main form now which opens the new form in a new window.Instead,is there a way to embed the second form in the main form itself?
Sure. In form design view put Subform control on the main form and when the wizard runs choose an appropriate form to be DataSource for the Subform control.
Sep 2 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.