Populate a datasheet and extraction of data from datasheet

[Savita]

Hi,

There is a requirement for
1)Populating a datasheet in a subform with a querystring which is dynamically built in VBA.This querystring is constructed based on the search criteria fields selected by the user in the main form.How can this be immplemented?

2_To click/double click a record in the datasheet,extract data and populate textfields,comboboxes with it which are in the main form.How is this achieved?Also,I can't find click events in a datasheet.

Pls let me know.
Thanks,
Savita

 

[Steve R.]

1. On your form create a combobox or comboboxes that have the criteria that you want.
2. I assume that your subform is in datasheet view.
3. For each combobox, use the CLICK property to set and activate the FILTER property for the subform. The subform should then display your desired results. Other issues, setting a default value for the combobox and resetting the comboboxes (turning off the filter).

See Allen Browne's website for how to refer to a subform.

The basic structure of using a filter:
me.filter = comboboxvalue
me.filteron =true

 

[Savita]

Thanks for the response.

1)Pls let me know a way to populate the subform(datasheet view) with a querystring passed from the main form.The query string has a join on 2 tables.

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

Savita

 

[Steve R.]

In ACCESS there are many ways to accomplish one task. In this case, I am proposing the use of a FILTER to display the selected records, not the use of SQL.

1. To populate the subform, set the record source property of the subform to the table or query that you want to use as the data source. However, I noticed that you plan to use two tables. These tables need to be joined through a query; so create a query based on these two tables and use that query name as the data source for your subform.

To populate the subform, based on a combobox on the main form you need to use the FILTER property on the subform.
For example:

Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """"
Me!Child14.Form.FilterOn = True

2. I don't understand "extract data and populate textfields,comboboxes"??? To "extract" data on the subform, you can use the double click event on a field in your subform. In the following example the field PROJNUM is used to open a from that displays all the information related to that record:

stDocName = "Projectqryfrm"
stLinkCriteria = "[project.projnum]= '" & Text01 & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The record source of that form also has to be your underlying query.

Now if you wish to use a table and/or query as the source data for making selections in a combobox you can use an ACCESS "wizard" when creating the combobox. Otherwise you can use SQL for the row source, for example"

SELECT projnum FROM projectqry;

 

[Savita]

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.

 

[Steve R.]

If you do not have a book on Access, I would recommend that you get one. Below is some partial code that builds a query when anyone of five comboboxes are clicked. See my first response above, this is just a more complex structure. You can replace me.filter with a variable name such as MySQLqry. This can be used to populate a from or combobox. Good luck.

Select Case Casenum1
        Case 1 'NO items selected
            Casenum2 = 1
        Case 2 '1 1 Item Selected
            Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """"
            Rem ---------------------------------------------------------------------------------------------
        Case 7 '2 Items Selected 4-5
            Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """ AND [ACTTYP]=""" & Me.Combo5 & """"
            REM --------------------------------------------------------------------------------------------------------------------
        Case 17 '3 items selected 4-5-6
            Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """ AND [ACTTYP]=""" & Me.Combo5 & """ AND [doctype]=""" & Me.Combo6 & """"
            Rem -------------------------------------------------------------------------------------------------------------------
        Case 26 '4 items selected 4-5-6-7
            Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """ AND [ACTTYP]=""" & Me.Combo5 & """ AND [doctype]=""" & Me.Combo6 & """ AND [gagency]=""" & Me.Combo7 & """"
        Case 31 '5 items selected 4-5-6-7-8
            Me!Child14.Form.Filter = "[projtyp] = """ & Me.Combo4 & """  AND [ACTTYP]=""" & Me.Combo5 & """ AND [doctype]=""" & Me.Combo6 & """ AND [gagency]=""" & Me.Combo7 & """ AND [county]=""" & Me.Combo8 & """"        
        Rem ERROR  SITUATION------------------------------------------
        Case Else 'Error Situation
            MSG1 = "There is an error in the select statement.  No matching case statement found.  Check the code."
            TITLE1 = "Select Statement Error"
            MsgBox MSG1, vbOKOnly, TITLE1
    End Select

 

[Savita]

Thanks.

Any solution for 2nd point?I need to double click/click a record in the datasheet and populate some textfields (which are on the main form) with the data in the record.

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.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

 

[Steve R.]

To me you are going about this wrong. I may not fully understand what you are trying to do. If I understand your question correctly, you want to display all the data associated with a specific record in a subform. The approach I am attempting to convey is opening a NEW form when you double-click on a record (field) in the subform. Of course you could have textboxes on your main form that "load" values when you click on the subform, which I am not recommending. Please see my prior post for opening a new form that displays your selected data.

2. I don't understand "extract data and populate textfields,comboboxes"??? To "extract" data on the subform, you can use the double click event on a field in your subform. In the following example the field PROJNUM is used to open a from that displays all the information related to that record:
Code:

stDocName = "Projectqryfrm"
stLinkCriteria = "[project.projnum]= '" & Text01 & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Please note, that the code above is placed on the on-double-click event in the PROJNUM field on you SUBFORM not your main form. Again, I would suggest getting an Access reference book.

 

[Savita]

Thanks.The issue of displaying data in datasheet view and reading data from a selected record got resolved.I have created a new form in datasheet layout and opening it from the main form.
Is there a way to embed this form in the main form instead of opening it in a separate window.I would like to display the second form within the first form itself.

 

[Steve R.]

That is what I have been advising. You can insert a subform into the mainform by using the Access "toolbox" when in design view of the main form. Also get an Access reference book.

If your main form is called MYFORMfrm, call your subfrom something like MYFORMsubfrm so that you can remember their association. After a while it becomes difficult to remember "linked" forms due to the number of forms you will be developing.

Again, the on-click event to open a NEW form to display your data will be on a field in your subfrom, NOT your mainform.

Tecnically, you should not need this since you will be working from your subform, but if you need to refer to your subform from the mainform, please see Allen Browne's website for how to refer to control in a subform.