Filtering datasheet by combobox selection (Revisited) (1 Viewer)

vent

Registered User.
Local time
Today, 06:08
Joined
May 5, 2017
Messages
160
Hi everyone,

I know this is similar to a post I've created before however things are slightly different now. Basically I have a form in splitform view and all i have right now is a search bar that filters based on what the user types in. However I would like to add a combo box with 2 column:



(I would post a screenshot but for some reason I keep getting asked to put the image's URL, which it doesn't have because it's a screenshot)

Codes on left and description on right. The columns are based off a separate table, however the codes appear on the datasheet in one of the columns. So if a user selects for example "1323" the datasheet would look for this value in that column and filter it based on the selection. If anyone has any insight on how to do this, that would be much appreciated.


****UPDATE****

So I've been playing around with this and I've included the combo box and here is the VBA for it:

Code:
Private Sub Combo565_AfterUpdate()

      'filter all values where cboFilter value is in Complete field string
       Me.Filter = "InStr([Program(s)],""" & Me.Combo565.Value & """)"
       ' Debug.Print Me.Filter
        Me.FilterOn = True

End Sub

The datasheet does filter, but very strangely. For example if I select '1323' in the combo box, the datasheet gives me some results, but none contain the value '1323'. Does anyone have any idea why this might be?
 
Last edited:

Ranman256

Well-known member
Local time
Today, 06:08
Joined
Apr 9, 2015
Messages
4,339
in the combo afterupdate event:

Code:
sub cboBox_afterupdate()

If IsNull(cboBox) Then
  Me.FilterOn = False
Else
  Me.Filter = "[PartID]=" & cboBox 
  Me.FilterOn = True
End If
end sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:08
Joined
May 7, 2009
Messages
19,175
add code to your combobox AfterUpdate Event:

Code:
Private Sub yourCombo_AfterUpdate()
	Dim frm As Form
	Set frm = Forms!YourSplitFormName
	frm.Filter = "[field in your form]=" & Me.yourCombo
	frm.FilterOn=True
End Sub
 

vent

Registered User.
Local time
Today, 06:08
Joined
May 5, 2017
Messages
160
Hi guys I tried both your examples and I got the same error, which was

Runtime error 3709 The Search key was not found in any record

Code:
Private Sub Combo565_AfterUpdate()

   Dim frm As Form
    Set frm = Forms!frmNewAgencySearch
    frm.Filter = "[Program(s)]=" & Me.Combo565
   [I][B]frm.FilterOn = True[/B][/I]
End Sub

and the last part of the code being highligted
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:08
Joined
May 7, 2009
Messages
19,175
what does [Program(s)] field hold on your split form? is it text or number.

if text use:

frm.Filter="[Program(s)]='" & Me.Combo565.Column(1) & "'"
 

irsmalik

Registered User.
Local time
Today, 14:08
Joined
Jan 28, 2015
Messages
88
Hello friends.
I am making a form to Select Some Records and then send it to Print.

I want to use a Form in DataSheet View. Using ComboBox for Selecting a Record from thousands of records.

Upon selecting from ComboBox other 5 fields must display. I want to continue it till the required quantity of Records select.

Now this form works for Only 1 Record and then tab stop. and not going
further. The logic is like this :

Select Received.DrawingNo
, Received.Rev
, Received.Title
, Received.Type
, Received.Purpose
From Received
Order BY Received.DrawingNo;


Why Tap stop and not going further. Where I am making mistake. Is my logic, to use DataSheet View form is correct. I will be thankful if some open the attachment and can help.

Thanks
irsmalik
 

Attachments

  • DrawingIssuance.zip
    39.4 KB · Views: 149

Users who are viewing this thread

Top Bottom