Show specific items in combo box for each record in datasheet (1 Viewer)

vent · Jun 29, 2017

Hi everyone

So I have a datasheet in split form view with 6 columns visible and I have a search box that filters as you type based on data that's in all 6 columns (e.g. user types in "smith" and the query looks if this is present in at least one column and gives me back records where this is present in any column(s)). However one of the columns is set as a combo box with several acronyms as items listed. If the user types in e.g. FHP, the datasheet lists all the records that have this in that column. This is fine and all but I was wondering is there a way for me to have it set where if the user types in "FHP" the datasheet would filter but instead of where the combo box only gives me FHP + other acronyms specific to that record? Because how it's currently set, the drop down lists everything in there.

Also is there a way to make this datasheet un-editable? I went into the form property sheet and changed Allow Additions, Allow Deletions to No. But this affected my search bar since as it wouldn't allow me to type anything in. You guys are the best. Thank you!

CJ_London · Jun 29, 2017

first things first - sounds like your combobox is based on a multivalue field. Is this the case?

Also, please provide the sql to your form recordsource and any code relating to the form.

vent · Jun 29, 2017

By "multivalue field" I'm aware that this combo box isn't retrieved off another table or query. So whoever inputted these must have done it manually so there fore a Value List. And here is the code in the form

Code:

Option Compare Database
Option Explicit

Private Sub cmdReset_Click()
Me.txtSearch = ""
    Me.SrchText = ""
    Me.txtSearch.SetFocus
    DoCmd.Requery
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub SrchText_AfterUpdate()
Me.SrchText.Requery
End Sub

Private Sub txtSearch_Change()
'Create a string (text) variable
    Dim vSearchString As String
    vSearchString = txtSearch.Text
    SrchText.Value = vSearchString
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
        End If
    'Me.SearchResults = Me.SearchResults.ItemData(1)
    'Me.SearchResults.SetFocus
    DoCmd.Requery
    Me.txtSearch.SetFocus
    If Not IsNull(Len(Me.txtSearch)) Then
        Me.txtSearch.SelStart = Len(Me.txtSearch)
        End If
End Sub

as well as the SQL:

Code:

[B]SELECT[/B] AgencyINFO.[Organization Entity/Agency Name(Legal Name)], AgencyINFO.[Subsidary Name], AgencyINFO.[Expiry Date], AgencyINFO.[Insurance Expiry Date], AgencyINFO.[WSIB Employer Declaration Complete?], AgencyINFO.Address, AgencyINFO.City, AgencyINFO.Prov, AgencyINFO.[Postal Code], AgencyINFO.[Contact Name], AgencyINFO.Email, AgencyINFO.[Phone/Extension], AgencyINFO.[Program(s)]
[B]FROM [/B]AgencyINFO
[B]WHERE[/B] (((AgencyINFO.[Organization Entity/Agency Name(Legal Name)]) Like "*" & [Forms]![splitAgencySearch]![SrchText] & "*"))
[B]OR[/B] AgencyINFO.[Subsidary Name]LIKE "*" &  Forms!splitAgencySearch!SrchText & "*"
[B]OR[/B] AgencyINFO.[Program(s)]LIKE "*" &  Forms!splitAgencySearch!SrchText & "*"
[B]OR[/B] AgencyINFO.[WSIB Employer Declaration Complete?]LIKE "*" &  Forms!splitAgencySearch!SrchText & "*"
[B]OR[/B] AgencyINFO.[Expiry Date]LIKE "*" &  Forms!splitAgencySearch!SrchText & "*"
[B]OR[/B] AgencyINFO.[Insurance Expiry Date]LIKE "*" &  Forms!splitAgencySearch!SrchText & "*";

CJ_London · Jun 29, 2017

if it is a value list, then the values in the list will be the same for each record - and only one value can be selected for each record so your search query should work.

So I don't understand what you mean by 'is there a way for me to have it set where if the user types in "FHP" the datasheet would filter but instead of where the combo box only gives me FHP + other acronyms specific to that record?'

what is the rowsource to the valuelist and which field does it apply to? And if it is a multi column value list, how many columns? which is the bound column and which columns are hidden?

It will also help if you can provide some examples of what you mean

vent · Jun 29, 2017

Hi sorry I forgot to mention that in the split form view, the datasheet is on the bottom and text boxes bound to their respective field are on top (e.g. Agency, Email, Contact, and Programs, etc.). Programs is the field set as a combo box and all it's items are acronyms (i.e. FHP). Whenever the user types something in, clicks on a record, the text boxes autopopulate, however the combo box lists ALL the programs. Is it possible to set it where when the user clicks on a record in the datasheet, the combo box only gives the items for that specific record clicked. Because some records only have FHP, while others have it plus more. Is this clearer?

CJ_London · Jun 29, 2017

no, afraid not. Please provide the information I asked for.

Note that split forms have limitations so it may not be even possible to do what you want (which I'm still not clear on)

vent · Jul 4, 2017

CJ_London said:
no, afraid not. Please provide the information I asked for.

Note that split forms have limitations so it may not be even possible to do what you want (which I'm still not clear on)

Actually, it's ok. I got a previous mentor to look at it and they gave me some valuable insight. Thanks though.

Show specific items in combo box for each record in datasheet (1 Viewer)

vent

Registered User.

CJ_London

Super Moderator

vent

Registered User.

CJ_London

Super Moderator

vent

Registered User.

CJ_London

Super Moderator

vent

Registered User.

Similar threads

Users who are viewing this thread