Filtering with use of datasheet

[daveonion]

Hi, the query is... I have a datasheet and what i want to do is filter a form depending on the record clicked on in the datasheet, is this possible? So basically within the datasheet the user will have a choice of which records they would like to fully edit, they will then select the record the datasheet will close and the form underneath will have the record selected, any help would be greatly appreciated, heres hoping
Dave

 

[beetee]

Access has a cool capability where you can show a form in either datasheet or form view.

So, create your data entry form. Display it initially in datasheet view. Handle OnClick events by detecting if the form is in datasheet view or not. If it is in datasheet view, change it to form view.

You can add a button to the form to switch back to datasheet view.

To programatically toggle between form and datasheet view, use something like this:
DoCmd.RunCommand acCmdDatasheetView
or
DoCmd.RunCommand acCmdFormView

To tell if you are in form or datasheet view, use something like this:
Select Case Me.CurrentView
Case 1
Debug.Print = "Data Sheet"
Case 2
Debug.Print = "Form"
End Select

Thus, with a little programming you can solve your problem using a single form.

 

[daveonion]

Beetee, thats not what i want the form to do, what i need is to show a selection of filtered records on a subform (which will only contain 4 fields instead of the 20 that the other form consists off), the user will then choose a record and the form will show all fields to edit.

 

[beetee]

Sure, that's possible as well.

The rough idea is:
Embed a subform datasheet in your 'main' form. Use the OnCurrent event in the subform datasheet to detect when the user clicks into a new row. Grab some ID field from the datasheet, then set focus into the equivalent ID field in the form, and do a find record.

 

[saltecho]

Hi, I think this may be what you are looking for:


In the datasheet form put this code in the Declarations section:

Option Compare Database
Option Explicit

Private WithEvents m_form As Form

In the datasheet form put this code in the close button On_Click Event It supposes that the datasheet form has a subform in a control called SubSummary and the primary key is called PkId. It also assumes the main form is called frmMainForm:

Private Sub cmdExit_Click()
On Error Resume Next

Dim lngId As Long
lngId = Me!SubSummary.Form!PkId

If IsLoaded("frmMainForm&quot Then
m_form.Filter = "[PkIdID] = " & lngId
m_form.FilterOn = True
Forms!frmMainForm.Visible = True
End If
DoCmd.Close acForm, "frmOrdersSummary"
End Sub

This goes in the datasheet Open Event:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
'Set m_form to the main form object
Set m_form = Forms!frmMainForm
End Sub

A bit of explanation. Declaring m_form as a Private object WithEvents and defining it on the Open event of the datasheet makes the main form (provided it is loaded but invisible) synchronise and be controlled by the current form. You have access to all its events and can cause them to fire from within the code on the controlling form.

Selecting any record on your datasheet should immediately filter the underlying main form to the record selected when you close the datasheet.

Hope this helps.

Saltecho

 

[daveonion]

Saltecho, thanks for that, it sounds like what i'm looking for, however the code i'm using doesn't seem to work
surnameselect is the actual datasheet, car number the primary key, car the main form and form3 is the form that contains command2 and the parent form of surnameselect.
When i click the button it goes to isloaded and says "sub or function not defined".
Any ideas? i'm completetly stuck,

Private Sub Command2_Click()
On Error Resume Next
Dim lngId As Long
lngId = Me!surnameselect.Form![Car Number]
If IsLoaded("car&quot Then
m_form.Filter = "[carnumber] = " & lngId
m_form.FilterOn = True
Forms!Car.Visible = True
End If
DoCmd.Close acForm, "form3"
End Sub